[
https://issues.apache.org/jira/browse/CALCITE-7297?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18039191#comment-18039191
]
Julian Hyde commented on CALCITE-7297:
--------------------------------------
Good catch. I’ll also note that the GROUP BY key has the same value for the
whole subquery and could therefore be safely removed, leaving the subquery with
“GROUP BY ()”.
> The result is incorrect when the GROUP BY key in a subquery is a
> RexFieldAccess
> -------------------------------------------------------------------------------
>
> Key: CALCITE-7297
> URL: https://issues.apache.org/jira/browse/CALCITE-7297
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: weihua zhang
> Priority: Major
>
> {code:sql}
> SELECT *,
> (SELECT COUNT(*)
> FROM ( SELECT empno, ename, job FROM emp WHERE emp.deptno = dept.deptno) AS
> sub
> GROUP BY deptno
> ) AS num_dept_groups
> FROM dept;
> {code}
> right result:
> {code:java}
> DEPTNO, DNAME, LOC, NUM_DEPT_GROUPS
> 10, ACCOUNTING, NEW YORK, 3
> 20, RESEARCH, DALLAS, 5
> 30, SALES, CHICAGO, 6
> 40, OPERATIONS, BOSTON, null
> {code}
> but return wrong result:
> {code:java}
> DEPTNO, DNAME, LOC, NUM_DEPT_GROUPS
> 10, ACCOUNTING, NEW YORK, 14
> 20, RESEARCH, DALLAS, 14
> 30, SALES, CHICAGO, 14
> 40, OPERATIONS, BOSTON, 14
> {code}
> {code:java}
> LogicalProject(variablesSet=[[$cor1]], deptno=[$0], dname=[$1], loc=[$2],
> NUM_DEPT_GROUPS=[$SCALAR_QUERY({
> LogicalProject(EXPR$0=[$1])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject($f0=[$cor1.deptno]) // why $cor1 ???
> LogicalFilter(condition=[=($7, $cor1.deptno)]) // why $cor1 ???
> LogicalTableScan(table=[[testdb, emp]])
> })])
> LogicalTableScan(table=[[testdb, dept]])
> {code}
> {code:java}
> LogicalCorrelate(correlation=[$cor1], joinType=[left], requiredColumns=[{0}])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(EXPR$0=[$1])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject($f0=[$cor1.DEPTNO])
> LogicalFilter(condition=[=($7, $cor1.DEPTNO)])
> LogicalTableScan(table=[[scott, EMP]])
> {code}
> {code:java}
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f1=[$4])
> LogicalJoin(condition=[=($0, $3)], joinType=[left])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])
> LogicalProject(DEPTNO0=[$1], EXPR$0=[$2])
> LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT()])
> LogicalProject($f0=[$8], DEPTNO0=[$8])
> LogicalJoin(condition=[true], joinType=[inner]) // because no
> condition, get wrong reult
> LogicalFilter(condition=[IS NOT NULL($7)])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
