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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> > 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 <[email protected]> 于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 > >> >
