Hi, hackers! We have encountered a bug in the planner which raises an error 'variable not found in target lists'.
To reproduce apply patch
'0001-Disable-JOIN_SEMI-and-JOIN_RIGHT_SEMI-paths.patch'
and run this query:
```
create table t1(i int, j int);
create table t2(i int, j int);
create table t3(i int, j int);
create table t4(i int, j int);
create table t5(i int, j int);
create unique index on t2(i, j);
select t1.j from t1
left join t2 on t1.i = t2.i and t2.j = 1
left join (
select i from t3
where exists (
select true from t4
left join t5 as t5 on t5.j = t4.j
left join t5 as t6 on t6.j = t4.j
where t4.i = t3.i
and t4.i =2
)
) t on t1.j = t.i;
```
This bug was caused by 'rebuild_eclass_attr_needed' function which
did not process EC with constants. The logic behind it is clear - we
can substitute these attributes with constants and do not use them
later, but it is not true when `EXISTS` converts to JOIN. If planner
decided to use Unique + Sort, then the following happens:
1. `t4.i`, `t3.i` and `2` are contained in same equivalence class, so
'ec_has_const' is 'true'
2. During 'rebuild_eclass_attr_needed' their EC is skipped because it
contains constant (src/backend/optimizer/path/equivclass.c:2590)
```
if (list_length(ec->ec_members) > 1 && !ec->ec_has_const)
```
3. 'attr_needed' of 't4.i' is still NULL, so it is not added to 'pathtarget'
of 'RelOptInfo' (src/backend/optimizer/util/relnode.c:1199)
```
if (!bms_nonempty_difference(baserel->attr_needed[ndx], relids))
continue; /* nope, skip it */
```
4. 'UniquePath' is created, but `t4.i` (which must be unique expression)
is not in 'pathtarget' of RelOptInfo, so added to path's targetlist
manually (src/backend/optimizer/plan/planner.c:8395)
```
tle = tlist_member(uniqexpr, newtlist);
if (!tle)
{
tle = makeTargetEntry((Expr *) uniqexpr,
nextresno,
NULL,
false);
newtlist = lappend(newtlist, tle);
}
```
5. When creating a Plan targetlist is just copied from Path
6. At the very end 'set_plan_references' can not find expression at
level below (because it was "not needed" and was not added to
targetlist), so it throws 'variable not found in subplan target lists'
This patch fixes this error by taking considering multi-member EC
even with constants, but skipping constant members during members
iteration.
There are 4 attachments:
- 'schema.sql' - schema for reproducing the bug
- 'query.sql' - actual query to raise error
- '0001-Disable-JOIN_SEMI-and-JOIN_RIGHT_SEMI-paths.patch' - disable
JOIN_SEMI to be able to reproduce the bug
- '0001-fix-variable-not-found-in-subplan-target-lists.patch' - patch
with actual fix of this bug
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.
---
Regards,
Sergey Soloviev
Tantor Labs LLC
schema.sql
Description: application/sql
query.sql
Description: application/sql
From 9ba6ea7c742b7985b43c8de463b6959f4e16c400 Mon Sep 17 00:00:00 2001 From: Sergey Soloviev <[email protected]> Date: Fri, 22 Aug 2025 16:43:14 +0300 Subject: [PATCH] Disable 'JOIN_SEMI' and 'JOIN_RIGHT_SEMI' paths This patch disables creation of SEMI/RIGHT-SEMI paths in order to make reproducing the bug easier. --- src/backend/optimizer/path/joinrels.c | 35 +++++++++++++++------------ 1 file changed, 19 insertions(+), 16 deletions(-) diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 535248aa525..aded9e11b83 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -972,22 +972,25 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, * then do an innerjoin (see comments in join_is_legal). In the * latter case we can't apply JOIN_SEMI joining. */ - if (bms_is_subset(sjinfo->min_lefthand, rel1->relids) && - bms_is_subset(sjinfo->min_righthand, rel2->relids)) - { - if (is_dummy_rel(rel1) || is_dummy_rel(rel2) || - restriction_is_constant_false(restrictlist, joinrel, false)) - { - mark_dummy_rel(joinrel); - break; - } - add_paths_to_joinrel(root, joinrel, rel1, rel2, - JOIN_SEMI, sjinfo, - restrictlist); - add_paths_to_joinrel(root, joinrel, rel2, rel1, - JOIN_RIGHT_SEMI, sjinfo, - restrictlist); - } + + /* + * if (bms_is_subset(sjinfo->min_lefthand, rel1->relids) && + * bms_is_subset(sjinfo->min_righthand, rel2->relids)) + * { + * if (is_dummy_rel(rel1) || is_dummy_rel(rel2) || + * restriction_is_constant_false(restrictlist, joinrel, false)) + * { + * mark_dummy_rel(joinrel); + * break; + * } + * add_paths_to_joinrel(root, joinrel, rel1, rel2, + * JOIN_SEMI, sjinfo, + * restrictlist); + * add_paths_to_joinrel(root, joinrel, rel2, rel1, + * JOIN_RIGHT_SEMI, sjinfo, + * restrictlist); + * } + */ /* * If we know how to unique-ify the RHS and one input rel is -- 2.43.0
From 4ebad7d772827fe2f3f34fcf6681d49fe4a829ff Mon Sep 17 00:00:00 2001 From: Sergey Soloviev <[email protected]> Date: Fri, 22 Aug 2025 16:37:02 +0300 Subject: [PATCH] fix: variable not found in subplan target lists When restoring 'attr_needed' after self-join removal we should also consider EC with constants because some plan nodes can use such constant attributes. For example, after converting `EXISTS` to JOIN planner can create `UniquePath` referencing attribute which is contained in EC with constant. But because `attr_needed` is NULL then this attribute is not returned by JOIN, so at `set_plan_references` fails to find this attribute in targetlist of node below. --- src/backend/optimizer/path/equivclass.c | 21 +++++++++++++++------ 1 file changed, 15 insertions(+), 6 deletions(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 441f12f6c50..58ca75ab7a8 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2586,18 +2586,27 @@ rebuild_eclass_attr_needed(PlannerInfo *root) */ Assert(ec->ec_childmembers == NULL); - /* Need do anything only for a multi-member, no-const EC. */ - if (list_length(ec->ec_members) > 1 && !ec->ec_has_const) + /* + * Need do anything only for a multi-member. Note that some + * wrapping plan nodes (i.e. UniquePath) can reference attributes + * contained in EC with constants. + */ + if (list_length(ec->ec_members) > 1) { ListCell *lc2; foreach(lc2, ec->ec_members) { EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2); - List *vars = pull_var_clause((Node *) cur_em->em_expr, - PVC_RECURSE_AGGREGATES | - PVC_RECURSE_WINDOWFUNCS | - PVC_INCLUDE_PLACEHOLDERS); + List *vars; + + if (cur_em->em_is_const) + continue; + + vars = pull_var_clause((Node *) cur_em->em_expr, + PVC_RECURSE_AGGREGATES | + PVC_RECURSE_WINDOWFUNCS | + PVC_INCLUDE_PLACEHOLDERS); add_vars_to_attr_needed(root, vars, ec->ec_relids); list_free(vars); -- 2.43.0
