From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: aborschev@gmail.com
Subject: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
Date: Fri, 05 Aug 2022 12:33:21 +0000
Message-ID: <17575-e63bafc19daef4c7@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17575
Logged by: Alexey Borschev
Email address: aborschev@gmail.com
PostgreSQL version: 14.4
Operating system: Ubuntu
Description:
Hi, PG hackers!
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:
SELECT row(NULL::int) = row(NULL::int) AS "test= "
, row(NULL::int) IS NULL AS IS_NULL
, row(NULL::int) IS NOT NULL AS NOT_NULL
, row(NULL::int) IS DISTINCT FROM NULL AS IS_DISTINCT_FROM_NULL
, row(NULL::int) IS NOT DISTINCT FROM NULL AS
NOT_DISTINCT_FROM_NULL
test= | is_null | not_null | is_distinct_from_null |
not_distinct_from_null
--------+---------+----------+-----------------------+------------------------
| t | f | t | f
But here row(NULL::int) IS NULL -> true, and row(NULL::int) IS NOT DISTINCT
FROM NULL -> false !
Functions num_nulls and num_nonnulls consider row(...) as non-nulls:
SELECT num_nulls(row(NULL::int), row(NULL::int, NULL::TEXT), row(NULL::int,
'Bob'::TEXT))
, num_nonnulls(row(NULL::int), row(NULL::int, NULL::TEXT), row(NULL::int,
'Bob'::TEXT)) ;
num_nulls | num_nonnulls
-----------+--------------
0 | 3
Next point:
I expected that IS NULL and IS NOT NULL operators must always return
opposite results, but:
SELECT row(NULL::int, 'Bob'::TEXT) IS NULL AS
Row_IsNULL
, row(NULL::int, 'Bob'::TEXT) IS NOT NULL AS
Row_NotNULL ;
row_isnull | row_notnull
------------+-------------
f | f
- They both return False on same input!
Can we fix or document this PG issue?
These tests was done on fresh installation of PG 14 vanilla, Ubuntu, no
additional configuration:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
P.S.
It would be nice to have an abbreviation for IS NOT DISTINCT FROM operator,
for example == ,
and have this operator supported in == ANY(...) and JOINs (hash, merge,
nested loops)
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, aborschev@gmail.com
Subject: Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
In-Reply-To: <17575-e63bafc19daef4c7@postgresql.org>
* 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