Repository: tobymao/sqlglot
Pull request: Fix!(bigquery): preserve quoted table paths
Fixes #3014

Problem statement:
Unexpected unnest
**Before you file an issue**
- Make sure you specify the "read" dialect eg. parse_one(sql, read="spark")
- Check if the issue still exists on main 

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
```
>>> import sqlglot
>>> sqlglot.transpile("""WITH Coordinates AS (SELECT [1,2] AS position)
... SELECT results FROM Coordinates, `Coordinates.position` AS results;
... """, read="bigquery", write="presto")[0]
```
should have result
```
'WITH Coordinates AS (SELECT ARRAY[1, 2] AS position) SELECT results FROM Coordinates, Coordinates.position AS results'
```
actually I got the unnest

```
WITH Coordinates AS (SELECT ARRAY[1, 2] AS position) SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS _t(results)
```
**Official Documentation**
Please include links to official SQL documentation related to your issue.

Hints from issue discussion:
these are equivalent so it looks right to me
~Your example contains backticks which is actually invalid BQ:~

```
--  Not found: Dataset ...:Coordinates was not found in location ... 
WITH Coordinates AS (SELECT [1,2] AS position)
SELECT results FROM Coordinates, `Coordinates.position` AS results;
```

The result is as you said if we get rid of the backticks, but I see no issue - the semantics are preserved:

```
presto> WITH Coordinates AS (SELECT ARRAY[1, 2] AS position) SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS _t(results);
 results 
---------
       1 
       2 
(2 rows)
```

<img width="536" alt="Screenshot 2024-02-23 at 2 17 49 AM" src="https://github.com/tobymao/sqlglot/assets/46752250/b5bf6865-8163-4848-8e05-e22b0f920929">

SQLGlot doesn't guarantee that the generated SQL which match the input, it aims to produce something with equivalent semantics.
If you create a table
`Coordinates.position`
You will see different results

>SQLGlot doesn't guarantee that the generated SQL which match the input, it aims to produce something with equivalent semantics.

The semantics depends on the context
- if you have Coordinates.position, it produce one semantic
- if you don't have Coordinates.position, it produce different

You need more input to make decision
I see what you mean - that makes sense, thanks. I'll try to fix this tomorrow.
Thanks! Take your time. I am not in rush

Commit message: Improve coverage

Patch to review:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index b7db71d76c..0e22a219fd 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -275,15 +275,16 @@ def normalize_identifier(self, expression: E) -> E:
             # by default. The following check uses a heuristic to detect tables based on whether
             # they are qualified. This should generally be correct, because tables in BigQuery
             # must be qualified with at least a dataset, unless @@dataset_id is set.
-            if (
-                not isinstance(parent, exp.UserDefinedFunction)
-                and not (
+            case_sensitive = (
+                isinstance(parent, exp.UserDefinedFunction)
+                or (
                     isinstance(parent, exp.Table)
                     and parent.db
-                    and not parent.meta.get("maybe_column")
+                    and (parent.meta.get("quoted_table") or not parent.meta.get("maybe_column"))
                 )
-                and not expression.meta.get("is_table")
-            ):
+                or expression.meta.get("is_table")
+            )
+            if not case_sensitive:
                 expression.set("this", expression.this.lower())
 
         return expression
@@ -468,7 +469,7 @@ def _parse_table_parts(
 
             if isinstance(table.this, exp.Identifier) and "." in table.name:
                 catalog, db, this, *rest = (
-                    t.cast(t.Optional[exp.Expression], exp.to_identifier(x))
+                    t.cast(t.Optional[exp.Expression], exp.to_identifier(x, quoted=True))
                     for x in split_num_words(table.name, ".", 3)
                 )
 
@@ -476,6 +477,7 @@ def _parse_table_parts(
                     this = exp.Dot.build(t.cast(t.List[exp.Expression], [this, *rest]))
 
                 table = exp.Table(this=this, db=db, catalog=catalog)
+                table.meta["quoted_table"] = True
 
             return table
 
@@ -778,6 +780,11 @@ class Generator(generator.Generator):
             "within",
         }
 
+        def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
+            if expression.meta.get("quoted_table"):
+                expression.set("this", f"`{'.'.join(p.pop().name for p in expression.parts)}`")
+            return super().table_sql(expression, sep=sep)
+
         def timetostr_sql(self, expression: exp.TimeToStr) -> str:
             this = expression.this if isinstance(expression.this, exp.TsOrDsToDate) else expression
             return self.func("FORMAT_DATE", self.format_time(expression), this.this)
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index d4f15cebb4..577b7db19a 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -5,6 +5,7 @@
     ParseError,
     TokenError,
     UnsupportedError,
+    exp,
     parse,
     transpile,
 )
@@ -18,14 +19,11 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
-        self.validate_all(
-            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
-            write={
-                "bigquery": "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
-                "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
-            },
-        )
+        self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql(), '"x"."y"."z"')
+        self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
 
+        self.validate_identity("SELECT * FROM `my-project.my-dataset.my-table`")
+        self.validate_identity("CREATE OR REPLACE TABLE `a.b.c` CLONE `a.b.d`")
         self.validate_identity("SELECT x, 1 AS y GROUP BY 1 ORDER BY 1")
         self.validate_identity("SELECT * FROM x.*")
         self.validate_identity("SELECT * FROM x.y*")
@@ -104,6 +102,14 @@ def test_bigquery(self):
         self.validate_identity("SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x")
         self.validate_identity("SELECT ARRAY((SELECT AS STRUCT 1 AS a, 2 AS b))")
         self.validate_identity("SELECT ARRAY((SELECT AS STRUCT 1 AS a, 2 AS b) LIMIT 10)")
+        self.validate_identity("CAST(x AS CHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS NCHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS NVARCHAR)", "CAST(x AS STRING)")
+        self.validate_identity("CAST(x AS TIMESTAMPTZ)", "CAST(x AS TIMESTAMP)")
+        self.validate_identity("CAST(x AS RECORD)", "CAST(x AS STRUCT)")
+        self.validate_identity(
+            "SELECT * FROM `SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW`"
+        )
         self.validate_identity(
             "SELECT * FROM test QUALIFY a IS DISTINCT FROM b WINDOW c AS (PARTITION BY d)"
         )
@@ -186,10 +192,6 @@ def test_bigquery(self):
             """SELECT JSON '"foo"' AS json_data""",
             """SELECT PARSE_JSON('"foo"') AS json_data""",
         )
-        self.validate_identity(
-            "CREATE OR REPLACE TABLE `a.b.c` CLONE `a.b.d`",
-            "CREATE OR REPLACE TABLE a.b.c CLONE a.b.d",
-        )
         self.validate_identity(
             "SELECT * FROM UNNEST(x) WITH OFFSET EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET",
             "SELECT * FROM UNNEST(x) WITH OFFSET AS offset EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET AS offset",
@@ -203,6 +205,13 @@ def test_bigquery(self):
             r"REGEXP_EXTRACT(svc_plugin_output, '\\\\\\((.*)')",
         )
 
+        self.validate_all(
+            "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+            write={
+                "bigquery": "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E6S%z', x)",
+                "duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
+            },
+        )
         self.validate_all(
             "SELECT results FROM Coordinates, Coordinates.position AS results",
             write={
@@ -210,6 +219,13 @@ def test_bigquery(self):
                 "presto": "SELECT results FROM Coordinates, UNNEST(Coordinates.position) AS _t(results)",
             },
         )
+        self.validate_all(
+            "SELECT results FROM Coordinates, `Coordinates.position` AS results",
+            write={
+                "bigquery": "SELECT results FROM Coordinates, `Coordinates.position` AS results",
+                "presto": 'SELECT results FROM Coordinates, "Coordinates"."position" AS results',
+            },
+        )
         self.validate_all(
             "SELECT results FROM Coordinates AS c, UNNEST(c.position) AS results",
             read={
@@ -471,8 +487,8 @@ def test_bigquery(self):
         self.validate_all(
             "CREATE OR REPLACE TABLE `a.b.c` COPY `a.b.d`",
             write={
-                "bigquery": "CREATE OR REPLACE TABLE a.b.c COPY a.b.d",
-                "snowflake": "CREATE OR REPLACE TABLE a.b.c CLONE a.b.d",
+                "bigquery": "CREATE OR REPLACE TABLE `a.b.c` COPY `a.b.d`",
+                "snowflake": 'CREATE OR REPLACE TABLE "a"."b"."c" CLONE "a"."b"."d"',
             },
         )
         (
@@ -512,11 +528,6 @@ def test_bigquery(self):
             ),
         )
         self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
-        self.validate_all("CAST(x AS CHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS NCHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS NVARCHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"bigquery": "CAST(x AS TIMESTAMP)"})
-        self.validate_all("CAST(x AS RECORD)", write={"bigquery": "CAST(x AS STRUCT)"})
         self.validate_all(
             'SELECT TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE)',
             write={
@@ -622,19 +633,9 @@ def test_bigquery(self):
                 "bigquery": "PARSE_TIMESTAMP('%Y.%m.%d %I:%M:%S%z', x)",
             },
         )
-        self.validate_all(
+        self.validate_identity(
             "CREATE TEMP TABLE foo AS SELECT 1",
-            write={"bigquery": "CREATE TEMPORARY TABLE foo AS SELECT 1"},
-        )
-        self.validate_all(
-            "SELECT * FROM `SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW`",
-            write={
-                "bigquery": "SELECT * FROM SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW",
-            },
-        )
-        self.validate_all(
-            "SELECT * FROM `my-project.my-dataset.my-table`",
-            write={"bigquery": "SELECT * FROM `my-project`.`my-dataset`.`my-table`"},
+            "CREATE TEMPORARY TABLE foo AS SELECT 1",
         )
         self.validate_all(
             "REGEXP_CONTAINS('foo', '.*')",
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a33c81ba25..990453ba32 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -820,7 +820,7 @@ SELECT
   `TOp_TeRmS`.`refresh_date` AS `day`,
   `TOp_TeRmS`.`term` AS `top_term`,
   `TOp_TeRmS`.`rank` AS `rank`
-FROM `bigquery-public-data`.`GooGle_tReNDs`.`TOp_TeRmS` AS `TOp_TeRmS`
+FROM `bigquery-public-data.GooGle_tReNDs.TOp_TeRmS` AS `TOp_TeRmS`
 WHERE
   `TOp_TeRmS`.`rank` = 1
   AND CAST(`TOp_TeRmS`.`refresh_date` AS DATE) >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK)
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 2adaf25a2e..85480b190c 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -29,6 +29,11 @@ SELECT results FROM c.db.Coordinates AS Coordinates, UNNEST(coordinates.position
 SELECT results FROM db.coordinates, Coordinates.position AS results;
 SELECT results FROM c.db.coordinates AS coordinates, UNNEST(Coordinates.position) AS results;
 
+# title: bigquery schema name clashes with CTE name - this is a join, not an implicit unnest
+# dialect: bigquery
+WITH Coordinates AS (SELECT [1, 2] AS position) SELECT results FROM Coordinates, `Coordinates.position` AS results;
+WITH Coordinates AS (SELECT [1, 2] AS position) SELECT results FROM Coordinates AS Coordinates, `c.Coordinates.position` AS results;
+
 # title: single cte
 WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a;
 WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a;
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index e3eb2e3ac2..1f782c8684 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -249,7 +249,7 @@ def test_replace_tables(self):
                 {"example.table": "`my-project.example.table`"},
                 dialect="bigquery",
             ).sql(),
-            'SELECT * FROM "my-project".example.table /* example.table */',
+            'SELECT * FROM "my-project"."example"."table" /* example.table */',
         )
 
     def test_expand(self):


Code review comments:
diff_hunk: @@ -778,6 +780,11 @@ class Generator(generator.Generator):
             "within",
         }
 
+        def table_sql(self, expression: exp.Table, sep: str = " AS ") -> str:
+            if expression.meta.get("quoted_table"):
path: sqlglot/dialects/bigquery.py
text: @user1:
can we do this one without mutation? since it's so common

expression = exp.Table(this=exp.Identifier(this=p.naem for ...))

@author:
What's the advantage of constructing a new `Table` vs mutating here? Remember that we'll also have to carry over the other args in the table.

@user1:
what do you mean you have to carry over args? also the ast doesn't look correct right, this is now a string and not an identifier

@author:
Whoops, good catch thanks - I intended for this to be an identifier.

> what do you mean you have to carry over args?

I mean that you can't just do `expression = exp.Table(this=...)` - what if `expression` had more [args](https://github.com/tobymao/sqlglot/blob/17e34e79d22e3c8211f1bf42047d4ed3557628b6/sqlglot/expressions.py#L2620)? With this overwrite here you'll drop them. This is what I was thinking:

```python
expression = exp.Table(**{"this": ..., **expression.args})
```

@author:
https://github.com/tobymao/sqlglot/pull/3021/commits/a9ac1c21e690ed6d3c3c0087542bf769677f0306

EDIT: forgot to get rid of the `pop`.. https://github.com/tobymao/sqlglot/pull/3021/commits/c4353a702e932bf26ea96ffa2d986fc670e795da