Thread

Re: Inconsistencies around Composite Row nullness

David G. Johnston <david.g.johnston@gmail.com> — 2025-11-02T18:19:53Z

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.