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

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2298?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16461262#comment-16461262 ] 

Julian Hyde commented on CALCITE-2298:
--------------------------------------

Good catch! Looks like you have written a test for {{RelOptRulesTest}}. The validator should detect this problem. Therefore the fix should be in the validator, and the test should be in {{SqlValidatorTest}}.

Probably the fix is that scope used to look for correlating variables in the sub-query should link into the scope that would be used to validate the HAVING clause.

> 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
>            Priority: Major
>
> {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)