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

On Mon, Jun 23, 2025 at 3:31 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Lakhin <exclusion@gmail.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.

FWIW, a16ef313f also causes GEQO to encounter the "failed to assign
all NestLoopParams" problem, which can be seen in one of our
regression test queries.

set geqo_threshold to 2;

regression=# explain (verbose, costs off)
select ss2.* from
  int4_tbl i41
  left join int8_tbl i8
    join (select i42.f1 as c1, i43.f1 as c2, 42 as c3
          from int4_tbl i42, int4_tbl i43) ss1
    on i8.q1 = ss1.c2
  on i41.f1 = ss1.c1,
  lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
where ss1.c2 = 0;
ERROR:  failed to assign all NestLoopParams to plan nodes

This problem vanishes with the proposed patch.

Thanks
Richard