SQL query with bindings for IN values no longer work

[mjashanks]

A change in the way SQL parameters work has made the SQL ... WHERE something IN {{ values }} impossible.

Original poster reported in MySQL, and I reproduced in Postgres

Discussed in #4963

^{Originally posted by micebrain March 19, 2022}
Hi,
I updated my app to v1.0.90 and found that SQL queries that has value binding to integer column is giving error:

error: invalid input syntax for type integer: "1,2"

I figured out that this only happened to queries that uses the in expression with multiple values. And example of the query is something like

SELECT * FROM customer WHERE id in ({{ customer_ids }});

with the customer_ids = 1,2

My expectation of the template output would be

SELECT * FROM customer WHERE in (1,2);

But the error message seems to indicate that it is now rendering as

SELECT * FROM customer WHERE in ('1,2');

How can I change this behaviour?

[micebrain]

Hi,
I updated to the latest version v1.0.99, however this it didn't seems to fix the issue.
I still get the same error in my test.

[mike12345567]

Hi @micebrain - could you provide an example of your query to test?

[micebrain]

Hi @mike12345567 sure, it's actually pretty simple. Here is the screenshot of the configured query

[mike12345567]

Hi @micebrain - can you try this query without the parentheses around the binding e.g. WHERE id in {{ customer_ids }}; - we've changed how Budibase manages these kind of statements and it will now automate the inclusion of the braces.

I will see if Budibase can detect that they've already been included in the query and not add them if they are already there - but I think removing them should fix your query.

[micebrain]

Hi @mike12345567
Yes that solved the problem. Thank you!