Skip to content

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

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
LocalFolioProduct opened this issue Oct 15, 2021 · 4 comments · Fixed by #365
Closed

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

LocalFolioProduct opened this issue Oct 15, 2021 · 4 comments · Fixed by #365
Assignees
@tseaver
Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. priority: p1 Important issue which blocks shipping the next release. Will be fixed prior to next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@LocalFolioProduct
Copy link

LocalFolioProduct commented Oct 15, 2021 •
edited
Loading

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

  1. Create Query containing IN filter
  2. Call Query.__str__() (e.g. print(Query), log(Query))
  3. Execute query
  4. 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)
@product-auto-label product-auto-label bot added the api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. label Oct 15, 2021
@stevenzhang-support
Copy link

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

@tswast 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
Copy link
Contributor

tseaver commented Oct 26, 2021

@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 tseaver added the needs more info This issue needs more information from the customer to proceed. label Oct 26, 2021
@tseaver
Copy link
Contributor

tseaver commented Oct 26, 2021

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

@tseaver tseaver self-assigned this Oct 26, 2021
tseaver added a commit that referenced this issue Oct 26, 2021
@tseaver
fix: avoid scribbling on (reused) bind param
0919488 
Closes #357.
@tseaver tseaver mentioned this issue Oct 26, 2021
Merged
@tseaver tseaver removed the needs more info This issue needs more information from the customer to proceed. label Oct 26, 2021
@tseaver
Copy link
Contributor

tseaver commented Oct 26, 2021

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.

gcf-merge-on-green bot pushed a commit that referenced this issue Oct 26, 2021
@tseaver
fix: avoid scribbling on (reused) bind param (#365)
d28cac5 
Closes #357.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tseaver tseaver

Labels
api: bigquery Issues related to the googleapis/python-bigquery-sqlalchemy API. priority: p1 Important issue which blocks shipping the next release. Will be fixed prior to 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: avoid scribbling on (reused) bind param googleapis/python-bigquery-sqlalchemy
4 participants
@tseaver @tswast @LocalFolioProduct @stevenzhang-support