From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: weird GROUPING SETS and ORDER BY behaviour |
Date: | 2024-01-08 10:23:51 |
Message-ID: | CAEzk6ffZs15JjxhB73e1Z6tnd_PbvU-GOxJYGe38KpggoQcZYg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Geoff Winkless | 2024-01-08 11:12:43 | Re: weird GROUPING SETS and ORDER BY behaviour |
Previous Message | Andy Fan | 2024-01-08 09:52:57 | Re: Shared detoast Datum proposal |