pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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>
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 09:14 Vik Fearing <vik@postgresfriends.org>
parent: PG Bug reporting form <noreply@postgresql.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 12:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
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