PG Bug reporting form <noreply@postgresql.org> writes:
> It seems the comparison of row value expressions with respect to NULLs is
> inconsistent depending on whether the expressions are compared directly
> (first column), or indirectly from derived tables (second column). My
> reading of the SQL standard is that the second one is incorrect.
This is per the documentation [1], which says
The SQL specification requires row-wise comparison to return NULL if
the result depends on comparing two NULL values or a NULL and a
non-NULL. PostgreSQL does this only when comparing the results of two
row constructors (as in Section 9.23.5) or comparing a row constructor
to the output of a subquery (as in Section 9.22). In other contexts
where two composite-type values are compared, two NULL field values
are considered equal, and a NULL is considered larger than a
non-NULL. This is necessary in order to have consistent sorting and
indexing behavior for composite types.
The short answer here is that comparison of two non-null composite type
values cannot be allowed to yield null, or we could not sort or index
them. That'd be a high price to pay for conforming to a dubious-to-
begin-with spec detail.
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON