We implemented the GROUP_ID() function in
CALCITE-512 but we got the specification wrong, and it returns the wrong result.
GROUP_ID is not in the SQL standard. It is implemented only by Oracle.
I mistakenly believed that GROUP_ID() is equivalent to GROUPING_ID(g1, ..., gn) (in a query with GROUP BY g1, ..., gn). In fact, GROUP_ID is useful only if you have duplicate grouping sets. If grouping sets are distinct, GROUP_ID() will always return zero.
produces grouping sets (deptno, job), (deptno), (). These are distinct, so GROUP_ID() is 0 for all rows.
As you can see, the grouping set () occurs twice. So there is one row in the result for each occurrence: the first occurrence has g = 0; the second has g = 1.
In my fix for
CALCITE-1069, I will change GROUP_ID() to always return 0. This is wrong, but nevertheless closer to the required behavior.