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 >
