Re: BUG #18953: Planner fails to build plan for complex query with LATERAL references

On Wed, Jun 11, 2025 at 5:33 PM Richard Guo <guofenglinux@gmail.com> wrote:
> In this query, the join between t3 and s is placed into a separate
> join sub-problem due to the from_collapse_limit.  This join is deemed
> not legal by join_is_legal(), as have_dangerous_phv() thinks the PHV
> could pose a hazard as described in that function's comment.  As a
> result, no join could be built for this sub-problem.

> No idea how to fix this though.  Any thoughts?

This might be a silly idea, but if we can't find a valid plan for a
join sub-problem, perhaps we could consider flattening the
sub-joinlist into the higher level to explore a solution in a broader
search space.  The top-most joinlist should always be able to produce
a valid plan, otherwise something must be wrong during planning.

This may violate the from_collapse_limit restriction, but such cases
are expected to be very rare.

Thanks
Richard