| 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 |
| 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 |