[
https://issues.apache.org/jira/browse/CALCITE-7930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18142612#comment-18142612
]
Mihai Budiu commented on CALCITE-7930:
--------------------------------------
This was discovered while writing tests for CALCITE-7929
> 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
> Priority: Minor
>
> 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)
