[ https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16828850#comment-16828850 ]
Haisheng Yuan commented on CALCITE-2948: ---------------------------------------- I think you have missed our last conversation that transforming expressions is not the way to go, because the solution you proposed doesn't work for a = $b%s, a=func($b), a > $b etc.. After SubQueryRemoveRule, I am expecting the following plan: {code:java} select * from R where r1 in (select s1 from S where r2 = s2+1) Correlate |-- R +--Filter (r1 = s1 and r2 = s2+1) +-- S {code} Check the method SubQueryRemoveRule.rewriteIn. BTW, the plan of testInSubqueryWithNonEqualCondition4 in your latest patch is wrong. > 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)