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.solov...@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.
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
