From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jinhui-lai@foxmail.com
Subject: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results
Date: Fri, 25 Jul 2025 04:43:03 +0000
Message-ID: <18999-4dbdbaeafb1c1023@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jinhui-lai@foxmail.com
Subject: Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results
In-Reply-To: <18999-4dbdbaeafb1c1023@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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