[CALCITE-6562] incorrect value calculated for AS MEASURE aggregate when leveraging multiple GROUPING SETS groups

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.37.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Adam Markowitz
• Created: 03/Sep/24 23:20
• Updated: 03/Sep/24 23:20

Description

 

!set outputformat mysql
!use scott

create view empm as
select *, avg(sal) as measure avg_sal
from emp;
(0 rows modified)

!update

SELECT job, year(hiredate) as hire_year, avg_sal FROM empm
GROUP BY GROUPING SETS
(
  (YEAR(hiredate), job),
  (YEAR(hiredate)),
  ()
)
ORDER BY job, YEAR(hiredate);
+-----------+-----------+---------+
| JOB       | HIRE_YEAR | AVG_SAL |
+-----------+-----------+---------+
| ANALYST   |      1981 | 3000.00 |
| ANALYST   |      1987 | 3000.00 |
| CLERK     |      1980 |  800.00 |
| CLERK     |      1981 |  950.00 |
| CLERK     |      1982 | 1300.00 |
| CLERK     |      1987 | 1100.00 |
| MANAGER   |      1981 | 2758.33 |
| PRESIDENT |      1981 | 5000.00 |
| SALESMAN  |      1981 | 1400.00 |
|           |      1980 |  800.00 |
|           |      1981 | 2282.50 |
|           |      1982 | 1300.00 |
|           |      1987 | 2050.00 |
|           |           | 2073.21 |
+-----------+-----------+---------+
(14 rows)

!ok 

failure:

 

 

< |           |      1980 |  800.00 |
< |           |      1981 | 2282.50 |
< |           |      1982 | 1300.00 |
< |           |      1987 | 2050.00 |
< |           |           | 2073.21 |
---
> |           |      1980 |         |
> |           |      1981 |         |
> |           |      1982 |         |
> |           |      1987 |         |
> |           |           |         | 

grouping set groups that contain NULLs do not render the aggregate value.

 

using an inline aggregate produces the correct results:

 

SELECT job, year(hiredate) as hire_year, avg(sal) as avg_sal FROM empm
GROUP BY GROUPING SETS
(
  (YEAR(hiredate), job),
  (YEAR(hiredate)),
  ()
)
ORDER BY job, YEAR(hiredate); 

 

 

May possibly be related to CALCITE-6561  (Perhaps NULL dimension values are not properly completing the aggregate lifecycle for AS MEASURE aggregates properly?)