search

databricks / databricks-sql-python

Databricks SQL Connector for Python
Apache License 2.0
155 stars 89 forks source link

Double Parenthesis in Query Interpolation with List Parameters for IN clause #307

Open stan-lebedynskyi-hunters-ai opened 9 months ago

stan-lebedynskyi-hunters-ai commented 9 months ago

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.

susodapop commented 9 months ago

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.

stan-lebedynskyi-hunters-ai commented 8 months ago

Thank you!

Will be monitoring this thread for updates.

susodapop commented 8 months ago

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.

stan-lebedynskyi-hunters-ai commented 8 months ago

Good morning @susodapop Do you know if there are any updates here?

Thank you!