René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Unnest Function In BigQuery SQLAlchemy Plugin Joins Twice When Selecting Other Columns in CTE or Subquery ","articleBody":"From a customer support request:\r\n\r\n**Issue summary**: This issue is closely related to [Unnest function in BigQuery SQLAlchemy plugin joins twice when selecting other columns googleapis/python-bigquery-sqlalchemy#353](https://github.com/googleapis/python-bigquery-sqlalchemy/issues/353). After the previous issue was resolved, queries using the UNNEST function that are not in Common Table Expressions (CTE) or subqueries work as expected. See the following minimal example:\r\n\r\n```\r\nfrom sqlalchemy import create_engine, MetaData, select, func as F, Table, Column, Integer, ARRAY\r\n\r\nmetadata = MetaData()\r\ntable = Table(\r\n  \"table1\",\r\n  metadata,\r\n  Column(\"foo\", Integer),\r\n  Column(\"bars\", ARRAY(Integer))\r\n)\r\n\r\nengine = create_engine(\"bigquery://\", future=True)\r\nq = select(table.c.foo, F.unnest(table.c.bars).column_valued(\"bar\"))\r\nprint(q.compile(engine))\r\n```\r\n\r\nThis outputs the following, which is expected:\r\n```\r\nSELECT `table1`.`foo`, `bar` \r\nFROM `table1`, unnest(`table1`.`bars`) AS `bar`\r\n```\r\n\r\nHowever, if we use the table as a subquery or CTE, the issue manifests again. The following code (continuing from above):\r\n\r\n```\r\nq = q.cte(\"cte\")\r\nq = select(*q.columns)\r\nprint(q.compile(engine))\r\n```\r\n\r\nProduces the next output:\r\n\r\n```\r\nWITH `cte` AS \r\n(SELECT `table1`.`foo` AS `foo`, `bar` \r\nFROM `table1`, `table1` `table1_1`, unnest(`table1_1`.`bars`) AS `bar`)\r\n SELECT `cte`.`foo`, `cte`.`bar` \r\nFROM `cte`\r\n```\r\nNote that `table1` is duplicated in the `FROM` clause again.\r\n\r\n**Expected behavior**: It is expected to generate the following query instead (note the different FROM clause):\r\n```\r\nWITH `cte` AS \r\n(SELECT `table1`.`foo`, `bar` \r\nFROM `table1`, unnest(`table1`.`bars`) AS `bar`)\r\n SELECT `cte`.`foo`, `cte`.`bar` \r\nFROM `cte`\r\n```","author":{"url":"https://github.com/mgtcardenas","@type":"Person","name":"mgtcardenas"},"datePublished":"2021-10-28T15:54:05.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":11},"url":"https://github.com/368/python-bigquery-sqlalchemy/issues/368"}