You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Anupam Aggarwal <an...@gmail.com> on 2019/03/11 20:20:23 UTC
RelToSqlConverter support for EnumerableLimit
Hi,
We are using calcite to do MV rewrite, for we convert the original sql to
a relNode, optimize the relNode and reconvert it back to Sql (using
RelToSqlConverter)
Currently a query like this
*Select * from foo.bar where limit 10 ; *
Results in the following plan (after a MV rewrite to a different table)
EnumerableLimit(fetch=[1]): rowcount = 1.0, cumulative cost = {1.0 rows,
2.0 cpu, 0.0 io}, id = 303
EnumerableTableScan(table=[[viewSchema, barView]]): rowcount = 1.0,
cumulative cost = {0.0 rows, 1.0 cpu, 0.0 io}, id = 176
While converting the relNode to sql using RelToSqlConverter we get an
exception saying
Caused by: java.lang.reflect.InvocationTargetException
Caused by: java.lang.AssertionError: Need to implement
org.apache.calcite.adapter.enumerable.EnumerableLimit
Seems like RelToSqlConverter does not handle EnumerableLimit , Is this a bug ?
The fix is adding a visit method to RelToSqlConverter with
EnumerableLimit as the function argument.
public Result visit(EnumerableLimit e) {
Result x = visitChild(0, e.getInput());
Builder builder = x.builder(e, Clause.FETCH);
if (e.fetch != null) {
builder = x.builder(e, Clause.FETCH);
builder.setFetch(builder.context.toSql(null, e.fetch));
x = builder.result();
}
if (e.offset != null) {
builder = x.builder(e, Clause.OFFSET);
builder.setOffset(builder.context.toSql(null, e.offset));
x = builder.result();
}
return x;
}
Found some relevant discussion here -
https://jira.apache.org/jira/browse/CALCITE-1003.
Can you please help in understanding if this handling is correct? (or
if there is a better way )
Thanks
Anupam
Re: RelToSqlConverter support for EnumerableLimit
Posted by Anupam Aggarwal <an...@gmail.com>.
Thanks Stamatis, will try this.
On Tue, Mar 12, 2019 at 9:52 PM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hi Anupam,
>
> After the merge of Calcite-2827 [1] to master, it is possible to get as an
> ouput of the volcano planner a logical plan.
> Maybe using this new feature you can use the MV substitution without
> introducing physical operators.
>
> Best,
> Stamatis
>
> [1] https://issues.apache.org/jira/browse/CALCITE-2827
>
> Στις Τρί, 12 Μαρ 2019 στις 1:57 μ.μ., ο/η Anupam Aggarwal <
> anupam.aggarwal@gmail.com> έγραψε:
>
> > Thanks Julian.
> >
> > I am using VolcanoPlanner to do the MV rewrite, to get an optimised
> relNode
> > (and then convert to Sql)
> > (wasn't able to get the HepPlanner do the MV substitution using a logical
> > plan)
> > Logged a JIRA (will open a PR shortly)
> > https://jira.apache.org/jira/projects/CALCITE/issues/CALCITE-2915
> >
> > Thanks
> > Anupam
> >
> >
> > On Tue, Mar 12, 2019 at 2:28 AM Julian Hyde <jh...@apache.org> wrote:
> >
> > > It’s a bit unusual that you are converting physical algebra
> (enumerable)
> > > to SQL.
> > >
> > > EnumerableLimit only exists in physical algebra.
> > >
> > > In logical algebra, there is Sort, which includes an optional limit and
> > > offset. So, you could perhaps convert EnumerableLimit to Sort before
> > > calling RelToSqlConverter. Or add a handler to RelToSqlConverter that
> > does
> > > this internally.
> > >
> > > In any case, please log a JIRA case. Contributions welcome.
> > >
> > > Julian
> > >
> > >
> > > > On Mar 11, 2019, at 1:20 PM, Anupam Aggarwal <
> > anupam.aggarwal@gmail.com>
> > > wrote:
> > > >
> > > > Hi,
> > > >
> > > > We are using calcite to do MV rewrite, for we convert the original
> sql
> > > to
> > > > a relNode, optimize the relNode and reconvert it back to Sql (using
> > > > RelToSqlConverter)
> > > >
> > > > Currently a query like this
> > > >
> > > > *Select * from foo.bar where limit 10 ; *
> > > >
> > > > Results in the following plan (after a MV rewrite to a different
> table)
> > > >
> > > > EnumerableLimit(fetch=[1]): rowcount = 1.0, cumulative cost = {1.0
> > rows,
> > > > 2.0 cpu, 0.0 io}, id = 303
> > > > EnumerableTableScan(table=[[viewSchema, barView]]): rowcount = 1.0,
> > > > cumulative cost = {0.0 rows, 1.0 cpu, 0.0 io}, id = 176
> > > >
> > > > While converting the relNode to sql using RelToSqlConverter we get an
> > > > exception saying
> > > > Caused by: java.lang.reflect.InvocationTargetException
> > > > Caused by: java.lang.AssertionError: Need to implement
> > > > org.apache.calcite.adapter.enumerable.EnumerableLimit
> > > >
> > > > Seems like RelToSqlConverter does not handle EnumerableLimit , Is
> this
> > a
> > > bug ?
> > > > The fix is adding a visit method to RelToSqlConverter with
> > > > EnumerableLimit as the function argument.
> > > >
> > > >
> > > > public Result visit(EnumerableLimit e) {
> > > > Result x = visitChild(0, e.getInput());
> > > > Builder builder = x.builder(e, Clause.FETCH);
> > > >
> > > > if (e.fetch != null) {
> > > > builder = x.builder(e, Clause.FETCH);
> > > > builder.setFetch(builder.context.toSql(null, e.fetch));
> > > > x = builder.result();
> > > > }
> > > > if (e.offset != null) {
> > > > builder = x.builder(e, Clause.OFFSET);
> > > > builder.setOffset(builder.context.toSql(null, e.offset));
> > > > x = builder.result();
> > > > }
> > > > return x;
> > > > }
> > > >
> > > > Found some relevant discussion here -
> > > > https://jira.apache.org/jira/browse/CALCITE-1003.
> > > > Can you please help in understanding if this handling is correct? (or
> > > > if there is a better way )
> > > >
> > > > Thanks
> > > > Anupam
> > >
> > >
> >
>
Re: RelToSqlConverter support for EnumerableLimit
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Anupam,
After the merge of Calcite-2827 [1] to master, it is possible to get as an
ouput of the volcano planner a logical plan.
Maybe using this new feature you can use the MV substitution without
introducing physical operators.
Best,
Stamatis
[1] https://issues.apache.org/jira/browse/CALCITE-2827
Στις Τρί, 12 Μαρ 2019 στις 1:57 μ.μ., ο/η Anupam Aggarwal <
anupam.aggarwal@gmail.com> έγραψε:
> Thanks Julian.
>
> I am using VolcanoPlanner to do the MV rewrite, to get an optimised relNode
> (and then convert to Sql)
> (wasn't able to get the HepPlanner do the MV substitution using a logical
> plan)
> Logged a JIRA (will open a PR shortly)
> https://jira.apache.org/jira/projects/CALCITE/issues/CALCITE-2915
>
> Thanks
> Anupam
>
>
> On Tue, Mar 12, 2019 at 2:28 AM Julian Hyde <jh...@apache.org> wrote:
>
> > It’s a bit unusual that you are converting physical algebra (enumerable)
> > to SQL.
> >
> > EnumerableLimit only exists in physical algebra.
> >
> > In logical algebra, there is Sort, which includes an optional limit and
> > offset. So, you could perhaps convert EnumerableLimit to Sort before
> > calling RelToSqlConverter. Or add a handler to RelToSqlConverter that
> does
> > this internally.
> >
> > In any case, please log a JIRA case. Contributions welcome.
> >
> > Julian
> >
> >
> > > On Mar 11, 2019, at 1:20 PM, Anupam Aggarwal <
> anupam.aggarwal@gmail.com>
> > wrote:
> > >
> > > Hi,
> > >
> > > We are using calcite to do MV rewrite, for we convert the original sql
> > to
> > > a relNode, optimize the relNode and reconvert it back to Sql (using
> > > RelToSqlConverter)
> > >
> > > Currently a query like this
> > >
> > > *Select * from foo.bar where limit 10 ; *
> > >
> > > Results in the following plan (after a MV rewrite to a different table)
> > >
> > > EnumerableLimit(fetch=[1]): rowcount = 1.0, cumulative cost = {1.0
> rows,
> > > 2.0 cpu, 0.0 io}, id = 303
> > > EnumerableTableScan(table=[[viewSchema, barView]]): rowcount = 1.0,
> > > cumulative cost = {0.0 rows, 1.0 cpu, 0.0 io}, id = 176
> > >
> > > While converting the relNode to sql using RelToSqlConverter we get an
> > > exception saying
> > > Caused by: java.lang.reflect.InvocationTargetException
> > > Caused by: java.lang.AssertionError: Need to implement
> > > org.apache.calcite.adapter.enumerable.EnumerableLimit
> > >
> > > Seems like RelToSqlConverter does not handle EnumerableLimit , Is this
> a
> > bug ?
> > > The fix is adding a visit method to RelToSqlConverter with
> > > EnumerableLimit as the function argument.
> > >
> > >
> > > public Result visit(EnumerableLimit e) {
> > > Result x = visitChild(0, e.getInput());
> > > Builder builder = x.builder(e, Clause.FETCH);
> > >
> > > if (e.fetch != null) {
> > > builder = x.builder(e, Clause.FETCH);
> > > builder.setFetch(builder.context.toSql(null, e.fetch));
> > > x = builder.result();
> > > }
> > > if (e.offset != null) {
> > > builder = x.builder(e, Clause.OFFSET);
> > > builder.setOffset(builder.context.toSql(null, e.offset));
> > > x = builder.result();
> > > }
> > > return x;
> > > }
> > >
> > > Found some relevant discussion here -
> > > https://jira.apache.org/jira/browse/CALCITE-1003.
> > > Can you please help in understanding if this handling is correct? (or
> > > if there is a better way )
> > >
> > > Thanks
> > > Anupam
> >
> >
>
Re: RelToSqlConverter support for EnumerableLimit
Posted by Anupam Aggarwal <an...@gmail.com>.
Thanks Julian.
I am using VolcanoPlanner to do the MV rewrite, to get an optimised relNode
(and then convert to Sql)
(wasn't able to get the HepPlanner do the MV substitution using a logical
plan)
Logged a JIRA (will open a PR shortly)
https://jira.apache.org/jira/projects/CALCITE/issues/CALCITE-2915
Thanks
Anupam
On Tue, Mar 12, 2019 at 2:28 AM Julian Hyde <jh...@apache.org> wrote:
> It’s a bit unusual that you are converting physical algebra (enumerable)
> to SQL.
>
> EnumerableLimit only exists in physical algebra.
>
> In logical algebra, there is Sort, which includes an optional limit and
> offset. So, you could perhaps convert EnumerableLimit to Sort before
> calling RelToSqlConverter. Or add a handler to RelToSqlConverter that does
> this internally.
>
> In any case, please log a JIRA case. Contributions welcome.
>
> Julian
>
>
> > On Mar 11, 2019, at 1:20 PM, Anupam Aggarwal <an...@gmail.com>
> wrote:
> >
> > Hi,
> >
> > We are using calcite to do MV rewrite, for we convert the original sql
> to
> > a relNode, optimize the relNode and reconvert it back to Sql (using
> > RelToSqlConverter)
> >
> > Currently a query like this
> >
> > *Select * from foo.bar where limit 10 ; *
> >
> > Results in the following plan (after a MV rewrite to a different table)
> >
> > EnumerableLimit(fetch=[1]): rowcount = 1.0, cumulative cost = {1.0 rows,
> > 2.0 cpu, 0.0 io}, id = 303
> > EnumerableTableScan(table=[[viewSchema, barView]]): rowcount = 1.0,
> > cumulative cost = {0.0 rows, 1.0 cpu, 0.0 io}, id = 176
> >
> > While converting the relNode to sql using RelToSqlConverter we get an
> > exception saying
> > Caused by: java.lang.reflect.InvocationTargetException
> > Caused by: java.lang.AssertionError: Need to implement
> > org.apache.calcite.adapter.enumerable.EnumerableLimit
> >
> > Seems like RelToSqlConverter does not handle EnumerableLimit , Is this a
> bug ?
> > The fix is adding a visit method to RelToSqlConverter with
> > EnumerableLimit as the function argument.
> >
> >
> > public Result visit(EnumerableLimit e) {
> > Result x = visitChild(0, e.getInput());
> > Builder builder = x.builder(e, Clause.FETCH);
> >
> > if (e.fetch != null) {
> > builder = x.builder(e, Clause.FETCH);
> > builder.setFetch(builder.context.toSql(null, e.fetch));
> > x = builder.result();
> > }
> > if (e.offset != null) {
> > builder = x.builder(e, Clause.OFFSET);
> > builder.setOffset(builder.context.toSql(null, e.offset));
> > x = builder.result();
> > }
> > return x;
> > }
> >
> > Found some relevant discussion here -
> > https://jira.apache.org/jira/browse/CALCITE-1003.
> > Can you please help in understanding if this handling is correct? (or
> > if there is a better way )
> >
> > Thanks
> > Anupam
>
>
Re: RelToSqlConverter support for EnumerableLimit
Posted by Julian Hyde <jh...@apache.org>.
It’s a bit unusual that you are converting physical algebra (enumerable) to SQL.
EnumerableLimit only exists in physical algebra.
In logical algebra, there is Sort, which includes an optional limit and offset. So, you could perhaps convert EnumerableLimit to Sort before calling RelToSqlConverter. Or add a handler to RelToSqlConverter that does this internally.
In any case, please log a JIRA case. Contributions welcome.
Julian
> On Mar 11, 2019, at 1:20 PM, Anupam Aggarwal <an...@gmail.com> wrote:
>
> Hi,
>
> We are using calcite to do MV rewrite, for we convert the original sql to
> a relNode, optimize the relNode and reconvert it back to Sql (using
> RelToSqlConverter)
>
> Currently a query like this
>
> *Select * from foo.bar where limit 10 ; *
>
> Results in the following plan (after a MV rewrite to a different table)
>
> EnumerableLimit(fetch=[1]): rowcount = 1.0, cumulative cost = {1.0 rows,
> 2.0 cpu, 0.0 io}, id = 303
> EnumerableTableScan(table=[[viewSchema, barView]]): rowcount = 1.0,
> cumulative cost = {0.0 rows, 1.0 cpu, 0.0 io}, id = 176
>
> While converting the relNode to sql using RelToSqlConverter we get an
> exception saying
> Caused by: java.lang.reflect.InvocationTargetException
> Caused by: java.lang.AssertionError: Need to implement
> org.apache.calcite.adapter.enumerable.EnumerableLimit
>
> Seems like RelToSqlConverter does not handle EnumerableLimit , Is this a bug ?
> The fix is adding a visit method to RelToSqlConverter with
> EnumerableLimit as the function argument.
>
>
> public Result visit(EnumerableLimit e) {
> Result x = visitChild(0, e.getInput());
> Builder builder = x.builder(e, Clause.FETCH);
>
> if (e.fetch != null) {
> builder = x.builder(e, Clause.FETCH);
> builder.setFetch(builder.context.toSql(null, e.fetch));
> x = builder.result();
> }
> if (e.offset != null) {
> builder = x.builder(e, Clause.OFFSET);
> builder.setOffset(builder.context.toSql(null, e.offset));
> x = builder.result();
> }
> return x;
> }
>
> Found some relevant discussion here -
> https://jira.apache.org/jira/browse/CALCITE-1003.
> Can you please help in understanding if this handling is correct? (or
> if there is a better way )
>
> Thanks
> Anupam