Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results

> [1] https://www.postgresql.org/docs/current/functions-comparison.html
> Pretty weird, I agree, but that's what the SQL standard says to do.


Hi, Tom,


Thanks for your reply, I get your detailed explanation. I also agree that is weird. Especially, the the c0's constraint is NOT NULL.


Moreover, this case produce different results among different PG versions.


#PG 17.5, 16, 15, 14, 13, 12, 11, 10...#
CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(a,)');
INSERT INTO t0 VALUES ('(,b)');
SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
 count
-------
     0
SELECT COUNT(c0) FROM t0 WHERE c0 IS  NULL;
 count
-------
     1


#PG 17.0-17.4#
CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(a,)');
INSERT INTO t0 VALUES ('(,b)');
SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
 count
-------
     3
SELECT COUNT(c0) FROM t0 WHERE c0 IS  NULL;
 count
-------
     0


In my opinion, I think the result of PG 17.0-17.4 is more reasonable, because this result does not conflict with the NOT NULL constraint, nor does it conflict with the equivalence relationship of “3+0=3” (while 1+0 != 3). As you said, this is in line with the SQL standard. I wonder if the change in PG 17.5 to handle NULL and NON-NULL is to comply with the SQL standard.

Thanks for your time.


Best regard,
Jinhui