[CALCITE-2295] Correlated SubQuery with Project will generate error plan

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.15.0
• Component/s: core
• Assignee: Unassigned
• Reporter: godfrey he
• Created: 02/May/18 12:02
• Updated: 28/Feb/24 06:03

Description

test case in RelOptRulesTest.java:

@Test public void testDecorrelateWithProject() throws Exception {
    final String sql = "select sal,\n"
                       + "exists (select * from emp_b where emp.deptno = emp_b.deptno)\n"
                       + "from sales.emp";
    checkSubQuery(sql).withLateDecorrelation(true).check();
}

after SqlToRelConverter, the plan is

LogicalProject(SAL=[$5], EXPR$1=[EXISTS({
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
  LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
})])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

NOTES: LogicalProject does not have any variablesSet attributes.

the correct plan should be:

LogicalProject(SAL=[$5], EXPR$1=[EXISTS({
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
  LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
})], variablesSet=[[$cor0]])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])