weird GROUPING SETS and ORDER BY behaviour - Mailing list pgsql-hackers

From Geoff Winkless
Subject weird GROUPING SETS and ORDER BY behaviour
Date
Msg-id CAEzk6fcgXWabEG+RFDaG6tDmFX6g1h7LPGUdrX85Pb0XB3B76g@mail.gmail.com
Whole thread Raw
Responses Re: weird GROUPING SETS and ORDER BY behaviour
List 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



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Add new protocol message to change GUCs for usage with future protocol-only GUCs
Next
From: Schoemans Maxime
Date:
Subject: Re: Implement missing join selectivity estimation for range types