Repository: tobymao/sqlglot

Issue:
UNPIVOT expression with subquery alias fails
Hi!

We encounter an issue with trying to parse a BigQuery query that contains an UNPIVOT statement with an aliased sub query.
An example query that fails:

```
SELECT *
FROM (SELECT * FROM db.`my-table`) AS a
 UNPIVOT (
   (column) FOR column_name IN (value1, value2)
)
```

The stack trace we receive is:
```
Traceback (most recent call last):
  File "/Users/shay/Work/sql-lineage/query_analyzer.py", line 22, in __init__
    expressions = sqlglot.parse(self.query, self.dialect)
  File "/Users/shay/.local/share/virtualenvs/sql-lineage-XcD5rJZj/lib/python3.8/site-packages/sqlglot/__init__.py", line 72, in parse
    return dialect.parse(sql, **opts)
  File "/Users/shay/.local/share/virtualenvs/sql-lineage-XcD5rJZj/lib/python3.8/site-packages/sqlglot/dialects/dialect.py", line 163, in parse
    return self.parser(**opts).parse(self.tokenizer.tokenize(sql), sql)
  File "/Users/shay/.local/share/virtualenvs/sql-lineage-XcD5rJZj/lib/python3.8/site-packages/sqlglot/parser.py", line 775, in parse
    return self._parse(
  File "/Users/shay/.local/share/virtualenvs/sql-lineage-XcD5rJZj/lib/python3.8/site-packages/sqlglot/parser.py", line 841, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/shay/.local/share/virtualenvs/sql-lineage-XcD5rJZj/lib/python3.8/site-packages/sqlglot/parser.py", line 884, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 4, Col: 2.
```

Thank you for your help and your great project!!!

Discussion:
GeorgeSittas: This isn't completed yet, I think we also need pivots/unpivots for unnests etc, so I'll try to refactor a bit.
GeorgeSittas: I tried the following example to test if pivots are allowed after `UNNEST` and Bigquery fails with `PIVOT is not allowed with array scans`:

```sql
SELECT * FROM UNNEST([
    STRUCT(1 as sales, 'Q1' as quarter), 
    (2, 'Q2'),
    (3, 'Q3'),
    (4, 'Q4')
  ])
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
```

Therefore, I suppose that we don't need to handle UNNEST and this should suffice for the original issue.
CC: @ShayYaari

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 43f538cc..0cb84ba9 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -162,6 +162,7 @@ class DuckDB(Dialect):
             exp.JSONBExtract: arrow_json_extract_sql,
             exp.JSONBExtractScalar: arrow_json_extract_scalar_sql,
             exp.LogicalOr: rename_func("BOOL_OR"),
+            exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.Pivot: no_pivot_sql,
             exp.Properties: no_properties_sql,
             exp.RegexpExtract: _regexp_extract_sql,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index d7cbac48..d0b30caf 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -311,6 +311,7 @@ class Postgres(Dialect):
             exp.DateSub: _date_add_sql("-"),
             exp.DateDiff: _date_diff_sql,
             exp.LogicalOr: rename_func("BOOL_OR"),
+            exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.Min: min_or_least,
             exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
             exp.ArrayContains: lambda self, e: self.binary(e, "@>"),
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index aef9de34..aed775c7 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -265,6 +265,7 @@ class Presto(Dialect):
             exp.Lateral: _explode_to_unnest_sql,
             exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"),
             exp.LogicalOr: rename_func("BOOL_OR"),
+            exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.Quantile: _quantile_sql,
             exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"),
             exp.SafeDivide: no_safe_divide_sql,
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 9b159a4e..bdcc8de8 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -280,6 +280,8 @@ class Snowflake(Dialect):
             exp.DataType: _datatype_sql,
             exp.If: rename_func("IFF"),
             exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
+            exp.LogicalOr: rename_func("BOOLOR_AGG"),
+            exp.LogicalAnd: rename_func("BOOLAND_AGG"),
             exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
             exp.Matches: rename_func("DECODE"),
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 05ee53f8..c271f6f5 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -157,6 +157,7 @@ class Spark(Hive):
             exp.VariancePop: rename_func("VAR_POP"),
             exp.DateFromParts: rename_func("MAKE_DATE"),
             exp.LogicalOr: rename_func("BOOL_OR"),
+            exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.DayOfMonth: rename_func("DAYOFMONTH"),
             exp.DayOfYear: rename_func("DAYOFYEAR"),
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index ed7c7413..e1e04caf 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -79,17 +79,19 @@ class SQLite(Dialect):
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,  # type: ignore
             exp.DateAdd: _date_add_sql,
+            exp.DateStrToDate: lambda self, e: self.sql(e, "this"),
+            exp.GroupConcat: _group_concat_sql,
             exp.ILike: no_ilike_sql,
             exp.JSONExtract: arrow_json_extract_sql,
             exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
             exp.JSONBExtract: arrow_json_extract_sql,
             exp.JSONBExtractScalar: arrow_json_extract_scalar_sql,
             exp.Levenshtein: rename_func("EDITDIST3"),
+            exp.LogicalOr: rename_func("MAX"),
+            exp.LogicalAnd: rename_func("MIN"),
             exp.TableSample: no_tablesample_sql,
-            exp.DateStrToDate: lambda self, e: self.sql(e, "this"),
             exp.TimeStrToTime: lambda self, e: self.sql(e, "this"),
             exp.TryCast: no_trycast_sql,
-            exp.GroupConcat: _group_concat_sql,
         }
 
         def datediff_sql(self, expression: exp.DateDiff) -> str:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 0c345b3b..3d385b9c 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3493,7 +3493,11 @@ class Log10(Func):
 
 
 class LogicalOr(AggFunc):
-    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
+    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
+
+
+class LogicalAnd(AggFunc):
+    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
 
 
 class Lower(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 59366498..9a21a69e 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1359,8 +1359,8 @@ class Generator:
         sql = self.query_modifiers(
             expression,
             self.wrap(expression),
-            self.expressions(expression, key="pivots", sep=" "),
             alias,
+            self.expressions(expression, key="pivots", sep=" "),
         )
 
         return self.prepend_ctes(expression, sql)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 90fdade9..faac009c 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2085,6 +2085,8 @@ class Parser(metaclass=_Parser):
         subquery = self._parse_select(table=True)
 
         if subquery:
+            if not subquery.args.get("pivots"):
+                subquery.set("pivots", self._parse_pivots())
             return subquery
 
         this = self._parse_table_parts(schema=schema)


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 22387daf..26eeaee1 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -8,6 +8,9 @@ class TestBigQuery(Validator):
     def test_bigquery(self):
         self.validate_identity("SELECT STRUCT<ARRAY<STRING>>(['2023-01-17'])")
         self.validate_identity("SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))")
+        self.validate_identity(
+            "SELECT * FROM (SELECT * FROM `t`) AS a UNPIVOT((c) FOR c_name IN (v1, v2))"
+        )
 
         self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 1ac910c5..9a2daa6b 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -17,6 +17,28 @@ class TestSnowflake(Validator):
         )
         self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
 
+        self.validate_all(
+            "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test",
+            write={
+                "": "SELECT LOGICAL_OR(c1), LOGICAL_OR(c2) FROM test",
+                "duckdb": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+                "postgres": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+                "snowflake": "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test",
+                "spark": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test",
+                "sqlite": "SELECT MAX(c1), MAX(c2) FROM test",
+            },
+        )
+        self.validate_all(
+            "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test",
+            write={
+                "": "SELECT LOGICAL_AND(c1), LOGICAL_AND(c2) FROM test",
+                "duckdb": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+                "postgres": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+                "snowflake": "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test",
+                "spark": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test",
+                "sqlite": "SELECT MIN(c1), MIN(c2) FROM test",
+            },
+        )
         self.validate_all(
             "TO_CHAR(x, y)",
             read={