I started investigating but didn't get a chance to follow up. I don't know whether Calcite or Postgres is correct. I suspect the difference is due to the expression 'comm is null'. Because I don't know whether GROUPING_ID's behavior is defined if its arguments are not all columns. But I think the correct behavior would be to make it equivalent to the result if you make the expression into a column - by adding 'comm is null AS c' in a subquery or CTE.
Usually Postgres has the correct behavior, but we shouldn't let it win by default. :) On Tue, Mar 12, 2024 at 2:35 PM Mihai Budiu <mbu...@gmail.com> wrote: > > I have been staring at a particular Quidem test from agg.iq: > https://github.com/apache/calcite/blame/b412fa4ac0b1340a9f4e50c1d7ae4c869dcd1d2f/core/src/test/resources/sql/agg.iq#L1304 > > select sum(sal) as s, > grouping_id(job, deptno, comm is null) as g, > group_id() as i > from "scott".emp > group by grouping sets ((job, deptno, comm is null), > (job, deptno), (job, comm is null), (job, comm is null)) > order by g, i, s desc; > > Our runtime produces a different result than the result in the quidem test. > Postgres seems to agree with our runtime (I had to slightly modify the query, > since Postgres does not have group_id(), but it does agree on the reduced set > of columns). > > The results differ in the G column only, where the following 5 lines have a 2 > in Postgres, but a 0 in Calcite (whenever group_id() is 1 the results differ > in the G colum): > > +---------+---+---+ > | S | G | I | > +---------+---+---+ > ... > | 8275.00 | 0 | 1 | > | 6000.00 | 0 | 1 | > | 5600.00 | 0 | 1 | > | 5000.00 | 0 | 1 | > | 4150.00 | 0 | 1 | > > This test has been added 3 years ago, as part of > https://issues.apache.org/jira/browse/CALCITE-4748 by > https://github.com/apache/calcite/commits?author=NobiGo > > I am wondering how the test results from Quidem are generally validated. > > Since I am not sure this is a bug in Calcite I haven't filed a Jira case. > > Can someone else validate the output of this query on some other database > that supports grouping and grouping sets? > > Thank you! > Mihai
