From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Thomas Munro <thomas.munro@gmail.com>, ganeshmmahesh@gmail.com,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17068: Incorrect ordering of a particular row.
In-reply-to: 
 <CAApHDvoKk=NO7H6p8UNjSXbio8vq9yUjZhHO_Xr=C14WMs9i+A@mail.gmail.com>
References: <17068-18d0626f1d26394d@postgresql.org>
 <CAApHDvr=A5ZMYm-Jy1g4AyfMBeOJGfP_5zJEW9N9k_S5PjwN4Q@mail.gmail.com>
 <CA+hUKG+TrnLnccaspLsimQnm34wUsdxSpxQiS9jrTScUo=kFiQ@mail.gmail.com>
 <CAApHDvoKk=NO7H6p8UNjSXbio8vq9yUjZhHO_Xr=C14WMs9i+A@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Wed, 23 Jun 2021 01:46:16 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1714545.1624370422.1@sss.pgh.pa.us>
Date: Tue, 22 Jun 2021 10:00:22 -0400
Message-ID: <1714546.1624370422@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1714546.1624370422%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 23 Jun 2021 at 01:26, Thomas Munro <thomas.munro@gmail.com> wrote:
>> If you change the SELECT list to output alias3::text, there's a
>> different value there:

> Yeah, if I change it to ORDER BY alias3.lastname, nullnamesb.id;
> instead of ORDER BY alias3.*, nullnamesb.id; then the tie break using
> btint4fastcmp() is called more often.  It's also getting called with
> the missing 4s which I mentioned upthread.
> See the two outputs.  That points me towards something weird going on
> in record_cmp().

I believe Munro's point is that in some rows alias3.* is a NULL composite
value, while in other rows it is a composite containing one NULL, and they
don't sort the same.  Presumably the former are from left-join extension
while the latter come from actual table rows having NULL in that column.

(I'd suspected something of the kind, but being caffeine-deprived I'd
first added "alias3.* IS NULL" to the query, which of course fails to
expose the difference.  Thanks SQL.)

In short, I see no bug here.  It is kind of obscure though.

			regards, tom lane