Bug Snowflake - Prepared statement with several inputs does not work with multiselect

💬 App Building
,
1

There is a bug we face with prepared statement in Retool when we apply multiple inputs as filters for SQL queries for Snowflake.
Here are the steps to reproduce it:

  1. In your Snowflake DB, create a table like this:
CREATE OR REPLACE TABLE SANDBOX.TEST_RETOOL (
    COL1 VARCHAR,
    COL2 VARCHAR
);

INSERT INTO SANDBOX.TEST_RETOOL VALUES ('a', 'A'), ('b', 'B'), ('c', 'C');
  1. In Retool, create an app with 2 components, one Select and one Multiselect:

image
image933×81 4.83 KB

  1. Finally, create SQL queries and see the errors:
Name Query Result
Filter COL1 
SELECT COL1, COL2
FROM SANDBOX.TEST_RETOOL
WHERE COL1 = {{ select1.value }}
Works fine
Filter COL2 
SELECT COL1, COL2
FROM SANDBOX.TEST_RETOOL
WHERE COL2 IN ( {{ multiselect2.value }} )
Works fine
Filter COL1 and COL2 
SELECT COL1, COL2
FROM SANDBOX.TEST_RETOOL
WHERE COL1 = {{ select1.value }}
  AND COL2 IN ( {{ multiselect2.value }} )

Error:

SQL compilation error:
Unsupported data type 'VARIANT'.
Filter COL2 and COL1 (swapped) 
SELECT COL1, COL2
FROM SANDBOX.TEST_RETOOL
WHERE COL2 IN ( {{ multiselect2.value }} )
  AND COL1 = {{ select1.value }}

Error:

SQL compilation error: error line 3 at position 15
Bind variable :1 not set

The error "Bind variable :1 not set" looks like an error coming from a misconfigured prepared statement.

Could you please confirm that you can reproduce the issue ?

Retool version 2.96.2

2 Likes
2

Hello, @aturiot thanks for such a well-written post!
The array structure for snowflake syntax is a little unusual so we have created a Query Snowflake doc that gives a tip on how to achieve snowflake's array contains function in Retool.
For your example, you'll want to use

SELECT COL1, COL2
FROM SANDBOX.TEST_RETOOL
WHERE COL1 = {{ select1.value }}
AND ARRAY_CONTAINS(COL2::variant, SPLIT({{multiselect2.value.join( )}}, ',') )

Let me know if this doesn't work!

2 Likes
3

@Amanda Thank you Amanda, I confirm that the suggestion works as expected :+1:

It could be great to put forward this link in case of SQL compilation error like here, because the current error is cryptic and hard to debug.