BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: aborschev(at)gmail(dot)com
Subject: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
Date: 2022-08-05 12:33:21
Message-ID: 17575-e63bafc19daef4c7@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17575
Logged by: Alexey Borschev
Email address: aborschev(at)gmail(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2022-08-05 13:17:31 Re: [External] Re: [PATCH] BUG FIX: inconsistent page found in BRIN_REGULAR_PAGE
Previous Message houzj.fnst@fujitsu.com 2022-08-05 09:30:00 RE: No-op updates with partitioning and logical replication started failing in version 13