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
