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

PG Bug reporting form <noreply(at)postgresql(dot)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