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
Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL |
Список | 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 по дате отправления: