Repository: tobymao/sqlglot
Pull request: fix(parser): avoid CTE values ALIAS gen, when ALIAS exists
Fixes #5318

Problem statement:
Bug: Databricks/Spark loses column-list for `VALUES … AS alias(col1, …)` inside a CTE
#### Environment  
| | |
|---|---|
| **sqlglot version** | `latest` |
| **Python** | 3.12.8 (on macOS arm64) |
| **Read dialect** | `databricks` (identical with `spark`) |
| **Write dialect** | `databricks` |

---

#### Fully reproducible snippet

```python
from sqlglot import parse_one

sql = """
WITH map AS (
  VALUES ('allure', 'Allure', 'US') AS map(app_id, brand, market)
)
SELECT app_id, brand, market FROM map;
"""

tree = parse_one(sql, read="databricks")
print("PARSED →", tree.sql(dialect="databricks"))
```

**Current output**

```sql
WITH map AS (
  SELECT *
  FROM (VALUES ('allure', 'Allure', 'US')) AS _values     -- **column names dropped**
)
SELECT app_id, brand, market                              -- **now undefined**
FROM map
```

`app_id, brand, market` disappear, so subsequent references fail in Spark/Databricks.

⸻

**Expected output: Alias entire derived columns**
```
WITH map AS (
  (VALUES ('allure', 'Allure', 'US')) AS map(app_id, brand, market)
)
SELECT app_id, brand, market
FROM map;
```

Hints from issue discussion:

Commit message: fix(parser): avoid CTE values ALIAS gen, when ALIAS exists

Patch to review:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 775ff5a1a3..6c42d03c38 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3394,8 +3394,9 @@ def _parse_cte(self) -> t.Optional[exp.CTE]:
             comments=comments,
         )
 
-        if isinstance(cte.this, exp.Values):
-            cte.set("this", exp.select("*").from_(exp.alias_(cte.this, "_values", table=True)))
+        values = cte.this
+        if isinstance(values, exp.Values) and not values.alias:
+            cte.set("this", exp.select("*").from_(exp.alias_(values, "_values", table=True)))
 
         return cte
 
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index d8e2cedb83..44bd7451f9 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -186,6 +186,8 @@ def test_databricks(self):
             "SELECT test, LISTAGG(email, '') AS Email FROM organizations GROUP BY test",
         )
 
+        self.validate_identity("WITH t AS (VALUES ('foo_val') AS t(foo1)) SELECT foo1 FROM t")
+
     # https://docs.databricks.com/sql/language-manual/functions/colonsign.html
     def test_json(self):
         self.validate_identity("SELECT c1:price, c1:price.foo, c1:price.bar[1]")


Code review comments:
diff_hunk: @@ -3394,8 +3394,9 @@ def _parse_cte(self) -> t.Optional[exp.CTE]:
             comments=comments,
         )
 
-        if isinstance(cte.this, exp.Values):
-            cte.set("this", exp.select("*").from_(exp.alias_(cte.this, "_values", table=True)))
+        values = cte.this
+        if isinstance(values, exp.Values) and not values.alias:
+            cte.set("this", exp.select("*").from_(exp.alias_(values, "_values", table=True)))
path: sqlglot/parser.py
text: @user1:
Why not reuse the `TableAlias` attached to the `Values` node instead of mangling it (which we do today)? This is going to generate inconsistent SQL depending on the presence (or not) of an alias, right? Doesn't seem right.