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

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> On Tue, Jun 17, 2025 at 12:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

After thinking about this for awhile, I'm not seeing how that could
happen. A PHV with ph_eval_at exceeding its syntactic scope could
only be created via something like

B left join lateral (select coalesce(B.x, ...) as Q from D) C

that is we need a non-strict targetlist expression that references
something outside the sub-select proper. In this case all paths
created for C will have required_outer mentioning B, because there's
no way to compute C's reltarget without an outer reference to B.
This might be embedded in

... join A on A.y = C.Q

which gives rise to the situation you describe --- but there's
no possibility of the planner trying to do this in the order

C join (B join A)

because it will think that all paths for C require B on the
outside. It will only consider

B join (C join A)

and the path for C will show B as required_outer.

So I'm inclined to leave that code as I had it. It's notationally
a bit simpler and it doesn't require assuming that we can ignore
the path's required_outer marking at this stage. If I'm wrong,
someone will eventually find a counterexample and we can fix it
then; the changes won't be large.

regards, tom lane