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
Date: Sun, 02 Nov 2025 13:27:54 -0500
Message-ID: <441300.1762108074@sss.pgh.pa.us> (raw)
In-Reply-To: <CAK7KUdApoBTLAEL0z+5U9Yv_o4gu6JTU9uC3_AZWSXSjm5jjGQ@mail.gmail.com>
References: <CAK7KUdApoBTLAEL0z+5U9Yv_o4gu6JTU9uC3_AZWSXSjm5jjGQ@mail.gmail.com>
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
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: tgl@sss.pgh.pa.us, christopher.m.hanks@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistencies around Composite Row nullness
In-Reply-To: <441300.1762108074@sss.pgh.pa.us>
* 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