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

Commitdddb564

Browse files
committed
Fix incorrect non-strict join recheck in MERGE WHEN NOT MATCHED BY SOURCE.
If a MERGE command contains WHEN NOT MATCHED BY SOURCE actions, themerge join condition is used by the executor to distinguish MATCHEDfrom NOT MATCHED BY SOURCE cases. However, this qual is executed usingthe output from the join subplan node, which nulls the output from thesource relation in the not matched case, and so the result may beincorrect if the join condition is "non-strict" -- for example,something like "src.col IS NOT DISTINCT FROM tgt.col".Fix this by enhancing the join recheck condition with an additional"src IS NOT NULL" check, so that it does the right thing whenevaluated using the output from the join subplan.Noted by Tom Lane while investigating bug #18634 from AlexanderLakhin.Back-patch to v17, where WHEN NOT MATCHED BY SOURCE support was addedto MERGE.Discussion:https://postgr.es/m/18634-db5299c937877f2b%40postgresql.org
1 parent1953196 commitdddb564

File tree

3 files changed

+101
-5
lines changed

3 files changed

+101
-5
lines changed

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

Lines changed: 61 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,9 @@ transform_MERGE_to_join(Query *parse)
158158
intjoinrti;
159159
List*vars;
160160
RangeTblRef*rtr;
161+
FromExpr*target;
162+
Node*source;
163+
intsourcerti;
161164

162165
if (parse->commandType!=CMD_MERGE)
163166
return;
@@ -226,13 +229,36 @@ transform_MERGE_to_join(Query *parse)
226229
* parse->jointree->quals are restrictions on the target relation (if the
227230
* target relation is an auto-updatable view).
228231
*/
232+
/* target rel, with any quals */
229233
rtr=makeNode(RangeTblRef);
230234
rtr->rtindex=parse->mergeTargetRelation;
235+
target=makeFromExpr(list_make1(rtr),parse->jointree->quals);
236+
237+
/* source rel (expect exactly one -- see transformMergeStmt()) */
238+
Assert(list_length(parse->jointree->fromlist)==1);
239+
source=linitial(parse->jointree->fromlist);
240+
241+
/*
242+
* index of source rel (expect either a RangeTblRef or a JoinExpr -- see
243+
* transformFromClauseItem()).
244+
*/
245+
if (IsA(source,RangeTblRef))
246+
sourcerti= ((RangeTblRef*)source)->rtindex;
247+
elseif (IsA(source,JoinExpr))
248+
sourcerti= ((JoinExpr*)source)->rtindex;
249+
else
250+
{
251+
elog(ERROR,"unrecognized source node type: %d",
252+
(int)nodeTag(source));
253+
sourcerti=0;/* keep compiler quiet */
254+
}
255+
256+
/* Join the source and target */
231257
joinexpr=makeNode(JoinExpr);
232258
joinexpr->jointype=jointype;
233259
joinexpr->isNatural= false;
234-
joinexpr->larg= (Node*)makeFromExpr(list_make1(rtr),parse->jointree->quals);
235-
joinexpr->rarg=linitial(parse->jointree->fromlist);/*source rel */
260+
joinexpr->larg= (Node*)target;
261+
joinexpr->rarg=source;
236262
joinexpr->usingClause=NIL;
237263
joinexpr->join_using_alias=NULL;
238264
joinexpr->quals=parse->mergeJoinCondition;
@@ -261,9 +287,39 @@ transform_MERGE_to_join(Query *parse)
261287
* use the join condition to distinguish between MATCHED and NOT MATCHED
262288
* BY SOURCE cases. Otherwise, it's no longer needed, and we set it to
263289
* NULL, saving cycles during planning and execution.
264-
*/
265-
if (!have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE])
266-
parse->mergeJoinCondition=NULL;
290+
*
291+
* We need to be careful though: the executor evaluates this condition
292+
* using the output of the join subplan node, which nulls the output from
293+
* the source relation when the join condition doesn't match. That risks
294+
* producing incorrect results when rechecking using a "non-strict" join
295+
* condition, such as "src.col IS NOT DISTINCT FROM tgt.col". To guard
296+
* against that, we add an additional "src IS NOT NULL" check to the join
297+
* condition, so that it does the right thing when performing a recheck
298+
* based on the output of the join subplan.
299+
*/
300+
if (have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE])
301+
{
302+
Var*var;
303+
NullTest*ntest;
304+
305+
/* source wholerow Var (nullable by the new join) */
306+
var=makeWholeRowVar(rt_fetch(sourcerti,parse->rtable),
307+
sourcerti,0, false);
308+
var->varnullingrels=bms_make_singleton(joinrti);
309+
310+
/* "src IS NOT NULL" check */
311+
ntest=makeNode(NullTest);
312+
ntest->arg= (Expr*)var;
313+
ntest->nulltesttype=IS_NOT_NULL;
314+
ntest->argisrow= false;
315+
ntest->location=-1;
316+
317+
/* combine it with the original join condition */
318+
parse->mergeJoinCondition=
319+
(Node*)make_and_qual((Node*)ntest,parse->mergeJoinCondition);
320+
}
321+
else
322+
parse->mergeJoinCondition=NULL;/* join condition not needed */
267323
}
268324

269325
/*

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2689,6 +2689,28 @@ DETAIL: drop cascades to table measurement_y2006m02
26892689
drop cascades to table measurement_y2006m03
26902690
drop cascades to table measurement_y2007m01
26912691
DROP FUNCTION measurement_insert_trigger();
2692+
--
2693+
-- test non-strict join clause
2694+
--
2695+
CREATE TABLE src (a int, b text);
2696+
INSERT INTO src VALUES (1, 'src row');
2697+
CREATE TABLE tgt (a int, b text);
2698+
INSERT INTO tgt VALUES (NULL, 'tgt row');
2699+
MERGE INTO tgt USING src ON tgt.a IS NOT DISTINCT FROM src.a
2700+
WHEN MATCHED THEN UPDATE SET a = src.a, b = src.b
2701+
WHEN NOT MATCHED BY SOURCE THEN DELETE
2702+
RETURNING merge_action(), src.*, tgt.*;
2703+
merge_action | a | b | a | b
2704+
--------------+---+---+---+---------
2705+
DELETE | | | | tgt row
2706+
(1 row)
2707+
2708+
SELECT * FROM tgt;
2709+
a | b
2710+
---+---
2711+
(0 rows)
2712+
2713+
DROP TABLE src, tgt;
26922714
-- prepare
26932715
RESET SESSION AUTHORIZATION;
26942716
-- try a system catalog

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1710,6 +1710,24 @@ SELECT * FROM new_measurement ORDER BY city_id, logdate;
17101710
DROPTABLE measurement, new_measurement CASCADE;
17111711
DROPFUNCTION measurement_insert_trigger();
17121712

1713+
--
1714+
-- test non-strict join clause
1715+
--
1716+
CREATETABLEsrc (aint, btext);
1717+
INSERT INTO srcVALUES (1,'src row');
1718+
1719+
CREATETABLEtgt (aint, btext);
1720+
INSERT INTO tgtVALUES (NULL,'tgt row');
1721+
1722+
MERGE INTO tgt USING srcONtgt.a IS NOT DISTINCTFROMsrc.a
1723+
WHEN MATCHED THENUPDATESET a=src.a, b=src.b
1724+
WHEN NOT MATCHED BY SOURCE THENDELETE
1725+
RETURNING merge_action(), src.*, tgt.*;
1726+
1727+
SELECT*FROM tgt;
1728+
1729+
DROPTABLE src, tgt;
1730+
17131731
-- prepare
17141732

17151733
RESET SESSION AUTHORIZATION;

0 commit comments

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