Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 33e729c

Browse filesBrowse files
deanrasheeddeanrasheed
committed
Fix EXPLAIN output for subplans in MERGE.
Given a subplan in a MERGE query, EXPLAIN would sometimes fail to properly display expressions involving Params referencing variables in other parts of the plan tree. This would affect subplans outside the topmost join plan node, for which expansion of Params would go via the top-level ModifyTable plan node. The problem was that "inner_tlist" for the ModifyTable node's deparse_namespace was set to the join node's targetlist, but "inner_plan" was set to the ModifyTable node itself, rather than the join node, leading to incorrect results when descending to the referenced variable. Fix and backpatch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWAv-sZuH%2BwG5xJ-%2BGt7qGNGX8wUQd3XYydMFDKgRB9nw%40mail.gmail.com
1 parent 20e5810 commit 33e729c
Copy full SHA for 33e729c

File tree

Expand file treeCollapse file tree

3 files changed

+79
-9
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+79
-9
lines changed

‎src/backend/utils/adt/ruleutils.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/ruleutils.c
+12-9Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -4988,8 +4988,11 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
49884988
* For a WorkTableScan, locate the parent RecursiveUnion plan node and use
49894989
* that as INNER referent.
49904990
*
4991-
* For MERGE, make the inner tlist point to the merge source tlist, which
4992-
* is same as the targetlist that the ModifyTable's source plan provides.
4991+
* For MERGE, pretend the ModifyTable's source plan (its outer plan) is
4992+
* INNER referent. This is the join from the target relation to the data
4993+
* source, and all INNER_VAR Vars in other parts of the query refer to its
4994+
* targetlist.
4995+
*
49934996
* For ON CONFLICT .. UPDATE we just need the inner tlist to point to the
49944997
* excluded expression's tlist. (Similar to the SubqueryScan we don't want
49954998
* to reuse OUTER, it's used for RETURNING in some modify table cases,
@@ -5004,17 +5007,17 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
50045007
dpns->inner_plan = find_recursive_union(dpns,
50055008
(WorkTableScan *) plan);
50065009
else if (IsA(plan, ModifyTable))
5007-
dpns->inner_plan = plan;
5008-
else
5009-
dpns->inner_plan = innerPlan(plan);
5010-
5011-
if (IsA(plan, ModifyTable))
50125010
{
50135011
if (((ModifyTable *) plan)->operation == CMD_MERGE)
5014-
dpns->inner_tlist = dpns->outer_tlist;
5012+
dpns->inner_plan = outerPlan(plan);
50155013
else
5016-
dpns->inner_tlist = ((ModifyTable *) plan)->exclRelTlist;
5014+
dpns->inner_plan = plan;
50175015
}
5016+
else
5017+
dpns->inner_plan = innerPlan(plan);
5018+
5019+
if (IsA(plan, ModifyTable) && ((ModifyTable *) plan)->operation == CMD_INSERT)
5020+
dpns->inner_tlist = ((ModifyTable *) plan)->exclRelTlist;
50185021
else if (dpns->inner_plan)
50195022
dpns->inner_tlist = dpns->inner_plan->targetlist;
50205023
else

‎src/test/regress/expected/merge.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/merge.out
+50Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1473,6 +1473,56 @@ WHEN MATCHED AND t.a < 10 THEN
14731473

14741474
DROP TABLE ex_msource, ex_mtarget;
14751475
DROP FUNCTION explain_merge(text);
1476+
-- EXPLAIN SubPlans and InitPlans
1477+
CREATE TABLE src (a int, b int, c int, d int);
1478+
CREATE TABLE tgt (a int, b int, c int, d int);
1479+
CREATE TABLE ref (ab int, cd int);
1480+
EXPLAIN (verbose, costs off)
1481+
MERGE INTO tgt t
1482+
USING (SELECT *, (SELECT count(*) FROM ref r
1483+
WHERE r.ab = s.a + s.b
1484+
AND r.cd = s.c - s.d) cnt
1485+
FROM src s) s
1486+
ON t.a = s.a AND t.b < s.cnt
1487+
WHEN MATCHED AND t.c > s.cnt THEN
1488+
UPDATE SET (b, c) = (SELECT s.b, s.cnt);
1489+
QUERY PLAN
1490+
-------------------------------------------------------------------------------------
1491+
Merge on public.tgt t
1492+
-> Hash Join
1493+
Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid
1494+
Hash Cond: (t.a = s.a)
1495+
Join Filter: (t.b < (SubPlan 1))
1496+
-> Seq Scan on public.tgt t
1497+
Output: t.ctid, t.a, t.b
1498+
-> Hash
1499+
Output: s.a, s.b, s.c, s.d, s.ctid
1500+
-> Seq Scan on public.src s
1501+
Output: s.a, s.b, s.c, s.d, s.ctid
1502+
SubPlan 1
1503+
-> Aggregate
1504+
Output: count(*)
1505+
-> Seq Scan on public.ref r
1506+
Output: r.ab, r.cd
1507+
Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d)))
1508+
SubPlan 4
1509+
-> Aggregate
1510+
Output: count(*)
1511+
-> Seq Scan on public.ref r_2
1512+
Output: r_2.ab, r_2.cd
1513+
Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d)))
1514+
SubPlan 3 (returns $9,$10)
1515+
-> Result
1516+
Output: s.b, $8
1517+
InitPlan 2 (returns $8)
1518+
-> Aggregate
1519+
Output: count(*)
1520+
-> Seq Scan on public.ref r_1
1521+
Output: r_1.ab, r_1.cd
1522+
Filter: ((r_1.ab = (s.a + s.b)) AND (r_1.cd = (s.c - s.d)))
1523+
(32 rows)
1524+
1525+
DROP TABLE src, tgt, ref;
14761526
-- Subqueries
14771527
BEGIN;
14781528
MERGE INTO sq_target t

‎src/test/regress/sql/merge.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/merge.sql
+17Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -939,6 +939,23 @@ WHEN MATCHED AND t.a < 10 THEN
939939
DROP TABLE ex_msource, ex_mtarget;
940940
DROP FUNCTION explain_merge(text);
941941

942+
-- EXPLAIN SubPlans and InitPlans
943+
CREATE TABLE src (a int, b int, c int, d int);
944+
CREATE TABLE tgt (a int, b int, c int, d int);
945+
CREATE TABLE ref (ab int, cd int);
946+
947+
EXPLAIN (verbose, costs off)
948+
MERGE INTO tgt t
949+
USING (SELECT *, (SELECT count(*) FROM ref r
950+
WHERE r.ab = s.a + s.b
951+
AND r.cd = s.c - s.d) cnt
952+
FROM src s) s
953+
ON t.a = s.a AND t.b < s.cnt
954+
WHEN MATCHED AND t.c > s.cnt THEN
955+
UPDATE SET (b, c) = (SELECT s.b, s.cnt);
956+
957+
DROP TABLE src, tgt, ref;
958+
942959
-- Subqueries
943960
BEGIN;
944961
MERGE INTO sq_target t

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.