pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16465: Inconsistent results from comparison of row value expressions
3+ messages / 3 participants
[nested] [flat]

* BUG #16465: Inconsistent results from comparison of row value expressions
@ 2020-05-27 08:00 PG Bug reporting form <noreply@postgresql.org>
  2020-05-27 09:14 ` Re: BUG #16465: Inconsistent results from comparison of row value expressions Vik Fearing <vik@postgresfriends.org>
  2020-05-27 12:16 ` Re: BUG #16465: Inconsistent results from comparison of row value expressions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: PG Bug reporting form @ 2020-05-27 08:00 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: lukas.eder@gmail.com

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16465: Inconsistent results from comparison of row value expressions
  2020-05-27 08:00 BUG #16465: Inconsistent results from comparison of row value expressions PG Bug reporting form <noreply@postgresql.org>
@ 2020-05-27 09:14 ` Vik Fearing <vik@postgresfriends.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Vik Fearing @ 2020-05-27 09:14 UTC (permalink / raw)
  To: lukas.eder@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16465: Inconsistent results from comparison of row value expressions
  2020-05-27 08:00 BUG #16465: Inconsistent results from comparison of row value expressions PG Bug reporting form <noreply@postgresql.org>
@ 2020-05-27 12:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2020-05-27 12:16 UTC (permalink / raw)
  To: lukas.eder@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2020-05-27 12:16 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-27 08:00 BUG #16465: Inconsistent results from comparison of row value expressions PG Bug reporting form <noreply@postgresql.org>
2020-05-27 09:14 ` Vik Fearing <vik@postgresfriends.org>
2020-05-27 12:16 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox