Re: [BUG] Remove self joins causes 'variable not found in subplan target lists' error

On Thu, Aug 28, 2025 at 6:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> This leaves us with some pretty unappetizing choices about what
> to do in v18:
>
> 1. Try to emulate the proposed HEAD fix.
>
> 2. Try to fix up the SJE patch so that it calculates relid changes
> honestly, or at least no less honestly than what happened before
> a3179ab69.
>
> 3. Revert SJE as well as a3179ab69 in v18.
>
> I will have a look at #1.  If there's somebody who wants to look
> at #2, feel free, but it won't be me because I don't understand
> the SJE patch well enough.  Either way, it's not great to be
> doing stuff like this just days before rc1 ...

I took a look at #2.  I don't have a good understanding of the SJE
patch either, so I might be missing something.

The core dump you mentioned can be reproduced with this query.

create table sj (a int unique, b int);

explain (verbose, costs off)
select t3.a from sj t1
    join sj t2 on t1.a = t2.a
    join lateral (select t1.a offset 0) t3 on true;

The reason is that remove_rel_from_query() does not update baserels'
lateral_vars lists and thus there are still references to the removed
relid there.

After fixing that issue, another error occurs during the regression
tests.

explain (costs off) select 1 from
   (sk k1 join sk k2 on k1.a = k2.a)
   join (sj j1 join sj j2 on j1.a = j2.a) on j1.b = k1.b;
ERROR:  variable not found in subplan target lists

The reason is that remove_rel_from_query() removes the old relid from
the attr_needed arrays but fails to add the substitute relid to them.

Hence, attached is the fix for SJE after reverting a3179ab69.  With
it, all regression tests pass.

Thanks
Richard