[
https://issues.apache.org/jira/browse/CALCITE-7297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7297:
----------------------------------
Description:
{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}
was:
{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]) // root cause:
because no condition, get wrong reult
LogicalFilter(condition=[IS NOT NULL($7)])
LogicalTableScan(table=[[scott, EMP]])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[scott, DEPT]])
{code}
lack condition in join
> 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)
