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

Commit34ae54a

Browse files
committed
Fix wrong varnullingrels error for MERGE WHEN NOT MATCHED BY SOURCE.
If a MERGE command contains WHEN NOT MATCHED BY SOURCE actions, thesource relation appears on the outer side of the join. Thus, any Varsreferring to the source in the merge join condition, actions, andRETURNING list should be marked as nullable by the join, since theyare used in the ModifyTable node above the join. Note that this onlyapplies to the copy of join condition used in the executor todistinguish MATCHED from NOT MATCHED BY SOURCE cases. Vars in theoriginal join condition, inside the join node itself, should not bemarked.Failure to correctly mark these Vars led to a "wrong varnullingrels"error in the final stage of query planning, in some circumstances. Wehappened to get away without this in all previous tests, since theyall involved a ModifyTable node directly on top of the join node, sothat the top plan targetlist coincided with the output of the join,and the varnullingrels check was more lax. However, if another plannode, such as a one-time filter Result node, gets inserted between theModifyTable node and the join node, then a stricter check is applied,which fails.Per bug #18634 from Alexander Lakhin. Thanks to Tom Lane and RichardGuo for review and analysis.Back-patch to v17, where WHEN NOT MATCHED BY SOURCE support was addedto MERGE.Discussion:https://postgr.es/m/18634-db5299c937877f2b%40postgresql.org
1 parentd7d297f commit34ae54a

File tree

3 files changed

+89
-0
lines changed

3 files changed

+89
-0
lines changed

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -282,6 +282,45 @@ transform_MERGE_to_join(Query *parse)
282282
bms_make_singleton(parse->mergeTargetRelation),
283283
bms_make_singleton(joinrti));
284284

285+
/*
286+
* If the source relation is on the outer side of the join, mark any
287+
* source relation Vars in the join condition, actions, and RETURNING list
288+
* as nullable by the join. These Vars will be added to the targetlist by
289+
* preprocess_targetlist(), so it's important to mark them correctly here.
290+
*
291+
* It might seem that this is not necessary for Vars in the join
292+
* condition, since it is inside the join, but it is also needed above the
293+
* join (in the ModifyTable node) to distinguish between the MATCHED and
294+
* NOT MATCHED BY SOURCE cases -- see ExecMergeMatched(). Note that this
295+
* creates a modified copy of the join condition, for use above the join,
296+
* without modifying the the original join condition, inside the join.
297+
*/
298+
if (jointype==JOIN_LEFT||jointype==JOIN_FULL)
299+
{
300+
parse->mergeJoinCondition=
301+
add_nulling_relids(parse->mergeJoinCondition,
302+
bms_make_singleton(sourcerti),
303+
bms_make_singleton(joinrti));
304+
305+
foreach_node(MergeAction,action,parse->mergeActionList)
306+
{
307+
action->qual=
308+
add_nulling_relids(action->qual,
309+
bms_make_singleton(sourcerti),
310+
bms_make_singleton(joinrti));
311+
312+
action->targetList= (List*)
313+
add_nulling_relids((Node*)action->targetList,
314+
bms_make_singleton(sourcerti),
315+
bms_make_singleton(joinrti));
316+
}
317+
318+
parse->returningList= (List*)
319+
add_nulling_relids((Node*)parse->returningList,
320+
bms_make_singleton(sourcerti),
321+
bms_make_singleton(joinrti));
322+
}
323+
285324
/*
286325
* If there are any WHEN NOT MATCHED BY SOURCE actions, the executor will
287326
* use the join condition to distinguish between MATCHED and NOT MATCHED

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2711,6 +2711,35 @@ SELECT * FROM tgt;
27112711
(0 rows)
27122712

27132713
DROP TABLE src, tgt;
2714+
2715+
--
2716+
-- test for bug #18634 (wrong varnullingrels error)
2717+
--
2718+
CREATE TABLE bug18634t (a int, b int, c text);
2719+
INSERT INTO bug18634t VALUES(1, 10, 'tgt1'), (2, 20, 'tgt2');
2720+
CREATE VIEW bug18634v AS
2721+
SELECT * FROM bug18634t WHERE EXISTS (SELECT 1 FROM bug18634t);
2722+
CREATE TABLE bug18634s (a int, b int, c text);
2723+
INSERT INTO bug18634s VALUES (1, 2, 'src1');
2724+
MERGE INTO bug18634v t USING bug18634s s ON s.a = t.a
2725+
WHEN MATCHED THEN UPDATE SET b = s.b
2726+
WHEN NOT MATCHED BY SOURCE THEN DELETE
2727+
RETURNING merge_action(), s.c, t.*;
2728+
merge_action | c | a | b | c
2729+
--------------+------+---+----+------
2730+
UPDATE | src1 | 1 | 2 | tgt1
2731+
DELETE | | 2 | 20 | tgt2
2732+
(2 rows)
2733+
2734+
SELECT * FROM bug18634t;
2735+
a | b | c
2736+
---+---+------
2737+
1 | 2 | tgt1
2738+
(1 row)
2739+
2740+
DROP TABLE bug18634t CASCADE;
2741+
NOTICE: drop cascades to view bug18634v
2742+
DROP TABLE bug18634s;
27142743
-- prepare
27152744
RESET SESSION AUTHORIZATION;
27162745
-- try a system catalog

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1727,6 +1727,27 @@ MERGE INTO tgt USING src ON tgt.a IS NOT DISTINCT FROM src.a
17271727
SELECT*FROM tgt;
17281728

17291729
DROPTABLE src, tgt;
1730+
1731+
--
1732+
-- test for bug #18634 (wrong varnullingrels error)
1733+
--
1734+
CREATETABLEbug18634t (aint, bint, ctext);
1735+
INSERT INTO bug18634tVALUES(1,10,'tgt1'), (2,20,'tgt2');
1736+
CREATEVIEWbug18634vAS
1737+
SELECT*FROM bug18634tWHERE EXISTS (SELECT1FROM bug18634t);
1738+
1739+
CREATETABLEbug18634s (aint, bint, ctext);
1740+
INSERT INTO bug18634sVALUES (1,2,'src1');
1741+
1742+
MERGE INTO bug18634v t USING bug18634s sONs.a=t.a
1743+
WHEN MATCHED THENUPDATESET b=s.b
1744+
WHEN NOT MATCHED BY SOURCE THENDELETE
1745+
RETURNING merge_action(),s.c, t.*;
1746+
1747+
SELECT*FROM bug18634t;
1748+
1749+
DROPTABLE bug18634t CASCADE;
1750+
DROPTABLE bug18634s;
17301751

17311752
-- prepare
17321753

0 commit comments

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