Repository: tobymao/sqlglot

Issue:
Unable to parse the oracle query when query has CONNECT BY followed by START WITH clause
Hello Team,
Warm greetings!

Parser is throwing an error when there is CONNECT BY followed by START WITH clause!

**Oracle Documentation -**
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E
![image](https://github.com/tobymao/sqlglot/assets/142426118/ce13a736-cb2d-4924-9e4c-e40fe2ae34c5)


**Code for your reference -**
```

import sqlglot.expressions as exp

# works fine
query1 ="""
SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
"""
# Doesn't work
query2 ="""
SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
   START WITH last_name = 'King'
"""

expressions = sqlglot.parse_one(query2, read='oracle')
x = list(expressions.find_all(exp.Table))

for i in x:
  listTables = str(i).split(' ')[0]
  print(listTables)
```

Note that above queries are valid oracle queries.

query2 fails as START WITH clause is after the connect by clause.
Request you to add support for the same.

Thanks ton in advance!

Thanks,
Sachin

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 121698af..025e66fd 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -130,6 +130,7 @@ class MySQL(Dialect):
             "ENUM": TokenType.ENUM,
             "FORCE": TokenType.FORCE,
             "IGNORE": TokenType.IGNORE,
+            "LOCK TABLES": TokenType.COMMAND,
             "LONGBLOB": TokenType.LONGBLOB,
             "LONGTEXT": TokenType.LONGTEXT,
             "MEDIUMBLOB": TokenType.MEDIUMBLOB,
@@ -142,6 +143,7 @@ class MySQL(Dialect):
             "START": TokenType.BEGIN,
             "SIGNED": TokenType.BIGINT,
             "SIGNED INTEGER": TokenType.BIGINT,
+            "UNLOCK TABLES": TokenType.COMMAND,
             "UNSIGNED": TokenType.UBIGINT,
             "UNSIGNED INTEGER": TokenType.UBIGINT,
             "YEAR": TokenType.YEAR,
@@ -373,7 +375,7 @@ class MySQL(Dialect):
                 self._match_texts({"INDEX", "KEY"})
 
             this = self._parse_id_var(any_token=False)
-            type_ = self._match(TokenType.USING) and self._advance_any() and self._prev.text
+            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
             schema = self._parse_schema()
 
             options = []
@@ -413,7 +415,7 @@ class MySQL(Dialect):
                 this=this,
                 schema=schema,
                 kind=kind,
-                type=type_,
+                index_type=index_type,
                 options=options,
             )
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 98afddc8..3b39efb8 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1321,7 +1321,13 @@ class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 
 # https://dev.mysql.com/doc/refman/8.0/en/create-table.html
 class IndexColumnConstraint(ColumnConstraintKind):
-    arg_types = {"this": False, "schema": True, "kind": False, "type": False, "options": False}
+    arg_types = {
+        "this": False,
+        "schema": True,
+        "kind": False,
+        "index_type": False,
+        "options": False,
+    }
 
 
 class InlineLengthColumnConstraint(ColumnConstraintKind):
@@ -1354,7 +1360,7 @@ class TitleColumnConstraint(ColumnConstraintKind):
 
 
 class UniqueColumnConstraint(ColumnConstraintKind):
-    arg_types = {"this": False}
+    arg_types = {"this": False, "index_type": False}
 
 
 class UppercaseColumnConstraint(ColumnConstraintKind):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 399b48bc..d086e8ab 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -705,7 +705,9 @@ class Generator:
     def uniquecolumnconstraint_sql(self, expression: exp.UniqueColumnConstraint) -> str:
         this = self.sql(expression, "this")
         this = f" {this}" if this else ""
-        return f"UNIQUE{this}"
+        index_type = expression.args.get("index_type")
+        index_type = f" USING {index_type}" if index_type else ""
+        return f"UNIQUE{this}{index_type}"
 
     def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> str:
         return self.sql(expression, "this")
@@ -2740,13 +2742,13 @@ class Generator:
         kind = f"{kind} INDEX" if kind else "INDEX"
         this = self.sql(expression, "this")
         this = f" {this}" if this else ""
-        type_ = self.sql(expression, "type")
-        type_ = f" USING {type_}" if type_ else ""
+        index_type = self.sql(expression, "index_type")
+        index_type = f" USING {index_type}" if index_type else ""
         schema = self.sql(expression, "schema")
         schema = f" {schema}" if schema else ""
         options = self.expressions(expression, key="options", sep=" ")
         options = f" {options}" if options else ""
-        return f"{kind}{this}{type_}{schema}{options}"
+        return f"{kind}{this}{index_type}{schema}{options}"
 
     def nvl2_sql(self, expression: exp.Nvl2) -> str:
         if self.NVL2_SUPPORTED:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a3dcc493..111c4bc2 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -848,6 +848,8 @@ class Parser(metaclass=_Parser):
     WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER}
     WINDOW_SIDES = {"FOLLOWING", "PRECEDING"}
 
+    FETCH_TOKENS = ID_VAR_TOKENS - {TokenType.ROW, TokenType.ROWS, TokenType.PERCENT}
+
     ADD_CONSTRAINT_TOKENS = {TokenType.CONSTRAINT, TokenType.PRIMARY_KEY, TokenType.FOREIGN_KEY}
 
     DISTINCT_TOKENS = {TokenType.DISTINCT}
@@ -2913,6 +2915,10 @@ class Parser(metaclass=_Parser):
         )
         connect = self._parse_conjunction()
         self.NO_PAREN_FUNCTION_PARSERS.pop("PRIOR")
+
+        if not start and self._match(TokenType.START_WITH):
+            start = self._parse_conjunction()
+
         return self.expression(exp.Connect, start=start, connect=connect)
 
     def _parse_order(
@@ -2984,7 +2990,7 @@ class Parser(metaclass=_Parser):
             direction = self._match_set((TokenType.FIRST, TokenType.NEXT))
             direction = self._prev.text if direction else "FIRST"
 
-            count = self._parse_number()
+            count = self._parse_field(tokens=self.FETCH_TOKENS)
             percent = self._match(TokenType.PERCENT)
 
             self._match_set((TokenType.ROW, TokenType.ROWS))
@@ -3815,7 +3821,9 @@ class Parser(metaclass=_Parser):
     def _parse_unique(self) -> exp.UniqueColumnConstraint:
         self._match_text_seq("KEY")
         return self.expression(
-            exp.UniqueColumnConstraint, this=self._parse_schema(self._parse_id_var(any_token=False))
+            exp.UniqueColumnConstraint,
+            this=self._parse_schema(self._parse_id_var(any_token=False)),
+            index_type=self._match(TokenType.USING) and self._advance_any() and self._prev.text,
         )
 
     def _parse_key_constraint_options(self) -> t.List[str]:


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 802a073d..9deccbfb 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -19,6 +19,7 @@ class TestMySQL(Validator):
             },
         )
 
+        self.validate_identity("CREATE TABLE foo (a BIGINT, UNIQUE (b) USING BTREE)")
         self.validate_identity("CREATE TABLE foo (id BIGINT)")
         self.validate_identity("CREATE TABLE 00f (1d BIGINT)")
         self.validate_identity("UPDATE items SET items.price = 0 WHERE items.id >= 5 LIMIT 10")
@@ -107,9 +108,8 @@ class TestMySQL(Validator):
         )
 
     def test_identity(self):
-        self.validate_identity(
-            "SELECT * FROM x ORDER BY BINARY a", "SELECT * FROM x ORDER BY CAST(a AS BINARY)"
-        )
+        self.validate_identity("UNLOCK TABLES")
+        self.validate_identity("LOCK TABLES `app_fields` WRITE")
         self.validate_identity("SELECT 1 XOR 0")
         self.validate_identity("SELECT 1 && 0", "SELECT 1 AND 0")
         self.validate_identity("SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2")
@@ -133,6 +133,9 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE A LIKE B")
         self.validate_identity("SELECT * FROM t1, t2 FOR SHARE OF t1, t2 SKIP LOCKED")
         self.validate_identity("SELECT a || b", "SELECT a OR b")
+        self.validate_identity(
+            "SELECT * FROM x ORDER BY BINARY a", "SELECT * FROM x ORDER BY CAST(a AS BINARY)"
+        )
         self.validate_identity(
             """SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))"""
         )
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 2dfd1796..b497caa4 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -50,6 +50,10 @@ class TestOracle(Validator):
             "SELECT UNIQUE col1, col2 FROM table",
             "SELECT DISTINCT col1, col2 FROM table",
         )
+        self.validate_identity(
+            "SELECT * FROM T ORDER BY I OFFSET nvl(:variable1, 10) ROWS FETCH NEXT nvl(:variable2, 10) ROWS ONLY",
+            "SELECT * FROM T ORDER BY I OFFSET COALESCE(:variable1, 10) ROWS FETCH NEXT COALESCE(:variable2, 10) ROWS ONLY",
+        )
 
         self.validate_all(
             "NVL(NULL, 1)",
@@ -218,3 +222,25 @@ INNER JOIN JSON_TABLE(:emps, '$[*]' COLUMNS (empno NUMBER PATH '$')) jt
   ON ar.empno = jt.empno""",
             pretty=True,
         )
+
+    def test_connect_by(self):
+        start = "START WITH last_name = 'King'"
+        connect = "CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4"
+        body = """
+            SELECT last_name "Employee",
+            LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
+            FROM employees
+            WHERE level <= 3 AND department_id = 80
+        """
+        pretty = """SELECT
+  last_name AS "Employee",
+  LEVEL,
+  SYS_CONNECT_BY_PATH(last_name, '/') AS "Path"
+FROM employees
+START WITH last_name = 'King'
+CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
+WHERE
+  level <= 3 AND department_id = 80"""
+
+        for query in (f"{body}{start}{connect}", f"{body}{connect}{start}"):
+            self.validate_identity(query, pretty, pretty=True)