Skip to content

Query compilation breaks using the literal_binds kwarg with an IN operator in the Query on SQLAlchemy 1.4 #252

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
dylanpbacon opened this issue Aug 17, 2021 · 0 comments · Fixed by #285
Closed

Query compilation breaks using the literal_binds kwarg with an IN operator in the Query on SQLAlchemy 1.4 #252

dylanpbacon opened this issue Aug 17, 2021 · 0 comments · Fixed by #285
Assignees
@jimfulton
Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@dylanpbacon
Copy link

Environment details

  • OS type and version: MacOS Big Sur 11.5.1
  • Python version: 3.7.4
  • pip version: 20.3.3
  • sqlalchemy-bigquery version: 0.10.0
  • sqlalchemy version: 1.4.22

Steps to reproduce

  1. Create a SQLAlchemy query using an in_ or notin_ parameter.
  2. 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.
@product-auto-label product-auto-label bot added the api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. label Aug 17, 2021
@jimfulton jimfulton self-assigned this Aug 19, 2021
@jimfulton jimfulton mentioned this issue Aug 19, 2021
Merged
4 tasks
@tswast tswast added priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. labels Aug 19, 2021
@jimfulton jimfulton mentioned this issue Aug 19, 2021
Merged
4 tasks
@machow machow mentioned this issue Mar 29, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@jimfulton jimfulton

Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix: in-operator literal binds not handled properly jimfulton/python-bigquery-sqlalchemy
3 participants
@tswast @jimfulton @dylanpbacon