iwanttobepowerful commented on code in PR #4495:
URL: https://github.com/apache/calcite/pull/4495#discussion_r2295070035
##########
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:
1st group is (deptno, job)
3rd group is (deptno, job) too
Is this intended?
--
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]
