SQL Execution Differs Between Staging and Production

We are very confused while developing our app on this one.

We pass through some filters from our application into a relatively simple SQL query. We built this large feature change for our application on staging and when we released to production, everything broke.

In our staging environment, our where conditions which are being supplemented by the additional scope look like this:

            AND credit_type = ANY ({{credit_type_filter}})
            AND estimated_conversion_date <= {{estimated_conversion_date_filter}}
            AND project_id = ANY ({{project_id_filter}})

This executes no problem. When executing the exact same script in production (same db schema), this modification needs to be made to the exact same additional scope in order for this to work:

      AND credit_type IN ({{credit_type_filter.map(a => `'${a}'`).join(",")}})
      AND estimated_conversion_date <= '{{estimated_conversion_date_filter}}'
      AND project_id IN ({{project_id_filter}})

This is untenable for us as a development team because we cannot maintain the same code between our staging and production environment...

For added context - where does one of these filters come from like "credit_type_filter"? It is a multiselect which, on change, calls our SQL query with the additional scope {{inventory_credit_type_filter.value}} passed in the event handler.

Turns out this is the difference in the prepared sql statement and it only be activated in production :slight_smile:

Nice, sounds like toggling "Disable converting queries to prepared statements" on the resource settings page did the trick (more about this, and the associated risks, in our docs: Read data from SQL databases | Retool Docs).

Thanks for sharing the answer back here, I'm sure it will help others searching to get unblocked in the future when facing the same scenario!