Mihai Budiu created CALCITE-7930:
------------------------------------
Summary: Correlated sub-query with ROLLUP or GROUPING SETS loses
the () group when the sub-query input is empty
Key: CALCITE-7930
URL: https://issues.apache.org/jira/browse/CALCITE-7930
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.42.0
Reporter: Mihai Budiu
A correlated sub-query that groups by ROLLUP, CUBE or GROUPING SETS with an
empty grouping set () loses the row of the () group when its input is empty
for an outer row. The () group must give one row even for an empty input (see
CALCITE-7421. The query returns a wrong result.
{code:java}
!use scott
!set outputformat mysql
!set expand false
# Number of groups of ROLLUP(job) for each department.
# DEPT 40 has no employees, so only the () group remains: G is 1.
select deptno,
(select count(*) from
(select count(*) as n from emp where emp.deptno = dept.deptno
group by rollup(job)) as t) as g
from dept;
+--------+---+
| DEPTNO | G |
+--------+---+
| 10 | 4 |
| 20 | 4 |
| 30 | 4 |
| 40 | 1 |
+--------+---+
(4 rows)
!ok
{code}
PostgreSQL 14 returns the rows above. Calcite returns G = 0 for DEPT 40:
|| Decorrelator || expand=false || expand=true ||
| RelDecorrelator | 0 (wrong) | 1 |
| TopDownGeneralDecorrelator | 0 (wrong) | 0 (wrong) |
* With GROUP BY () in place of ROLLUP(job), both decorrelators return 1.
* EXISTS over the same sub-query is correct (CALCITE-7421).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
