Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,

On Sat, Sep 5, 2026 at 8:09 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> It seems to me the root cause is that in a partitionwise child join,
> root->curOuterRels holds child relids, but PlaceHolderInfo.ph_eval_at
> is always expressed in top-parent relids. So in
> replace_nestloop_params_mutator the subset check fails for a PHV
> evaluated at the outer child rel.

The required-outer set passed to identify_current_nestloop_params()
has the same problem. It is in terms of child rels once a
parameterized child join path has been reparameterized by an upper
child join, and identify_current_nestloop_params() also matches it to
PlaceHolderInfo.ph_eval_at, which is always expressed in top-parent
relids.

I suspected this could cause a NestLoopParam for a PlaceHolderVar to
never be claimed by any nestloop node, resulting in "failed to assign
all NestLoopParams to plan nodes" errors. It took me quite a while,
but I eventually found a query that hits it. So it's real. (Please
see the test case in the attached patch.)

EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
(SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON
t3.a = t4.a
WHERE t4.b = 0) t2 ON t1.a = t2.a
WHERE t1.c = t2.c AND t2.a IS NOT NULL;
ERROR: failed to assign all NestLoopParams to plan nodes

Attached is a patch to fix both bugs.

(I'm surprised it has taken us so long to find these bugs. I suspect
part of the reason is that partitionwise join is disabled by default.
AFAICS, enable_partitionwise_join and enable_partitionwise_aggregate
are the only planner method GUCs that are off by default. I wonder if
we should turn them on by default, so that bugs in these areas get
found sooner.)

- Richard