[ https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16826331#comment-16826331 ]
Haisheng Yuan commented on CALCITE-2948: ---------------------------------------- [~danny0405] For the following query, {code:java} select deptno from dept d where deptno in (select deptno from EMP where empno=d.deptno+1); {code} The patch still generates complicated plan. > SqlToRelConverter generates complicated logical plan 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: 1h 20m > 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)