BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
Дата
Msg-id 17071-24dc13fbfa29672d@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17071
Logged by:          Tobias Wendorff
Email address:      tobias.wendorff@tu-dortmund.de
PostgreSQL version: 12.6
Operating system:   Debian Buster
Description:

If the result set of a query has only one row, but an additional row gets
added by GROUP BY rollup(), a subsequent ORDER BY is ignored by the
planner.

test case:
https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=e467725a3fae6df51f7ad8e89052de98

query:
SELECT
    '2021-01-01'::date AS month
GROUP BY
    rollup(month)
ORDER BY
    month NULLS FIRST;

query plan:
GroupAggregate  (cost=0.00..0.03 rows=2 width=0) (actual time=0.003..0.003
rows=2 loops=1)
  Group Key: '2021-01-01'::date
  Group Key: ()
  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000
rows=1 loops=1)
Planning time: 0.017 ms
Execution time: 0.027 ms

workaround:
CTE or subquery with subsequent sorting

Tested on dbfiddle.uk using versions 9.5, 9.6, 10, 11, 12 and 13.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Sudheer H R
Дата:
Сообщение: Re: Found a buffer-overflow defect in asynchronous database connection API PQconnectPoll
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Found a buffer-overflow defect in asynchronous database connection API PQconnectPoll