[
https://issues.apache.org/jira/browse/CALCITE-7126?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18012938#comment-18012938
]
Zhen Chen commented on CALCITE-7126:
------------------------------------
[~mbudiu] As I mentioned earlier, if ROLLUP is fully expanded, the result will
be incorrect because the original expansion logic makes each "groupSet" the
same (and complete, such as (0, 1, 2)). It will not produce a case where the
"groupSet" is empty, so this issue does not get exposed.
> The calculation result of grouping function is wrong
> ----------------------------------------------------
>
> Key: CALCITE-7126
> URL: https://issues.apache.org/jira/browse/CALCITE-7126
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.40.0
> Reporter: Zhen Chen
> Priority: Major
> Labels: pull-request-available
>
> The calculation result of grouping function is wrong.
> in agg.iq result is
> {code:java}
> +--------+-----+-------+--------+-------------+----------+----------+
> | DEPTNO | JOB | SAL | EXPR$3 | DEPTNO_FLAG | JOB_FLAG | SAL_FLAG |
> +--------+-----+-------+--------+-------------+----------+----------+
> | 10 | aa | | 1250 | 0 | 0 | 1 |
> | 10 | aa | | 1250 | 0 | 0 | 0 |
> | 10 | | 7000 | | 0 | 1 | 0 |
> | 10 | | 10000 | 1000 | 0 | 1 | 0 |
> | 10 | | 11500 | 250 | 0 | 1 | 0 |
> | 20 | aa | | 500 | 0 | 0 | 1 |
> | 20 | aa | | 500 | 0 | 0 | 0 |
> | 20 | | 8000 | 500 | 0 | 1 | 0 |
> +--------+-----+-------+--------+-------------+----------+----------+
> (8 rows)
> {code}
> in pgsql result is
> {code:java}
> deptno | job | sal | sum | deptno_flag | job_flag | sal_flag
> --------+-----+-------+------+-------------+----------+----------
> 10 | aa | | 1250 | 0 | 0 | 1
> 10 | aa | | 1250 | 0 | 0 | 1
> 10 | | 7000 | | 0 | 1 | 0
> 10 | | 10000 | 1000 | 0 | 1 | 0
> 10 | | 11500 | 250 | 0 | 1 | 0
> 20 | aa | | 500 | 0 | 0 | 1
> 20 | aa | | 500 | 0 | 0 | 1
> 20 | | 8000 | 500 | 0 | 1 | 0
> (8 rows)
> {code}
> The sal_flag column has incorrect values in rows 2 and 7.
> SQL is
> {code:java}
> WITH emps_data AS (
> SELECT * FROM (VALUES
> (100, 10, 'Bill', 10000, 1000, 'aa'),
> (110, 10, 'Theodore', 11500, 250, 'aa'),
> (150, 10, 'Sebastian', 7000, NULL, 'aa'),
> (200, 20, 'Eric', 8000, 500, 'aa')
> ) AS t(empno, deptno, ename, sal, comm, job)
> )
> SELECT deptno, job, sal, SUM(comm),
> GROUPING(deptno) AS deptno_flag,
> GROUPING(job) AS job_flag,
> GROUPING(sal) AS sal_flag
> FROM emps_data
> GROUP BY GROUPING SETS ((deptno, job), (deptno, sal), (deptno, job))
> ORDER BY deptno, job, sal;
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
