Repository: tobymao/sqlglot

Issue:
Oracle PRIOR ... CONNECT BY Token failed to parse
**Problem**
The `PRIOR` Token is not accepted when inside a `SELECT` statement: 
```
sql_request = """"
select 
    id,
    PRIOR name AS parent_name,
    name
from family_tree
connect by nocycle
    prior id = parent_id
"""
parsed = parse_one(sql_request, read="oracle")
```
I got the following error: 
```
ParseError                                Traceback (most recent call last)
Cell In[8], 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 3, Col: 14.
  select 
    id,
    PRIOR name AS parent_name,
    name
from family_tree
connect by nocycle
    prior id = parent_id
```
It is useful here to query the name of the parent next to the child name, in the `family_tree` table.

However, the parsing works just find when skipping the `PRIOR` Token with this query:
```
select 
    id,
    name
from family_tree
connect by nocycle
    id = parent_id
```
This should not be working because a `CONNECT BY` condition must be qualified by the `PRIOR` operator.

**Official Documentation**
- Oracle Doc about the `CONNECT BY`  statement-> https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E
- Oracle Doc specifying that the `PRIOR` token has to exist inside the `CONNECT BY` clause -> https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Hierarchical-Query-Operators.html#GUID-4CC13EEB-846A-4254-93FC-E91E678BD302
- Stackoverflow thread about `PRIOR` use in `SELECT` statement -> https://stackoverflow.com/questions/35826092/prior-in-select-list

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index e183db727d..c0be24c7d1 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -126,6 +126,7 @@ class Parser(parser.Parser):
         NO_PAREN_FUNCTION_PARSERS = {
             **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
             "NEXT": lambda self: self._parse_next_value_for(),
+            "PRIOR": lambda self: self.expression(exp.Prior, this=self._parse_bitwise()),
             "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True),
         }
 
@@ -249,6 +250,9 @@ def _parse_into(self) -> t.Optional[exp.Into]:
 
             return self.expression(exp.Into, bulk_collect=bulk_collect, expressions=expressions)
 
+        def _parse_connect_with_prior(self):
+            return self._parse_assignment()
+
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
         JOIN_HINTS = False
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index dd4efbea6f..1e7b0d0ec8 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4463,6 +4463,14 @@ def _parse_qualify(self) -> t.Optional[exp.Qualify]:
             return None
         return self.expression(exp.Qualify, this=self._parse_assignment())
 
+    def _parse_connect_with_prior(self) -> t.Optional[exp.Expression]:
+        self.NO_PAREN_FUNCTION_PARSERS["PRIOR"] = lambda self: self.expression(
+            exp.Prior, this=self._parse_bitwise()
+        )
+        connect = self._parse_assignment()
+        self.NO_PAREN_FUNCTION_PARSERS.pop("PRIOR")
+        return connect
+
     def _parse_connect(self, skip_start_token: bool = False) -> t.Optional[exp.Connect]:
         if skip_start_token:
             start = None
@@ -4473,11 +4481,7 @@ def _parse_connect(self, skip_start_token: bool = False) -> t.Optional[exp.Conne
 
         self._match(TokenType.CONNECT_BY)
         nocycle = self._match_text_seq("NOCYCLE")
-        self.NO_PAREN_FUNCTION_PARSERS["PRIOR"] = lambda self: self.expression(
-            exp.Prior, this=self._parse_bitwise()
-        )
-        connect = self._parse_assignment()
-        self.NO_PAREN_FUNCTION_PARSERS.pop("PRIOR")
+        connect = self._parse_connect_with_prior()
 
         if not start and self._match(TokenType.START_WITH):
             start = self._parse_assignment()


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 20b60766d0..e8339ac920 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -1,4 +1,4 @@
-from sqlglot import exp, UnsupportedError
+from sqlglot import exp, UnsupportedError, ParseError, parse_one
 from tests.dialects.test_dialect import Validator
 
 
@@ -708,3 +708,11 @@ def test_analyze(self):
         self.validate_identity(
             "ANALYZE TABLE tbl VALIDATE STRUCTURE CASCADE COMPLETE OFFLINE INTO db.tbl"
         )
+
+    def test_prior(self):
+        self.validate_identity(
+            "SELECT id, PRIOR name AS parent_name, name FROM tree CONNECT BY NOCYCLE PRIOR id = parent_id"
+        )
+
+        with self.assertRaises(ParseError):
+            parse_one("PRIOR as foo", read="oracle")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 1369bc1fdf..de063d0918 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1055,6 +1055,12 @@ def test_snowflake(self):
             },
         )
 
+        with self.assertRaises(ParseError):
+            parse_one(
+                "SELECT id, PRIOR name AS parent_name, name FROM tree CONNECT BY NOCYCLE PRIOR id = parent_id",
+                dialect="snowflake",
+            )
+
     def test_null_treatment(self):
         self.validate_all(
             r"SELECT FIRST_VALUE(TABLE1.COLUMN1) OVER (PARTITION BY RANDOM_COLUMN1, RANDOM_COLUMN2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MY_ALIAS FROM TABLE1",