Thread

1. Inconsistencies around Composite Row nullness

   Chris Hanks <christopher.m.hanks@gmail.com> — 2025-11-02T16:52:20Z

   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
   

2. 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.
   

3. Re: Inconsistencies around Composite Row nullness

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-11-02T18:27:54Z

   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