Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results - Mailing list pgsql-bugs

> 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,