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

Alexander Lakhin <exclusion(at)gmail(dot)com> writes:
> 17.06.2025 19:29, Tom Lane wrote:
>> 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.

> Please look at the following (simplified version of a query generated by
> SQLsmith), which produces errors after a16ef313f2:

Hah, that didn't take long! Your second case is indeed a
counterexample to my argument. We end up with a PHV having
phrels (b 7 8) which needs to be evaluated here:

{NESTPATH
:jpath.path.pathtype 356
:parent_relids (b 1 6 7)
:required_outer (b 8)
...
:jpath.outerjoinpath
{PATH
:pathtype 339
:parent_relids (b 7)
:required_outer (b)

Doing things the way Richard wanted to (ie using the nestloop's
required_outer) gets past the "failed to assign all NestLoopParams"
error, but then we get the same "unrecognized node type: 22" error
as in the first example.

That error seems considerably nastier to fix. I think it is a
pre-existing problem, though I don't currently have an explanation
why we've not seen it reported before. What is happening is that
we pull up a PHV for "c", containing a SubLink for (SELECT true),
and only some copies of it get put through preprocess_expression,
which is responsible for converting SubLinks to SubPlans among
other essential tasks. It seems that up to now we've always
managed to use only preprocessed copies in the finished plan.
But now a not-preprocessed copy is managing to get through to
the executor, which promptly spits up because it doesn't know
what to do with a SubLink.

We could probably hack this in a localized way by ensuring that
we push a preprocessed copy of the PHV into the left plan's tlist.
(One way to get that would be to look in the placeholder_list for the
correct phid.) However, now that I've seen this I have very little
faith that there aren't other bugs of the same ilk, that somehow
we've not seen up to now. I'm thinking about what we must do to
ensure that all PHVs are preprocessed once and only once, perhaps
at the moment they get put into the placeholder_list.

I don't have a patch for that yet, but attached is something that
gets rid of the "failed to assign all NestLoopParams" problem.

regards, tom lane