From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Hanks <christopher.m.hanks@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistencies around Composite Row nullness
In-reply-to: 
 <CAK7KUdApoBTLAEL0z+5U9Yv_o4gu6JTU9uC3_AZWSXSjm5jjGQ@mail.gmail.com>
References: 
 <CAK7KUdApoBTLAEL0z+5U9Yv_o4gu6JTU9uC3_AZWSXSjm5jjGQ@mail.gmail.com>
Comments: In-reply-to Chris Hanks <christopher.m.hanks@gmail.com>
	message dated "Sun, 02 Nov 2025 11:52:20 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <441299.1762108074.1@sss.pgh.pa.us>
Date: Sun, 02 Nov 2025 13:27:54 -0500
Message-ID: <441300.1762108074@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/441300.1762108074%40sss.pgh.pa.us>
Precedence: bulk

Chris Hanks <christopher.m.hanks@gmail.com> writes:
> I've experienced some logically inconsistent query output on my local
> Postgres instance, version string: PostgreSQL 18.0 (Homebrew) on
> aarch64-apple-darwin25.0.0, compiled by Apple clang version 17.0.0
> (clang-1700.3.19.1), 64-bit

Yeah, it's not terribly consistent, but neither is the SQL standard
in this area.  I believe what is happening in your first example
is that the construct "ROW(a, b, ...) = ROW(x, y, ...)" is being
broken down into "(a = x) AND (b = y) AND ...", from which you can
get a NULL result as described.  However, the insertion of coalesce()
stops that decomposition from happening, and then what you get is the
behavior of the native composite-type comparators (record_eq and
friends).  Those functions adhere to the btree requirement of
producing a total order of the datatype, ie null results are not OK,
so they report that ROW(NULL, 2) = ROW(NULL, 2) is true not null.

We could get rid of some of the inconsistency by eliminating that
special treatment of equality of two row-constructors, but I'm
afraid there would be complaints from people who were relying on
that behavior for optimization purposes.

			regards, tom lane