Thread: BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count

The following bug has been logged on the website:

Bug reference:      18948
Logged by:          Wenking Deng
Email address:      dengwenking@gmail.com
PostgreSQL version: 15.0
Operating system:   Docker
Description:

This bug demonstrates a discrepancy in PostgreSQL when comparing the result
of an aggregate function used inline versus stored in a view. The queries
SELECT MAX(c0) FROM t0 and SELECT c0 FROM v1 produce exactly the same value.
However, when applying a WHERE (1 = NULL) condition, the inline version
returns one row (with NULL), while the view version returns zero rows.
Reproduce Steps:
-- 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 1: Inline MAX() without WHERE
db1=# SELECT MAX(c0) FROM t0;
      max
---------------
 3.3521347e+38
(1 row)
-- Query 2: View value (same result)
db1=# SELECT c0 FROM v1;
      c0
---------------
 3.3521347e+38
(1 row)
-- 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)
Expected Result:
Both SELECT should return one row with NULL.

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