Repository: tobymao/sqlglot
Pull request: fix!: unqualify UNNEST only the left most part of a column
Fixes #5062

In the previous implementation, `unqualify_unnest` stripped qualifying prefixes from every aliased `UNNEST` expression when the column path matched the `table` or `db` name. This behavior was intended to support dialects like BigQuery and Redshift because the optimizer adds via the `qualify` aliases to the `UNNEST`. 

However, this approach led to incorrect behavior in certain cases. For example:
```
SELECT * FROM t1, UNNEST("t1"."t2"."t3") "t2" ("col")
```
In this query, `unqualify_unnest` would incorrectly remove `"t2"` from the path, resulting in an incorrect SQL output after transpilation (duckb -> bigquery).

Problem statement:
Missing Struct field wrapped in UNNEST when transpiling to BigQuery dialect
**Before you file an issue**
I have a SQL that tries to unnest a list field of a struct. However, if the table alias of the unnest table factor is the same as the field name in the struct, it will be removed after transpiling to BigQuery dialect.

**Fully reproducible code snippet**
The following code can reproduce the issue:
```python
import sqlglot

# The `member` is a struct column of the `groups` table.
sql = """
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
"""
print("input: \n", sql)
result = sqlglot.transpile(sql, read=None, write="bigquery")[0]
print("output: \n", result)
```
The result is
```sql
input: 
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
output: 
SELECT * FROM `groups`, UNNEST(`groups`.`list`) AS `element`
```

However, the expected result is
```sql
SELECT * FROM `groups`, UNNEST(`groups`.`members`.`list`) AS `element`
```

Hints from issue discussion:
Hey @goldmedal, can you provide some more details on what you're trying to achieve? Looks like you're transpiling from another dialect to BigQuery, but it's not clear what that is.
> Hey [@goldmedal](https://github.com/goldmedal), can you provide some more details on what you're trying to achieve? Looks like you're transpiling from another dialect to BigQuery, but it's not clear what that is.

Thanks for replying @georgesittas 
I think it's a generic case (that's why I tried `None` as the input dialect). Anyway, let me use DuckDB as an example to explain clearly. The SQL is used to unnest the array in the struct. In DuckDB, the valid SQL is like:
```
create table "groups" (member struct(list int[]));
D select * from "groups", unnest("groups"."member"."list") as "members"("element");
┌────────────────────────┬─────────┐
│         member         │ element │
│ struct(list integer[]) │  int32  │
├────────────────────────┴─────────┤
│              0 rows              │
└──────────────────────────────────┘
```

However, if I tried to transpile this SQL to the BigQuery dialect:
```python
import sqlglot

# The `member` is a struct column of the `groups` table.
sql = """
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
"""
print("input: \n", sql)
result = sqlglot.transpile(sql, read="duckdb", write="bigquery")[0]
print("output: \n", result)
```
The result is
```
input: 
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
output: 
SELECT * FROM `groups`, UNNEST(`groups`.`list`) AS `element`
```
See the output, we can find that the `members` is missing. The expected result should be:
```SQL
SELECT * FROM `groups`, UNNEST(`groups`.`members`.`list`) AS `element`
```
Refer to [The doc of BigQuery unnest](https://cloud.google.com/bigquery/docs/arrays#flattening_arrays), the table alias is the column name of the unnest result. I guessed that there are some issues when removing the table name `"members"("element")`

Commit message: fix!: unqualify UNNEST only the left most part of a column

Patch to review:
diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index 84e4c45dc9..da462732bf 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -309,10 +309,9 @@ def unqualify_unnest(expression: exp.Expression) -> exp.Expression:
         }
         if unnest_aliases:
             for column in expression.find_all(exp.Column):
-                if column.table in unnest_aliases:
-                    column.set("table", None)
-                elif column.db in unnest_aliases:
-                    column.set("db", None)
+                leftmost_part = column.parts[0]
+                if leftmost_part.arg_key != "this" and leftmost_part.this in unnest_aliases:
+                    leftmost_part.pop()
 
     return expression
 
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 377cfe81d1..efd0c9cf0b 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1684,6 +1684,39 @@ def test_bigquery(self):
             "EXPORT DATA WITH CONNECTION myproject.us.myconnection OPTIONS (URI='gs://path*.csv.gz', FORMAT='CSV') AS SELECT * FROM all_rows"
         )
 
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1") "t1" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t1" AS "col"',
+            },
+        )
+
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t2`.`t3`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1"."t2"."t3") "t1" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t2`.`t3`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t2"."t3" AS "col"',
+            },
+        )
+
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t1`.`t2`.`t3`.`t4`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1"."t2"."t3"."t4") "t3" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t1`.`t2`.`t3`.`t4`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t1"."t2"."t3"."t4" AS "col"',
+            },
+        )
+
     def test_errors(self):
         with self.assertRaises(TokenError):
             transpile("'\\'", read="bigquery")


Code review comments:
diff_hunk: @@ -1684,6 +1684,39 @@ def test_bigquery(self):
             "EXPORT DATA WITH CONNECTION myproject.us.myconnection OPTIONS (URI='gs://path*.csv.gz', FORMAT='CSV') AS SELECT * FROM all_rows"
         )
 
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1") "t1" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t1" AS "col"',
+            },
+        )
+
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t2`.`t3`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1"."t2"."t3") "t1" ("col")',
path: tests/dialects/test_bigquery.py
text: @user1:
it's a bit weird that we've reused the `t1` alias for the unnest in this test.

@author:
I see, should change it.