Sqlalchemy in statement is incorrectly compiled for the in clause
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 35/100
Research direction
Reproduce the issue using the shown create_engine, select, statement compilation, and conn.execute entry points on version 2.9.6. Compare the literal_binds output with the parameterized SQL and parameters sent during execution. Done means an IN clause containing True executes successfully without a boolean-versus-integer type mismatch.
Written by the indexing model from the issue text.
Description
Version 2.9.6
Repro:
from sqlalchemy import create_engine, MetaData, Table, select, column
# Assuming you have a working database connection string
engine = create_engine('...'). # make sure to specify the catalog
metadata = MetaData()
engine.execute("create table if not exists bogdankyryliuk.bool_test as select 1 as one_val, True as bool_val")
test_table = Table('bool_test', metadata, autoload_with=engine, schema='bogdankyryliuk')
stmt = select(test_table).where(test_table.c.bool_val.in_([True]))
compiled_stmt = stmt.compile(compile_kwargs={"literal_binds": True})
print(compiled_stmt)
with engine.connect() as conn:
result = conn.execute(stmt)
for row in result:
print(row)
Compiled statement is printed as expected:
SELECT bogdankyryliuk.bool_test.one_val, bogdankyryliuk.bool_test.bool_val
FROM bogdankyryliuk.bool_test
WHERE bogdankyryliuk.bool_test.bool_val IN (true)
However conn.execute(stmt) executes different statement:
DatabaseError: (databricks.sql.exc.ServerOperationError) [DATATYPE_MISMATCH.DATA_DIFF_TYPES] Cannot resolve "(bool_val IN (1))" due to data type mismatch: Input to `in` should all be the same type, but it's ["BOOLEAN", "INT"]. SQLSTATE: 42K09; line 3 pos 40
[SQL: SELECT bogdankyryliuk.bool_test.one_val, bogdankyryliuk.bool_test.bool_val
FROM bogdankyryliuk.bool_test
WHERE bogdankyryliuk.bool_test.bool_val IN (%(bool_val_1_1)s)]
[parameters: {'bool_val_1_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/4xp6)
That leads to the exception
- Dominant language
- Python
- Stars
- 233
- Forks
- 152
- Avg merge
- 21h 5m
- Merged PRs (30d)
- 10
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from databricks/databricks-sql-python
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 84/100
All issues in databricks/databricks-sql-python
Similar issues
-
essnmx good first issue
Difficulty 1/5 Under an hour Newbie friendliness 95/100
-
[Feature] 奇物选择添加优先级 Open
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
syfoud/Simulated_Scepter#174 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 75/100
Giskard-AI/giskard-oss#2840 · 1 comment ·
-
A claim comment carrying the issue number is silently declined while the workflow reports success Openarea: repo bug perceived difficulty: 2
Difficulty 2/5 1-3 hours Newbie friendliness 70/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 75/100
yeti-platform/yeti#1380 ·