pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
4+ messages / 4 participants
[nested] [flat]
* BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
@ 2022-08-05 12:33 PG Bug reporting form <noreply@postgresql.org>
2022-08-05 13:52 ` Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL David G. Johnston <david.g.johnston@gmail.com>
2022-08-05 14:23 ` Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2022-08-05 12:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: aborschev@gmail.com
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
2022-08-05 12:33 BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL PG Bug reporting form <noreply@postgresql.org>
@ 2022-08-05 13:52 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2022-08-05 13:52 UTC (permalink / raw)
To: aborschev@gmail.com <aborschev@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
https://www.postgresql.org/docs/current/functions-comparison.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
2022-08-05 12:33 BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL PG Bug reporting form <noreply@postgresql.org>
@ 2022-08-05 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-08 14:03 ` Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL Алексей Борщёв <aborschev@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-08-05 14:23 UTC (permalink / raw)
To: aborschev@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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:
I don't see any particular reason to expect that. row(NULL)
is a row object containing one null field, which is in fact not
identical to a null composite value. The SQL spec dictates that
IS NULL should return true for both cases, but that doesn't mean
that no other operator is allowed to distinguish them. I'd say
that this is a wart of IS NULL rather than desirable behavior
we should copy elsewhere.
> Next point:
> I expected that IS NULL and IS NOT NULL operators must always return
> opposite results, but:
You have not read the SQL standard, then. It's quite clear
about that.
> Can we fix or document this PG issue?
... nor our documentation. See
https://www.postgresql.org/docs/current/functions-comparison.html
para beginning "If the expression is row-valued,", near the
bottom of the page.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL
2022-08-05 12:33 BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL PG Bug reporting form <noreply@postgresql.org>
2022-08-05 14:23 ` Re: BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-08 14:03 ` Алексей Борщёв <aborschev@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Алексей Борщёв @ 2022-08-08 14:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Thank You, David and Tom!
Now these tricks around row(null) are much clearer to me!
I think this issue should be closed.
пт, 5 авг. 2022 г. в 17:23, Tom Lane <tgl@sss.pgh.pa.us>:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > 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:
>
> I don't see any particular reason to expect that. row(NULL)
> is a row object containing one null field, which is in fact not
> identical to a null composite value. The SQL spec dictates that
> IS NULL should return true for both cases, but that doesn't mean
> that no other operator is allowed to distinguish them. I'd say
> that this is a wart of IS NULL rather than desirable behavior
> we should copy elsewhere.
>
> > Next point:
> > I expected that IS NULL and IS NOT NULL operators must always return
> > opposite results, but:
>
> You have not read the SQL standard, then. It's quite clear
> about that.
>
> > Can we fix or document this PG issue?
>
> ... nor our documentation. See
>
> https://www.postgresql.org/docs/current/functions-comparison.html
>
> para beginning "If the expression is row-valued,", near the
> bottom of the page.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-08 14:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-05 12:33 BUG #17575: row(NULL) IS NULL inconsistent with IS NOT DISTINCT FROM NULL PG Bug reporting form <noreply@postgresql.org>
2022-08-05 13:52 ` David G. Johnston <david.g.johnston@gmail.com>
2022-08-05 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-08 14:03 ` Алексей Борщёв <aborschev@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox