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.
