pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedSort Order inconsistent when using Grouping Sets Rollup
2+ messages / 2 participants
[nested] [flat]
* Sort Order inconsistent when using Grouping Sets Rollup
@ 2022-11-22 05:06 Chris Rohlfs <seeken@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Chris Rohlfs @ 2022-11-22 05:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
The attached file will create a table and run 2 queries, the first of which
is not honoring the requested sort order. I expect the Nulls first.
I have found this problem in Postgres 12.7 (pop os) and 14.6 (mac
Postgres.app)
Incorrect:
select "last_name", "first_name", count("actor_id")
from actor
where (( "actor_id" >= 1 ) and ( "last_name" = 'GUINESS' ) *and (
"first_name" = 'PENELOPE' )*)
group by rollup( 1, 2 )
order by 1 asc nulls first, 2 asc nulls first;
last_name | first_name | count
-----------+------------+-------
GUINESS | PENELOPE | 1
GUINESS | | 1
| | 1
Expected:
select "last_name", "first_name", count("actor_id")
from actor
where (( "actor_id" >= 1 ) and ( "last_name" = 'GUINESS' ))
group by rollup( 1, 2 )
order by 1 asc nulls first, 2 asc nulls first;
last_name | first_name | count
-----------+------------+-------
| | 1
GUINESS | | 1
GUINESS | PENELOPE | 1
(3 rows)
The only difference between the two queries is the "first_name" filter
added to the second.
Thank you!
Chris
Attachments:
bug.sql (application/sql, 1.1K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Sort Order inconsistent when using Grouping Sets Rollup
@ 2022-11-22 15:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Rohlfs <seeken@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-11-22 15:41 UTC (permalink / raw)
To: Chris Rohlfs <seeken@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Chris Rohlfs <seeken@gmail.com> writes:
> The attached file will create a table and run 2 queries, the first of which
> is not honoring the requested sort order. I expect the Nulls first.
Yeah, this is a known issue that's a bit difficult to fix. Because
you've constrained both the first_name and last_name columns to have
unique values:
> where (( "actor_id" >= 1 ) and ( "last_name" = 'GUINESS' ) and (
> "first_name" = 'PENELOPE' ))
the planner is of the opinion that sorting on those columns is a no-op,
so it doesn't bother to emit a sort step after the aggregation. We
need to teach it that the output of GROUP BY, when there are grouping
sets, is not identical to the input because of possible injection of
null values. I'm working on some patches that should lead to that result,
but they won't appear till v16 at the earliest.
As a grotty workaround, you could do the ordering in a different
query level:
=# explain select * from (
select "last_name", "first_name", count("actor_id")
from actor
where (( "actor_id" >= 1 ) and ( "last_name" = 'GUINESS' ) and ( "first_name" = 'PENELOPE' ))
group by rollup( 1, 2 )
) ss
order by 2 asc nulls first, 1 asc nulls first;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Sort (cost=23.71..23.72 rows=3 width=72)
Sort Key: actor.first_name NULLS FIRST, actor.last_name NULLS FIRST
-> GroupAggregate (cost=0.00..23.69 rows=3 width=72)
Group Key: actor.last_name, actor.first_name
Group Key: actor.last_name
Group Key: ()
-> Seq Scan on actor (cost=0.00..23.65 rows=1 width=68)
Filter: ((actor_id >= 1) AND (last_name = 'GUINESS'::text) AND (first_name = 'PENELOPE'::text))
(8 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-11-22 15:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-22 05:06 Sort Order inconsistent when using Grouping Sets Rollup Chris Rohlfs <seeken@gmail.com>
2022-11-22 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
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