Thread

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

Andrei Lepikhov <lepihov@gmail.com> — 2025-08-25T13:59:49Z

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 ECs within the EXISTS part.

-- 
regards, Andrei Lepikhov