weird GROUPING SETS and ORDER BY behaviour

Поиск
Список
Период
Сортировка
От Geoff Winkless
Тема weird GROUPING SETS and ORDER BY behaviour
Дата
Msg-id CAEzk6fcgXWabEG+RFDaG6tDmFX6g1h7LPGUdrX85Pb0XB3B76g@mail.gmail.com
обсуждение исходный текст
Ответы Re: weird GROUPING SETS and ORDER BY behaviour
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Add new protocol message to change GUCs for usage with future protocol-only GUCs
Следующее
От: Schoemans Maxime
Дата:
Сообщение: Re: Implement missing join selectivity estimation for range types