Mihai Budiu created CALCITE-7931:
------------------------------------
Summary: RelDecorrelator gives wrong results for a correlated
sub-query with GROUPING SETS that contain a constant key
Key: CALCITE-7931
URL: https://issues.apache.org/jira/browse/CALCITE-7931
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.42.0
Environment: Related to CALCITE-7929 CALCITE-7930
Reporter: Mihai Budiu
A correlated sub-query whose GROUPING SETS, ROLLUP or CUBE contain a constant
key returns wrong results after RelDecorrelator, with no error.
{code:java}
!use scott
!set outputformat mysql
!set expand false
# Each department has 2 groups, ('a') and ().
# DEPT 40 is left out because of CALCITE-7930.
select deptno,
(select count(*) from
(select count(*) as n from emp where emp.deptno = dept.deptno
group by grouping sets (('a'), ())) as t) as g
from dept
where deptno < 40;
+--------+---+
| DEPTNO | G |
+--------+---+
| 10 | 2 |
| 20 | 2 |
| 30 | 2 |
+--------+---+
(3 rows)
!ok
{code}
PostgreSQL 14 returns the rows above (with 'a'::text, because PostgreSQL
rejects a string constant in GROUP BY). RelDecorrelator returns G = 1 for
each department, with expand=false and with expand=true.
TopDownGeneralDecorrelator returns the correct result.
Number of groups for each of DEPT 10, 20 and 30:
||GROUP BY||Correct (PostgreSQL)||RelDecorrelator||
|GROUPING SETS (('a'), ())|2|1|
|CUBE('a')|2|1|
|ROLLUP('a', job)|5|0|
|ROLLUP(job, 'a')|7|3|
|GROUPING SETS (('a', job), (job))|6|0|
|GROUPING SETS (('a', job), ('a'))|4|0|
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
