From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: sander.evers@topicus.nl
Subject: BUG #18662: ORDER BY after GROUPING SETS does not order correctly for certain WHERE condition
Date: Thu, 17 Oct 2024 12:56:39 +0000
Message-ID: <18662-515b2a51728c237c@postgresql.org> (raw)
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...
but I didn't find it in the TODO list.
Thanks for looking into it!
Sander Evers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, sander.evers@topicus.nl
Subject: Re: BUG #18662: ORDER BY after GROUPING SETS does not order correctly for certain WHERE condition
In-Reply-To: <18662-515b2a51728c237c@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox