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

On Sat, Aug 23, 2025 at 12:27 AM Sergey Soloviev
<sergey.soloviev@tantorlabs.ru> wrote:
> I would like write a test in 'join.sql', but for now it requires patches
> to easily reproduce the bug. I appreciate it if someone could find
> an easier way to reproduce the bug and write a simple test.

Nice catch!  Here's a query that reproduces the error without needing
to hack the code.

create table t (a int, b int);
create unique index on t (a);

select t1.a from t t1
  left join t t2 on t1.a = t2.a
       join t t3 on true
where exists (select 1 from t t4
                join t t5 on t4.b = t5.b
                join t t6 on t5.b = t6.b
              where t1.a = t4.a and t3.a = t5.a and t4.a = 2);
ERROR:  variable not found in subplan target lists

This bug was introduced by commit:

commit a3179ab692be4314d5ee5cd56598976c487d5ef2
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Fri Sep 27 16:04:04 2024 -0400

    Recalculate where-needed data accurately after a join removal.

(I'm a bit surprised it took us so long to discover it.)

When distributing qual clause "t1.a = t4.a", distribute_qual_to_rels
adds t1.a and t4.a that are used in this clause to the targetlists of
their relations.  (However, if the clause gets absorbed into an EC
that contains const, this can result in adding Vars to relations that
do not actually require them.  But that's a different problem.)

However, when rebuilding attr_needed bits for t1.a and t4.a after the
join removal, rebuild_eclass_attr_needed fails to restore the bits
because it skips ECs that contain constant, as explained by Sergey.
Later, it turns out that t4.a is needed at the join leval t4/t5/t6 for
the unique-ification of the RHS of the semi-join.

The proposed patch can fix this error.  However, I'm wondering if we
could address it from the unique-ification side instead.  If a Var
we're trying to unique-ify is known to be equal to a constant, then we
shouldn't need to unique-ify that Var -- and if it's not needed at
upper levels, it shouldn't need to be in the targetlist of the unique
path.  For example, in the query above, t4.a does not need to be added
in the targetlist of the unique path, right?

Thanks
Richard