From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18953: Planner fails to build plan for complex query with
 LATERAL references
In-reply-to: 
 <CAMbWs4_wYvQWHCwBfSXj9OQ-fWn+xnntR+EVzks2KB0eRWcRpw@mail.gmail.com>
References: <18953-1c9883a9d4afeb30@postgresql.org>
 <CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
 <1268645.1749765012@sss.pgh.pa.us> <1499514.1749848324@sss.pgh.pa.us>
 <CAMbWs4_E7wC3LX17jr+RnRunz9ROcwhn0eXt0riJ6FjbxB0g+g@mail.gmail.com>
 <527134.1750087495@sss.pgh.pa.us>
 <CAMbWs4_wYvQWHCwBfSXj9OQ-fWn+xnntR+EVzks2KB0eRWcRpw@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Tue, 17 Jun 2025 11:20:20 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <835658.1750177751.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 17 Jun 2025 12:29:11 -0400
Message-ID: <835659.1750177751@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/835659.1750177751%40sss.pgh.pa.us>
Precedence: bulk

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