[
https://issues.apache.org/jira/browse/CALCITE-3919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin updated CALCITE-3919:
---------------------------------
Description:
Currently, when apply ProjectJoinTransposeRule, the join condition may be also
pushed, for example
for sql
{noformat}
select emp.ename, sum(bonus.sal) from emp left outer join bonus on emp.ename =
bonus.ename and floor(emp.sal) > 10 group by emp.ename
{noformat}
the algebra is
{noformat}
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(ENAME=[$1], SAL0=[$11])
LogicalJoin(condition=[AND(=($1, $9), >(FLOOR($5), 10))], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
{noformat}
After applying *ProjectJoinTransposeRule*, the join condition 'floor(emp.sal) >
10' is also pushed down,
{noformat}
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(ENAME=[$0], SAL0=[$3])
LogicalJoin(condition=[AND(=($0, $2), $1)], joinType=[left])
LogicalProject(ENAME=[$1], >=[>(FLOOR($5), 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0], SAL=[$2])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
{noformat}
In some cases, users may want to push Project past Join, but keep the join
condition unchanged, so we can upgrade *ProjectJoinTransposeRule* to allow user
choose whether to keep join condition
was:
Currently, when apply ProjectJoinTransposeRule, the join condition may be also
pushed, for example
for sql
The relation algebra
for sql
{noformat}
select emp.ename, sum(bonus.sal) from emp left outer join bonus on emp.ename =
bonus.ename and floor(emp.sal) > 10 group by emp.ename
{noformat}
is
{noformat}
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(ENAME=[$1], SAL0=[$11])
LogicalJoin(condition=[AND(=($1, $9), >(FLOOR($5), 10))], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
{noformat}
After applying *ProjectJoinTransposeRule*, the join condition 'floor(emp.sal) >
10' is also pushed down,
{noformat}
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(ENAME=[$0], SAL0=[$3])
LogicalJoin(condition=[AND(=($0, $2), $1)], joinType=[left])
LogicalProject(ENAME=[$1], >=[>(FLOOR($5), 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0], SAL=[$2])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
{noformat}
In some cases, users may want to push Project past Join, but keep the join
condition unchanged, so we can upgrade *ProjectJoinTransposeRule* to allow user
choose whether to keep join condition
> Upgrade ProjectJoinTransposeRule to allow user choose whether to keep join
> condition
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-3919
> URL: https://issues.apache.org/jira/browse/CALCITE-3919
> Project: Calcite
> Issue Type: Improvement
> Reporter: Wang Yanlin
> Priority: Major
>
> Currently, when apply ProjectJoinTransposeRule, the join condition may be
> also pushed, for example
> for sql
> {noformat}
> select emp.ename, sum(bonus.sal) from emp left outer join bonus on emp.ename
> = bonus.ename and floor(emp.sal) > 10 group by emp.ename
> {noformat}
> the algebra is
> {noformat}
> LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
> LogicalProject(ENAME=[$1], SAL0=[$11])
> LogicalJoin(condition=[AND(=($1, $9), >(FLOOR($5), 10))], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> {noformat}
> After applying *ProjectJoinTransposeRule*, the join condition 'floor(emp.sal)
> > 10' is also pushed down,
> {noformat}
> LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
> LogicalProject(ENAME=[$0], SAL0=[$3])
> LogicalJoin(condition=[AND(=($0, $2), $1)], joinType=[left])
> LogicalProject(ENAME=[$1], >=[>(FLOOR($5), 10)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(ENAME=[$0], SAL=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> {noformat}
> In some cases, users may want to push Project past Join, but keep the join
> condition unchanged, so we can upgrade *ProjectJoinTransposeRule* to allow
> user choose whether to keep join condition
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
