Hi, Just a gentle follow-up on the CYCLE support PR: https://github.com/apache/calcite/pull/5294
I'd appreciate a review when someone has time. On Sun, Sep 27, 2026 at 9:10 PM Vladislav Pyatkov <[email protected]> wrote: > 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 > -- Vladislav Pyatkov
