The following behavior seems like a possible bug based on this documentation https://docs.retool.com/docs/working-with-select-components.
I have a multi Select with the values generated by a query {{company_list.data.company}}
I have a query running off this multiselect values
select *
from orders
where company = any({{ comp_company_selections.value }})
The query fails to run and the UI simply states "Assert"
From this image you can see the values are being read and query syntax appears correct. The backend db is redshift.
If I manualy run the query it runs fine
select * from orders where company = any
(
'{Sibi,A.P.E.S}'
);
If I run the following to see whats sent to the db
select query, trim(querytxt) as sqlquery
from stl_query
order by query desc
the query I manually populate the values with hits the db but the ones from the multi-select don't appear to be leaving retool and reaching the db.
My current work around is to list indexes in an in statement
select *
from orders
where company in(
{{ comp_company_selections.value[0] }},
{{ comp_company_selections.value[1] }},
{{ comp_company_selections.value[2] }},
{{ comp_company_selections.value[3] }}
)
This works but limits count to however many items i put in array manually.
Hey @tye_sibi and sorry for the delay! I think your issue is that you’re not destructuring the array, and Redshift can’t handle the []. Can you try using {{ comp_company_selections.value.join(',') }} or something along those lines? The difference between what Retool is returning and what you ran manually against Redshift seems to be array brackets
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
The primary reason this causes so much trouble is because of prepared statements, SQL languages which need parenthesized arrays are challenging to get into the right format. The options are:
Do substring matching
Split a string input into an array and cast all values to the correct type (what this post is trying to do)
Disable prepared statements and just turn your array into a string that would work normally in the db console
Hope this helps, do you think this could work for your use case here?
Was there supposed to be a picture after the substring matching bullet point? I see a link but no picture. Also, do you have any more information about disabling prepared statements? Thanks
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, I 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 Are there any updates regarding this issue? I just tried almost all the solutions from @tye_sibi's first comment and it is still not working..
Thanks in advance
Hi @Chris-Thompson Unfortunately, none of the options from the Aug post worked for me. I'll now try with the CHARINDEX solution that @trox is suggesting below --> Super thanks for the tip btw.
Are there any updates regarding this issue? I just tried almost all the solutions from 
Unfortunately, none of the options from the Aug post worked for me. I'll now try with the CHARINDEX solution that 
