databricks
Double Parenthesis in Query Interpolation with List Parameters for IN clause
Summary: When using the Databricks SQL Python integration to interpolate list parameters into a SQL query, the list parameters are incorrectly wrapped in double parentheses. This results in a malformed SQL query, causing execution errors on the Databricks side.
Details: I encountered an issue while using the Databricks SQL Python integration to dynamically inject a list of values into a SQL query. The expected behavior is to interpolate the list parameters into the query, properly formatted for an IN clause. However, the interpolation wraps the list in an extra set of parentheses, leading to a Databricks runtime error.
Query template
query = """
select *
from SOME_TABLE
where ID IN (%(id_list)s)
"""
parameters = {'id_list': [1, 2, 3]}
# Interpolation process here
Expected:
select *
from SOME_TABLE
where ID IN (1, 2, 3)
Actual:
select *
from SOME_TABLE
where ID IN ((1, 2, 3))
I know that removing outer parathesis will solve the error, but the same query is used by Snowflake connector which requires lists to be wrapped in parenthesis.
By the way, it might be the other way around and I might need to submit bug to Snowflake community. Let me know what you think.
Thanks for opening this issue. I've started a discussion internally to see if we should / how we can address this.
By the way, it might be the other way around and I might need to submit bug to Snowflake community.
I don't think this is really a "bug" in either connector. It's just a difference in syntax. We mention in the docs for inline sequence parameters that the precise behaviour of this syntax is beyond the scope of the PEP-249 standard. Which means connector developers are free to implement however they deem fit.
Thanks. We have a few folks out for end-of-year so it will be a few days before we can get the right eyes on this.
Good morning @susodapop Do you know if there are any updates here?
Thank you!