Re: BUG #16465: Inconsistent results from comparison of row value expressions

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