[ https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16827382#comment-16827382 ]
Danny Chan commented on CALCITE-2948: ------------------------------------- For pattern to simplify: {code:java} SemiJoin - lef1 - Join(inner) - left2 - node{code} My current simplify logic is: # If the left1 and left2 are equivalent # If the SemiJoin join keys are all unique to datasource # If the SemiJoin join lefts keys and right keys come from same table/view Then we can remove the above SemiJoin. > Complicated logical plan generated for in subquery with non-equi condition > -------------------------------------------------------------------------- > > Key: CALCITE-2948 > URL: https://issues.apache.org/jira/browse/CALCITE-2948 > Project: Calcite > Issue Type: Bug > Components: core > Reporter: Haisheng Yuan > Assignee: Danny Chan > Priority: Major > Labels: pull-request-available, sub-query > Time Spent: 1.5h > Remaining Estimate: 0h > > Repro: > Add the following test to SqlToRelConverterTest.java. > {code:java} > @Test public void testSubQueryIN() { > final String sql = "select deptno\n" > + "from EMP e\n" > + "where deptno in (select deptno\n" > + "from EMP where empno=e.empno+1)"; > sql(sql).ok(); > } > {code} > Plan: > {code:java} > LogicalProject(DEPTNO=[$7]) > LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{0, 1}]) > LogicalProject(DEPTNO=[$7], EMPNO0=[$9]) > LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalProject(EMPNO=[$0]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > {code} > One join would suffice. -- This message was sent by Atlassian JIRA (v7.6.3#76005)