xiedeyantu commented on code in PR #4495:
URL: https://github.com/apache/calcite/pull/4495#discussion_r2295074751
##########
core/src/test/resources/sql/agg.iq:
##########
@@ -3897,4 +3899,117 @@ EnumerableCalc(expr#0=[{inputs}],
expr#1=[CAST($t0):BIGINT NOT NULL], M=[$t1])
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# [CALCITE-7126] The calculation result of grouping function is wrong
+SELECT deptno, job, sal, SUM(comm),
+ GROUPING(deptno) AS deptno_flag,
+ GROUPING(job) AS job_flag,
+ GROUPING(sal) AS sal_flag
+FROM emp
+GROUP BY GROUPING SETS ((deptno, job), (deptno, sal), (deptno, job))
+ORDER BY deptno, job, sal;
++--------+-----------+---------+---------+-------------+----------+----------+
+| DEPTNO | JOB | SAL | EXPR$3 | DEPTNO_FLAG | JOB_FLAG | SAL_FLAG |
++--------+-----------+---------+---------+-------------+----------+----------+
+| 10 | CLERK | | | 0 | 0 | 1 |
+| 10 | CLERK | | | 0 | 0 | 1 |
+| 10 | MANAGER | | | 0 | 0 | 1 |
+| 10 | MANAGER | | | 0 | 0 | 1 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 |
+| 10 | PRESIDENT | | | 0 | 0 | 1 |
+| 10 | | 1300.00 | | 0 | 1 | 0 |
+| 10 | | 2450.00 | | 0 | 1 | 0 |
+| 10 | | 5000.00 | | 0 | 1 | 0 |
+| 20 | ANALYST | | | 0 | 0 | 1 |
+| 20 | ANALYST | | | 0 | 0 | 1 |
+| 20 | CLERK | | | 0 | 0 | 1 |
+| 20 | CLERK | | | 0 | 0 | 1 |
+| 20 | MANAGER | | | 0 | 0 | 1 |
+| 20 | MANAGER | | | 0 | 0 | 1 |
+| 20 | | 800.00 | | 0 | 1 | 0 |
+| 20 | | 1100.00 | | 0 | 1 | 0 |
+| 20 | | 2975.00 | | 0 | 1 | 0 |
+| 20 | | 3000.00 | | 0 | 1 | 0 |
+| 30 | CLERK | | | 0 | 0 | 1 |
+| 30 | CLERK | | | 0 | 0 | 1 |
+| 30 | MANAGER | | | 0 | 0 | 1 |
+| 30 | MANAGER | | | 0 | 0 | 1 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 |
+| 30 | SALESMAN | | 2200.00 | 0 | 0 | 1 |
+| 30 | | 950.00 | | 0 | 1 | 0 |
+| 30 | | 1250.00 | 1900.00 | 0 | 1 | 0 |
+| 30 | | 1500.00 | 0.00 | 0 | 1 | 0 |
+| 30 | | 1600.00 | 300.00 | 0 | 1 | 0 |
+| 30 | | 2850.00 | | 0 | 1 | 0 |
++--------+-----------+---------+---------+-------------+----------+----------+
+(30 rows)
+
+!ok
+
+EnumerableMergeUnion(all=[true])
+ EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC])
+ EnumerableCalc(expr#0..6=[{inputs}], DEPTNO=[$t2], JOB=[$t0], SAL=[$t1],
EXPR$3=[$t3], DEPTNO_FLAG=[$t4], JOB_FLAG=[$t5], SAL_FLAG=[$t6])
+ EnumerableAggregate(group=[{2, 5, 7}], groups=[[{2, 7}, {5, 7}]],
EXPR$3=[SUM($6)], DEPTNO_FLAG=[GROUPING($7)], JOB_FLAG=[GROUPING($2)],
SAL_FLAG=[GROUPING($5)])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC],
dir2=[ASC])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[null:DECIMAL(7, 2)],
expr#6=[1], DEPTNO=[$t1], JOB=[$t0], SAL=[$t5], EXPR$3=[$t2],
DEPTNO_FLAG=[$t3], JOB_FLAG=[$t4], SAL_FLAG=[$t6])
+ EnumerableAggregate(group=[{2, 7}], EXPR$3=[SUM($6)],
DEPTNO_FLAG=[GROUPING($7)], JOB_FLAG=[GROUPING($2)])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# The subset of GROUPING SETS cannot contain all the fields in the GROUPING
and GROUPING SETS has duplicate groupings
+SELECT deptno, job, sal,
+ SUM(comm) AS sum_comm,
+ GROUPING(deptno, job, sal) AS flag,
+ GROUP_ID() AS g
+FROM emp
+GROUP BY GROUPING SETS ((deptno, job), (deptno, sal), (deptno, job))
Review Comment:
This is to validate the correctness of SQL semantics, since in many cases
SQL is generated by code. But I understand your point—writing it manually like
this probably doesn’t make much sense. Also, some databases (like Databend)
inherently avoid generating duplicate records for repeated GROUPING SETs.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
