I'm using MSSQL and the Query Library. This query gives me results:
SELECT * FROM table
WHERE id IN ('1234', '5678')
When I introduce a variable and specify default values in the UI, none of these work. I just get an empty result set back! 
ids = ['1234', '5678']
-- FAIL
SELECT * FROM table
WHERE id IN ({{ ids }})
-- FAIL
SELECT * FROM table
WHERE id IN ({{ ids.map(id => `'${id}'`) }})
-- FAIL
SELECT * FROM table
WHERE id IN ({{ ids.map(id => `'${id}'`).join(',') }})
-- FAIL
SELECT * FROM table
WHERE id IN (SELECT value FROM STRING_SPLIT({{ ids.join(',')}}, ','))
-- FAIL
SELECT * FROM table
WHERE id IN (SELECT value FROM STRING_SPLIT({{ ids.map(id => `'${id}'`).join(',')}}, ','))
I'm pulling my hair out over here. Is it not possible to use WHERE IN (...dynamic data...) in Retool?
Hi @alanlindsay! It depends on the language you're using. Since you mentioned you're using MSSQL, the syntax should be something like this:
SELECT * FROM users WHERE id IN ( SELECT convert(int, value) FROM string_split({{ [1, 2, 3] }}, ',') )
https://docs.retool.com/docs/sql-cheatsheet#use-arrays-in-queries
Let me know if that works for you!
One of my original queries works now. I have seen this before, where Retool just doesn't work, almost as if it is using some cached version behind the scenes instead of the most recent edit, and then it correctly works the next day. So, this original query works (as it always should have):
-- FAILED previously, WORKS today
SELECT * FROM table
WHERE id IN (SELECT value FROM STRING_SPLIT({{ ids.join(',') }}, ','))
Along with the variation syntax you suggest:
SELECT * FROM table
WHERE id IN (SELECT value FROM STRING_SPLIT({{ ids }}, ','))
I suppose this issue is "solved" but there appears to be another underlying issue where Retool uses old versions of query for a certain period of time. Since there are zero debug tools on the Query Library it is really difficult to tell.
Definitely very weird and you're not the first user to report something like this. It does seem like some sort of cacheing issue. Please do let us know if it keeps happening!
Out of curiosity, does this happen with regular queries or mainly just Query Library queries?