Multi-Select fails with redshift queries prepared statements

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

This was an interesting method to try but it still fails.
This works

company = any( string_to_array('Tricon American Homes,Cinch Home Services',',' ) )

This does not

company = any( string_to_array( {{comp_company_selections.value.join(',')}} , ',')    ) 

If I return the working string above as a transform
multi_transform

return 'Tricon American Homes,Cinch Home Services'

passed to this in the where clause

company = any( string_to_array( {{multi_transform.value}} , ',')   ) 

It does not work

This also doesn’t work

company = any( 
{{ comp_company_selections.value.join(',') }}
             ) 

This works but only if a single selection is made because its passing a string

company in( 
{{ comp_company_selections.value.join(',') }}
             ) 

This doesn’t work

company in( 
string_to_array({{ comp_company_selections.value.join(',') }},',')
             ) 

If I have the transform return an array like this

return ['Tricon American Homes','Cinch Home Services']

this does not work

company in( {{multi_transform.value}} ) 

This also does not work

company = any ( {{multi_transform.value}} )

removing the () doesn’t help

Gotcha. Seems like you were able to get it working!

Still not working quite right but my index specification work around still works so no worries.

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

Hey @jprovance3x3!

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
  • ![](upload://gZycrGHX9NExm9G3OFJqT7mk2yR.png)
  • 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

Sorry! This is the image:

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.

Great!! Thanks for the information and the warning

Hi :slight_smile: 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

Hey @annafonte!

Do any of the suggestions in my post from August 21st here work? Let me know if not and I'd be happy to dig back in! :slight_smile:

Using the Redshift function CHARINDEX worked for me.

CHARINDEX(<SQL Column>,{{chart4.selectedPoints.map(d=>d.x).join()}}) > 0

1 Like

Hi @Chris-Thompson :slight_smile: 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.

1 Like

Here are a few examples that hopefully will work for everyone!

What I tried to do is follow this doc which says we can turn the list into a scalar array and then use the ANY() function to query the items.

Example for Strings:

select * from products where name = ANY( {{ '{' + multiselect2.value + '}' }})

Example for Integers:

select * from products where id = ANY( {{ '{' + multiselect1.value + '}' }}::integer[])

1 Like