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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
Дата
Msg-id 17575-e63bafc19daef4c7@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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)


В списке pgsql-bugs по дате отправления:

Предыдущее
От: "houzj.fnst@fujitsu.com"
Дата:
Сообщение: RE: No-op updates with partitioning and logical replication started failing in version 13
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: [External] Re: [PATCH] BUG FIX: inconsistent page found in BRIN_REGULAR_PAGE