Re: weird GROUPING SETS and ORDER BY behaviour

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

In response to

Responses

Browse pgsql-hackers by date

  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