[CALCITE-2948] Complicated logical plan generated for in subquery with non-equi condition

• Type: Improvement
• Status: Reopened
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Labels: pull-request-available sub-query
• Assignee: Danny Chen
• Reporter: Haisheng Yuan
• Created: 24/Mar/19 03:37
• Updated: 01/Aug/24 17:46

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 deptno\n"
        + "from EMP where empno=e.empno+1)";
    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=[$7], EMPNO0=[$9])
        LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
          LogicalProject(EMPNO=[$0])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])

One join would suffice.

Issue Links

• relates to: CALCITE-1624 Inefficient plan for NOT IN correlated subqueries
• links to: GitHub Pull Request #1169