25.08.2025 16:28, Richard Guo пишет:
> 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
Hi, Richard!
Thanks for finding this example. I have added it to join.sql regress test.
New patch version is attached.
I also thought about case to just check Var to be a constant. But
the main question is 'if it is the only node affected?'. Example shows
that error is caused by Unique path, but maybe there are another
nodes which will cause such error.
Yes only 'create_unique_paths' creates new TargetEntry. But I think
the root cause is that when recreating 'attr_needed', the dependency
between this relation and some other relation is lost.
---
Regards,
Sergey Soloviev
Tantor Labs LLC
