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@sss.pgh.pa.us> wrote:
> Richard Guo <guofenglinux@gmail.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