On Friday, August 5, 2022, PG Bug reporting form <
noreply@postgresql.org> wrote:
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 !
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?
It is documented. The paragraph just below the tip.
David J.
