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

25.08.2025 16:59, Andrei Lepikhov:
> On 25/8/2025 15:28, Richard Guo wrote:
>> 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
> Thanks for your reproduction.
> Unfortunately, it works only in the absence of an ANALYZE, like the original example.
> Also, I would say it is not a self-join-related issue. This example employs the removal of the 'unnecessary left
join'.Currently, I'm unsure why this example causes the issue: the removing t2 table shouldn't have any references in
ECswithin the EXISTS part. 
>
Hi!

Yes, this is not created by SJE, but this bug introduced by commit adding SJE logic:
first remove any 'attr_needed' (and other info) and then restore it according
to only needed relations.

Provided example shows bug in the code.

'attr_needed' is cleared at src/backend/optimizer/plan/analyzejoins.c:526.
If we dump the state for relation t4, then we will get

attr_needed[a] = {1, 6} /* {t1, t4} */

And also, there is EC = {t1.a, t4.a, 2}. This comes from WHERE in EXISTS:

t1.a = t4.a AND t4.a = 2

But during the second phase (recreating 'attr_needed') we see that EC contains
constant (2), so skip recreating 'attr_needed[a]' for t4, but it previously had t1
in 'attr_needed' which was not removed by join elimination logic. Roughly
speaking, we have lost dependency with t1.

Thus, error is caused not by removing t2 itself, but by the manipulations
involved.
---
Regards,
Sergey Solviev
Tantor Labs LLC