Skip to content

Something seems to be mutating sqlalchemy expressions #127

Closed
@machow

Description

@machow

Hey, thanks for all your work on this library! I'm looking into adding bigquery support to siuba--which makes heavy use of sqlalchemy--and ran into a small hitch with expressions producing different SQL when run multiple times. AFAIK these expressions should be immutable at this level of use, so I wonder if pybigquery is doing some kind of mutation under the hood?

Environment details

pip --version
pip 18.1 from /Users/machow/.virtualenvs/siuba-bigquery/lib/python3.6/site-packages/pip (python 3.6)

python --version
Python 3.6.8

pip show pybigquery
Name: pybigquery
Version: 0.6.1
Summary: SQLAlchemy dialect for BigQuery
Home-page: https://github.com/googleapis/python-bigquery-sqlalchemy
Author: The PyBigQuery Authors
Author-email: googleapis-packages@google.com
License: UNKNOWN
Location: /Users/machow/.virtualenvs/siuba-bigquery/lib/python3.6/site-packages
Requires: sqlalchemy, google-cloud-bigquery, google-auth, future
Required-by: 

Steps to reproduce

Run the following code to setup:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, String, MetaData
from sqlalchemy import sql

# note: I'm not sure how to mock a sqlalchemy engine, but we won't need
# to actually execute a query
engine = create_engine("bigquery://any-project-id-you-can-connect-to")

tbl = Table("my_dataset.my_table", MetaData(bind = engine), Column("some_col", String))

Note that running the following code returns a valid SQL query:

sel = tbl.select()

sel2 = sel.with_only_columns([{c.key: c for c in sel.inner_columns}["some_col"]])
print(.select([sel2.alias()]).limit(5))

Output:

SELECT `anon_1`.`some_col` AS `anon_1_some_col` 
FROM (SELECT `my_dataset.my_table`.`some_col` AS `some_col` 
FROM `my_dataset.my_table`) AS `anon_1`
 LIMIT %(param_1)s

However, if we re-run only the last two lines, it produces a new (invalid) query, which prefixes column names with `anon_1`.`my_dataset`:

sel2 = sel.with_only_columns([{c.key: c for c in sel.inner_columns}["some_col"]])
print(.select([sel2.alias()]).limit(5))

output

SELECT `anon_1`.`my_dataset`.`my_table_some_col` AS `anon_1_my_dataset_my_table_some_col` 
FROM (SELECT `my_dataset.my_table`.`some_col` AS `my_dataset_my_table_some_col` 
FROM `my_dataset.my_table`) AS `anon_1`
 LIMIT %(param_1)s

I wonder if it's related to the use_label attribute being set (#78), since it's mutating an element attribute.

Metadata

Metadata

Assignees

No one assigned

    Labels

    🚨This issue needs some love.api: bigqueryIssues related to the googleapis/python-bigquery-sqlalchemy API.triage meI really want to be triaged.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions