pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows
2+ messages / 2 participants
[nested] [flat]

* BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows
@ 2026-11-18 01:53 PG Bug reporting form <noreply@postgresql.org>
  2026-11-18 21:29 ` Re: BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows David Rowley <dgrowleyml@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-11-18 01:53 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19915
Logged by:          chunling qin
Email address:      303677365@qq.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.








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows
  2026-11-18 01:53 BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-18 21:29 ` David Rowley <dgrowleyml@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David Rowley @ 2026-11-18 21:29 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

On Wed, 18 Nov 2026 at 21:38, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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;
> ```

This is effectively a duplicate of bugs #19619, #19697, and #19713.

Can you reply here with the answer to the question in [1]? We'd very
much like to know why you think this is important.

David

[1] https://postgr.es/m/1187008.1790094524@sss.pgh.pa.us






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-18 21:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-18 01:53 BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows PG Bug reporting form <noreply@postgresql.org>
2026-11-18 21:29 ` David Rowley <dgrowleyml@gmail.com>

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