BUG #18997: Two equivalent queries return different results
The following bug has been logged on the website:
Bug reference: 18997
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 these "SELECT COUNT(c0) FILTER (WHERE c0 IS NOT NULL) FROM t0" and
"SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;" are equivalent. However,
their results are different.
Please you can reproduce it as follows:
CREATE TYPE composite AS (a float8, b float8);
CREATE TABLE t0 (c0 composite NOT NULL);
INSERT INTO t0 VALUES ('(,)');
test=# SELECT COUNT(c0) FILTER (WHERE c0 IS NOT NULL) FROM t0;
count
-------
0
(1 row)
test=# SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;
count
-------
1
(1 row)
Moreover, when I execute this case in the former versions, such as pg 16,
15... these two queries return the same result.
Best regard,
Jinhui
PG Bug reporting form <noreply@postgresql.org> writes:
I think these "SELECT COUNT(c0) FILTER (WHERE c0 IS NOT NULL) FROM t0" and
"SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;" are equivalent. However,
their results are different.
You sure your server is 17.5, and not something a little older?
There was a bug of this sort in earlier 17.x releases, but
we fixed it in 17.5. For me, both queries produce "0" as
expected.
regards, tom lane
Dear Tom Lane,
Sorry for my careless. I check my PG version again, it is 17.4. Since my PG docker' tag is latest, I mistakenly thought it was the latest version (17.5).
Thanks for your reply.
Best regards,
Jinhui
原始邮件
发件人：Tom Lane <tgl@sss.pgh.pa.us>
发件时间：2025年7月25日 01:33
收件人：jinhui-lai <jinhui-lai@foxmail.com>
抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18997: Two equivalent queries return different results
PG Bug reporting form <noreply@postgresql.org> writes:
> I think these "SELECT COUNT(c0) FILTER (WHERE c0 IS NOT NULL) FROM t0" and
> "SELECT COUNT(c0) FROM t0 WHERE c0 IS NOT NULL;" are equivalent. However,
> their results are different.
You sure your server is 17.5, and not something a little older?
There was a bug of this sort in earlier 17.x releases, but
we fixed it in 17.5. For me, both queries produce "0" as
expected.
regards, tom lane