[
https://issues.apache.org/jira/browse/CALCITE-6561?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6561:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Minor
> Labels: pull-request-available
>
> 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)
