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

The following bug has been logged on the website:

Bug reference:      16465
Logged by:          Lukas Eder
Email address:      lukas.eder@gmail.com
PostgreSQL version: 12.2
Operating system:   Debian 12.2-2.pgdg100+1 (Docker)
Description:

Consider this query:

---------------------------------------------------
SELECT (1, NULL::INT) = (1, NULL::INT), A = B
FROM (SELECT (1, NULL::INT) A, (1, NULL::INT) B) T;
---------------------------------------------------

The result is:

?column?|?column?|
--------|--------|
        |true    |

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.

On 5/27/20 10:00 AM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      16465
> Logged by:          Lukas Eder
> Email address:      lukas.eder@gmail.com
> PostgreSQL version: 12.2
> Operating system:   Debian 12.2-2.pgdg100+1 (Docker)
> Description:        
> 
> Consider this query:
> 
> ---------------------------------------------------
> SELECT (1, NULL::INT) = (1, NULL::INT), A = B
> FROM (SELECT (1, NULL::INT) A, (1, NULL::INT) B) T;
> ---------------------------------------------------
> 
> The result is:
> 
> ?column?|?column?|
> --------|--------|
>         |true    |
> 
> 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.

I concur.

The second one appears to be wrong; it should also return NULL.
-- 
Vik Fearing

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