Re: Inconsistencies around Composite Row nullness

Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)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