Subquery in Snowflake select clause not evaluating row by row?

Question

I'm trying to write a very straightforward query: I want to pull all accounts in a snowflake table, and flag accounts (True/False) that can be linked to a record in the opportunity table with "Startup" in the type field.

Here is a query that I thought would return what I am looking for:

SELECT acc.id , acc.id IN ( SELECT DISTINCT(opp.accountid) FROM "OPPORTUNITY" opp WHERE opp.type LIKE '%Startup%' ) AS startup FROM "ACCOUNT" acc

The account table has ~100,000 distinct records and the subquery returns a list of only ~11,000 distinct account ids, yet the field aliased as 'startup' contains TRUE for each account. I've tried diagnosing why this returns all True with no luck. I also tried writing this same query using EXISTS with a correlated subquery and got the same results.

I was able to build a working query by essentially left joining the sub query after the FROM clause, but I would love to understand why the query above fails to evaluate correctly for each row.

Any help would be appreciated. Thanks!

Stuart Ozer · Accepted Answer · 2019-09-24 00:14:06Z

I cannot reproduce the behavior you see:

create or replace table ACCOUNT(id int); insert into ACCOUNT values (1), (2), (3); create or replace table OPPORTUNITY(accountid int, type varchar); insert into OPPORTUNITY values (1, 'Startup'), (2, 'Startup');

Running your query above then produces the result:

Row ID STARTUP 1 1 TRUE 2 2 TRUE 3 3 FALSE

Thanks for responding! Weird right? I've used almost identical logic in other queries and never had an issue before. I've had several coworkers take a look at this issue and it has stumped everyone. Any other ideas?

Rich Murnane · Accepted Answer · 2019-09-26 20:22:54Z

it'd be interesting for us to see the results of these two queries:

SELECT COUNT(DISTINCT accountid) FROM opportunity WHERE type LIKE '%Startup%'; SELECT COUNT(DISTINCT accountid) FROM opportunity WHERE type iLike '%Startup%';

Something like the following might be your ultimate goal I think:

SELECT acc.id, startup.accountid FROM ACCOUNT acc LEFT OUTER JOIN ( SELECT COUNT(DISTINCT accountid) as accountid FROM opportunity WHERE type iLike '%Startup%' AND accountid IS NOT NULL) startup ON acc.id = startup.accountid;

**** begin additional info ****

After reading the comments, I decided to play with this a little more, sure enough my test is working, perhaps you have nulls or similar messing up your data or results? I'm not sure but this is working for me!

create table account (id varchar); insert into account values ('A'), ('B'),('C'),('D'); create table opportunity (accountid varchar, type varchar); insert into opportunity values ('D', 'New Subscription'), ('X', 'Stuff'), ('Y', 'Stuff'), ('Z', 'Stuff'); select account.id, account.id in (select distinct accountid FROM opportunity WHERE type like '%Subscr%') as startup FROM account; ID STARTUP A FALSE B FALSE C FALSE D TRUE

Thanks for the response! The two queries above both returned the same amount (~11k). And thanks for the second query with the left join. It does work as intended (and I'll use that as a solution for now), but I'd still love to figure out what might be going wrong in the original query - any other ideas?
Can you share a small set (2-3 rows) of example values from OPPORTUNITY and ACCOUNT that deliver "TRUE" for the IN test where they should deliver false? I would like to try to create a tiny reproduceable set reflecting actual values from the tables...
Hey Stuart, definitely. Here's a small subset of the data I'm querying: ACCOUNT {id: 'A'} {id: 'B'} {id: 'C'} {id: 'D'} OPPORTUNITY {accountid: 'A', type: 'New Subscription'} {accountid: 'B', type: 'New Subscription - Startup'} {accountid: 'D', type: 'New Subscription'} The query in my original post returns TRUE for all accounts, but I'm trying to only flag Account 'B' as TRUE
I get the same bad result when the ACCOUNT and OPPORTUNITY tables use VARIANT type, but not when using fully-structured columns -- so a bug I'll report.
Interesting, all values (acc.id, opp.account, opp.type) in the tables are stored as VARCHARs. You're saying that you had success with fully structured columns?

Collectives™ on Stack Overflow

Subquery in Snowflake select clause not evaluating row by row?

2 Answers 2

1 Comment

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Related