[ https://issues.apache.org/jira/browse/CALCITE-3919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17082795#comment-17082795 ]
Wang Yanlin commented on CALCITE-3919: -------------------------------------- Hi, [~Chunwei Lei] , I'm currently working on a project which depends on calcite. In this project, I need to recognize some special pattern in the condition of join, and do some rewrite according to it. This is not a common sense semantics-equal rewrite. While I need to push project past join, but to keep the pattern in join condition. BTW, I'm OK to just forbid this kind of join condition push-down, it makes things simple. > 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)