Julian, Hi Julian,
Thank you for the detailed feedback. I'll track CYCLE in a single Jira issue and prepare a single PR, ready to be merged as one squashed commit, with support for executing queries. I'll keep your advice in mind and make sure to add enough tests, including cases where validation should fail, and check that the error messages are clear. Thanks for pointing out SEARCH as well. I came across CYCLE while adapting a query from another database, so I may also encounter a need for SEARCH. I've created the following Jira issues: CYCLE - https://issues.apache.org/jira/browse/CALCITE-7814 SEARCH - https://issues.apache.org/jira/browse/CALCITE-7815 On Wed, Sep 23, 2026 at 11:25 PM Julian Hyde <[email protected]> wrote: > Wow, I thought I knew what was in the SQL standard! It was optional in > SQL-1999. As of 2021, only Oracle and DB2 implemented it, but now > Postgres does also. MariaDB supports a non-standard variation; DuckDB, > SQLite, BigQuery and Snowflake do not support it. > > Yes, it would be great if you contributed this feature. > > Developing using those 4 subtasks makes sense, but I see this landing > as a single squashed commit, under a single Jira case. The commit > should be able to execute queries. (Maybe you extend one of the > physical operators that implement enumerable convention, or maybe you > can remove CYCLE using a rewrite rule or desugaring.) Be sure to add > negative validation tests (i.e. ensure good messages if the user does > something wrong). Devise a simple example query (say using a handful > of employee rows, or a directed graph with A connects to B, B connects > to C, etc.) so that people can learn by example. > > You should log a case for adding SEARCH support also. No need to start > work on fixing it; it's just a placeholder for future discussions; > include a simple example query. > > Julian > > > > On Wed, Sep 23, 2026 at 12:43 PM Mihai Budiu <[email protected]> wrote: > > > > If it's standard SQL it makes sense for Calcite to support it. > > > > Your proposal for the work breakdown makes sense; smaller PRs are always > easier to review. > > > > Mihai > > ________________________________ > > From: Vladislav Pyatkov <[email protected]> > > Sent: Wednesday, September 23, 2026 7:29 AM > > To: [email protected] <[email protected]> > > Subject: [DISCUSS] Support SQL-standard CYCLE clause in recursive CTEs > > > > Hi everyone, > > > > My name is Vladislav Pyatkov. I have been involved in maintaining Apache > > Ignite for a long time. Ignite uses Calcite for SQL parsing and query > > analysis, and I’m interested in contributing to Calcite’s development. > > > > I’d like to add support for the SQL-standard CYCLE clause in recursive > > CTEs. Calcite currently supports WITH RECURSIVE, but does not accept this > > clause. > > The proposed syntax, following an individual CTE’s AS (...) definition, > is: > > > > CYCLE column_name [, column_name ...] > > SET mark_column TO mark_value DEFAULT default_value > > USING path_column > > > > This detects repeated keys along each recursive path, adds cycle-mark and > > path columns, and prevents further expansion from a cycle-closing row > while > > retaining that row in the result. > > I propose representing the clause as an optional node attached to > > *SqlWithItem*: > > > > SqlWith > > ├── withList > > │ └── SqlWithItem > > │ ├── name, columnList, recursive > > │ ├── query: seed UNION [ALL] recursive_term > > │ └── cycle: SqlCycleClause > > │ ├── columns > > │ ├── markColumn > > │ ├── markValue > > │ ├── defaultValue > > │ └── pathColumn > > └── body > > > > I’d suggest tracking the work in an umbrella Jira issue with four > subtasks: > > > > 1. Parsing, AST representation, and unparsing. > > 2. Semantic validation and type derivation. > > 3. SQL-to-relational conversion. > > 4. Enumerable execution support and end-to-end tests. > > > > I would start with the first subtask, with validation explicitly > rejecting > > *CYCLE* until its processing is implemented. For relational conversion, > I’d > > initially explore reusing *LogicalRepeatUnion* with additional > projections, > > filters, and path expressions. > > > > Does this scope and incremental approach make sense? Is there existing > work > > or a preferred design that I should build on? > > -- > > Vladislav Pyatkov > -- Vladislav Pyatkov
