P2: Oracle CONNECT BY / hierarchical query parsing (START WITH, PRIOR, NOCYCLE)

[ajitpratap0]

Problem

Oracle hierarchical queries (CONNECT BY, START WITH, PRIOR, NOCYCLE) are among the most commonly used Oracle-specific features for traversing tree-structured data (org charts, bill-of-materials, filesystem hierarchies). Keywords are reserved since v1.9.0 but parsing is not implemented.

This also affects MariaDB (added in v1.14.0) which has Oracle-compatible CONNECT BY syntax.

Syntax to Support

-- Basic hierarchical query
SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id

-- With NOCYCLE (for cyclic data)
SELECT *
FROM categories
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR id = parent_id

-- With SYS_CONNECT_BY_PATH and CONNECT_BY_ISLEAF
SELECT SYS_CONNECT_BY_PATH(name, '/') AS path,
       CONNECT_BY_ISLEAF AS is_leaf,
       CONNECT_BY_ISCYCLE AS is_cycle
FROM tree
START WITH parent IS NULL
CONNECT BY PRIOR id = parent

-- ORDER SIBLINGS BY
SELECT id, name, LEVEL
FROM hierarchy
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY name

AST Nodes Needed

type ConnectByClause struct {
    StartWith  Expression        // START WITH condition (optional)
    ConnectBy  Expression        // CONNECT BY condition
    NoCycle    bool              // NOCYCLE keyword present
    Prior      *PriorExpression  // PRIOR pseudo-column wrapper
}

type PriorExpression struct {
    Expr Expression  // the expression after PRIOR
}

// SelectStatement addition
type SelectStatement struct {
    // ...existing fields...
    ConnectBy *ConnectByClause  // Oracle/MariaDB hierarchical
}

// Special functions
// LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE, CONNECT_BY_ROOT

Acceptance Criteria

• START WITH condition parsed (optional)
• CONNECT BY condition parsed
• CONNECT BY NOCYCLE condition parsed
• PRIOR as a unary operator in expressions
• LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE as pseudo-column identifiers
• SYS_CONNECT_BY_PATH(col, sep) as a function call
• ORDER SIBLINGS BY clause
• Formatter renders CONNECT BY correctly
• Works in both Oracle and MariaDB dialect modes
• 30+ test cases
• docs/SQL_COMPATIBILITY.md updated