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

The following bug has been logged on the website:

Bug reference:      18999
Logged by:          Jinhui Lai
Email address:      jinhui-lai@foxmail.com
PostgreSQL version: 17.5
Operating system:   ubuntu 22.04
Description:

Dear PG developers,

Thanks for reading my report.

I think "SELECT COUNT(c0) FROM t0" = "SELECT COUNT(c0) FROM t0 WHERE c0 IS
NOT NULL" + "SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL". However, the
following case violates this equivalence relationship.

Please you can reproduce it as follows:

CREATE TYPE composite AS (a TEXT, b TEXT);
CREATE TABLE t0 (c0 composite UNIQUE NOT NULL);
INSERT INTO t0 VALUES ('(,)');
INSERT INTO t0 VALUES ('(NULL,)');
INSERT INTO t0 VALUES ('(,NULL)');

SELECT COUNT(c0) FROM t0 ;
 count
-------
     3

SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
 count
-------
     0

SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL;
 count
-------
     1

SELECT COUNT(c0) FROM t0 WHERE c0 IS NULL OR c0 IS NOT NULL;
 count
-------
     1


SELECT VERSION()
version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Thanks for you time.

Best regards,
Jinhui