Repository: tobymao/sqlglot

Issue:
Oracle PIVOT statement failed to parse correctly
**Problem**
The parsing of the `PIVOT` statement is compromised when we are doing `JOIN` before it. I should be able to parse this query:

```
sql_request = """
SELECT
    consumer.id,
    event_date
  FROM consumer
  LEFT JOIN groceries on consumer.groceries_id = consumer.id
  PIVOT (
    max(type_id)
    FOR consumer_type
    IN
    (
    1,
    2,
    3,
    4
    )
  )

"""
parsed = parse_one(sql_request, read="oracle")
```
But I got the following error: 
```
ParseError                                Traceback (most recent call last)
Cell In[4], line 1
----> 1 parsed = parse_one(sql_request, read="oracle")

File /usr/local/lib/python3.11/site-packages/sqlglot/__init__.py:139, in parse_one(sql, read, dialect, into, **opts)
    137     result = dialect.parse_into(into, sql, **opts)
    138 else:
--> 139     result = dialect.parse(sql, **opts)
    141 for expression in result:
    142     if not expression:

File /usr/local/lib/python3.11/site-packages/sqlglot/dialects/dialect.py:920, in Dialect.parse(self, sql, **opts)
    919 def parse(self, sql: str, **opts) -> t.List[t.Optional[exp.Expression]]:
--> 920     return self.parser(**opts).parse(self.tokenize(sql), sql)

File /usr/local/lib/python3.11/site-packages/sqlglot/parser.py:1416, in Parser.parse(self, raw_tokens, sql)
   1402 def parse(
   1403     self, raw_tokens: t.List[Token], sql: t.Optional[str] = None
   1404 ) -> t.List[t.Optional[exp.Expression]]:
   1405     """
   1406     Parses a list of tokens and returns a list of syntax trees, one tree
   1407     per parsed SQL statement.
   (...)
   1414         The list of the produced syntax trees.
   1415     """
-> 1416     return self._parse(
   1417         parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
   1418     )

File /usr/local/lib/python3.11/site-packages/sqlglot/parser.py:1488, in Parser._parse(self, parse_method, raw_tokens, sql)
   1485     expressions.append(parse_method(self))
   1487     if self._index < len(self._tokens):
-> 1488         self.raise_error("Invalid expression / Unexpected token")
   1490     self.check_errors()
   1492 return expressions

File /usr/local/lib/python3.11/site-packages/sqlglot/parser.py:1529, in Parser.raise_error(self, message, token)
   1517 error = ParseError.new(
   1518     f"{message}. Line {token.line}, Col: {token.col}.\n"
   1519     f"  {start_context}\033[4m{highlight}\033[0m{end_context}",
   (...)
   1525     end_context=end_context,
   1526 )
   1528 if self.error_level == ErrorLevel.IMMEDIATE:
-> 1529     raise error
   1531 self.errors.append(error)

ParseError: Invalid expression / Unexpected token. Line 6, Col: 6.
  r.id,
    event_date
  FROM consumer
  LEFT JOIN groceries on consumer.groceries_id = consumer.id
  PIVOT (
    max(type_id)
    FOR consumer_type
    IN
    (
    1,
    2,
    3,
    4
    )
  )

```

However, it can be parsed with workarounds: 
- when putting the `JOIN` after the pivot 
```
SELECT
    consumer.id,
    event_date
FROM consumer
PIVOT (
  max(type_id)
  FOR consumer_type
  IN
  (
  1,
  2,
  3,
  4
  )
)
LEFT JOIN groceries on consumer.groceries_id = consumer.id
```
- when putting the `JOIN` statement between parenthesis
```
SELECT
    consumer.id,
    event_date
FROM ( 
  consumer
  LEFT JOIN groceries on consumer.groceries_id = consumer.id
)
PIVOT (
  max(type_id)
  FOR consumer_type
  IN
  (
  1,
  2,
  3,
  4
  )
)
```

**Official Documentation**
Oracle PIVOT Doc -> https://docs.oracle.com/en/database/oracle/property-graph/21.4/spgdg/pivot.html

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index ddf009535d..6d31c43a6f 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2497,6 +2497,7 @@ class Join(Expression):
         "hint": False,
         "match_condition": False,  # Snowflake
         "expressions": False,
+        "pivots": False,
     }
 
     @property
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 7649b0f114..0fcae3984f 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2296,7 +2296,8 @@ def join_sql(self, expression: exp.Join) -> str:
         if op_sql != "STRAIGHT_JOIN":
             op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
 
-        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}"
+        pivots = self.expressions(expression, key="pivots", sep="", flat=True)
+        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}{pivots}"
 
     def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->") -> str:
         args = self.expressions(expression, flat=True)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a2c822bc00..dd4efbea6f 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3738,6 +3738,8 @@ def _parse_join(
 
             kwargs["this"].set("joins", joins if joins else None)
 
+        kwargs["pivots"] = self._parse_pivots()
+
         comments = [c for token in (method, side, kind) if token for c in token.comments]
         return self.expression(exp.Join, comments=comments, **kwargs)
 


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 2671d719a5..20b60766d0 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -47,6 +47,9 @@ def test_oracle(self):
         self.validate_identity("SELECT * FROM V$SESSION")
         self.validate_identity("SELECT TO_DATE('January 15, 1989, 11:00 A.M.')")
         self.validate_identity("SELECT INSTR(haystack, needle)")
+        self.validate_identity(
+            "SELECT * FROM consumer LEFT JOIN groceries ON consumer.groceries_id = consumer.id PIVOT(MAX(type_id) FOR consumer_type IN (1, 2, 3, 4))"
+        )
         self.validate_identity(
             "SELECT * FROM test UNPIVOT INCLUDE NULLS (value FOR Description IN (col AS 'PREFIX ' || CHR(38) || ' SUFFIX'))"
         )