From: Tom Lane <tgl@sss.pgh.pa.us>
To: dengwenking@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18948: Equivalent MAX() result in view and inline query
 yields inconsistent row count
In-reply-to: <18948-a05bf0726c2c993a@postgresql.org>
References: <18948-a05bf0726c2c993a@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sat, 07 Jun 2025 02:44:08 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <15513.1749312588.1@sss.pgh.pa.us>
Date: Sat, 07 Jun 2025 12:09:48 -0400
Message-ID: <15514.1749312588@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/15514.1749312588%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> -- Setup: Create table t0 and view v1
> CREATE TABLE t0 (c0 REAL);
> INSERT INTO t0 (c0) VALUES (1.830250668324684e+38);
> INSERT INTO t0 (c0) VALUES (-3.29559603270012e+38);
> INSERT INTO t0 (c0) VALUES (3.352134689102562e+38);
> CREATE VIEW v1 AS SELECT MAX(c0) AS c0 FROM t0;


> -- Query 3: Inline MAX() with WHERE (1 = NULL)
> db1=# SELECT MAX(c0) FROM t0 WHERE (1 = NULL);
>  max
> -----
> (1 row)
> -- Query 4: View value with same WHERE clause
> db1=# SELECT (c0) FROM v1 WHERE (1 = NULL);
>  c0
> ----
> (0 rows)

> Both SELECT should return one row with NULL.

No, they should not.  In your query 3, the WHERE filter applies before
aggregation happens, so it removes all the input rows to the MAX().
In your query 4, the WHERE filter applies after the MAX(), that is
it acts on the aggregated row(s).  To write an exact equivalent of
query 4 without using a view or sub-select, you'd need to write
the filter condition in HAVING.

			regards, tom lane