From: Jinhui Lai <jinhui-lai@foxmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results
Date: Fri, 25 Jul 2025 22:40:29 +0800
Message-ID: <tencent_40B5E802081EE140CD8433AB8A30002DDF06@qq.com> (raw)
In-Reply-To: <1856868.1753450099@sss.pgh.pa.us>
References: <18999-4dbdbaeafb1c1023@postgresql.org>
<1856868.1753450099@sss.pgh.pa.us>
> [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
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: jinhui-lai@foxmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18999: Equivalent queries processing WHERE IS NULL & WHERE IS NOT NULL produce mutually exclusive results
In-Reply-To: <tencent_40B5E802081EE140CD8433AB8A30002DDF06@qq.com>
* 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