Skip to content

Unnest function in BigQuery SQLAlchemy plugin joins twice when selecting other columns #353

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
tswast opened this issue Oct 11, 2021 · 1 comment · Fixed by #361
Closed

Unnest function in BigQuery SQLAlchemy plugin joins twice when selecting other columns #353

tswast opened this issue Oct 11, 2021 · 1 comment · Fixed by #361
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

@tswast
Copy link
Collaborator

tswast commented Oct 11, 2021

From a customer support request:

Using the UNNEST function generates a CROSS JOIN with a table to itself.
A minimal example is included below. The following Python code:

from sqlalchemy import create_engine, MetaData, select, func as F, Table, Column, Integer, ARRAY
metadata = MetaData()
table = Table(
  "table1",
  metadata,
  Column("foo", Integer),
  Column("bar", ARRAY(Integer))
)
engine = create_engine("bigquery://", future=True)
q = select(table.c.foo).where(F.unnest(table.c.bar).column_valued() == 1)
print(q.compile(engine))

Generates this query:

SELECT `table1`.`foo` 
FROM `table1`, `table1` `table1_1`, unnest(`table1_1`.`bar`) AS `anon_1` 
WHERE `anon_1` = %(param_1:INT64)s

I expect it to generate this query instead (note the different FROM clause):

SELECT `table1`.`foo` 
FROM `table1`,  unnest(`table1`.`bar`) AS `anon_1` 
WHERE `anon_1` = %(param_1:INT64)s
@tswast tswast added 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. labels Oct 11, 2021
@tseaver tseaver self-assigned this Oct 26, 2021
@tseaver
Copy link
Contributor

tseaver commented Oct 26, 2021

@tswast I think I have a fix in hand for this one.

tseaver added a commit that referenced this issue Oct 26, 2021
@tseaver
fix: avoid creating aliases for already-known tables
a98f818 
Closes #353.
@tseaver tseaver mentioned this issue Oct 26, 2021
Merged
tswast pushed a commit that referenced this issue Oct 26, 2021
@tseaver
fix: avoid creating aliases for already-known tables (#361)
1ce4e14 
Closes #353.
@mgtcardenas mgtcardenas mentioned this issue Oct 28, 2021
Closed
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 creating aliases for already-known tables googleapis/python-bigquery-sqlalchemy
2 participants
@tseaver @tswast