Wrong results from a parameterized Append

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Wrong results from a parameterized Append
Date: 2026-10-08 01:05:54
Message-ID: CAMbWs49jVFRZ7oOgMK9zYt7d=SLxpiz=dN=QMu5iV8TS+9T0AA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I ran into a wrong-result issue on master (also reproducible on v16
and later, I believe).

create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1, 0), (2, 0), (3, 0);
insert into t2 values (1, 1);
analyze t1, t2;

select * from t1 where t1.a in
(select s.a + t1.b
from (select a from t2 where t2.b > t1.b
union all
select a from t2 where t2.b > t1.b) s, t1 t3);
a | b
---+---
1 | 0
2 | 0
3 | 0
(3 rows)

Only the first row should be returned. Let's look at its plan:

Nested Loop Semi Join
-> Seq Scan on t1
-> Nested Loop
-> Seq Scan on t1 t3
-> Materialize
-> Append
-> Seq Scan on t2
Filter: (b > t1.b)
-> Seq Scan on t2 t2_1
Filter: (b > t1.b)

So where does the join filter for "t1.a = s.a + t1.b" go?

The clause is EC-derived, and the UNION ALL members get no child
version of the member "s.a + t1.b" since it references t1, so their
parameterized scans cannot enforce it. But create_append_path()
builds the Append's ParamPathInfo with get_baserel_parampathinfo(),
which generates the clause for the parent as if the Append could
enforce it. get_param_path_clause_serials() knows not to trust that
and intersects the children of an AppendPath, but for a Material path
on top of the Append it falls into the "baserel path" branch and
returns the parent's ppi_serials. create_nestloop_path() then drops
the clause from the semijoin as already enforced below.

I believe the same happens with any path that inherits its subpath's
ParamPathInfo: Memoize, Projection, Sort, IncrementalSort, Unique, Agg
and GroupingSets, although I don't have proof. Attached is a patch
that makes get_param_path_clause_serials() look through those paths,
and uses it in get_memoize_path() too, which reads ppi_serials
directly.

Thoughts?

- Richard

Attachment Content-Type Size
v1-0001-Look-through-wrapper-paths-when-collecting-enforc.patch application/octet-stream 8.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2026-10-08 01:09:09 Re: Fix "unexpected logical decoding status change" error; from concurrent logical decoding activation
Previous Message Chao Li 2026-10-08 00:48:21 Re: [PG19]pg_verifybackup never finishes on a gzip-compressed tar backup