[ https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16819972#comment-16819972 ]
Volodymyr Vysotskyi commented on CALCITE-2948: ---------------------------------------------- [~danny0405], the aggregation for these cases guarantees that the resulting rows number will be not greater than the row number of the outer table, since without aggregate it may happen for the case when joining columns with repeated values. Could you please check whether this case works correctly with your fix? > 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: 10m > 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)