From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
Cc: | 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 11:12:43 |
Message-ID: | CAEzk6femZE4Gd-b2ZWwjZCd-rMqF8hsPnhkDvMonXNhk4yokaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Guo | 2024-01-08 11:14:11 | Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 |
Previous Message | Geoff Winkless | 2024-01-08 10:23:51 | Re: weird GROUPING SETS and ORDER BY behaviour |