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?
I have also tried using:
where lv.id = ANY({{state1.value.split(",")}})
and
where lv.id = ANY({{state1.value}})
but both of those give me the error:
SQL compilation error: Invalid query block: :.
Hi Mark, thanks for the suggestion! I tried that and the query does run but it has the same outcome as where id in (state1.value) where it only pulls the first value in state even though the query string shows all of them. When you tested this, did you try with a temporary state variable containing an array? I don't think it's a snowflake issue, something is happening with temporary state being passed to the query.
Ah, I did not try using a temporary state variable, just with an interpolated array. I’ll try with the temp state now to see if I can reproduce the behavior you’re describing
Why I'm using state in the first place is because I want to take a static list of options and split it into two lists based on user input. So I'm getting a list of values from one query, setting that in temp state, showing current state in a table, and when a row is clicked on, it moves the id of that row to another temp state variable, that is displayed in the table beside it (and updates state1 to no longer have that value in it). This all seems to work fine except for the fact that I can't get more than one row to show up in my state tables.
Maybe it's not just a state thing, because when I try to use the original query data {{origVersion.data.ID}} (which is exactly what I'm setting state to be) it also only shows the first value. Weirdly I'm very confident that using {{origVersion.data.ID}} worked perfectly fine a week ago
I also made sure this wasn't a case of the query running before state is fully set, the query using state only runs after the JS query setting state is finished, and the query is set to only run when manually triggered, so that shouldn't be the issue either.
For anyone who may come across this thread in the future, we identified the issue as being caused by a bug in the conversion of queries to prepared statements. By disabling this conversion on the resource, we were able to get the queries to run. I have filed a bug report on this issue, and will update this thread when a fix had been implemented!
We will post here when there is an update though!