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
Date: Sat, 07 Jun 2025 12:09:48 -0400
Message-ID: <15514.1749312588@sss.pgh.pa.us> (raw)
In-Reply-To: <18948-a05bf0726c2c993a@postgresql.org>
References: <18948-a05bf0726c2c993a@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dengwenking@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count
In-Reply-To: <15514.1749312588@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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