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

On 25/8/2025 15:28, Richard Guo wrote:
> On Sat, Aug 23, 2025 at 12:27 AM Sergey Soloviev
> <sergey(dot)soloviev(at)tantorlabs(dot)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 ECs within the EXISTS part.

--
regards, Andrei Lepikhov