pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedInconsistencies around Composite Row nullness
3+ messages / 3 participants
[nested] [flat]
* Inconsistencies around Composite Row nullness
@ 2025-11-02 16:52 Chris Hanks <christopher.m.hanks@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Chris Hanks @ 2025-11-02 16:52 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello -
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
I also reproduced it on the most recent Postgres version available at
db-fiddle.com, version string: PostgreSQL 17.0 on x86_64-pc-linux-gnu,
compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
This first statement resolves, reasonably, to NULL:
SELECT ROW(NULL::integer, 2) = ROW(NULL::integer, 2)
This next statement resolves to ROW(NULL, 2):
SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2))
These final two statements each resolve to true, which is inconsistent with
the previous statements (each should resolve to NULL):
SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2)) = ROW(NULL::integer, 2)
SELECT coalesce(ROW(NULL::integer, 2)) = ROW(NULL::integer, 2)
Thank you!
Chris
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inconsistencies around Composite Row nullness
@ 2025-11-02 18:19 David G. Johnston <david.g.johnston@gmail.com>
parent: Chris Hanks <christopher.m.hanks@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2025-11-02 18:19 UTC (permalink / raw)
To: Chris Hanks <christopher.m.hanks@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sunday, November 2, 2025, Chris Hanks <christopher.m.hanks@gmail.com>
wrote:
> Hello -
>
> 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
>
> I also reproduced it on the most recent Postgres version available at
> db-fiddle.com, version string: PostgreSQL 17.0 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
>
> This first statement resolves, reasonably, to NULL:
> SELECT ROW(NULL::integer, 2) = ROW(NULL::integer, 2)
>
Yes, ROW constructed values within an equality resolve using SQL row
constructor comparison rules.
> This next statement resolves to ROW(NULL, 2):
> SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2))
>
This statement is technically impossible - nothing resolves to “ROW(…)” -
the fact that ROW (a row constructor) is involved is erased when passing
the result of the expression through a function such that a plain
composite/record is produced. It is necessary, for the rest of the system
to function correctly, that records are comparable using (null equals null
=> true) semantics (is distinct; composite type comparison).
> These final two statements each resolve to true, which is inconsistent
> with the previous statements (each should resolve to NULL):
> SELECT coalesce(ROW(NULL::integer, 2), ROW(1, 2)) = ROW(NULL::integer, 2)
> SELECT coalesce(ROW(NULL::integer, 2)) = ROW(NULL::integer, 2)
>
See specifically the commentary in row constructor comparison 9.25.5 and
composite type comparison 9.25.6 in the documentation.
There is a patch to further expound/consolidate discussion on this topic
(null handling in PostgreSQL) presently awaiting committer attention.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inconsistencies around Composite Row nullness
@ 2025-11-02 18:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Hanks <christopher.m.hanks@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-11-02 18:27 UTC (permalink / raw)
To: Chris Hanks <christopher.m.hanks@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-02 18:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-02 16:52 Inconsistencies around Composite Row nullness Chris Hanks <christopher.m.hanks@gmail.com>
2025-11-02 18:19 ` David G. Johnston <david.g.johnston@gmail.com>
2025-11-02 18:27 ` 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