Repository: tobymao/sqlglot

Issue:
Failure to parse complex UNPIVOT statement with Oracle Query
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- 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.

**Official Documentation**
Please include links to official SQL documentation related to your issue.

I have a very complex query that a client wrote. I am using sqlglot to parse the sql and to traverse the lineage.  I cannot post the exact query as it has sensitive information. However, I was able to reproduce it with a simplified sample.

I am using sqlglot 25.14.0, which was posted 5 hours before this post. however the issue existed in prior versions of sqlglot

Here is the test query:

SELECT
    *
FROM
    test
UNPIVOT INCLUDE NULLS(
    value FOR Description IN (
        col AS 'PREFIX ' || CHR(38) || ' SUFFIX'
    )
)

I am calling:

ast = parse_one(sql_stmt, read='oracle', dialect='oracle')

where sql_stmt is the sql I posted.

sqlglot is failing on the concatenation of strings using CHR(38).

It works If I change the sql to (removing the concatenation):

SELECT
    *
FROM
    test
UNPIVOT INCLUDE NULLS(
    value FOR Description IN (
        col AS 'PREFIX & SUFFIX'
    )
)

Let me know if you need more information.

Discussion:

Patch:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index acd4c096..67400514 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2909,11 +2909,13 @@ class Generator(metaclass=_Generator):
 
     def pivotalias_sql(self, expression: exp.PivotAlias) -> str:
         alias = expression.args["alias"]
+
         identifier_alias = isinstance(alias, exp.Identifier)
+        literal_alias = isinstance(alias, exp.Literal)
 
         if identifier_alias and not self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
             alias.replace(exp.Literal.string(alias.output_name))
-        elif not identifier_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
+        elif not identifier_alias and literal_alias and self.UNPIVOT_ALIASES_ARE_IDENTIFIERS:
             alias.replace(exp.to_identifier(alias.output_name))
 
         return self.alias_sql(expression)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 6171580c..fb78d1ab 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3776,8 +3776,10 @@ class Parser(metaclass=_Parser):
             this = self._parse_select_or_expression()
 
             self._match(TokenType.ALIAS)
-            alias = self._parse_field()
+            alias = self._parse_bitwise()
             if alias:
+                if isinstance(alias, exp.Column) and not alias.db:
+                    alias = alias.this
                 return self.expression(exp.PivotAlias, this=this, alias=alias)
 
             return this


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 79b8a024..4a570721 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -44,6 +44,9 @@ class TestOracle(Validator):
         self.validate_identity("SELECT COUNT(*) * 10 FROM orders SAMPLE (10) SEED (1)")
         self.validate_identity("SELECT * FROM V$SESSION")
         self.validate_identity("SELECT TO_DATE('January 15, 1989, 11:00 A.M.')")
+        self.validate_identity(
+            "SELECT * FROM test UNPIVOT INCLUDE NULLS (value FOR Description IN (col AS 'PREFIX ' || CHR(38) || ' SUFFIX'))"
+        )
         self.validate_identity(
             "SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name"
         )