diff options
| -rw-r--r-- | src/backend/utils/adt/ruleutils.c | 21 | ||||
| -rw-r--r-- | src/test/regress/expected/merge.out | 50 | ||||
| -rw-r--r-- | src/test/regress/sql/merge.sql | 17 |
3 files changed, 79 insertions, 9 deletions
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index f0b16fa23e5..f01cc2521c8 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -4973,8 +4973,11 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan) * For a WorkTableScan, locate the parent RecursiveUnion plan node and use * that as INNER referent. * - * For MERGE, make the inner tlist point to the merge source tlist, which - * is same as the targetlist that the ModifyTable's source plan provides. + * For MERGE, pretend the ModifyTable's source plan (its outer plan) is + * INNER referent. This is the join from the target relation to the data + * source, and all INNER_VAR Vars in other parts of the query refer to its + * targetlist. + * * For ON CONFLICT .. UPDATE we just need the inner tlist to point to the * excluded expression's tlist. (Similar to the SubqueryScan we don't want * to reuse OUTER, it's used for RETURNING in some modify table cases, @@ -4989,17 +4992,17 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan) dpns->inner_plan = find_recursive_union(dpns, (WorkTableScan *) plan); else if (IsA(plan, ModifyTable)) - dpns->inner_plan = plan; - else - dpns->inner_plan = innerPlan(plan); - - if (IsA(plan, ModifyTable)) { if (((ModifyTable *) plan)->operation == CMD_MERGE) - dpns->inner_tlist = dpns->outer_tlist; + dpns->inner_plan = outerPlan(plan); else - dpns->inner_tlist = ((ModifyTable *) plan)->exclRelTlist; + dpns->inner_plan = plan; } + else + dpns->inner_plan = innerPlan(plan); + + if (IsA(plan, ModifyTable) && ((ModifyTable *) plan)->operation == CMD_INSERT) + dpns->inner_tlist = ((ModifyTable *) plan)->exclRelTlist; else if (dpns->inner_plan) dpns->inner_tlist = dpns->inner_plan->targetlist; else diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 28a69802d72..bfdf59a2750 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1483,6 +1483,56 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + QUERY PLAN +------------------------------------------------------------------------------------- + Merge on public.tgt t + -> Hash Join + Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid + Hash Cond: (t.a = s.a) + Join Filter: (t.b < (SubPlan 1)) + -> Seq Scan on public.tgt t + Output: t.ctid, t.a, t.b + -> Hash + Output: s.a, s.b, s.c, s.d, s.ctid + -> Seq Scan on public.src s + Output: s.a, s.b, s.c, s.d, s.ctid + SubPlan 1 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r + Output: r.ab, r.cd + Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d))) + SubPlan 4 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_2 + Output: r_2.ab, r_2.cd + Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d))) + SubPlan 3 (returns $9,$10) + -> Result + Output: s.b, $8 + InitPlan 2 (returns $8) + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_1 + Output: r_1.ab, r_1.cd + Filter: ((r_1.ab = (s.a + s.b)) AND (r_1.cd = (s.c - s.d))) +(32 rows) + +DROP TABLE src, tgt, ref; -- Subqueries BEGIN; MERGE INTO sq_target t diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 82faa7364ca..ac6d2f0e0ee 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -948,6 +948,23 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); + +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + +DROP TABLE src, tgt, ref; + -- Subqueries BEGIN; MERGE INTO sq_target t |