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

On Tue, Jun 17, 2025 at 12:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > In identify_current_nestloop_params(), I wonder if we should use the
> > join path's required-outer rels rather than the left path's as the
> > "outerrelids". It seems that there may be cases where the join path
> > is parameterized by some other rel while its left path itself is not
> > parameterized at all.

> I went back and forth on that. In principle, we shouldn't put such
> a calculation into the left path if it's not parameterized by the
> rel supplying the required value. In practice, if the required value
> is getting passed down to the join path then it's available to the
> left path too, so it should work fine whether the left path is marked
> like that or not. In the only test case I have that exercises this
> logic, both paths are marked with the required_outer anyway, so it
> doesn't seem to matter. If we can find a case where that's not so,
> then probably we should do it the other way.

I tried to find such a case but didn't succeed; though I suspect
that's simply because I haven't tried hard enough. Conceptually, I'm
thinking of a query where 1) A laterally references both B and C while
B does not reference any other relation, and 2) A has a PHV parameter
whose ph_eval_at includes both B and C. In such a query, the B/A join
path is parameterized by C, while its left path B is not parameterized
at all. In order to add the PHV to B's tlist, we'll need to consider
the join path's required-outer rels not just those of the left path.

Thanks
Richard