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

Commit89ee14a

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 parent7c61d23 commit89ee14a

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
@@ -5013,8 +5013,11 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
50135013
* For a WorkTableScan, locate the parent RecursiveUnion plan node and use
50145014
* that as INNER referent.
50155015
*
5016-
* For MERGE, make the inner tlist point to the merge source tlist, which
5017-
* is same as the targetlist that the ModifyTable's source plan provides.
5016+
* For MERGE, pretend the ModifyTable's source plan (its outer plan) is
5017+
* INNER referent. This is the join from the target relation to the data
5018+
* source, and all INNER_VAR Vars in other parts of the query refer to its
5019+
* targetlist.
5020+
*
50185021
* For ON CONFLICT .. UPDATE we just need the inner tlist to point to the
50195022
* excluded expression's tlist. (Similar to the SubqueryScan we don't want
50205023
* to reuse OUTER, it's used for RETURNING in some modify table cases,
@@ -5029,17 +5032,17 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
50295032
dpns->inner_plan=find_recursive_union(dpns,
50305033
(WorkTableScan*)plan);
50315034
elseif (IsA(plan,ModifyTable))
5032-
dpns->inner_plan=plan;
5033-
else
5034-
dpns->inner_plan=innerPlan(plan);
5035-
5036-
if (IsA(plan,ModifyTable))
50375035
{
50385036
if (((ModifyTable*)plan)->operation==CMD_MERGE)
5039-
dpns->inner_tlist=dpns->outer_tlist;
5037+
dpns->inner_plan=outerPlan(plan);
50405038
else
5041-
dpns->inner_tlist=((ModifyTable*)plan)->exclRelTlist;
5039+
dpns->inner_plan=plan;
50425040
}
5041+
else
5042+
dpns->inner_plan=innerPlan(plan);
5043+
5044+
if (IsA(plan,ModifyTable)&& ((ModifyTable*)plan)->operation==CMD_INSERT)
5045+
dpns->inner_tlist= ((ModifyTable*)plan)->exclRelTlist;
50435046
elseif (dpns->inner_plan)
50445047
dpns->inner_tlist=dpns->inner_plan->targetlist;
50455048
else

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1483,6 +1483,56 @@ WHEN MATCHED AND t.a < 10 THEN
14831483

14841484
DROP TABLE ex_msource, ex_mtarget;
14851485
DROP FUNCTION explain_merge(text);
1486+
-- EXPLAIN SubPlans and InitPlans
1487+
CREATE TABLE src (a int, b int, c int, d int);
1488+
CREATE TABLE tgt (a int, b int, c int, d int);
1489+
CREATE TABLE ref (ab int, cd int);
1490+
EXPLAIN (verbose, costs off)
1491+
MERGE INTO tgt t
1492+
USING (SELECT *, (SELECT count(*) FROM ref r
1493+
WHERE r.ab = s.a + s.b
1494+
AND r.cd = s.c - s.d) cnt
1495+
FROM src s) s
1496+
ON t.a = s.a AND t.b < s.cnt
1497+
WHEN MATCHED AND t.c > s.cnt THEN
1498+
UPDATE SET (b, c) = (SELECT s.b, s.cnt);
1499+
QUERY PLAN
1500+
-------------------------------------------------------------------------------------
1501+
Merge on public.tgt t
1502+
-> Hash Join
1503+
Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid
1504+
Hash Cond: (t.a = s.a)
1505+
Join Filter: (t.b < (SubPlan 1))
1506+
-> Seq Scan on public.tgt t
1507+
Output: t.ctid, t.a, t.b
1508+
-> Hash
1509+
Output: s.a, s.b, s.c, s.d, s.ctid
1510+
-> Seq Scan on public.src s
1511+
Output: s.a, s.b, s.c, s.d, s.ctid
1512+
SubPlan 1
1513+
-> Aggregate
1514+
Output: count(*)
1515+
-> Seq Scan on public.ref r
1516+
Output: r.ab, r.cd
1517+
Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d)))
1518+
SubPlan 4
1519+
-> Aggregate
1520+
Output: count(*)
1521+
-> Seq Scan on public.ref r_2
1522+
Output: r_2.ab, r_2.cd
1523+
Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d)))
1524+
SubPlan 3 (returns $9,$10)
1525+
-> Result
1526+
Output: s.b, $8
1527+
InitPlan 2 (returns $8)
1528+
-> Aggregate
1529+
Output: count(*)
1530+
-> Seq Scan on public.ref r_1
1531+
Output: r_1.ab, r_1.cd
1532+
Filter: ((r_1.ab = (s.a + s.b)) AND (r_1.cd = (s.c - s.d)))
1533+
(32 rows)
1534+
1535+
DROP TABLE src, tgt, ref;
14861536
-- Subqueries
14871537
BEGIN;
14881538
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
@@ -948,6 +948,23 @@ WHEN MATCHED AND t.a < 10 THEN
948948
DROPTABLE ex_msource, ex_mtarget;
949949
DROPFUNCTION explain_merge(text);
950950

951+
-- EXPLAIN SubPlans and InitPlans
952+
CREATETABLEsrc (aint, bint, cint, dint);
953+
CREATETABLEtgt (aint, bint, cint, dint);
954+
CREATETABLEref (abint, cdint);
955+
956+
EXPLAIN (verbose, costs off)
957+
MERGE INTO tgt t
958+
USING (SELECT*, (SELECTcount(*)FROM ref r
959+
WHEREr.ab=s.a+s.b
960+
ANDr.cd=s.c-s.d) cnt
961+
FROM src s) s
962+
ONt.a=s.aANDt.b<s.cnt
963+
WHEN MATCHEDANDt.c>s.cnt THEN
964+
UPDATESET (b, c)= (SELECTs.b,s.cnt);
965+
966+
DROPTABLE src, tgt, ref;
967+
951968
-- Subqueries
952969
BEGIN;
953970
MERGE INTO sq_target t

0 commit comments

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