[jira] [Comment Edited] (CALCITE-3919) Upgrade ProjectJoinTransposeRule to allow user choose whether to keep join condition

["Chunwei Lei (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17082069#comment-17082069 ] 

Chunwei Lei edited comment on CALCITE-3919 at 4/13/20, 5:19 AM:
----------------------------------------------------------------

I don't think it is a bug. The condition seems useless if it is already pushed down.  Why would you want to retain it?


was (Author: chunwei lei):
I don't think it is a bug. The condition seems useless if it is already pushed down.  Why would you want to retain it?

 

> 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
>            Assignee: Wang Yanlin
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> 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)