[
https://issues.apache.org/jira/browse/CALCITE-3919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17097950#comment-17097950
]
Wang Yanlin commented on CALCITE-3919:
--------------------------------------
Hi, [~zabetak] , I came across a similar case when apply the
*FilterJoinRule.FILTER_ON_JOIN* rule. In this case I just want to keep filter
past down join and keep the join condition unchanged. But after applying the
rule, part of the join condition also pushed down.
What do you think of it, should I also forbid the push-down. If someone want
to push join condition down, he/she can use *FilterJoinRule.JOIN* to do it.
The sql of the case is
{code:java}
final String sql = "select e.deptno, e.ename, d.name\n"
+ "from emp as e\n"
+ "join dept as d\n"
+ "on e.deptno = d.deptno\n"
+ "and name = 'dbc'\n"
+ "where ename = 'abc'\n";
{code}
Before apply any rule, the relation algebra is
{noformat}
LogicalProject(DEPTNO=[$7], ENAME=[$1], NAME=[$10])
LogicalFilter(condition=[=($1, 'abc')])
LogicalJoin(condition=[AND(=($7, $9), =($10, 'dbc'))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
after apply FilterJoinRule.FILTER_ON_JOIN, the returned algebra is
{noformat}
LogicalProject(DEPTNO=[$7], ENAME=[$1], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalFilter(condition=[=($1, 'abc')])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalFilter(condition=[=($1, 'dbc')])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
as you can see, part of the join condition also pushed down.
> 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)
