Adam Markowitz created CALCITE-6562:
---------------------------------------
Summary: incorrect value calculated for AS MEASURE aggregate when
leveraging multiple GROUPING SETS groups
Key: CALCITE-6562
URL: https://issues.apache.org/jira/browse/CALCITE-6562
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: Adam Markowitz
{code:java}
!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 {code}
failure:
{code:java}
< | | 1980 | 800.00 |
< | | 1981 | 2282.50 |
< | | 1982 | 1300.00 |
< | | 1987 | 2050.00 |
< | | | 2073.21 |
---
> | | 1980 | |
> | | 1981 | |
> | | 1982 | |
> | | 1987 | |
> | | | | {code}
grouping set groups that contain NULLs do not render the aggregate value.
using an inline aggregate produces the correct results:
{code:java}
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); {code}
May possibly be related to CALCITE-6561 (Perhaps NULL dimension values are not
properly completing the aggregate lifecycle for AS MEASURE aggregates properly?)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
