You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "godfrey he (JIRA)" <ji...@apache.org> on 2018/05/02 12:46:00 UTC
[jira] [Created] (CALCITE-2298) Correlated SubQuery in Having
generates error plan when correlated fields does not exist
godfrey he created CALCITE-2298:
-----------------------------------
Summary: Correlated SubQuery in Having generates error plan when correlated fields does not exist
Key: CALCITE-2298
URL: https://issues.apache.org/jira/browse/CALCITE-2298
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.15.0
Reporter: godfrey he
Assignee: Julian Hyde
{code}
@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();
}
{code}
for now, we will get the following plan:
{code}
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]])
{code}
However emp.job is not the grouping fields or the aggCall result.
the expected result is throwing an Exception, like:
{code}
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)])'
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)