Closed
Description
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.