pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18997: Two equivalent queries return different results
3+ messages / 3 participants
[nested] [flat]
* BUG #18997: Two equivalent queries return different results
@ 2025-07-24 14:45 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-07-24 14:45 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jinhui-lai@foxmail.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18997: Two equivalent queries return different results
@ 2025-07-24 17:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-07-24 17:33 UTC (permalink / raw)
To: jinhui-lai@foxmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18997: Two equivalent queries return different results
@ 2025-07-24 17:49 =?utf-8?B?SmluaHVpIExhaQ==?= <jinhui-lai@foxmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jinhui Lai @ 2025-07-24 17:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-07-24 17:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-24 14:45 BUG #18997: Two equivalent queries return different results PG Bug reporting form <noreply@postgresql.org>
2025-07-24 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-24 17:49 ` =?utf-8?B?SmluaHVpIExhaQ==?= <jinhui-lai@foxmail.com>
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