[CALCITE-2949] Double aggregate generated for in subquery with distinct

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: core
• Assignee: Unassigned
• Reporter: Haisheng Yuan
• Created: 24/Mar/19 03:56
• Updated: 24/Mar/19 18:11
• Resolved: 24/Mar/19 18:11

Description

Repro:
Add the following test to SqlToRelConverterTest.java.

@Test public void testSubQueryIN() {
    final String sql = "select deptno\n"
        + "from EMP e\n"
        + "where deptno in (select distinct deptno\n"
        + "from EMP where empno=e.empno)";
    sql(sql).ok();
  }

Plan:

LogicalProject(DEPTNO=[$7])
  LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalAggregate(group=[{0, 1}])
      LogicalProject(DEPTNO=[$0], EMPNO=[$1])
        LogicalAggregate(group=[{0, 1}])
          LogicalProject(DEPTNO=[$7], EMPNO=[$0])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])

One LogicalAggregate would suffice. Although the 2 aggregates can be merged by rules described in issue CALCITE-1172, which is not available yet.

Issue Links

• relates to: CALCITE-1172 Add rule to flatten two Aggregate operators into one