Horton
Menu

Thread

  1. BUG #18997: Two equivalent queries return different results

    PG Bug reporting form <noreply@postgresql.org> — 2025-07-24T14:45:17Z

    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

  2. Re: BUG #18997: Two equivalent queries return different results

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-24T17:33:36Z

    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

  3. Re: BUG #18997: Two equivalent queries return different results

    Jinhui Lai <jinhui-lai@foxmail.com> — 2025-07-24T17:49:54Z

    Dear Tom Lane,


Sorry for my careless. I check my PG version again, it is&nbsp; 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&gt;
发件时间：2025年7月25日 01:33
收件人：jinhui-lai <jinhui-lai@foxmail.com&gt;
抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
主题：Re: BUG #18997: Two equivalent queries return different results



       PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;<noreply@postgresql.org&gt;&nbsp;writes:
&gt;&nbsp;I&nbsp;think&nbsp;these&nbsp;"SELECT&nbsp;COUNT(c0)&nbsp;FILTER&nbsp;(WHERE&nbsp;c0&nbsp;IS&nbsp;NOT&nbsp;NULL)&nbsp;FROM&nbsp;t0"&nbsp;and
&gt;&nbsp;"SELECT&nbsp;COUNT(c0)&nbsp;FROM&nbsp;t0&nbsp;WHERE&nbsp;c0&nbsp;IS&nbsp;NOT&nbsp;NULL;"&nbsp;are&nbsp;equivalent.&nbsp;However,
&gt;&nbsp;their&nbsp;results&nbsp;are&nbsp;different.

You&nbsp;sure&nbsp;your&nbsp;server&nbsp;is&nbsp;17.5,&nbsp;and&nbsp;not&nbsp;something&nbsp;a&nbsp;little&nbsp;older?
There&nbsp;was&nbsp;a&nbsp;bug&nbsp;of&nbsp;this&nbsp;sort&nbsp;in&nbsp;earlier&nbsp;17.x&nbsp;releases,&nbsp;but
we&nbsp;fixed&nbsp;it&nbsp;in&nbsp;17.5.&nbsp;&nbsp;For&nbsp;me,&nbsp;both&nbsp;queries&nbsp;produce&nbsp;"0"&nbsp;as
expected.

			regards, tom&nbsp;lane