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
