Yes, you are both correct. I got an expected plan by using ProjectToCalcRule ``` PLAN=EnumerableCalc(expr#0..4=[{inputs}], expr#5=[12], expr#6=[*($t5, $t3)], expr#7=[3], expr#8=[*($t7, $t3)], expr#9=[*($t3, $t5)], expr#10=[*($t3, $t7)], expr#11=[+($t9, $t10)], salary=[$t3], EXPR$1=[$t6], EXPR$2=[$t8], EXPR$3=[$t11], EXPR$4=[$t11])\n EnumerableTableScan(table=[[hr, emps]])\n\n ```
But I got an unexpected query when using RelToSql. I hope to extract CommonSubExpressions in SQL, because Trino does not support CommonSubExpressions optimization. (Presto supports it since 0.245). ``` SELECT "salary", 12 * "salary", 3 * "salary", "salary" * 12 + "salary" * 3, "salary" * 12 + "salary" * 3 FROM "hr"."emps" ``` I'm thinking about using the optimization of Calc, but change CalcToSql. What do you think? FYI: In my project, I convert HiveSQL to PrestoSQL, and use some optimizations of Calcite. Thanks for your reply! On Fri, 15 Jul 2022 at 09:44, Benchao Li <libenc...@apache.org> wrote: > Agree with Julian, RexProgram could do this work. > > I did this before in physical codegen based on Calc's RexProgram, and it > works very well. > > Julian Hyde <jhyde.apa...@gmail.com> 于2022年7月14日周四 23:21写道: > > > It seems that you are tackling common scalar expressions (RexNodes) > > whereas, based on the name the other rule is dealing with common > relational > > expressions (RelNode). > > > > For your rule, consider using RexProgram, RexProgramBuilder, and Calc. > > They already convert a list of expressions to a DAG, so that no > expression > > is calculated more than once. > > > > Julian > > > > > On Jul 14, 2022, at 06:31, Jiajun Xie <jiajunbernou...@gmail.com> > wrote: > > > > > > Hello, all~ > > > I am writing an RelOptRule that is for CommonSubExpressions. Here is > an > > > example that I completed. > > > sql: `select sal, sal * 12, sal * 3, sal * 12 + sal * 3 from emp` > > > planBefore: ```LogicalProject(SAL=[$5], EXPR$1=[*($5, 12)], > > > EXPR$2=[*($5, 3)], EXPR$3=[+(*($5, 12), *($5, 3))]) > > > LogicalTableScan(table=[[CATALOG, SALES, EMP]])``` > > > planAfter: ```LogicalProject(SAL=[$0], EXPR$1=[$1], EXPR$2=[$2], > > > EXPR$3=[+($1, $2)]) > > > LogicalProject(SAL=[$5], CSE$0=[*($5, 12)], CSE$1=[*($5, 3)]) > > > LogicalTableScan(table=[[CATALOG, SALES, EMP]])``` > > > I found a RelOptRule that is named as CommonRelSubExprRule, but it is > > > abstract. I wonder if anyone has implemented it? Is it the same as > what I > > > am doing? > > > Thanks very much~ > > > > > -- > > Best, > Benchao Li >