You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Shawn Yarbrough <sh...@heavy.ai> on 2022/04/05 20:32:41 UTC
current status of WITH RECURSIVE?
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
Re: current status of WITH RECURSIVE?
Posted by Yanjing Wang <zh...@gmail.com>.
Hi Shawn,
I meet the same problem with you, but changes on the parser and sqlNode
converted to sql will satisfy my scenarios, have you started work on them?
If so, could you share your work? Otherwise, I will have a try on it and
share in the thread.
Shawn Yarbrough <sh...@heavy.ai> 于2022年4月6日周三 08:34写道：
> 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 <ru...@gmail.com> 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 <shawn.yarbrough@heavy.ai
> >
> > 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
> > >
> >
>
Re: current status of WITH RECURSIVE?
Posted by Shawn Yarbrough <sh...@heavy.ai>.
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 <ru...@gmail.com> 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 <sh...@heavy.ai>
> 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
> >
>
Re: current status of WITH RECURSIVE?
Posted by Ruben Q L <ru...@gmail.com>.
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 <sh...@heavy.ai>
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
>