This repository was archived by the owner on May 16, 2026. It is now read-only.
Repository navigation
This repository was archived by the owner on May 16, 2026. It is now read-only.
Unnest function in BigQuery SQLAlchemy plugin joins twice when selecting other columns #353
Description
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
Activity
tseaver commentedon Oct 26, 2021
@tswast I think I have a fix in hand for this one.
fix: avoid creating aliases for already-known tables
fix: avoid creating aliases for already-known tables (#361)