Thread

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

   PG Bug reporting form <noreply@postgresql.org> — 2025-06-07T02:44:08Z

   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.
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-07T16:09:48Z

   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