You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Pranav Deshpande <de...@gmail.com> on 2022/08/19 18:12:37 UTC
How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Hi Team,
How can I convert a BindableTableScan with projects and Filters back to a
RelTree with a project node, a filter node and a tablescan node?
I am doing this because I encountered the following issue (steps detailed
below).
1. I have a query:eg. Select colA, colB from myTable where colA > 1;
2. The logical plan for this is a filter on top of a project on top of a
TableScan.
3. If the table we created is a ProjectableFilterableTable and we have
added the ProjectTableScan rule etc., then after optimization we have a
single node which is a tablescan that contains filters and project and can
be executed by the bindable convention.
4. If I convert this node to a SQL, I get the wrong SQL.
To get a SQL, I do the following:
RelToSqlConverter converter = new
RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
This only gives me Select * from myTable;
However, if I only have a ScannableTable and we haven't added the
ProjectTableScan rule etc. then for the physical plan we also get a
BindableFilter on top of a BindableProject on top of a BindableTablescan.
If i convert this back using the same code then I get the correct statement
back: Select colA, colB from myTable where colA > 1
Hence I would like to know how to convert a BindableTableScan with projects
and Filters back to a RelTree with a project node, a filter node and a
tablescan node & would appreciate the community's help on the same.
Thanks & Regards,
Pranav
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Jiajun Xie <ji...@gmail.com>.
Hi benchao,
I want to fix the bug, can I log a jira issue?
On Sat, 20 Aug 2022 at 11:13, Benchao Li <li...@apache.org> wrote:
> Pranav,
>
> This is a good question. To me, I would take this as a bug, and we could
> improve the
> RelToSqlConverter to treat BindableTableScan specially.
> Could you please help log a Jira issue? Contributions are welcome!
>
> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六 02:13写道：
>
> > Hi Team,
> > How can I convert a BindableTableScan with projects and Filters back to a
> > RelTree with a project node, a filter node and a tablescan node?
> >
> > I am doing this because I encountered the following issue (steps detailed
> > below).
> >
> > 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> > 2. The logical plan for this is a filter on top of a project on top of a
> > TableScan.
> > 3. If the table we created is a ProjectableFilterableTable and we have
> > added the ProjectTableScan rule etc., then after optimization we have a
> > single node which is a tablescan that contains filters and project and
> can
> > be executed by the bindable convention.
> > 4. If I convert this node to a SQL, I get the wrong SQL.
> >
> > To get a SQL, I do the following:
> >
> > RelToSqlConverter converter = new
> > RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> >
> > This only gives me Select * from myTable;
> >
> > However, if I only have a ScannableTable and we haven't added the
> > ProjectTableScan rule etc. then for the physical plan we also get a
> > BindableFilter on top of a BindableProject on top of a BindableTablescan.
> >
> > If i convert this back using the same code then I get the correct
> statement
> > back: Select colA, colB from myTable where colA > 1
> >
> > Hence I would like to know how to convert a BindableTableScan with
> projects
> > and Filters back to a RelTree with a project node, a filter node and a
> > tablescan node & would appreciate the community's help on the same.
> >
> > Thanks & Regards,
> > Pranav
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Pranav Deshpande <de...@gmail.com>.
Thank you Stamatis, I will take a look at the same.
Regards,
Pranav
On Mon, Aug 22, 2022 at 8:36 AM Stamatis Zampetakis <za...@gmail.com>
wrote:
> There is a utility (ToLogicalConverter[1]) to convert some kinds of plans
> to logical equivalents. However, the way it is right now it cannot handle
> your use-case.
> Since it handles some Enumerable operators, it wouldn't be surprising to
> support a few Bindable as well, although I would agree that the rule-based
> approach seems cleaner.
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
>
> On Sun, Aug 21, 2022 at 6:35 AM Benchao Li <li...@apache.org> wrote:
>
> > I agree that a separate planner rule is better. Besides, if you are
> > constructing the optimizing rules
> > by yourself in your project, you can avoid this by not adding
> > `FilterTableScanRule` and
> > `ProjectTableScanRule`.
> >
> > Pranav,
> > there is a doc[1] about how to contribute to Calcite.
> >
> > [1] https://calcite.apache.org/develop/#contributing
> >
> > Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
> >
> > > Agree with Hyde, there are many problems when converting a physical
> > > RelNode directly to SQL. For example,
> > > https://issues.apache.org/jira/browse/CALCITE-2915
> > >
> > > In my project, I have an object holding Logical RelNode and Physical
> > > RelNode. When I want to convert sql, I can use Logical RelNode. I hope
> > it's
> > > useful to you, Pranav.
> > >
> > > On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com>
> > wrote:
> > >
> > > > It doesn’t seem quite right to convert a physical RelNode directly to
> > > SQL.
> > > > It’s probably better to map it back to logical RelNode(s) first.
> > > >
> > > > I don’t know whether we have the software to do that mapping. If we
> > > don’t,
> > > > consider using planner rules. They’re often the right way in Calcite.
> > > >
> > > > Julian
> > > >
> > > > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > > > deshpande.v.pranav@gmail.com> wrote:
> > > > >
> > > > > Hi Bencaho,
> > > > > Thank you very much for your reply. Could you please tell me the
> > > > procedure
> > > > > to create this issue on JIRA?
> > > > >
> > > > > Thanks & Regards,
> > > > > Pranav
> > > > >
> > > > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <libenchao@apache.org
> >
> > > > wrote:
> > > > >>
> > > > >> Pranav,
> > > > >>
> > > > >> This is a good question. To me, I would take this as a bug, and we
> > > could
> > > > >> improve the
> > > > >> RelToSqlConverter to treat BindableTableScan specially.
> > > > >> Could you please help log a Jira issue? Contributions are welcome!
> > > > >>
> > > > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> > > 02:13写道：
> > > > >>
> > > > >>> Hi Team,
> > > > >>> How can I convert a BindableTableScan with projects and Filters
> > back
> > > > to a
> > > > >>> RelTree with a project node, a filter node and a tablescan node?
> > > > >>>
> > > > >>> I am doing this because I encountered the following issue (steps
> > > > detailed
> > > > >>> below).
> > > > >>>
> > > > >>> 1. I have a query:eg. Select colA, colB from myTable where colA >
> > 1;
> > > > >>> 2. The logical plan for this is a filter on top of a project on
> top
> > > of
> > > > a
> > > > >>> TableScan.
> > > > >>> 3. If the table we created is a ProjectableFilterableTable and we
> > > have
> > > > >>> added the ProjectTableScan rule etc., then after optimization we
> > > have a
> > > > >>> single node which is a tablescan that contains filters and
> project
> > > and
> > > > >> can
> > > > >>> be executed by the bindable convention.
> > > > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > > > >>>
> > > > >>> To get a SQL, I do the following:
> > > > >>>
> > > > >>> RelToSqlConverter converter = new
> > > > >>>
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > > > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > > > >>>
> > > > >>> This only gives me Select * from myTable;
> > > > >>>
> > > > >>> However, if I only have a ScannableTable and we haven't added the
> > > > >>> ProjectTableScan rule etc. then for the physical plan we also
> get a
> > > > >>> BindableFilter on top of a BindableProject on top of a
> > > > BindableTablescan.
> > > > >>>
> > > > >>> If i convert this back using the same code then I get the correct
> > > > >> statement
> > > > >>> back: Select colA, colB from myTable where colA > 1
> > > > >>>
> > > > >>> Hence I would like to know how to convert a BindableTableScan
> with
> > > > >> projects
> > > > >>> and Filters back to a RelTree with a project node, a filter node
> > and
> > > a
> > > > >>> tablescan node & would appreciate the community's help on the
> same.
> > > > >>>
> > > > >>> Thanks & Regards,
> > > > >>> Pranav
> > > > >>>
> > > > >>
> > > > >>
> > > > >> --
> > > > >>
> > > > >> Best,
> > > > >> Benchao Li
> > > > >>
> > > >
> > >
> >
> >
> > --
> >
> > Best,
> > Benchao Li
> >
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Stamatis Zampetakis <za...@gmail.com>.
There is a utility (ToLogicalConverter[1]) to convert some kinds of plans
to logical equivalents. However, the way it is right now it cannot handle
your use-case.
Since it handles some Enumerable operators, it wouldn't be surprising to
support a few Bindable as well, although I would agree that the rule-based
approach seems cleaner.
Best,
Stamatis
[1]
https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
On Sun, Aug 21, 2022 at 6:35 AM Benchao Li <li...@apache.org> wrote:
> I agree that a separate planner rule is better. Besides, if you are
> constructing the optimizing rules
> by yourself in your project, you can avoid this by not adding
> `FilterTableScanRule` and
> `ProjectTableScanRule`.
>
> Pranav,
> there is a doc[1] about how to contribute to Calcite.
>
> [1] https://calcite.apache.org/develop/#contributing
>
> Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
>
> > Agree with Hyde, there are many problems when converting a physical
> > RelNode directly to SQL. For example,
> > https://issues.apache.org/jira/browse/CALCITE-2915
> >
> > In my project, I have an object holding Logical RelNode and Physical
> > RelNode. When I want to convert sql, I can use Logical RelNode. I hope
> it's
> > useful to you, Pranav.
> >
> > On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com>
> wrote:
> >
> > > It doesn’t seem quite right to convert a physical RelNode directly to
> > SQL.
> > > It’s probably better to map it back to logical RelNode(s) first.
> > >
> > > I don’t know whether we have the software to do that mapping. If we
> > don’t,
> > > consider using planner rules. They’re often the right way in Calcite.
> > >
> > > Julian
> > >
> > > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > > deshpande.v.pranav@gmail.com> wrote:
> > > >
> > > > Hi Bencaho,
> > > > Thank you very much for your reply. Could you please tell me the
> > > procedure
> > > > to create this issue on JIRA?
> > > >
> > > > Thanks & Regards,
> > > > Pranav
> > > >
> > > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org>
> > > wrote:
> > > >>
> > > >> Pranav,
> > > >>
> > > >> This is a good question. To me, I would take this as a bug, and we
> > could
> > > >> improve the
> > > >> RelToSqlConverter to treat BindableTableScan specially.
> > > >> Could you please help log a Jira issue? Contributions are welcome!
> > > >>
> > > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> > 02:13写道：
> > > >>
> > > >>> Hi Team,
> > > >>> How can I convert a BindableTableScan with projects and Filters
> back
> > > to a
> > > >>> RelTree with a project node, a filter node and a tablescan node?
> > > >>>
> > > >>> I am doing this because I encountered the following issue (steps
> > > detailed
> > > >>> below).
> > > >>>
> > > >>> 1. I have a query:eg. Select colA, colB from myTable where colA >
> 1;
> > > >>> 2. The logical plan for this is a filter on top of a project on top
> > of
> > > a
> > > >>> TableScan.
> > > >>> 3. If the table we created is a ProjectableFilterableTable and we
> > have
> > > >>> added the ProjectTableScan rule etc., then after optimization we
> > have a
> > > >>> single node which is a tablescan that contains filters and project
> > and
> > > >> can
> > > >>> be executed by the bindable convention.
> > > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > > >>>
> > > >>> To get a SQL, I do the following:
> > > >>>
> > > >>> RelToSqlConverter converter = new
> > > >>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > > >>>
> > > >>> This only gives me Select * from myTable;
> > > >>>
> > > >>> However, if I only have a ScannableTable and we haven't added the
> > > >>> ProjectTableScan rule etc. then for the physical plan we also get a
> > > >>> BindableFilter on top of a BindableProject on top of a
> > > BindableTablescan.
> > > >>>
> > > >>> If i convert this back using the same code then I get the correct
> > > >> statement
> > > >>> back: Select colA, colB from myTable where colA > 1
> > > >>>
> > > >>> Hence I would like to know how to convert a BindableTableScan with
> > > >> projects
> > > >>> and Filters back to a RelTree with a project node, a filter node
> and
> > a
> > > >>> tablescan node & would appreciate the community's help on the same.
> > > >>>
> > > >>> Thanks & Regards,
> > > >>> Pranav
> > > >>>
> > > >>
> > > >>
> > > >> --
> > > >>
> > > >> Best,
> > > >> Benchao Li
> > > >>
> > >
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Jiajun Xie <ji...@gmail.com>.
Pranav, there are `QueryExecution` that holding logicalPlan, optimizedPlan
and sparkPlan(physical) in Spark:
https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/QueryExecution.scala
In fact, my code is similar to it. I have an object holding
ValidatedSqlNode, LogicalRelNode, physicalRelNode.
On Tue, 23 Aug 2022 at 23:03, Benchao Li <li...@apache.org> wrote:
> Pranav,
> There is a doc[1] which talks about how to write a rule. You can also take
> a look at existing
> rules such as FilterTableScanRule[2], ProjectTableScanRule[3] for
> reference.
>
> [1] https://calcite.apache.org/docs/howto.html#create-a-planner-rule
> [2]
>
> https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/rules/FilterTableScanRule.java
> [3]
>
> https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/rules/ProjectTableScanRule.java
>
> Pranav Deshpande <de...@gmail.com> 于2022年8月22日周一 23:00写道：
>
> > Hi Benchao,
> > Thank you very much for referring me to this document.
> >
> > Could you please provide me with a pointer on how to write the optimizer
> > rule(s) you mentioned in part I of your answer?
> >
> > Regards,
> > Pranav
> >
> > On Sat, Aug 20, 2022 at 11:35 PM Benchao Li <li...@apache.org>
> wrote:
> >
> > > I agree that a separate planner rule is better. Besides, if you are
> > > constructing the optimizing rules
> > > by yourself in your project, you can avoid this by not adding
> > > `FilterTableScanRule` and
> > > `ProjectTableScanRule`.
> > >
> > > Pranav,
> > > there is a doc[1] about how to contribute to Calcite.
> > >
> > > [1] https://calcite.apache.org/develop/#contributing
> > >
> > > Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
> > >
> > > > Agree with Hyde, there are many problems when converting a physical
> > > > RelNode directly to SQL. For example,
> > > > https://issues.apache.org/jira/browse/CALCITE-2915
> > > >
> > > > In my project, I have an object holding Logical RelNode and
> Physical
> > > > RelNode. When I want to convert sql, I can use Logical RelNode. I
> hope
> > > it's
> > > > useful to you, Pranav.
> > > >
> > > > On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com>
> > > wrote:
> > > >
> > > > > It doesn’t seem quite right to convert a physical RelNode directly
> to
> > > > SQL.
> > > > > It’s probably better to map it back to logical RelNode(s) first.
> > > > >
> > > > > I don’t know whether we have the software to do that mapping. If we
> > > > don’t,
> > > > > consider using planner rules. They’re often the right way in
> Calcite.
> > > > >
> > > > > Julian
> > > > >
> > > > > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > > > > deshpande.v.pranav@gmail.com> wrote:
> > > > > >
> > > > > > Hi Bencaho,
> > > > > > Thank you very much for your reply. Could you please tell me the
> > > > > procedure
> > > > > > to create this issue on JIRA?
> > > > > >
> > > > > > Thanks & Regards,
> > > > > > Pranav
> > > > > >
> > > > > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <
> libenchao@apache.org
> > >
> > > > > wrote:
> > > > > >>
> > > > > >> Pranav,
> > > > > >>
> > > > > >> This is a good question. To me, I would take this as a bug, and
> we
> > > > could
> > > > > >> improve the
> > > > > >> RelToSqlConverter to treat BindableTableScan specially.
> > > > > >> Could you please help log a Jira issue? Contributions are
> welcome!
> > > > > >>
> > > > > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> > > > 02:13写道：
> > > > > >>
> > > > > >>> Hi Team,
> > > > > >>> How can I convert a BindableTableScan with projects and Filters
> > > back
> > > > > to a
> > > > > >>> RelTree with a project node, a filter node and a tablescan
> node?
> > > > > >>>
> > > > > >>> I am doing this because I encountered the following issue
> (steps
> > > > > detailed
> > > > > >>> below).
> > > > > >>>
> > > > > >>> 1. I have a query:eg. Select colA, colB from myTable where
> colA >
> > > 1;
> > > > > >>> 2. The logical plan for this is a filter on top of a project on
> > top
> > > > of
> > > > > a
> > > > > >>> TableScan.
> > > > > >>> 3. If the table we created is a ProjectableFilterableTable and
> we
> > > > have
> > > > > >>> added the ProjectTableScan rule etc., then after optimization
> we
> > > > have a
> > > > > >>> single node which is a tablescan that contains filters and
> > project
> > > > and
> > > > > >> can
> > > > > >>> be executed by the bindable convention.
> > > > > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > > > > >>>
> > > > > >>> To get a SQL, I do the following:
> > > > > >>>
> > > > > >>> RelToSqlConverter converter = new
> > > > > >>>
> > RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > > > > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > > > > >>>
> > > > > >>> This only gives me Select * from myTable;
> > > > > >>>
> > > > > >>> However, if I only have a ScannableTable and we haven't added
> the
> > > > > >>> ProjectTableScan rule etc. then for the physical plan we also
> > get a
> > > > > >>> BindableFilter on top of a BindableProject on top of a
> > > > > BindableTablescan.
> > > > > >>>
> > > > > >>> If i convert this back using the same code then I get the
> correct
> > > > > >> statement
> > > > > >>> back: Select colA, colB from myTable where colA > 1
> > > > > >>>
> > > > > >>> Hence I would like to know how to convert a BindableTableScan
> > with
> > > > > >> projects
> > > > > >>> and Filters back to a RelTree with a project node, a filter
> node
> > > and
> > > > a
> > > > > >>> tablescan node & would appreciate the community's help on the
> > same.
> > > > > >>>
> > > > > >>> Thanks & Regards,
> > > > > >>> Pranav
> > > > > >>>
> > > > > >>
> > > > > >>
> > > > > >> --
> > > > > >>
> > > > > >> Best,
> > > > > >> Benchao Li
> > > > > >>
> > > > >
> > > >
> > >
> > >
> > > --
> > >
> > > Best,
> > > Benchao Li
> > >
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Benchao Li <li...@apache.org>.
Pranav,
There is a doc[1] which talks about how to write a rule. You can also take
a look at existing
rules such as FilterTableScanRule[2], ProjectTableScanRule[3] for reference.
[1] https://calcite.apache.org/docs/howto.html#create-a-planner-rule
[2]
https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/rules/FilterTableScanRule.java
[3]
https://github.com/apache/calcite/blob/7de37aefaaa72959a0145053319fae2956fbb9f1/core/src/main/java/org/apache/calcite/rel/rules/ProjectTableScanRule.java
Pranav Deshpande <de...@gmail.com> 于2022年8月22日周一 23:00写道：
> Hi Benchao,
> Thank you very much for referring me to this document.
>
> Could you please provide me with a pointer on how to write the optimizer
> rule(s) you mentioned in part I of your answer?
>
> Regards,
> Pranav
>
> On Sat, Aug 20, 2022 at 11:35 PM Benchao Li <li...@apache.org> wrote:
>
> > I agree that a separate planner rule is better. Besides, if you are
> > constructing the optimizing rules
> > by yourself in your project, you can avoid this by not adding
> > `FilterTableScanRule` and
> > `ProjectTableScanRule`.
> >
> > Pranav,
> > there is a doc[1] about how to contribute to Calcite.
> >
> > [1] https://calcite.apache.org/develop/#contributing
> >
> > Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
> >
> > > Agree with Hyde, there are many problems when converting a physical
> > > RelNode directly to SQL. For example,
> > > https://issues.apache.org/jira/browse/CALCITE-2915
> > >
> > > In my project, I have an object holding Logical RelNode and Physical
> > > RelNode. When I want to convert sql, I can use Logical RelNode. I hope
> > it's
> > > useful to you, Pranav.
> > >
> > > On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com>
> > wrote:
> > >
> > > > It doesn’t seem quite right to convert a physical RelNode directly to
> > > SQL.
> > > > It’s probably better to map it back to logical RelNode(s) first.
> > > >
> > > > I don’t know whether we have the software to do that mapping. If we
> > > don’t,
> > > > consider using planner rules. They’re often the right way in Calcite.
> > > >
> > > > Julian
> > > >
> > > > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > > > deshpande.v.pranav@gmail.com> wrote:
> > > > >
> > > > > Hi Bencaho,
> > > > > Thank you very much for your reply. Could you please tell me the
> > > > procedure
> > > > > to create this issue on JIRA?
> > > > >
> > > > > Thanks & Regards,
> > > > > Pranav
> > > > >
> > > > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <libenchao@apache.org
> >
> > > > wrote:
> > > > >>
> > > > >> Pranav,
> > > > >>
> > > > >> This is a good question. To me, I would take this as a bug, and we
> > > could
> > > > >> improve the
> > > > >> RelToSqlConverter to treat BindableTableScan specially.
> > > > >> Could you please help log a Jira issue? Contributions are welcome!
> > > > >>
> > > > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> > > 02:13写道：
> > > > >>
> > > > >>> Hi Team,
> > > > >>> How can I convert a BindableTableScan with projects and Filters
> > back
> > > > to a
> > > > >>> RelTree with a project node, a filter node and a tablescan node?
> > > > >>>
> > > > >>> I am doing this because I encountered the following issue (steps
> > > > detailed
> > > > >>> below).
> > > > >>>
> > > > >>> 1. I have a query:eg. Select colA, colB from myTable where colA >
> > 1;
> > > > >>> 2. The logical plan for this is a filter on top of a project on
> top
> > > of
> > > > a
> > > > >>> TableScan.
> > > > >>> 3. If the table we created is a ProjectableFilterableTable and we
> > > have
> > > > >>> added the ProjectTableScan rule etc., then after optimization we
> > > have a
> > > > >>> single node which is a tablescan that contains filters and
> project
> > > and
> > > > >> can
> > > > >>> be executed by the bindable convention.
> > > > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > > > >>>
> > > > >>> To get a SQL, I do the following:
> > > > >>>
> > > > >>> RelToSqlConverter converter = new
> > > > >>>
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > > > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > > > >>>
> > > > >>> This only gives me Select * from myTable;
> > > > >>>
> > > > >>> However, if I only have a ScannableTable and we haven't added the
> > > > >>> ProjectTableScan rule etc. then for the physical plan we also
> get a
> > > > >>> BindableFilter on top of a BindableProject on top of a
> > > > BindableTablescan.
> > > > >>>
> > > > >>> If i convert this back using the same code then I get the correct
> > > > >> statement
> > > > >>> back: Select colA, colB from myTable where colA > 1
> > > > >>>
> > > > >>> Hence I would like to know how to convert a BindableTableScan
> with
> > > > >> projects
> > > > >>> and Filters back to a RelTree with a project node, a filter node
> > and
> > > a
> > > > >>> tablescan node & would appreciate the community's help on the
> same.
> > > > >>>
> > > > >>> Thanks & Regards,
> > > > >>> Pranav
> > > > >>>
> > > > >>
> > > > >>
> > > > >> --
> > > > >>
> > > > >> Best,
> > > > >> Benchao Li
> > > > >>
> > > >
> > >
> >
> >
> > --
> >
> > Best,
> > Benchao Li
> >
>
--
Best,
Benchao Li
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Pranav Deshpande <de...@gmail.com>.
Hi Benchao,
Thank you very much for referring me to this document.
Could you please provide me with a pointer on how to write the optimizer
rule(s) you mentioned in part I of your answer?
Regards,
Pranav
On Sat, Aug 20, 2022 at 11:35 PM Benchao Li <li...@apache.org> wrote:
> I agree that a separate planner rule is better. Besides, if you are
> constructing the optimizing rules
> by yourself in your project, you can avoid this by not adding
> `FilterTableScanRule` and
> `ProjectTableScanRule`.
>
> Pranav,
> there is a doc[1] about how to contribute to Calcite.
>
> [1] https://calcite.apache.org/develop/#contributing
>
> Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
>
> > Agree with Hyde, there are many problems when converting a physical
> > RelNode directly to SQL. For example,
> > https://issues.apache.org/jira/browse/CALCITE-2915
> >
> > In my project, I have an object holding Logical RelNode and Physical
> > RelNode. When I want to convert sql, I can use Logical RelNode. I hope
> it's
> > useful to you, Pranav.
> >
> > On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com>
> wrote:
> >
> > > It doesn’t seem quite right to convert a physical RelNode directly to
> > SQL.
> > > It’s probably better to map it back to logical RelNode(s) first.
> > >
> > > I don’t know whether we have the software to do that mapping. If we
> > don’t,
> > > consider using planner rules. They’re often the right way in Calcite.
> > >
> > > Julian
> > >
> > > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > > deshpande.v.pranav@gmail.com> wrote:
> > > >
> > > > Hi Bencaho,
> > > > Thank you very much for your reply. Could you please tell me the
> > > procedure
> > > > to create this issue on JIRA?
> > > >
> > > > Thanks & Regards,
> > > > Pranav
> > > >
> > > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org>
> > > wrote:
> > > >>
> > > >> Pranav,
> > > >>
> > > >> This is a good question. To me, I would take this as a bug, and we
> > could
> > > >> improve the
> > > >> RelToSqlConverter to treat BindableTableScan specially.
> > > >> Could you please help log a Jira issue? Contributions are welcome!
> > > >>
> > > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> > 02:13写道：
> > > >>
> > > >>> Hi Team,
> > > >>> How can I convert a BindableTableScan with projects and Filters
> back
> > > to a
> > > >>> RelTree with a project node, a filter node and a tablescan node?
> > > >>>
> > > >>> I am doing this because I encountered the following issue (steps
> > > detailed
> > > >>> below).
> > > >>>
> > > >>> 1. I have a query:eg. Select colA, colB from myTable where colA >
> 1;
> > > >>> 2. The logical plan for this is a filter on top of a project on top
> > of
> > > a
> > > >>> TableScan.
> > > >>> 3. If the table we created is a ProjectableFilterableTable and we
> > have
> > > >>> added the ProjectTableScan rule etc., then after optimization we
> > have a
> > > >>> single node which is a tablescan that contains filters and project
> > and
> > > >> can
> > > >>> be executed by the bindable convention.
> > > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > > >>>
> > > >>> To get a SQL, I do the following:
> > > >>>
> > > >>> RelToSqlConverter converter = new
> > > >>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > > >>>
> > > >>> This only gives me Select * from myTable;
> > > >>>
> > > >>> However, if I only have a ScannableTable and we haven't added the
> > > >>> ProjectTableScan rule etc. then for the physical plan we also get a
> > > >>> BindableFilter on top of a BindableProject on top of a
> > > BindableTablescan.
> > > >>>
> > > >>> If i convert this back using the same code then I get the correct
> > > >> statement
> > > >>> back: Select colA, colB from myTable where colA > 1
> > > >>>
> > > >>> Hence I would like to know how to convert a BindableTableScan with
> > > >> projects
> > > >>> and Filters back to a RelTree with a project node, a filter node
> and
> > a
> > > >>> tablescan node & would appreciate the community's help on the same.
> > > >>>
> > > >>> Thanks & Regards,
> > > >>> Pranav
> > > >>>
> > > >>
> > > >>
> > > >> --
> > > >>
> > > >> Best,
> > > >> Benchao Li
> > > >>
> > >
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Benchao Li <li...@apache.org>.
I agree that a separate planner rule is better. Besides, if you are
constructing the optimizing rules
by yourself in your project, you can avoid this by not adding
`FilterTableScanRule` and
`ProjectTableScanRule`.
Pranav,
there is a doc[1] about how to contribute to Calcite.
[1] https://calcite.apache.org/develop/#contributing
Jiajun Xie <ji...@gmail.com> 于2022年8月21日周日 09:00写道：
> Agree with Hyde, there are many problems when converting a physical
> RelNode directly to SQL. For example,
> https://issues.apache.org/jira/browse/CALCITE-2915
>
> In my project, I have an object holding Logical RelNode and Physical
> RelNode. When I want to convert sql, I can use Logical RelNode. I hope it's
> useful to you, Pranav.
>
> On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com> wrote:
>
> > It doesn’t seem quite right to convert a physical RelNode directly to
> SQL.
> > It’s probably better to map it back to logical RelNode(s) first.
> >
> > I don’t know whether we have the software to do that mapping. If we
> don’t,
> > consider using planner rules. They’re often the right way in Calcite.
> >
> > Julian
> >
> > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > deshpande.v.pranav@gmail.com> wrote:
> > >
> > > Hi Bencaho,
> > > Thank you very much for your reply. Could you please tell me the
> > procedure
> > > to create this issue on JIRA?
> > >
> > > Thanks & Regards,
> > > Pranav
> > >
> > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org>
> > wrote:
> > >>
> > >> Pranav,
> > >>
> > >> This is a good question. To me, I would take this as a bug, and we
> could
> > >> improve the
> > >> RelToSqlConverter to treat BindableTableScan specially.
> > >> Could you please help log a Jira issue? Contributions are welcome!
> > >>
> > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> 02:13写道：
> > >>
> > >>> Hi Team,
> > >>> How can I convert a BindableTableScan with projects and Filters back
> > to a
> > >>> RelTree with a project node, a filter node and a tablescan node?
> > >>>
> > >>> I am doing this because I encountered the following issue (steps
> > detailed
> > >>> below).
> > >>>
> > >>> 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> > >>> 2. The logical plan for this is a filter on top of a project on top
> of
> > a
> > >>> TableScan.
> > >>> 3. If the table we created is a ProjectableFilterableTable and we
> have
> > >>> added the ProjectTableScan rule etc., then after optimization we
> have a
> > >>> single node which is a tablescan that contains filters and project
> and
> > >> can
> > >>> be executed by the bindable convention.
> > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > >>>
> > >>> To get a SQL, I do the following:
> > >>>
> > >>> RelToSqlConverter converter = new
> > >>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > >>>
> > >>> This only gives me Select * from myTable;
> > >>>
> > >>> However, if I only have a ScannableTable and we haven't added the
> > >>> ProjectTableScan rule etc. then for the physical plan we also get a
> > >>> BindableFilter on top of a BindableProject on top of a
> > BindableTablescan.
> > >>>
> > >>> If i convert this back using the same code then I get the correct
> > >> statement
> > >>> back: Select colA, colB from myTable where colA > 1
> > >>>
> > >>> Hence I would like to know how to convert a BindableTableScan with
> > >> projects
> > >>> and Filters back to a RelTree with a project node, a filter node and
> a
> > >>> tablescan node & would appreciate the community's help on the same.
> > >>>
> > >>> Thanks & Regards,
> > >>> Pranav
> > >>>
> > >>
> > >>
> > >> --
> > >>
> > >> Best,
> > >> Benchao Li
> > >>
> >
>
--
Best,
Benchao Li
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Pranav Deshpande <de...@gmail.com>.
Hi Jiajun Xie,
Thank you. Is there any open source reference for the code you are
mentioning?
Regards,
Pranav
On Sat, Aug 20, 2022 at 9:00 PM Jiajun Xie <ji...@gmail.com>
wrote:
> Agree with Hyde, there are many problems when converting a physical
> RelNode directly to SQL. For example,
> https://issues.apache.org/jira/browse/CALCITE-2915
>
> In my project, I have an object holding Logical RelNode and Physical
> RelNode. When I want to convert sql, I can use Logical RelNode. I hope it's
> useful to you, Pranav.
>
> On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com> wrote:
>
> > It doesn’t seem quite right to convert a physical RelNode directly to
> SQL.
> > It’s probably better to map it back to logical RelNode(s) first.
> >
> > I don’t know whether we have the software to do that mapping. If we
> don’t,
> > consider using planner rules. They’re often the right way in Calcite.
> >
> > Julian
> >
> > > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> > deshpande.v.pranav@gmail.com> wrote:
> > >
> > > Hi Bencaho,
> > > Thank you very much for your reply. Could you please tell me the
> > procedure
> > > to create this issue on JIRA?
> > >
> > > Thanks & Regards,
> > > Pranav
> > >
> > >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org>
> > wrote:
> > >>
> > >> Pranav,
> > >>
> > >> This is a good question. To me, I would take this as a bug, and we
> could
> > >> improve the
> > >> RelToSqlConverter to treat BindableTableScan specially.
> > >> Could you please help log a Jira issue? Contributions are welcome!
> > >>
> > >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六
> 02:13写道：
> > >>
> > >>> Hi Team,
> > >>> How can I convert a BindableTableScan with projects and Filters back
> > to a
> > >>> RelTree with a project node, a filter node and a tablescan node?
> > >>>
> > >>> I am doing this because I encountered the following issue (steps
> > detailed
> > >>> below).
> > >>>
> > >>> 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> > >>> 2. The logical plan for this is a filter on top of a project on top
> of
> > a
> > >>> TableScan.
> > >>> 3. If the table we created is a ProjectableFilterableTable and we
> have
> > >>> added the ProjectTableScan rule etc., then after optimization we
> have a
> > >>> single node which is a tablescan that contains filters and project
> and
> > >> can
> > >>> be executed by the bindable convention.
> > >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> > >>>
> > >>> To get a SQL, I do the following:
> > >>>
> > >>> RelToSqlConverter converter = new
> > >>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> > >>>
> > >>> This only gives me Select * from myTable;
> > >>>
> > >>> However, if I only have a ScannableTable and we haven't added the
> > >>> ProjectTableScan rule etc. then for the physical plan we also get a
> > >>> BindableFilter on top of a BindableProject on top of a
> > BindableTablescan.
> > >>>
> > >>> If i convert this back using the same code then I get the correct
> > >> statement
> > >>> back: Select colA, colB from myTable where colA > 1
> > >>>
> > >>> Hence I would like to know how to convert a BindableTableScan with
> > >> projects
> > >>> and Filters back to a RelTree with a project node, a filter node and
> a
> > >>> tablescan node & would appreciate the community's help on the same.
> > >>>
> > >>> Thanks & Regards,
> > >>> Pranav
> > >>>
> > >>
> > >>
> > >> --
> > >>
> > >> Best,
> > >> Benchao Li
> > >>
> >
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Jiajun Xie <ji...@gmail.com>.
Agree with Hyde, there are many problems when converting a physical
RelNode directly to SQL. For example,
https://issues.apache.org/jira/browse/CALCITE-2915
In my project, I have an object holding Logical RelNode and Physical
RelNode. When I want to convert sql, I can use Logical RelNode. I hope it's
useful to you, Pranav.
On Sun, 21 Aug 2022 at 02:50, Julian Hyde <jh...@gmail.com> wrote:
> It doesn’t seem quite right to convert a physical RelNode directly to SQL.
> It’s probably better to map it back to logical RelNode(s) first.
>
> I don’t know whether we have the software to do that mapping. If we don’t,
> consider using planner rules. They’re often the right way in Calcite.
>
> Julian
>
> > On Aug 20, 2022, at 03:26, Pranav Deshpande <
> deshpande.v.pranav@gmail.com> wrote:
> >
> > Hi Bencaho,
> > Thank you very much for your reply. Could you please tell me the
> procedure
> > to create this issue on JIRA?
> >
> > Thanks & Regards,
> > Pranav
> >
> >> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org>
> wrote:
> >>
> >> Pranav,
> >>
> >> This is a good question. To me, I would take this as a bug, and we could
> >> improve the
> >> RelToSqlConverter to treat BindableTableScan specially.
> >> Could you please help log a Jira issue? Contributions are welcome!
> >>
> >> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六 02:13写道：
> >>
> >>> Hi Team,
> >>> How can I convert a BindableTableScan with projects and Filters back
> to a
> >>> RelTree with a project node, a filter node and a tablescan node?
> >>>
> >>> I am doing this because I encountered the following issue (steps
> detailed
> >>> below).
> >>>
> >>> 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> >>> 2. The logical plan for this is a filter on top of a project on top of
> a
> >>> TableScan.
> >>> 3. If the table we created is a ProjectableFilterableTable and we have
> >>> added the ProjectTableScan rule etc., then after optimization we have a
> >>> single node which is a tablescan that contains filters and project and
> >> can
> >>> be executed by the bindable convention.
> >>> 4. If I convert this node to a SQL, I get the wrong SQL.
> >>>
> >>> To get a SQL, I do the following:
> >>>
> >>> RelToSqlConverter converter = new
> >>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> >>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> >>>
> >>> This only gives me Select * from myTable;
> >>>
> >>> However, if I only have a ScannableTable and we haven't added the
> >>> ProjectTableScan rule etc. then for the physical plan we also get a
> >>> BindableFilter on top of a BindableProject on top of a
> BindableTablescan.
> >>>
> >>> If i convert this back using the same code then I get the correct
> >> statement
> >>> back: Select colA, colB from myTable where colA > 1
> >>>
> >>> Hence I would like to know how to convert a BindableTableScan with
> >> projects
> >>> and Filters back to a RelTree with a project node, a filter node and a
> >>> tablescan node & would appreciate the community's help on the same.
> >>>
> >>> Thanks & Regards,
> >>> Pranav
> >>>
> >>
> >>
> >> --
> >>
> >> Best,
> >> Benchao Li
> >>
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Julian Hyde <jh...@gmail.com>.
It doesn’t seem quite right to convert a physical RelNode directly to SQL. It’s probably better to map it back to logical RelNode(s) first.
I don’t know whether we have the software to do that mapping. If we don’t, consider using planner rules. They’re often the right way in Calcite.
Julian
> On Aug 20, 2022, at 03:26, Pranav Deshpande <de...@gmail.com> wrote:
>
> Hi Bencaho,
> Thank you very much for your reply. Could you please tell me the procedure
> to create this issue on JIRA?
>
> Thanks & Regards,
> Pranav
>
>> On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org> wrote:
>>
>> Pranav,
>>
>> This is a good question. To me, I would take this as a bug, and we could
>> improve the
>> RelToSqlConverter to treat BindableTableScan specially.
>> Could you please help log a Jira issue? Contributions are welcome!
>>
>> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六 02:13写道：
>>
>>> Hi Team,
>>> How can I convert a BindableTableScan with projects and Filters back to a
>>> RelTree with a project node, a filter node and a tablescan node?
>>>
>>> I am doing this because I encountered the following issue (steps detailed
>>> below).
>>>
>>> 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
>>> 2. The logical plan for this is a filter on top of a project on top of a
>>> TableScan.
>>> 3. If the table we created is a ProjectableFilterableTable and we have
>>> added the ProjectTableScan rule etc., then after optimization we have a
>>> single node which is a tablescan that contains filters and project and
>> can
>>> be executed by the bindable convention.
>>> 4. If I convert this node to a SQL, I get the wrong SQL.
>>>
>>> To get a SQL, I do the following:
>>>
>>> RelToSqlConverter converter = new
>>> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
>>> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
>>>
>>> This only gives me Select * from myTable;
>>>
>>> However, if I only have a ScannableTable and we haven't added the
>>> ProjectTableScan rule etc. then for the physical plan we also get a
>>> BindableFilter on top of a BindableProject on top of a BindableTablescan.
>>>
>>> If i convert this back using the same code then I get the correct
>> statement
>>> back: Select colA, colB from myTable where colA > 1
>>>
>>> Hence I would like to know how to convert a BindableTableScan with
>> projects
>>> and Filters back to a RelTree with a project node, a filter node and a
>>> tablescan node & would appreciate the community's help on the same.
>>>
>>> Thanks & Regards,
>>> Pranav
>>>
>>
>>
>> --
>>
>> Best,
>> Benchao Li
>>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Pranav Deshpande <de...@gmail.com>.
Hi Bencaho,
Thank you very much for your reply. Could you please tell me the procedure
to create this issue on JIRA?
Thanks & Regards,
Pranav
On Fri, Aug 19, 2022 at 11:13 PM Benchao Li <li...@apache.org> wrote:
> Pranav,
>
> This is a good question. To me, I would take this as a bug, and we could
> improve the
> RelToSqlConverter to treat BindableTableScan specially.
> Could you please help log a Jira issue? Contributions are welcome!
>
> Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六 02:13写道：
>
> > Hi Team,
> > How can I convert a BindableTableScan with projects and Filters back to a
> > RelTree with a project node, a filter node and a tablescan node?
> >
> > I am doing this because I encountered the following issue (steps detailed
> > below).
> >
> > 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> > 2. The logical plan for this is a filter on top of a project on top of a
> > TableScan.
> > 3. If the table we created is a ProjectableFilterableTable and we have
> > added the ProjectTableScan rule etc., then after optimization we have a
> > single node which is a tablescan that contains filters and project and
> can
> > be executed by the bindable convention.
> > 4. If I convert this node to a SQL, I get the wrong SQL.
> >
> > To get a SQL, I do the following:
> >
> > RelToSqlConverter converter = new
> > RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> > SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
> >
> > This only gives me Select * from myTable;
> >
> > However, if I only have a ScannableTable and we haven't added the
> > ProjectTableScan rule etc. then for the physical plan we also get a
> > BindableFilter on top of a BindableProject on top of a BindableTablescan.
> >
> > If i convert this back using the same code then I get the correct
> statement
> > back: Select colA, colB from myTable where colA > 1
> >
> > Hence I would like to know how to convert a BindableTableScan with
> projects
> > and Filters back to a RelTree with a project node, a filter node and a
> > tablescan node & would appreciate the community's help on the same.
> >
> > Thanks & Regards,
> > Pranav
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: How can I convert a BindableTableScan with projects and Fitlers back to a Rel Tree with a project node, a filter node and a tablescan node?
Posted by Benchao Li <li...@apache.org>.
Pranav,
This is a good question. To me, I would take this as a bug, and we could
improve the
RelToSqlConverter to treat BindableTableScan specially.
Could you please help log a Jira issue? Contributions are welcome!
Pranav Deshpande <de...@gmail.com> 于2022年8月20日周六 02:13写道：
> Hi Team,
> How can I convert a BindableTableScan with projects and Filters back to a
> RelTree with a project node, a filter node and a tablescan node?
>
> I am doing this because I encountered the following issue (steps detailed
> below).
>
> 1. I have a query:eg. Select colA, colB from myTable where colA > 1;
> 2. The logical plan for this is a filter on top of a project on top of a
> TableScan.
> 3. If the table we created is a ProjectableFilterableTable and we have
> added the ProjectTableScan rule etc., then after optimization we have a
> single node which is a tablescan that contains filters and project and can
> be executed by the bindable convention.
> 4. If I convert this node to a SQL, I get the wrong SQL.
>
> To get a SQL, I do the following:
>
> RelToSqlConverter converter = new
> RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
> SqlNode sqlNode = converter.visitRoot(rablescan).asStatement();
>
> This only gives me Select * from myTable;
>
> However, if I only have a ScannableTable and we haven't added the
> ProjectTableScan rule etc. then for the physical plan we also get a
> BindableFilter on top of a BindableProject on top of a BindableTablescan.
>
> If i convert this back using the same code then I get the correct statement
> back: Select colA, colB from myTable where colA > 1
>
> Hence I would like to know how to convert a BindableTableScan with projects
> and Filters back to a RelTree with a project node, a filter node and a
> tablescan node & would appreciate the community's help on the same.
>
> Thanks & Regards,
> Pranav
>
--
Best,
Benchao Li