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