[
https://issues.apache.org/jira/browse/CALCITE-2298?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16467126#comment-16467126
]
godfrey he edited comment on CALCITE-2298 at 5/8/18 9:11 AM:
-------------------------------------------------------------
maybe, _AggregatingNamespace_ is needed for _AggregatingSelectScope._ then the
result of _resolve_ method is _AggregatingSelectScope_ instead of __
_AggregatingSelectScope'_s parent when a SELECT statement is an aggregation.
and this also can fix the bug:
https://issues.apache.org/jira/browse/CALCITE-2297, the plan details as
following:
{code:java}
<TestCase name="testDecorrelateWithHavingAgg">
<Resource name="sql">
<![CDATA[select ename
from sales.emp
group by ename, deptno
having max(sal) <=
(select max(sal) from sales.emp_b where emp.deptno = emp_b.deptno group by
ename)]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
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.DEPTNO, $7)])
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]])
]]>
</Resource>
<Resource name="planMid">
<![CDATA[
LogicalProject(ENAME=[$0])
LogicalProject(ENAME=[$0], DEPTNO=[$1], $f2=[$2])
LogicalFilter(condition=[<=($2, $3)])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])
LogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)])
LogicalProject(ENAME=[$1], DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalProject(EXPR$0=[$1])
LogicalAggregate(group=[{0}], EXPR$0=[MAX($1)])
LogicalProject(ENAME=[$1], SAL=[$5])
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</Resource>
</TestCase>
{code}
the correct requiredColumns is \{1} instead of \{7}, and the rowType should be
_LogicalAggregate(group=[\{0, 1}], agg#0=[MAX($2)])_'s _output rowType instead
of _LogicalTableScan(table=[[CATALOG, SALES, EMP]])__'s in
SqlToRelConverter#Blackboard#lookupExp(SqlQualified qualified) method (line
number: 4255).
was (Author: godfreyhe):
maybe, _AggregatingNamespace_ is needed for _AggregatingSelectScope._ then the
result of _resolve_ method is _AggregatingSelectScope_ instead of __
_AggregatingSelectScope'_s parent when a SELECT statement is an aggregation.
and this also can fix the bug:
https://issues.apache.org/jira/browse/CALCITE-2297, the plan details as
following:
{code:java}
<TestCase name="testDecorrelateWithHavingAgg">
<Resource name="sql">
<![CDATA[select ename
from sales.emp
group by ename, deptno
having max(sal) <=
(select max(sal) from sales.emp_b where emp.deptno = emp_b.deptno group by
ename)]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
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.DEPTNO, $7)])
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]])
]]>
</Resource>
<Resource name="planMid">
<![CDATA[
LogicalProject(ENAME=[$0])
LogicalProject(ENAME=[$0], DEPTNO=[$1], $f2=[$2])
LogicalFilter(condition=[<=($2, $3)])
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])
LogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)])
LogicalProject(ENAME=[$1], DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalProject(EXPR$0=[$1])
LogicalAggregate(group=[{0}], EXPR$0=[MAX($1)])
LogicalProject(ENAME=[$1], SAL=[$5])
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</Resource>
</TestCase>
{code}
the correct requiredColumns is \{1} instead of \{7}, and the rowType should be
_LogicalAggregate(group=[{0, 1}], agg#0=[MAX($2)])_'s output rowType instead of
_LogicalTableScan(table=[[CATALOG, SALES, EMP]])_'s in
SqlToRelConverter#Blackboard#lookupExp(SqlQualified qualified) method (line
number: 4255).
> 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)
