[jira] [Created] (CALCITE-7297) The result is incorrect when the GROUP BY key in a subquery is an outer variable

[weihua zhang (Jira)]

weihua zhang created CALCITE-7297:
-------------------------------------

             Summary: The result is incorrect when the GROUP BY key in a 
subquery is an outer variable
                 Key: CALCITE-7297
                 URL: https://issues.apache.org/jira/browse/CALCITE-7297
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: weihua zhang



{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







--
This message was sent by Atlassian Jira
(v8.20.10#820010)