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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dengwenking(at)gmail(dot)com
Subject: BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count
Date: 2025-06-07 02:44:08
Message-ID: 18948-a05bf0726c2c993a@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18948
Logged by: Wenking Deng
Email address: dengwenking(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message BATBAATAR Dorjpalam 2025-06-07 05:01:53 Re: BUG #18907: SSL error: bad length failure during transfer data in pipeline mode with libpq
Previous Message Masahiko Sawada 2025-06-06 17:23:42 Re: Logical replication 'invalid memory alloc request size 1585837200' after upgrading to 17.5