Closed
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