Re: Assert failure in standard_join_search()

Richard Guo <guofenglinux(at)gmail(dot)com> 于2026年10月11日周日 22:27写道：
> --- a/src/backend/optimizer/plan/initsplan.c
> +++ b/src/backend/optimizer/plan/initsplan.c
> @@ -2117,6 +2117,12 @@ make_outerjoininfo(PlannerInfo *root,
> */
> strict_relids = find_nonnullable_rels((Node *) clause);
>
> + /*
> + * find_nonnullable_rels looks inside PlaceHolderVars, so it can report
> + * rels that the clause references only laterally. Ignore those.
> + */
> + strict_relids = bms_int_members(strict_relids, clause_relids);
> +
>
> Thoughts?

That makes sense to me. To spell out the connection between the
incorrect commutation decision and the assertion failure, my
understanding is as follows.

In this query, the PHV representing s.x contains t2.a, but its phrels
and ph_eval_at are {t3}; t2 is a lateral dependency.
For the join to t4, pull_varnos() therefore returns {t3,t4}, whereas
find_nonnullable_rels() returns {t2,t4}.

The overlap with the lower t1/t2 join's min_righthand={t2} causes
make_outerjoininfo() to record an identity-3 commutation relationship.
Consequently, when we form the parameterized t3/t4 left join,
add_outer_joins_to_relids() withholds its OJ relid because the lower
t1/t2 join's OJ relid is not present yet.

However, the t1/t2 left join may already have been formed before it is
combined with the parameterized t3/t4 join.
When the two sides are subsequently combined, join_is_legal() skips
both SpecialJoinInfos because each join's minimum inputs are already
contained within one of the input relations.
The combination is therefore treated as an inner join, and
add_outer_joins_to_relids() does not add the withheld OJ relid.
Another construction order does include it, leaving two final joinrels
with different relid sets and triggering the assertion.

With your change, the lateral-only reference to t2 no longer
establishes the commutation relationship.
The t3/t4 join can still be built as a parameterized join, but its OJ
relid is added immediately.
The lateral/parameterization machinery handles its dependency on t2 separately.

The distinction between parameterization and identity-3 commutation
was what I initially missed.

--
Thanks,
Tender Wang