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
