From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Rohlfs <seeken@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Sort Order inconsistent when using Grouping Sets Rollup
In-reply-to: 
 <CAL48EtKDHCKnOkLdSgOmgBZBcahU2zpBqyzeET_ZM74uNZBFHg@mail.gmail.com>
References: 
 <CAL48EtKDHCKnOkLdSgOmgBZBcahU2zpBqyzeET_ZM74uNZBFHg@mail.gmail.com>
Comments: In-reply-to Chris Rohlfs <seeken@gmail.com>
	message dated "Mon, 21 Nov 2022 22:06:47 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3455208.1669131666.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 22 Nov 2022 10:41:06 -0500
Message-ID: <3455209.1669131666@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3455209.1669131666%40sss.pgh.pa.us>
Precedence: bulk

Chris Rohlfs <seeken@gmail.com> writes:
> The attached file will create a table and run 2 queries, the first of wh=
ich
> 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" >=3D 1 ) and ( "last_name" =3D 'GUINESS' ) a=
nd (
> "first_name" =3D '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:

=3D# explain select * from (
  select "last_name", "first_name", count("actor_id")
  from actor
  where (( "actor_id" >=3D 1 ) and ( "last_name" =3D 'GUINESS' ) and ( "fi=
rst_name" =3D 'PENELOPE' ))
  group by rollup( 1, 2 )
) ss
order by 2 asc nulls first, 1 asc nulls first;
                                                  QUERY PLAN              =
                                     =

--------------------------------------------------------------------------=
-------------------------------------
 Sort  (cost=3D23.71..23.72 rows=3D3 width=3D72)
   Sort Key: actor.first_name NULLS FIRST, actor.last_name NULLS FIRST
   ->  GroupAggregate  (cost=3D0.00..23.69 rows=3D3 width=3D72)
         Group Key: actor.last_name, actor.first_name
         Group Key: actor.last_name
         Group Key: ()
         ->  Seq Scan on actor  (cost=3D0.00..23.65 rows=3D1 width=3D68)
               Filter: ((actor_id >=3D 1) AND (last_name =3D 'GUINESS'::te=
xt) AND (first_name =3D 'PENELOPE'::text))
(8 rows)

			regards, tom lane