Hi Ruben, Helpful summary, thanks!
Looking at CALCITE-2812, it's encouraging that so much of the recursion feature has already been completed, such as the planner changes. I'm willing to attempt the SqlToRelConverter.java changes to complete the SQL side of this feature and I'd appreciate any suggestions you or others can provide about that. Best, Shawn On Tue, Apr 5, 2022 at 3:24 PM Ruben Q L <[email protected]> wrote: > Hello Shawn, > > Recursive queries are supported in Calcite using the RelBuilder API (with > the experimental RepeatUnion operator, implemented in [1]), as explained in > the documentation link that you sent. > However, the SqlToRel [2] (and the RelToSql [3]) features for this operator > are not implemented yet, i.e. you can build your recursive query via > RelBuilder, but you cannot create it from a "WITH RECURSIVE..." sql query. > In order to support your sql example, [2] would need to be implemented. > > Best, > Ruben > > [1] https://issues.apache.org/jira/browse/CALCITE-2812 > [2] https://issues.apache.org/jira/browse/CALCITE-129 > [3] https://issues.apache.org/jira/browse/CALCITE-4356 > > On Tue, Apr 5, 2022 at 9:33 PM Shawn Yarbrough <[email protected]> > wrote: > > > Hi, > > > > I'm curious about the current status of the Recursive Queries feature > > described on the Algebra documentation page: > > https://calcite.apache.org/docs/algebra.html#recursive-queries > > > > Those docs show some example recursive SQL that doesn't seem to work > either > > with the example/csv demo or with my downstream DBMS project. For example > > this simplified SQL fails: > > > > > WITH RECURSIVE aux AS (VALUES (1) UNION ALL VALUES (2)) SELECT * FROM > > aux; > > Error: Error while executing SQL "WITH RECURSIVE aux AS (VALUES (1) UNION > > ALL VALUES (2)) SELECT * FROM aux": parse failed: Incorrect syntax near > the > > keyword 'RECURSIVE' at line 1, column 6. > > Was expecting one of: > > <BRACKET_QUOTED_IDENTIFIER> ... > > <QUOTED_IDENTIFIER> ... > > <BACK_QUOTED_IDENTIFIER> ... > > <BIG_QUERY_BACK_QUOTED_IDENTIFIER> ... > > <HYPHENATED_IDENTIFIER> ... > > <IDENTIFIER> ... > > <UNICODE_QUOTED_IDENTIFIER> ... (state=,code=0) > > > > But simply removing the RECURSIVE keyword allows the remaining SQL to > > succeed: > > > > > WITH aux AS (VALUES (1) UNION ALL VALUES (2)) SELECT * FROM aux; > > +--------+ > > | EXPR$0 | > > +--------+ > > | 1 | > > | 2 | > > +--------+ > > 2 rows selected (0.043 seconds) > > > > I see ticket CALCITE-129 from 2014 describing recursive WITH queries is > > open, but reading through the email archives suggests it might be close > to > > completion. > > https://issues.apache.org/jira/browse/CALCITE-129 > > > > What remains to be done to fully support recursive queries and how might > I > > be able to help? > > > > Best, > > Shawn Yarbrough > > >
