IN filter causes Database syntax error if printed before execution #357

LocalFolioProduct · 2021-10-15T00:45:27Z

Environment details

OS type and version: macOS Big Sur 11.6
Python version: Python 3.9.5
pip version: 21.2.4
sqlalchemy-bigquery version: 1.2.0

Steps to reproduce

Create Query containing IN filter
Call Query.__str__() (e.g. print(Query), log(Query))
Execute query
DatabaseError 400 Syntax error raised

Code example

from sqlalchemy import String, Float, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Column

from bq_reporting import settings

Base = declarative_base()
BIG_QUERY_PROJECT = 'BIG_QUERY_PROJECT'

class BlockGroup(Base):
    __tablename__ = f'bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr'

    geo_id = Column(String, primary_key=True)
    nonfamily_households = Column(Float)


engine = create_engine(f'bigquery://{BIG_QUERY_PROJECT}')
session = sessionmaker(bind=engine)()

results = session.query(
    BlockGroup.geo_id
).filter(BlockGroup.geo_id.in_(['213213']))
print(results)
print(results.count())

Stack trace

SELECT `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` AS `bigquery_public_data_census_bureau_acs_blockgroup_2010_5yr_geo_id` 
FROM `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr` 
WHERE `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` IN UNNEST(%(geo_id_1:STRING)s)
Traceback (most recent call last):
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 203, in _execute
    self._query_job.result()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/job/query.py", line 1372, in result
    do_get_result()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/api_core/retry.py", line 283, in retry_wrapped_func
    return retry_target(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/api_core/retry.py", line 190, in retry_target
    return target()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/job/query.py", line 1362, in do_get_result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/job/base.py", line 708, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/api_core/future/polling.py", line 137, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Syntax error: Expected "(" or keyword UNNEST but got "@" at [4:80]

(job ID: 60c732c3-4b4e-4da6-9988-18ba20d389ee)

                                                                -----Query Job SQL Follows-----                                                                

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT count(*) AS `count_1` 
   2:FROM (SELECT `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` AS `bigquery_public_data_census_bureau_acs_blockgroup_2010_5yr_geo_id` 
   3:FROM `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr` 
   4:WHERE `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` IN @`geo_id_1`) AS `anon_1`
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 489, in with_closed_check
    return method(self, *args, **kwargs)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 166, in execute
    self._execute(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 205, in _execute
    raise exceptions.DatabaseError(exc)
google.cloud.bigquery.dbapi.exceptions.DatabaseError: 400 Syntax error: Expected "(" or keyword UNNEST but got "@" at [4:80]

(job ID: 60c732c3-4b4e-4da6-9988-18ba20d389ee)

                                                                -----Query Job SQL Follows-----                                                                

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT count(*) AS `count_1` 
   2:FROM (SELECT `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` AS `bigquery_public_data_census_bureau_acs_blockgroup_2010_5yr_geo_id` 
   3:FROM `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr` 
   4:WHERE `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` IN @`geo_id_1`) AS `anon_1`
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/localuser/Library/Application Support/JetBrains/PyCharm2021.1/scratches/scratch_9.py", line 26, in <module>
    print(results.count())
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3090, in count
    return self._from_self(col).enable_eagerloads(False).scalar()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2831, in scalar
    ret = self.one()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2808, in one
    return self._iter().one()
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result = self.session.execute(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 489, in with_closed_check
    return method(self, *args, **kwargs)
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 166, in execute
    self._execute(
  File "/Users/localuser/dev/venv/bq_reporting/lib/python3.9/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 205, in _execute
    raise exceptions.DatabaseError(exc)
sqlalchemy.exc.DatabaseError: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 Syntax error: Expected "(" or keyword UNNEST but got "@" at [4:80]

(job ID: 60c732c3-4b4e-4da6-9988-18ba20d389ee)

                                                                -----Query Job SQL Follows-----                                                                

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT count(*) AS `count_1` 
   2:FROM (SELECT `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` AS `bigquery_public_data_census_bureau_acs_blockgroup_2010_5yr_geo_id` 
   3:FROM `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr` 
   4:WHERE `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` IN @`geo_id_1`) AS `anon_1`
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
[SQL: SELECT count(*) AS `count_1` 
FROM (SELECT `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` AS `bigquery_public_data_census_bureau_acs_blockgroup_2010_5yr_geo_id` 
FROM `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr` 
WHERE `bigquery-public-data.census_bureau_acs.blockgroup_2010_5yr`.`geo_id` IN %(geo_id_1:STRING)s) AS `anon_1`]
[parameters: {'geo_id_1': ['213213']}]
(Background on this error at: https://sqlalche.me/e/14/4xp6)

The text was updated successfully, but these errors were encountered:

stevenzhang-support · 2021-10-15T00:59:28Z

This is my customer and please prioritize this bug report. The newer version should support compatibility for previous version PyPi package in syntax.

tseaver · 2021-10-26T17:07:37Z

@stevenzhang-support, @LocalFolioProduct Can you please report the version of bigquery-sqlalchemy where the example query passed? It fails for me with the same error on versions 1.1.0, 1.0.0, and 0.0.7.

tseaver · 2021-10-26T19:55:14Z

Also, please not which version of SQLAlchemy you are using, e.g.:

$ /path/to/venv/bin/pip show SQLAlchemy | grep "^Version:"
Version: 1.4.26

Closes #357.

tseaver · 2021-10-26T21:38:10Z

OK, the key here is that the failure only occurs on SQLAlchemy>=1.4.0, which is now doing transparent query caching. It is therefore no longer safe for us to "scribble" on the passed-in bindparam inside our visit_bindparam.

Closes #357.

product-auto-label bot added the api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. label Oct 15, 2021

tswast added type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. priority: p1 Important issue which blocks shipping the next release. Will be fixed prior to next release. labels Oct 25, 2021

tseaver added the needs more info This issue needs more information from the customer to proceed. label Oct 26, 2021

tseaver self-assigned this Oct 26, 2021

tseaver added a commit that referenced this issue Oct 26, 2021

fix: avoid scribbling on (reused) bind param

0919488

Closes #357.

tseaver mentioned this issue Oct 26, 2021

fix: avoid scribbling on (reused) bind param #365

Merged

tseaver removed the needs more info This issue needs more information from the customer to proceed. label Oct 26, 2021

gcf-merge-on-green bot closed this as completed in #365 Oct 26, 2021

gcf-merge-on-green bot pushed a commit that referenced this issue Oct 26, 2021

fix: avoid scribbling on (reused) bind param (#365)

d28cac5

Closes #357.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

IN filter causes Database syntax error if printed before execution #357

IN filter causes Database syntax error if printed before execution #357

LocalFolioProduct commented Oct 15, 2021 •

edited

Loading

stevenzhang-support commented Oct 15, 2021

tseaver commented Oct 26, 2021

tseaver commented Oct 26, 2021

tseaver commented Oct 26, 2021

IN filter causes Database syntax error if printed before execution #357

IN filter causes Database syntax error if printed before execution #357

Comments

LocalFolioProduct commented Oct 15, 2021 • edited Loading

Environment details

Steps to reproduce

Code example

Stack trace

stevenzhang-support commented Oct 15, 2021

tseaver commented Oct 26, 2021

tseaver commented Oct 26, 2021

tseaver commented Oct 26, 2021

LocalFolioProduct commented Oct 15, 2021 •

edited

Loading