Re: "failed to build any N-way joins" from a five-relation query

On Sat, Oct 10, 2026 at 11:37 AM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> The FULL JOIN is planned as a separate joinlist subproblem, and its
> resulting joinrel becomes an input to the outer join search.
> The loop over simple_rel_array does not account for this composite
> input's lateral dependencies.

Thanks for the report and the analysis. I agree that this is the key
point: the full join's joinrel is a single input to the outer join
search, so once s1 has to be included in the search, t3 has to come
along with it.

It is not specific to full joins, though. join_collapse_limit or
from_collapse_limit can also make sub-joinlist joinrels.

> The proposed fix is to also scan joinrel entries in root->initial_rels
> when expanding join_plus_rhs:

+1 to this idea, and I think we can take it one step further.
root->initial_rels contains all the inputs of the current join search,
base rels as well as sub-joinlist joinrels, so a single loop over it
can replace the loop over simple_rel_array in v1. For each input that
laterally references a rel found so far, we add all of its relids.

Hence, the attached v2.

- Richard