Hi everyone, Following up on the SEARCH support discussed earlier in this thread, I've now opened a PR for CALCITE-7815:
https://github.com/apache/calcite/pull/5370 It adds SEARCH DEPTH FIRST and SEARCH BREADTH FIRST for recursive CTEs, including use together with CYCLE, with Java and SQL tests and documentation. Could someone please take this on for review? On Thu, 08 Oct 2026 07:44:36 +0200, Alessandro Solimando < [email protected]> wrote: Hey Vladislav, Thanks for your contribution and happy to hear there is more to come! Ruben has write access so the PR can be merged already. It's common to wait for some extra time to give people a chance to chime in before merging. This time can be a bit longer if the contribution is larger/complex. I think you can safely assume the PR will be merged shortly and move to the next one locally. Stacked PRs are also a good way to keep making progress over multiple connected PRs. Best regards, Alessandro On Thu, Oct 8, 2026, 01:08 Vladislav Pyatkov <[email protected]> wrote: > Hi everyone, > > Ruben has reviewed the updated PR and given it an LGTM. Does anyone else > have any feedback, or could a committer merge it when convenient? > > https://github.com/apache/calcite/pull/5294 > > I'd like to wrap this up and move on to the next contribution. > > I hope I’m not being too persistent, but full support for standard SQL > recursive CTE syntax in Calcite is important to me, and I’d like to keep > contributing toward that goal. > > On Mon, Oct 5, 2026 at 1:28 PM Vladislav Pyatkov < [email protected]> > wrote: > > > Hi everyone, > > > > Thanks to Ruben for reviewing the PR. I've addressed all the comments and > > added the requested tests. > > > > Could someone take another look and let me know if anything else is > needed > > before merging? > > https://github.com/apache/calcite/pull/5294 > > > > On Thu, Oct 1, 2026 at 1:12 PM Vladislav Pyatkov <[email protected]> > > wrote: > > > >> 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 > >> > > > > > > -- > > Vladislav Pyatkov > > > > > -- > Vladislav Pyatkov >
