From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: exclusion@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18953: Planner fails to build plan for complex query with LATERAL references
Date: Tue, 17 Jun 2025 12:29:11 -0400
Message-ID: <835659.1750177751@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, guofenglinux@gmail.com, 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: <835659.1750177751@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox