Repository: tobymao/sqlglot

Issue:
Parsing oracle queries with ORDER SIBLING BY Clause
Hi Team,

I have observed that sqlglot parsing fails when oracle query has **order sibling by** clause.
SIBLINGS keyword is used to preserve ordering within the hierarchy.
eg.

```
SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name
```

**Documentation -**
https://docs.oracle.com/cd/B13789_01/server.101/b10759/queries003.htm

Request team to look into this. Thank you!

Thanks,
Shweta

Discussion:

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 3bd80976..76dc6fa7 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -108,6 +108,11 @@ class Oracle(Dialect):
             "XMLTABLE": _parse_xml_table,
         }
 
+        QUERY_MODIFIER_PARSERS = {
+            **parser.Parser.QUERY_MODIFIER_PARSERS,
+            TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
+        }
+
         TYPE_LITERAL_PARSERS = {
             exp.DataType.Type.DATE: lambda self, this, _: self.expression(
                 exp.DateStrToDate, this=this
@@ -239,6 +244,7 @@ class Oracle(Dialect):
             "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
             "MINUS": TokenType.EXCEPT,
             "NVARCHAR2": TokenType.NVARCHAR,
+            "ORDER SIBLINGS BY": TokenType.ORDER_SIBLINGS_BY,
             "SAMPLE": TokenType.TABLE_SAMPLE,
             "START": TokenType.BEGIN,
             "SYSDATE": TokenType.CURRENT_TIMESTAMP,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index de698c8c..fe912055 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1963,7 +1963,12 @@ class Offset(Expression):
 
 
 class Order(Expression):
-    arg_types = {"this": False, "expressions": True, "interpolate": False}
+    arg_types = {
+        "this": False,
+        "expressions": True,
+        "interpolate": False,
+        "siblings": False,
+    }
 
 
 # https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 41690a45..c67a2621 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1851,7 +1851,8 @@ class Generator:
     def order_sql(self, expression: exp.Order, flat: bool = False) -> str:
         this = self.sql(expression, "this")
         this = f"{this} " if this else this
-        order = self.op_expressions(f"{this}ORDER BY", expression, flat=this or flat)  # type: ignore
+        siblings = "SIBLINGS " if expression.args.get("siblings") else ""
+        order = self.op_expressions(f"{this}ORDER {siblings}BY", expression, flat=this or flat)  # type: ignore
         interpolated_values = [
             f"{self.sql(named_expression, 'alias')} AS {self.sql(named_expression, 'this')}"
             for named_expression in expression.args.get("interpolate") or []
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 85aef43b..fd4317e9 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3178,14 +3178,19 @@ class Parser(metaclass=_Parser):
     def _parse_order(
         self, this: t.Optional[exp.Expression] = None, skip_order_token: bool = False
     ) -> t.Optional[exp.Expression]:
+        siblings = None
         if not skip_order_token and not self._match(TokenType.ORDER_BY):
-            return this
+            if not self._match(TokenType.ORDER_SIBLINGS_BY):
+                return this
+
+            siblings = True
 
         return self.expression(
             exp.Order,
             this=this,
             expressions=self._parse_csv(self._parse_ordered),
             interpolate=self._parse_interpolate(),
+            siblings=siblings,
         )
 
     def _parse_sort(self, exp_class: t.Type[E], token: TokenType) -> t.Optional[E]:
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index de9d4c4a..669b2bc3 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -296,6 +296,7 @@ class TokenType(AutoName):
     ON = auto()
     OPERATOR = auto()
     ORDER_BY = auto()
+    ORDER_SIBLINGS_BY = auto()
     ORDERED = auto()
     ORDINALITY = auto()
     OUTER = auto()


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 62320e5f..d3fb6321 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -35,6 +35,9 @@ class TestOracle(Validator):
         self.validate_identity("SELECT * FROM table_name SAMPLE (25) s")
         self.validate_identity("SELECT COUNT(*) * 10 FROM orders SAMPLE (10) SEED (1)")
         self.validate_identity("SELECT * FROM V$SESSION")
+        self.validate_identity(
+            "SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name"
+        )
         self.validate_identity(
             "ALTER TABLE Payments ADD (Stock NUMBER NOT NULL, dropid VARCHAR2(500) NOT NULL)"
         )