Hi everyone, The PR for CYCLE support in recursive CTEs is available for review:
PR: https://github.com/apache/calcite/pull/5294 The implementation includes parsing, validation, query rewriting, and enumerable execution, with tests and documentation. Could someone take a look? Feedback on the rewriting approach and supported query shapes would be especially helpful. On Thu, Sep 24, 2026 at 12:28 AM Vladislav Pyatkov <[email protected]> wrote: > 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 > -- Vladislav Pyatkov
