Re: BUG #19799: Self-join elimination: "failed to build any 3-way joins" with LATERAL

PG Bug reporting form <noreply(at)postgresql(dot)org> 于2026年10月27日周二 04:32写道：
>
>
> CREATE TABLE t (a int PRIMARY KEY);
> INSERT INTO t VALUES (1), (2);
> SELECT s.y
> FROM t d LEFT JOIN
> (t x CROSS JOIN LATERAL (SELECT x.a AS v OFFSET 0) b
> CROSS JOIN LATERAL (SELECT coalesce(b.v, 0) FROM t c
> WHERE c.a = x.a) s(y))
> ON true;
> ERROR: XX000: failed to build any 3-way joins
> LOCATION: standard_join_search, allpaths.c:3537 (18.6)
>
> On builds with --enable-cassert the backend crashes instead:
>
> TRAP: failed Assert("!bms_is_member(rti, lateral_relids)"),
> File: "initsplan.c", Line: 1370 (master; Line 1086 on REL_18_STABLE)

Thanks for the report.

I investigated this issue and found that self-join elimination can
create a self-referential lateral dependency through a PlaceHolderVar.

In the reported query, before self-join elimination, the relevant
dependencies are approximately:

c -> b -> x

Here, the PlaceHolderVar is evaluated at c, and its expression has a
lateral dependency on the subquery b. The lateral subquery b, in turn,
references x.

SJE determines that x and c can be merged. When x is replaced by c,
the dependencies become:

c -> b -> c

After SJE restarts join planning, create_lateral_join_info() computes
the transitive closure of the lateral dependencies.
The dependency from b to c is consequently propagated back to c,
causing c to acquire a lateral dependency on itself.
This eventually results in the reported planner error, or an assertion
failure in an assertion-enabled build.

There are a few possible ways to address this:

1. Adjust the PlaceHolderVar evaluation and lateral-dependency
information while removing the relation.

This seems risky because ph_eval_at and ph_lateral describe semantic
constraints on where the expression may be evaluated. Rewriting them
merely to allow SJE could change the semantics of the PlaceHolderVar.

2. Build a general lateral-dependency graph and reject an SJE
candidate whenever contracting the two relations would form a cycle.

This would handle arbitrary dependency chains, but it requires a
relatively large amount of new SJE code, including graph traversal and
cycle detection.
That seems like more machinery than the known problem requires.

3. Reject the SJE candidate when a PlaceHolderVar exposes the
problematic dependency pattern.

The attached patch takes the third, conservative approach. Before
merging two self-join candidates, it checks whether a PlaceHolderVar
evaluated at one candidate depends laterally on an intermediate
relation which, in turn, references the other candidate. If so, SJE
skips that pair.

This is similar in spirit to commit
3138f248cd3de43dd0f96760fc666152ea8a5c37: instead of attempting to
rewrite PlaceHolderVar placement information, avoid an optimization
that would make the existing placement constraints invalid.

The check is intentionally narrower than a general graph-cycle detector.
In particular, it preserves SJE when only a direct lateral reference
exists between the two candidates.
That case is safe because the dependency disappears when the
referenced relation is merged. For example, the existing test:

select * from sj t1
left join lateral
(select t1.a as t1a, * from sj t2) s
on true
where t1.a = s.a;

continues to be reduced to a scan of t2; the patch does not introduce
a plan change for that query.
I have run the regression tests with the attached patch, and they pass.

Thoughts?
--
Thanks,
Tender Wang