Adam Markowitz created CALCITE-6561:
---------------------------------------
Summary: incorrect value calculated for AS MEASURE aggregate when
grouping by a column with NULLs
Key: CALCITE-6561
URL: https://issues.apache.org/jira/browse/CALCITE-6561
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: Adam Markowitz
Test case using an `AS MEASURE` aggregate with expected data:
{code:java}
!set outputformat mysql
!use scott
create view empm as
select *, avg(sal) as measure avg_sal
from emp;
(0 rows modified)
!update
# GROUP BY a dimension with NULLs
SELECT mgr, avg_sal FROM empm
GROUP BY mgr;
+------+---------+
| MGR | AVG_SAL |
+------+---------+
| 7566 | 3000.00 |
| 7698 | 1310.00 |
| 7782 | 1300.00 |
| 7788 | 1100.00 |
| 7839 | 2758.33 |
| 7902 | 800.00 |
| | 5000.00 |
+------+---------+
(7 rows)
!ok
{code}
The NULL value does not get the aggregate value causing a failure:
{code:java}
< | | 5000.00 |
---
> | | | {code}
However, swapping out the `AS MEASURE` column reference with an inline
aggregate results in the correct data:
{code:java}
SELECT mgr, avg(sal) as avg_sal FROM empm
GROUP BY mgr;
+------+---------+
| MGR | AVG_SAL |
+------+---------+
| 7566 | 3000.00 |
| 7698 | 1310.00 |
| 7782 | 1300.00 |
| 7788 | 1100.00 |
| 7839 | 2758.33 |
| 7902 | 800.00 |
| | 5000.00 |
+------+---------+
(7 rows)
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
