Closed
Description
Environment details
- OS type and version: MacOS Big Sur 11.5.1
- Python version: 3.7.4
- pip version: 20.3.3
sqlalchemy-bigqueryversion: 0.10.0sqlalchemyversion: 1.4.22
Steps to reproduce
- Create a SQLAlchemy query using an
in_ornotin_parameter. - Attempt to compile this query to text using the
compile()method with{"literal_binds": True}in the kwargs. This worked when running with SQLAlchemy version 1.3 and pybigquery version 0.5.0, but does not work with SA 1.4 and pybigquery 0.10.0.
Code example
from contextlib import contextmanager
from sqlalchemy import MetaData, Table, create_engine, select
engine = create_engine("bigquery://<your bigquery connection here>")
# Postgres connection, for comparison of proper literal binding and compilation
#engine = create_engine("postgresql://<your postgres connection here>")
@contextmanager
def test_query():
engine.execute(
"""
create table test as (
select 1 as val
union all select 2
union all select 3
)
"""
)
val = Table("test", MetaData(bind=engine), autoload=True).c.val
try:
yield select(
[val]
).where(val.in_([2]))
finally:
engine.execute("drop table test")
with test_query() as q:
print("Print:")
print(q)
print("\nCompile:")
print(q.compile(engine))
print("\nLiteral compile:")
print(q.compile(engine, compile_kwargs={"literal_binds": True}))
print("\nResult:")
print(tuple(engine.execute(q)))Stack trace
Print:
SELECT `test`.`val`
FROM `test`
WHERE `test`.`val` IN UNNEST([ [POSTCOMPILE_val_1]:INT64 ])
Compile:
SELECT `test`.`val`
FROM `test`
WHERE `test`.`val` IN UNNEST([ [POSTCOMPILE_val_1]:INT64 ])
Literal compile:
Traceback (most recent call last):
File "/Users/admin/Desktop/test_bq_in.py", line 37, in <module>
print(q.compile(engine, compile_kwargs={"literal_binds": True}))
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 489, in compile
return self._compiler(dialect, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 553, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/pybigquery/sqlalchemy_bigquery.py", line 222, in __init__
super(BigQueryCompiler, self).__init__(dialect, statement, *args, **kwargs)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 765, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 454, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 489, in process
return obj._compiler_dispatch(self, **kwargs)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
return meth(self, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 3239, in visit_select
kwargs,
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 3385, in _compose_select_body
select._where_criteria, from_linter=from_linter, **kwargs
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 1606, in _generate_delimited_and_list
return clauses[0]._compiler_dispatch(self, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
return meth(self, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 2068, in visit_binary
return disp(binary, operator_, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/pybigquery/sqlalchemy_bigquery.py", line 313, in visit_in_op_binary
self._generate_generic_binary(binary, " IN ", **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 2135, in _generate_generic_binary
self, eager_grouping=eager_grouping, **kw
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
return meth(self, **kw)
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/pybigquery/sqlalchemy_bigquery.py", line 437, in visit_bindparam
assert_(self.__expanded_param(param), f"Unexpected param: {param}")
File "/Users/admin/anaconda3/envs/sa_migration/lib/python3.7/site-packages/pybigquery/sqlalchemy_bigquery.py", line 63, in assert_
raise AssertionError(message)
AssertionError: Unexpected param: (2)
What we expect to see (From the Postgres running):
Print:
SELECT test.val
FROM test
WHERE test.val IN ([POSTCOMPILE_val_1])
Compile:
SELECT test.val
FROM test
WHERE test.val IN ([POSTCOMPILE_val_1])
Literal compile:
SELECT test.val
FROM test
WHERE test.val IN (2)
Result:
((2,),)
From the SQLAlchemy documentation (https://docs.sqlalchemy.org/en/14/faq/sqlexpressions.html#rendering-postcompile-parameters-as-bound-parameters) we expect this to work as setting literal_binds to True should implicitly set render_postcompile to True as well.