On 29/12/2023 12:00, Alexander Lakhin wrote:
Hi Alexander,
23.10.2023 14:29, Alexander Korotkov wrote:
Fixed all of the above. Thank you for catching this!
I've discovered that starting from d3d55ce57 the following query:
CREATE TABLE t(a int PRIMARY KEY);
WITH tt AS (SELECT * FROM t)
UPDATE t SET a = tt.a + 1 FROM tt
WHERE tt.a = t.a RETURNING t.a;
triggers an error "variable not found in subplan target lists".
(Commits 8a8ed916f and b5fb6736e don't fix this, unfortunately.)
Thanks for the report!
The problem is with the resultRelation field. We forget to replace the
relid here.
Could you check your issue with the patch in the attachment? Does it
resolve this case?
--
regards,
Andrei Lepikhov
Postgres Professional
diff --git a/src/backend/optimizer/plan/analyzejoins.c
b/src/backend/optimizer/plan/analyzejoins.c
index 6c02fe8908..f79c673afd 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1861,6 +1861,8 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark
*kmark, PlanRowMark *rmark,
/* Replace varno in all the query structures */
query_tree_walker(root->parse, replace_varno_walker, &ctx,
QTW_EXAMINE_SORTGROUP);
+ if (root->parse->resultRelation == toRemove->relid)
+ root->parse->resultRelation = toKeep->relid;
/* Replace links in the planner info */
remove_rel_from_query(root, toRemove, toKeep->relid, NULL, NULL);
@@ -1870,6 +1872,9 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark
*kmark, PlanRowMark *rmark,
toRemove->relid, toKeep->relid);
replace_varno((Node *) root->processed_groupClause,
toRemove->relid, toKeep->relid);
+ replace_relid(root->all_result_relids, toRemove->relid, toKeep->relid);
+ replace_relid(root->leaf_result_relids, toRemove->relid, toKeep->relid);
+
/*
* There may be references to the rel in root->fkey_list, but if so,
