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