I am having a similar problem. I think that Redshift receives a single string of values separated by commas (as opposed to a list of strings). The example from the Retool SQL FAQ gives the following Retool query.
select *
from users
where id IN ({{ [1, 2, 3].join() }})
I have tested a similar query in Retool and the error was "invalid input syntax for integer: "1,2,3"" when the column is INTEGER type. If you ran the following query in a database tool, you would produce the same error.
select *
from users
where id IN ('1,2,3')
A query that passes a list of strings does not throw an error and returns the correct rows.
select *
from users
where id IN ('1','2','3')
For VARCHAR type columns, the query would not error but would not return the correct rows. For example, the following query would only return a row with name a,b,c but would not return the row with name a.
select *
from users
where name IN ({{ ['a','b','c'].join() }})
In an earlier forum post, this problem was solved for SQL Server by using the STRING_SPLIT function. I have not found an equivalent function in Redshift. There is a function called SPLIT_PART in Redshift but it requires a positional argument and only returns the element of the list at that point. In the original post in this thread, the current work around requires the number of selections to be hard coded in before the selections are made and then each element is accessed directly in the Retool query editor. I found a work around that does not require that the exact number of selections is known but does hard code a maximum number of selections that can be chosen before the rest are (silently) ignored.
with numbers AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select *
from users
inner join (
select
SPLIT_PART({{multi_select.value.join()}}, ',', numbers.n) as id
from numbers
where numbers.n <= REGEXP_COUNT({{multi_select.value.join()}}, ',') + 1
) as selection
on selection.id = users.id
This approach of joining to a table of numbers appears in a few places.
Other things I tried that did not work:
In the SQL database tool, the following query worked as expected.
select *
from users
where users.id = any(string_to_array('1,2,3',','))
When I tried it in Retool as the following, it returned an error, "Specified types or functions (one per INFO message) not supported on Redshift tables".
select *
from users
where users.id = any(string_to_array({{multi_select.value.join()}},','))
I tried casting the selection to VARCHAR in the query, CAST({{multi_select.value.join()}} AS VARCHAR), but that returned the same error. I also tried putting single quotes around the selection, '{{brand_multi_select.value.join()}}', but that returned an error, "bind message supplies 1 parameters, but prepared statement "" requires 0".
I also tried using both JSON_PARSE and split_to_array with the unnesting features in Redshift. I could not find a way to parse and iterate over the results in the same query. Using where users.id = any(split_to_array('1,2,3',',')) and where users.id = any(json_parse('[1,2,3]')) return an error, "Invalid operation: op ANY/ALL (array) requires array on right side". I was able to parse the results to a temporary table and then iterate but this requires two separate queries and the permission to create temporary tables. This may be possible using two Retool queries where the first creates the table and the second is triggered by the first but I don't know if the temporary table would persist after the first query. Regardless, allowing a user to create arbitrary temporary tables from Retool seems like (possibly accidental) denial-of-service waiting to happen.
Another thing I tried, which is similar to the work around I am currently using but allows for an arbitrary number of selections, was the generate_series function in Redshift. This is apparently unsupported in Redshift and joining the series another table returns an error, "Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables."
select * from users
inner join (
select SPLIT_PART('1,2,3', ',', generate_series) as id from generate_series(1,3,1)
) as selection on selection.id = users.id
One thing I tried in the Retool console was joining the selections as a bunch of OR statements. This sent one string of WHERE (id = '1 OR id = 2 OR id = 3'), which does not return the correct value (and returns an error if the id column is INTEGER type).
select *
from users
where (id = ({{ [1, 2, 3].join(' OR id =') }}))
Let me know if there is anything else I can do to help solve this. Thanks