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
>
