URGENT: Resource returning no results on queries using arrays (overnight)

,
  • Goal: All SQL Queries using arrays returning no rows,

  • Steps: I've tried running the same query directly against the psql database which obviously returned the results. I've also tried replacing the variable with the actual value of the array, to no avail.

  • Details: here is the query:

SELECT DISTINCT ON (fop_capability_id)
       fop_id, fop_capability_id, match_score, cycle_id, origin_types.origin_type
FROM fop_to_job_role_capability_comparisons
JOIN fops ON fop_to_job_role_capability_comparisons.fop_id = fops.id
JOIN job_roles ON fop_to_job_role_capability_comparisons.job_role_id = job_roles.id
JOIN origin_types ON job_roles.origin_type_id = origin_types.id
WHERE fop_capability_id = ANY({{cycle_capabilities.data.map(cc=>cc.id)}})
ORDER BY fop_capability_id, match_score DESC;

The team has not touched this retool application since August
Screenshot 2025-01-20 at 17.16.15
.

This is public facing and being used by a client. This is a breaking bug.
Please advise with extreme urgency.

1 Like


Here are the results on supabase of the same exact query.

Hey @dio thanks for flagging this I'm doing some testing on my own instance to try and duplicate. Could you share if you are able to run any queries to this resource from within Retool? Are you seeing this same behavior with arrays in other resources?

Hey @dio I was able to duplicate the behavior you are seeing and noticed that the previous syntax that worked no longer does.

Old Syntax:

It looks like this worked up until 3.136.0-edge. For the latest cloud version, I was able to get arrays working with this syntax:

I have flagged this to our engineering team to see if this was an intended change, but is it possible to update your query to use this syntax to unblock your customers?

Thank you for the quick reply @cperea ! Unfortunately it really isn't feasible to change all of our queries in a reasonable time frame. Have the engineering team come back to you.

Hey @dio! Thanks for your patience.

I haven't specifically heard back from engineering about this apparent change, but just confirmed that the expected syntax does seem to work as of version 3.146.0. Can you confirm that your queries are running without any issues?