Wrong results from a parameterized Append
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
Richard Guo <guofenglinux@gmail.com> writes:
I ran into a wrong-result issue on master (also reproducible on v16
and later, I believe).
FWIW, this example reproduces for me on v17 and up, but not v16.
It might be worth determining why. A quick bisect run says that it
broke at
9f133763961e280d8ba692bcad0b061b861e9138 is the first bad commit
commit 9f133763961e280d8ba692bcad0b061b861e9138
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: Thu Feb 15 12:05:52 2024 +0200
Pull up ANY-SUBLINK with the necessary lateral support.
but I've not dug further than that.
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.
I think you're on to something, but this patch has a hasty look to
it. AFAICS, the root of the problem is that the wrapper-path-making
functions in pathnode.c just do
pathnode->path.param_info = subpath->param_info;
and we now see that that isn't good enough. I wonder if we should try
harder. At the very least I think it'd be advisable to make all of
those look like, say,
pathnode->path.param_info = get_wrapper_parampathinfo(subpath);
Even if get_wrapper_parampathinfo doesn't do anything beyond handing
back the subpath's param_info, it would provide a place for a comment
discussing what it's doing and why.
However, I feel like we should consider a redesign rather than just
a minimal fix. In particular, it took me a bit to reconstruct why
the code looks like it does now. I think it's this:
(1) Joins need special handling in get_param_path_clause_serials
because different join input pairs for the same result join may have
different joinrestrictinfo lists and thus enforce different sets of
rinfo serials; so we can't compute a ppi_serials value in advance.
(2) AppendPath needs special handling in case there are joins under
it, which would only occur for partitionwise joining. Otherwise
we could have precomputed the correct ppi_serials to begin with.
(I didn't check the code history, but I wonder if somebody rewrote
this for partitionwise joining and did not bother to add a comment
explaining that.)
But if that is the situation, then what about a wrapper path that
wraps a join? Seems like we need to be able to recurse and
compute a context-dependent set of ppi_serials for that too.
Now we could just recurse always, which is what your patch does, but
that feels annoyingly inefficient. I am wondering about a redesign
that would allow marking ParamPathInfos as context-dependent (if they
are for a join or something with a join under it) or not (everything
else). Then we only need to recurse when the hard cases occur,
and otherwise we can just believe the ppi_serials field.
Also, as a more nitpicky objection, I'd be inclined to refactor
get_param_path_clause_serials so that the whole thing is one big
switch over nodeTag(path), rather than the messy structure your
v1 patch would leave behind.
regards, tom lane
Here's a v2 patch incorporating the cosmetic changes I suggested,
and substantially enlarging the comments around the area.
I don't think we're ready to commit though, because I see a number
of loose ends.
I looked through the code and confirmed that ppi_rows is used only
as per the new comments, and that the mistake you found in
get_memoize_path() is the only unsafe use of ppi_serials.
However, there are a number of places in the memoize logic that
think they can use ppi_clauses, which seems about as unsafe.
I think that'll at worst cause some missed optimization chances,
but I'm not entirely certain.
Another interesting point is that Gather and GatherMerge are treated
like baserel paths, although what's under them might be a join or
append. This might be okay but I'm not quite convinced there either.
At least we probably should document this somewhere.
The fact that create_append_path sometimes uses
get_baserel_parampathinfo isn't reflected in my new comments either,
and I wonder if the fact that it does isn't breaking
get_param_path_clause_serials.
regards, tom lane
Hi Tom,
v2 looks right to me. Here is a case that fails on v16 too, using
plain LATERAL. With Richard's tables it gives 18 rows on 16 to
master, and 6 with v2:
set from_collapse_limit = 1;
set enable_hashjoin = off;
set enable_mergejoin = off;
select t1.*, ss.a from t1, lateral
(select s.a from t1 t3,
(select a from t2 where t2.b > t1.b
union all
select a from t2 where t2.b > t1.b) s) ss
where t1.a = ss.a + t1.b
The original SQL not work for 16 because v16 keeps a correlated I
N sublink as a SubPlan and never makes the semijoin. So we can
use Plain LATERAL to repro it.
Thanks,
Shihao
Hi,
On Fri Oct 9, 2026 at 4:13 PM -03, Tom Lane wrote:
Here's a v2 patch incorporating the cosmetic changes I suggested,
and substantially enlarging the comments around the area.
I don't think we're ready to commit though, because I see a number
of loose ends.
I've spent some time testing v2 with some planner GUC combinations
(hashjoin/mergejoin/material/ memoize off, collapse limits = 1,
partitionwise join, seqscan off...) and on master it returned wrong
results, and all of them had a Materialize on top of a parameterized
Append, as Richard described. With v2 all of them return the right
results, and the only plan change on those is the join filter coming
back. I didn't find any other wrong results with v2.
Just a small comment: Since Shihao's LATERAL case also fails on v16,
perhaps it's worth adding it to the regression tests too, so we have a
test that fails on all the back branches.
I also checked the back branches. v2 does not apply cleanly on 18, 17
and 16. On those branches only Material, Memoize, Unique and
GroupingSets paths copy the subpath's param_info, since the others
started doing that with 24225ad9aaf in v19. I think that on 17 and 16
there are two more differences: get_memoize_path() doesn't read
ppi_serials, and get_param_path_clause_serials() still has to handle
parameterized MergeAppend paths, which were only removed in 18.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
On Sat, Oct 10, 2026 at 4:13 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
However, there are a number of places in the memoize logic that
think they can use ppi_clauses, which seems about as unsafe.
I think that'll at worst cause some missed optimization chances,
but I'm not entirely certain.
I agree it's at worst a missed optimization. Joinrels and non-baserel
appendrels have NIL ppi_clauses and no lateral_vars, so
get_memoize_path() finds no cache key and we never build a Memoize on
top of them. A baserel appendrel can list a clause the Append doesn't
enforce, but such a clause references both sides, so
clause_sides_match_join() rejects it. In my example that costs us a
Memoize keyed on t1.b alone, and on that plan master drops the join
clause while v2 keeps it.
Another interesting point is that Gather and GatherMerge are treated
like baserel paths, although what's under them might be a join or
append. This might be okay but I'm not quite convinced there either.
At least we probably should document this somewhere.
I think this is OK. All callers of create_gather_path() and
create_gather_merge_path() pass required_outer = NULL, and we don't
generate parameterized partial paths, so a Gather/GatherMerge path
never has param_info. v3 adds an Assert plus a comment for this.
The fact that create_append_path sometimes uses
get_baserel_parampathinfo isn't reflected in my new comments either,
and I wonder if the fact that it does isn't breaking
get_param_path_clause_serials.
That's where the bogus serial comes from. In my example the
appendrel's ppi_serials includes "t1.a = s.a + t1.b", while the
intersection over its children is empty. A bare AppendPath is fine
since we intersect the children; only the wrappers were wrong, which
v2 fixes. v3 notes this in the comments for ParamPathInfo and
create_append_path.
Attached is v3. It also replaces the test case with a UNION ALL query
that gives wrong results on v16 as well as master.
- Richard
Hi Richard,
Thanks for updating v3, I did some tests with LATERAL test and it
all passed.
Also did some extra fuzz test in this area and did not find anything
Patch LGTM
Thanks,
Shihao
shihao zhong <zhong950419@gmail.com> writes:
Hi Richard,
Thanks for updating v3, I did some tests with LATERAL test and it
all passed.
Also did some extra fuzz test in this area and did not find anything
Patch LGTM
v3 LGTM too. Thanks Richard!
regards, tom lane
On Sun, Oct 25, 2026 at 3:35 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
shihao zhong <zhong950419@gmail.com> writes:
Also did some extra fuzz test in this area and did not find anything
Patch LGTM
v3 LGTM too. Thanks Richard!
Thanks! Pushed and back-patched.
- Richard