Re: weird GROUPING SETS and ORDER BY behaviour

On Sat, 6 Jan 2024 at 23:27, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> Well yes. I assumed that since it's required that a group expression is in the query itself that
> the grouping values were taken from the result set, I have to admit to some surprise that
> they're calculated twice (three times?).

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

Mildly interesting: you can pass column positions to GROUP BY and
ORDER BY but if you try to pass a position to GROUPING() (I wondered
if that would help the engine somehow) it fails:

SELECT
test1.n,
CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
FROM test1
GROUP BY
GROUPING SETS(
1,
2
)
ORDER BY
CASE WHEN GROUPING(1)=0 THEN 1 ELSE NULL END NULLS FIRST,
CASE WHEN GROUPING(2)=0 THEN 2 ELSE NULL END NULLS FIRST;

ERROR: arguments to GROUPING must be grouping expressions of the
associated query level

Geoff