pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count
2+ messages / 2 participants
[nested] [flat]
* BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count
@ 2025-06-07 02:44 PG Bug reporting form <noreply@postgresql.org>
2025-06-07 16:09 ` Re: BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-06-07 02:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dengwenking@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count
2025-06-07 02:44 BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count PG Bug reporting form <noreply@postgresql.org>
@ 2025-06-07 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-06-07 16:09 UTC (permalink / raw)
To: dengwenking@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-06-07 16:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-07 02:44 BUG #18948: Equivalent MAX() result in view and inline query yields inconsistent row count PG Bug reporting form <noreply@postgresql.org>
2025-06-07 16:09 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox