Repository: tobymao/sqlglot

Issue:
Oracle queries with keyword NOCYCLE not supported in CONNECT BY NOCYCLE PRIOR
**Version**
sqlglot 23.2.1.dev9

**Notes**
The query is working without the NOCYCLE keyword.

**Fully reproducible code snippet**
_Oracle DB_

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    manager_id NUMBER
);

-- Insertion de données de test
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'John Doe', NULL);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (3, 'Alice Johnson', 2);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (4, 'Bob Brown', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (5, 'Eva Lee', 4);


_Python:_
import sqlglot

sqlglot.parse_one(sql="""SELECT employee_id, employee_name, manager_id
FROM test_employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;""", read='oracle')

**Error message**

Traceback (most recent call last):
  File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/pydevconsole.py", line 364, in runcode
    coro = func()
           ^^^^^^
  File "<input>", line 1, in <module>
  File "/usr/local/lib/python3.11/site-packages/sqlglot/__init__.py", line 124, in parse_one
    result = dialect.parse(sql, **opts)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 490, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1163, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1232, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1273, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 4, Col: 24.
  _id, employee_name, manager_id
FROM test_employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

** Oracle Documentation **
https://docs.oracle.com/cd/B13789_01/server.101/b10759/queries003.htm

![hierarchical_query_clause](https://github.com/tobymao/sqlglot/assets/3640809/29503296-7deb-4977-98b3-744d27dc629b)

Discussion:

Patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 5ca0d5d2..742dfcea 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1839,7 +1839,7 @@ class Check(Expression):
 
 # https://docs.snowflake.com/en/sql-reference/constructs/connect-by
 class Connect(Expression):
-    arg_types = {"start": False, "connect": True}
+    arg_types = {"start": False, "connect": True, "nocycle": False}
 
 
 class Prior(Expression):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 99a3f120..caec78d8 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1846,8 +1846,9 @@ class Generator(metaclass=_Generator):
     def connect_sql(self, expression: exp.Connect) -> str:
         start = self.sql(expression, "start")
         start = self.seg(f"START WITH {start}") if start else ""
+        nocycle = " NOCYCLE" if expression.args.get("nocycle") else ""
         connect = self.sql(expression, "connect")
-        connect = self.seg(f"CONNECT BY {connect}")
+        connect = self.seg(f"CONNECT BY{nocycle} {connect}")
         return start + connect
 
     def prior_sql(self, expression: exp.Prior) -> str:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 01d13346..b380875f 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3505,6 +3505,7 @@ class Parser(metaclass=_Parser):
             return None
 
         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()
         )
@@ -3514,7 +3515,7 @@ class Parser(metaclass=_Parser):
         if not start and self._match(TokenType.START_WITH):
             start = self._parse_conjunction()
 
-        return self.expression(exp.Connect, start=start, connect=connect)
+        return self.expression(exp.Connect, start=start, connect=connect, nocycle=nocycle)
 
     def _parse_name_as_expression(self) -> exp.Alias:
         return self.expression(


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index d1033e47..526b0b5a 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -94,6 +94,9 @@ class TestOracle(Validator):
             "SELECT * FROM t SAMPLE (0.25)",
         )
         self.validate_identity("SELECT TO_CHAR(-100, 'L99', 'NL_CURRENCY = '' AusDollars '' ')")
+        self.validate_identity(
+            "SELECT * FROM t START WITH col CONNECT BY NOCYCLE PRIOR col1 = col2"
+        )
 
         self.validate_all(
             "CURRENT_TIMESTAMP BETWEEN TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND TO_DATE(f.C_EDATE, 'yyyy/mm/dd')",