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