The following bug has been logged on the website:
Bug reference: 18662
Logged by: Sander Evers
Email address: sander.evers@topicus.nl
PostgreSQL version: 16.4
Operating system: MacOS 14.7
Description:
I'm seeing inconsistent results for an ORDER BY in combination with GROUPING
SETS.
Without a WHERE condition, ordering works as expected. In the first column,
the nulls are at the end:
test=# select * from (values (1, 1), (1, 2), (2,1), (2,2)) as t (a,b)
group by grouping sets ((b),(a,b))
order by a, b;
a | b
---+---
1 | 1
1 | 2
2 | 1
2 | 2
| 1
| 2
But with a "WHERE a=1" condition, the nulls are mixed in between:
test=# select * from (values (1, 1), (1, 2), (2,1), (2,2)) as t (a,b)
where a=1
group by grouping sets ((b),(a,b))
order by a, b;
a | b
---+---
1 | 1
| 1
1 | 2
| 2
where I would expect them at the end again. When I filter the input values
myself, the query does produce the expected result:
test=# select * from (values (1, 1), (1, 2)) as t (a,b)
group by grouping sets ((b),(a,b))
order by a, b;
a | b
---+---
1 | 1
1 | 2
| 1
| 2
I got these results both on 16.4 (macOS) and 17.0 (docker):
PostgreSQL 16.4 (Homebrew) on aarch64-apple-darwin23.4.0, compiled by Apple
clang version 15.0.0 (clang-1500.3.9.4), 64-bit
PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
This might be the same bug as
https://www.postgresql.org/message-id/CAL48EtKDHCKnOkLdSgOmgBZBcahU2zpBqyzeET_ZM74uNZBFHg@mail.gmail.com
but I didn't find it in the TODO list.
Thanks for looking into it!
Sander Evers
