"in" statement in snowflake only pulling first entry?

I'm using temporary state as an input for a snowflake query, the state value is an array of numbers pulled by a different query. My query looks like this:

select l.id, p.name 
from table_l l
join table_p on p.id = l.p_id
where l.id in ({{state1.value}})

when I hover over the query it shows me the supposedly correct string version of the query, something like:

    select l.id, p.name 
    from table_l l
    join table_p on p.id = l.p_id
    where l.id in (1,2,3,4)

which I expect to return 4 values, but it is only returning whatever number is first (so in this case, 1). If I replace the {{state1.value}} with the actual numbers, it pulls in everything I would expect, if I try to treat state1.value as an array, it throws an error stating that the value isn't an array. How do I use the state value correctly to pull in all of the values I want instead of just the first one?

+1 on this one

The prepared statement seems alright but isn't doing the correct SQL query in the back end.

The same applies with the LIKE ALL clause.

image

image

image

The other 2 IDs do exist in the database

image

Hi @philippe-boyd-maxa, this has been identified as bug. you can follow their updates on this in this thread.

You probably want to use ARRAY_CONTAINS() instead of in. Check this tips and tricks thread on how to navigate with IN or similar functions/operators.

Regards

Hi @jocen, thanks for the heads up.

However ARRAY_CONTAINS will only check for exact match. What if I want to get the dimensions whose name contains a certain substrings? This is the exact use case for the LIKE ALL clause with some % for instance:

SELECT * from DIM_DIMENSION where DIMENSION_NAME like all ('%COMPAGNY_1%','%PROVIDER_2%');

Hi @philippe-boyd-maxa! I'll go ahead and make sure you're notified here as well when there's an update on that bug. In the meantime, like @mark mentioned in the linked thread, a potential workaround is to select the option to disable converting your queries to prepared statements.

As you may have read elsewhere - by default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.

You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, we often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

Hi @philippe-boyd-maxa!

Just want to follow up here, in the last couple of weeks we pushed an update that lets you use interpolation for arrays with prepared statements on so something like this should work properly now:

SELECT * from DIM_DIMENSION where DIMENSION_NAME like all ({{['%COMPAGNY_1%','%PROVIDER_2%']}});

:grinning_face_with_smiling_eyes:

1 Like

Hi @Kabirdas,

yes I got a heads-up from @kent not too long ago saying it would come to an on premise version (2.80) on November 24th. We will test it out then :slight_smile: