[
https://issues.apache.org/jira/browse/CALCITE-7297?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18039217#comment-18039217
]
Julian Hyde commented on CALCITE-7297:
--------------------------------------
While testing this, can you check what happens if no rows match the correlating
variable? (That is, {{dept}} has a {{deptno}} value that {{emp}} does not.) I
ask because the queries
{code}
SELECT COUNT(*) FROM aTable GROUP BY 'a literal';
SELECT COUNT(*) FROM aTable;
{code}
usually return the same result, but when {{aTable}} is empty, the first returns
0 rows and the second returns 1 row.
> 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])
> LogicalFilter(condition=[=($7, $cor1.deptno)])
> 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)
