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)
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 |