Hi Jiajun,
I'm thinking about using the optimization of Calc, but change CalcToSql.
Do you mean you want to translate the above query to this one?
```SQL
SELECT "salary", EXPR$1, EXPR$0, EXPR$2, EXPR$2
FROM (
SELECT "salary", EXPR$0, EXPR$1, EXPR$0 + EXPR$1 AS EXPR$2
FROM (
SELECT "salary", "salary" * 3 AS EXPR$0, "salary" * 12 AS EXPR$1
FROM "hr"."emps"
)
)
```
If this is your intended way, I think you may need to figure out whether
Trino
will merge these projections in the first place.
Even Trino could leverage the nested projections, you may still meet some
complex expression trees which are very deep, I don't think this is a good
solution.
Maybe the best way is to make Trino support this natively.
Jiajun Xie <[email protected]> 于2022年7月16日周六 16:51写道：
> 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 <[email protected]> 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 <[email protected]> 于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 <[email protected]>
> > 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
> >
>
--
Best,
Benchao Li
