| 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: | Whole Thread | Raw Message | 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.
| 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 |