[
https://issues.apache.org/jira/browse/CALCITE-2857?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16790053#comment-16790053
]
Haisheng Yuan commented on CALCITE-2857:
----------------------------------------
Can we add a filter (emp.job > dept.name) on top of the semi join on
(dept.deptno=emp.deptno)?
If set the join condition to dept.deptno=emp.deptno AND emp.job > dept.name,
will it be able to converted to hash join? though it depends how 3rd party
engines implement physical operator. Some engine's physical hash join has join
condition and join filter, some don't have.
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition
> --------------------------------------------------------------------------------
>
> Key: CALCITE-2857
> URL: https://issues.apache.org/jira/browse/CALCITE-2857
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: godfrey he
> Priority: Major
>
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition,
> e.g.
> {code:sql}
> select * from (select * from dept where dept.deptno in (select emp.deptno
> from emp where emp.job <> dept.name))R where R.deptno <= 10
> {code}
> Currently, this query will be converted to
> {code}
> SemiJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])
> LogicalFilter(condition=[<=($0, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7], NAME=[$9])
> LogicalJoin(condition=[<>($2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(NAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> however the following plan is more efficient than the above one:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalFilter(condition=[<=($0, 10)])
> SemiJoin(condition=[=AND(=($0, $3), <>($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(JOB=[$2], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
