Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit33e729c

Browse files
committed
Fix EXPLAIN output for subplans in MERGE.
Given a subplan in a MERGE query, EXPLAIN would sometimes fail toproperly display expressions involving Params referencing variables inother parts of the plan tree.This would affect subplans outside the topmost join plan node, forwhich expansion of Params would go via the top-level ModifyTable plannode. The problem was that "inner_tlist" for the ModifyTable node'sdeparse_namespace was set to the join node's targetlist, but"inner_plan" was set to the ModifyTable node itself, rather than thejoin node, leading to incorrect results when descending to thereferenced variable.Fix and backpatch to v15, where MERGE was introduced.Discussion:https://postgr.es/m/CAEZATCWAv-sZuH%2BwG5xJ-%2BGt7qGNGX8wUQd3XYydMFDKgRB9nw%40mail.gmail.com
1 parent20e5810 commit33e729c

File tree

3 files changed

+79
-9
lines changed

3 files changed

+79
-9
lines changed

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

Lines 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
elseif (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
elseif (dpns->inner_plan)
50195022
dpns->inner_tlist=dpns->inner_plan->targetlist;
50205023
else

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

Lines 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

Lines 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
DROPTABLE ex_msource, ex_mtarget;
940940
DROPFUNCTION explain_merge(text);
941941

942+
-- EXPLAIN SubPlans and InitPlans
943+
CREATETABLEsrc (aint, bint, cint, dint);
944+
CREATETABLEtgt (aint, bint, cint, dint);
945+
CREATETABLEref (abint, cdint);
946+
947+
EXPLAIN (verbose, costs off)
948+
MERGE INTO tgt t
949+
USING (SELECT*, (SELECTcount(*)FROM ref r
950+
WHEREr.ab=s.a+s.b
951+
ANDr.cd=s.c-s.d) cnt
952+
FROM src s) s
953+
ONt.a=s.aANDt.b<s.cnt
954+
WHEN MATCHEDANDt.c>s.cnt THEN
955+
UPDATESET (b, c)= (SELECTs.b,s.cnt);
956+
957+
DROPTABLE src, tgt, ref;
958+
942959
-- Subqueries
943960
BEGIN;
944961
MERGE INTO sq_target t

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp