Thread: BUG #18662: ORDER BY after GROUPING SETS does not order correctly for certain WHERE condition

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

PG Bug reporting form <noreply@postgresql.org> writes:
> I'm seeing inconsistent results for an ORDER BY in combination with GROUPING
> SETS.

Yup.  Your example actually works correctly as of HEAD (v18-to-be):

regression=# 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 | 2
   | 1
   | 2
(4 rows)

but I'm afraid there's zero chance of back-patching the fix,
as it's far too invasive (and under-tested, for now) for that.

> This might be the same bug as
> https://www.postgresql.org/message-id/CAL48EtKDHCKnOkLdSgOmgBZBcahU2zpBqyzeET_ZM74uNZBFHg@mail.gmail.com

Indeed.  Richard Guo recently completed the fix I alluded to in that
thread:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=247dea89f
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f5050f795

            regards, tom lane