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
v1-0001-Look-through-wrapper-paths-when-collecting-enforc.patch
Description: Binary data
