From: Tom Lane <tgl@sss.pgh.pa.us>
To: aborschev@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
Date: Fri, 05 Aug 2022 10:23:39 -0400
Message-ID: <3914924.1659709419@sss.pgh.pa.us> (raw)
In-Reply-To: <17575-e63bafc19daef4c7@postgresql.org>
References: <17575-e63bafc19daef4c7@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> I noticed strange behavior of ROW(NULL):
> I expect, that IS NULL operator should give the same result as IS NOT
> DISTINCT FROM NULL
> similarly, IS NOT NULL operator should give the same result as IS DISTINCT
> FROM NULL:
I don't see any particular reason to expect that. row(NULL)
is a row object containing one null field, which is in fact not
identical to a null composite value. The SQL spec dictates that
IS NULL should return true for both cases, but that doesn't mean
that no other operator is allowed to distinguish them. I'd say
that this is a wart of IS NULL rather than desirable behavior
we should copy elsewhere.
> Next point:
> I expected that IS NULL and IS NOT NULL operators must always return
> opposite results, but:
You have not read the SQL standard, then. It's quite clear
about that.
> Can we fix or document this PG issue?
... nor our documentation. See
https://www.postgresql.org/docs/current/functions-comparison.html
para beginning "If the expression is row-valued,", near the
bottom of the page.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, aborschev@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
In-Reply-To: <3914924.1659709419@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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