From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | lukas(dot)eder(at)gmail(dot)com |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #16465: Inconsistent results from comparison of row value expressions |
Date: | 2020-05-27 12:16:55 |
Message-ID: | 5355.1590581815@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-05-27 12:18:43 | Re: Re[2]: |
Previous Message | Andrey Klychkov | 2020-05-27 12:10:18 | Re[4]: |