[CALCITE-2298] Correlated SubQuery in Having generates error plan when correlated fields does not exist

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.15.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: godfrey he
• Created: 02/May/18 12:45
• Updated: 27/Feb/19 23:16

Description

 @Test public void testDecorrelateWithUnresolvedField() throws Exception {
    final String sql = "select ename\n"
                       + "from sales.emp\n"
                       + "group by ename, deptno\n"
                       + "having max(sal) <= \n"
                       + "  (select max(sal) from sales.emp_b where emp.job = emp_b.job group by ename)";
    checkSubQuery(sql).withLateDecorrelation(true).check();
  }

for now, we will get the following plan:

LogicalProject(ENAME=[$0])
  LogicalFilter(condition=[<=($2, $SCALAR_QUERY({
LogicalProject(EXPR$0=[$1])
  LogicalAggregate(group=[{0}], EXPR$0=[MAX($1)])
    LogicalProject(ENAME=[$1], SAL=[$5])
      LogicalFilter(condition=[=($cor0.JOB, $2)])
        LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
}))])
    LogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)])
      LogicalProject(ENAME=[$1], DEPTNO=[$7], SAL=[$5])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])

However emp.job is not the grouping fields or the aggCall result.
the expected result is throwing an Exception, like:

org.apache.calcite.runtime.CalciteContextException: From line 5, column 47 to line 5, column 50: Column 'JOB' not found in 'LogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)])'

Issue Links

• links to: PR687