[ https://issues.apache.org/jira/browse/CALCITE-2857?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Haisheng Yuan updated CALCITE-2857: ----------------------------------- Labels: sub-query (was: ) > 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 > Labels: sub-query > > 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)