weird GROUPING SETS and ORDER BY behaviour

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: weird GROUPING SETS and ORDER BY behaviour
Date: 2024-01-05 17:38:29
Message-ID: CAEzk6fcgXWabEG+RFDaG6tDmFX6g1h7LPGUdrX85Pb0XB3B76g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

We have some (generated) SQL that uses grouping sets to give us the
same data grouped in multiple ways (with sets of groups configurable
by the user), with the ordering of the rows the same as the grouping
set. This generally works fine, except for when one of the grouping
sets contains part of another grouping set joined against a subquery
(at least, I think that's the trigger).

Minimal example here:

SELECT seq, CONCAT('n', seq) AS n INTO TEMP TABLE test1 FROM
generate_series(1,5) AS seq;
SELECT seq, CONCAT('x', 6-seq) AS x INTO TEMP TABLE test2 FROM
generate_series(1,5) AS seq;

SELECT
GROUPING(test1.n) AS gp_n,
GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)))
AS gp_conc,
test1.n,
CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) FROM test1
GROUP BY
GROUPING SETS(
(test1.n),
(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)))
)
ORDER BY
CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END NULLS FIRST,
CASE WHEN GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)))=0 THEN concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)) ELSE NULL END NULLS FIRST;
gp_n | gp_conc | n | concat
------+---------+----+--------
1 | 0 | | n5x1
1 | 0 | | n4x2
1 | 0 | | n3x3
1 | 0 | | n2x4
1 | 0 | | n1x5
0 | 1 | n1 |
0 | 1 | n2 |
0 | 1 | n3 |
0 | 1 | n4 |
0 | 1 | n5 |

Am I missing some reason why the first set isn't sorted as I'd hoped?
Is the subquery value in the ORDER BY not the same as the value in the
main query? That seems... frustrating. I'd like to be able to say
"order by column (n)" but I don't think I can?

On Centos7, with the latest pg12 from the pg repo:
PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit

Thanks

Geoff

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Schoemans Maxime 2024-01-05 17:39:50 Re: Implement missing join selectivity estimation for range types
Previous Message Tom Lane 2024-01-05 17:35:35 Re: Add new protocol message to change GUCs for usage with future protocol-only GUCs