| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | 303677365(at)qq(dot)com |
| Subject: | BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows |
| Date: | 2026-11-18 01:53:01 |
| Message-ID: | 19915-7166b5b1b377ad97@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: 19915
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: Linux x86_64; master build 81b2179b492 (20devel)
Description:
**PostgreSQL version:** 20devel master build (commit 81b2179b492, built
2026-10-27). The grouping/distinct pushdown machinery involved has been
evolving through 2026 (see Related); the fix committed on 2026-07-06 is
present in this build, and the problem below still occurs, so current
released versions are likely affected as well; I have not verified on a
release build.
**Operating system:** Linux x86_64
**Description:**
An outer qual that wraps a grouping column in a function is pushed down past
a DISTINCT ON / grouping layer, when the corresponding qual over the bare
column is (correctly) not pushed. The pushed-down qual filters rows before
the grouping step, changing which rows the grouping sees, and the query
returns rows that the subquery, on its own, never produces.
**Reproduction (single session):**
```sql
DROP TABLE IF EXISTS m, m_sub CASCADE;
CREATE TABLE m (id int, a numeric);
INSERT INTO m VALUES (1, 1.00), (2, 1.0), (3, 2);
ANALYZE m;
-- Ground truth: the DISTINCT ON subquery output, materialized.
CREATE TABLE m_sub AS
SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id;
SELECT * FROM m_sub ORDER BY id;
```
```text
id | a
----+------
1 | 1.00
3 | 2
(2 rows)
```
Rows (1, 1.00) and (2, 1.0) are equal under numeric equality (the eqop used
for grouping/DISTINCT), so DISTINCT ON keeps the first per ORDER BY, id=1.
Filtering the materialized output gives the correct answer â no rows
(scale(1.00)=2, scale(2)=0):
```sql
SELECT * FROM m_sub WHERE scale(a) = 1; -- 0 rows
```
But in the single query, the qual is pushed below the Unique node (EXPLAIN
shows the filter on the scan), the grouping only sees row (2, 1.0), and the
query returns a row the subquery never produces:
```sql
EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s
WHERE scale(s.a) = 1;
```
```text
QUERY PLAN
------------------------------
Unique
-> Sort
Sort Key: m.a, m.id
-> Seq Scan on m
Filter: (scale(a) = 1)
(5 rows)
```
```sql
SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s
WHERE scale(s.a) = 1 ORDER BY id; -- returns (2, 1.0)
```
Proof that the returned row is not a row of the subquery output:
```sql
SELECT s.* FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s
WHERE scale(s.a) = 1
AND NOT EXISTS (SELECT 1 FROM m_sub o WHERE o.id = s.id AND o.a = s.a);
```
```text
id | a
----+-----
2 | 1.0
(1 row)
```
Control: an OFFSET 0 barrier (which blocks pushdown) returns 0 rows â so
the query, written over the subquery output, is expected to return nothing.
```sql
SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id OFFSET 0)
s
WHERE scale(s.a) = 1; -- 0 rows
```
The mechanism: scale(1.0) = 1 but scale(1.00) = 2, while 1.0 = 1.00 under
numeric equality. Filtering before grouping keeps the row the grouping would
discard (2, 1.0) instead of the row it would keep (1, 1.00). A qual over the
bare grouping column (a = 1) is not pushed (the planner knows the grouping
column's value is not stable across equal-but-differently-scaled members);
wrapping the column in an IMMUTABLE function defeats that check, because the
function's output is assumed derivable from the grouping key while it
actually depends on the presentation (scale) of the value, not its semantic
value.
**Root cause (analysis):**
The pushdown check for quals over grouped outputs
(expression_has_grouping_conflict / the equivalence-class based checks in
clauses.c) does not treat a function applied to a grouping column as
conflicting: it only rejects quals whose operands directly reference the
grouping column, relying on the function being IMMUTABLE to conclude its
value is determined by the group. For a collatable/deterministic type like
numeric, equal group members can have different function results (different
typmod/scale), so the pushed qual changes which member survives the
grouping.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-11-18 02:03:13 | Re: BUG #19911: Window aggregate over ROWS n FOLLOWING: "unexpected end of tuplestore" after spill |
| Previous Message | Tender Wang | 2026-11-18 01:42:41 | Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) |