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

Haibo Yan <tristan(dot)yim(at)gmail(dot)com> 于2026年10月28日周三 11:40写道：
>
>
> Your patch handles the reduced reproducer, but the following related
> case still fails with self-join elimination enabled:
>
> CREATE TABLE t (a int PRIMARY KEY);
> INSERT INTO t VALUES (1), (2);
>
> SELECT s.y
> FROM t x,
> abs(x.a) b,
> LATERAL (
> SELECT x.a + d.a + b.b AS y
> FROM t c, t d
> WHERE c.a = x.a AND d.a = x.a
> ) s
> GROUP BY CUBE (1);
>

Thanks for testing the patch and for providing this additional case.

I agree that the current patch is too narrow to handle this case. As I
mentioned in my previous message, the patch deliberately checks only
the dependency pattern exposed by the original reproducer. I also
considered a more general solution that models the lateral dependencies
as a graph and checks whether merging two candidate relations would
create a cycle.

At the time, however, I was not sure that adding the amount of code
needed for a general dependency-cycle check was justified for this
single reported case. Therefore, I chose the smaller and more
conservative fix as a first approach.

Your new example demonstrates an important limitation of that approach:
the dangerous dependency does not necessarily exist in the current
ph_eval_at and ph_lateral sets. It can appear only after the
candidate substitution changes the PHV evaluation sites and rewrites
the lateral references. Consequently, inspecting only the dependency
sets before the merge cannot cover this class of cases reliably.

Given this additional report, it now seems that a general dependency
cycle check is necessary. Such a check needs to simulate the candidate
substitution, derive the resulting PHV evaluation and lateral
dependencies, and reject the merge if the substituted dependency graph
contains a cycle. It also needs to update the dependency state after
an accepted merge, so that subsequent candidates are checked against
the updated graph.

--
Thanks,
Tender Wang