Re: weird GROUPING SETS and ORDER BY behaviour

On Mon, 8 Jan 2024 at 10:23, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> Seems there was a reason why I thought that: per the documentation:
>
> "The arguments to the GROUPING function are not actually evaluated,
> but they must exactly match expressions given in the GROUP BY clause
> of the associated query level."
>
> https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE

To throw a spanner in the works, it looks like it's not the test
itself that's failing: it's putting the ORDERing in a CASE at all that
fails.

... ORDER BY
CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, CASE
WHEN true THEN 2 ELSE 2 END;
n | concat
----+--------
n1 |
n2 |
n3 |
n4 |
n5 |
| n3x3
| n5x1
| n2x4
| n1x5
| n4x2

but without the CASE works fine:

... ORDER BY
CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, 2;
n | concat
----+--------
n4 |
n2 |
n3 |
n5 |
n1 |
| n1x5
| n2x4
| n3x3
| n4x2
| n5x1

What's even more of a head-scratcher is why fixing this this then
breaks the _first_ group's ORDERing.

It _looks_ like removing the CASE altogether and ordering by the
GROUPING value for all the grouping sets first:

ORDER BY
GROUPING(test1.n,CONCAT(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq))), 1, 2;

actually works. I'm trying to figure out if that scales up or if it's
just dumb luck that it works for my example.

Geoff