Issue with Dynamic Variables in SQL Queries After Recent Update

💬 App Building
1

Hello Retool Community!

I’m encountering an issue while running SQL queries that involve two dynamically set variables. When I include multiple {{ form.data.input }} references in my query, I receive an error indicating that I’m hitting a bind variable issue (specifically, “bind variable :1 not set”).

This problem appeared after a recent Retool update, as similar queries worked fine in the past. Below is an example of the problematic query:

SELECT DISTINCT
  a.*
FROM some_table a
  LEFT JOIN another_table b ON b.id = a.id
WHERE b.branch_code IN ({{ branchCodes.value }})
  AND a.status = 'active'
  AND NOT EXISTS (
    SELECT 1
    FROM campaign_management_table c
    WHERE c.some_id = a.id
    {{ campaignId ? `AND c.campaign_id = ${campaignId}` : '' }} -- Only include this if campaignId exists
      AND c.is_active = true
  );

In the debug console, I see the following error when executing this query:

compilation error: error line 5 at position 27 Bind variable :1 not set.

Here's the output from the debug console:

branchCodes.value: (9) ["A", "B", "J", "L", "L", "M", "S1", "S", "S3"]
form2.data.select2: 2
additionalScope: {}
triggeredById: ""
environment: "production"

Interestingly, when I modify the query to only use one dynamic variable, it runs successfully:

SELECT DISTINCT
  a.*
FROM some_table a
  LEFT JOIN another_table b ON b.id = a.id
WHERE b.branch_code IN ({{ branchCodes.value }})
  AND a.status = 'active'
  AND NOT EXISTS (
    SELECT 1
    FROM campaign_management_table c
    WHERE c.some_id = a.id
      AND c.is_active = true
  );

The debug output for this successful query is:

branchCodes.value: (9) ["A", "B", "J", "L", "L", "M", "S1", "S", "S3"]
additionalScope: {}
triggeredById: ""
environment: "production"

I’ve tried several workarounds, but it appears that using multiple bound variables with {{ }} consistently leads to this error. Has anyone else experienced this issue, or can you suggest a potential fix?

Thank you for your assistance!

2

Update. I tried something like this

WHERE 
  ARRAY_CONTAINS((SELECT VALUE FROM TABLE(FLATTEN(INPUT => {{ headerBranchCodes.value }}))), cc.BRANCH_CODE)
  AND ARRAY_CONTAINS((SELECT VALUE FROM TABLE(FLATTEN(INPUT => {{ statusFilter.value }}))), fm.STATUS);

This still causes a bind issue. If I take out the second one it runs fine. So again still just having one works (either of them) but both break. Not sure whats up with this.

3

Update! I found this cheat sheet and went to the Snowflake section. Two IN statements cant bind data from arrays (at least for this query Im running). The cheat sheet showed I can in fact do this but in a different way.

The fix:

WHERE 
  ARRAY_CONTAINS(cc.BRANCH_CODE::variant, SPLIT( {{headerBranchCodes.value.join()}}, ',') )
  AND 
    ARRAY_CONTAINS(to_varchar(fm.STATUS)::variant, SPLIT( {{statusFilter.value.join()}}, ',') )

Not entirely sure why the bind error was happening for this specific query since I have a few value IN ({{ item.value }}) being dynamically inserted into my queries elsewhere but this worked for my current use case.

3 Likes