Query works in staging environment but fails on switching to production

💬 Workflows
1

  1. My goal: Need help to execute a query in my workflow to get results

  2. Issue: I have a query to get some results from retool database. The query is as below (column name changed):

SELECT col1, col2
FROM mapping_table
WHERE col1 = ANY({{some_ids.data.test_results}})

The value of test_results is:

{"data":{"test_results":["lp-2493983235","lb-8109721066","lb-9927104553","lb-7881503220","lp-531786892"],"package_results":}}

This works fine if I run on staging environment but fails when I switch the environment to production environment, with this error:

{"data":null,"error":{"error":true,"message":"syntax error at or near ","","position":115,"isRetoolSystemError":false,"queryExecutionMetadata":{"estimatedResponseSizeBytes":99,"resourceTimeTakenMs":38,"isPreview":false,"resourceType":"retoolDb","lastReceivedFromResourceAt":1754554245377}}}

  1. Steps I've taken to troubleshoot:

I have checked the schema, they are identical on my staging and production environment.

  1. Additional info: Using retool paid plan
3

Hey @archit - thanks for reaching out. The divergent behavior that you're seeing here is definitely odd, as I would expect the query to either fail or succeed in both environments. If you check Retool Database's settings, are prepared statements still enabled?

image
image1972×714 47.7 KB

4

Have you had a chance to review this, @archit?