On Sat, 21 Jan 2023 at 14:18, Ted Yu <yuzhih...@gmail.com> wrote:
>
> On Sat, Jan 21, 2023 at 3:05 AM Dean Rasheed <dean.a.rash...@gmail.com> wrote:
>>
>> Rebased version, following 8eba3e3f02 and 5d29d525ff.
>>
Another rebased version attached.
> In transform_MERGE_to_join :
>
> + if (action->matchKind ==
> MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
> + tgt_only_tuples = true;
> + if (action->matchKind ==
> MERGE_WHEN_NOT_MATCHED_BY_TARGET)
>
> There should be an `else` in front of the second `if`.
> When tgt_only_tuples and src_only_tuples are both true, we can come out of
> the loop.
>
I decided not to do that. Adding an "else" doesn't change the code
that the compiler generates, and IMO it's slightly more readable
without it, since it keeps the line length shorter, and the test
conditions aligned, but that's a matter of opinion / personal
preference.
I think adding extra logic to exit the loop early if both
tgt_only_tuples and src_only_tuples are true would be a premature
optimisation, increasing the code size for no real benefit. In
practice, there are unlikely to be more than a few merge actions in
the list.
Regards,
Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index b87ad5c..1482ede
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -396,8 +396,8 @@
originally matched appears later in the list of actions.
On the other hand, if the row is concurrently updated or deleted so
that the join condition fails, then <command>MERGE</command> will
- evaluate the condition's <literal>NOT MATCHED</literal> actions next,
- and execute the first one that succeeds.
+ evaluate the condition's <literal>NOT MATCHED [BY TARGET]</literal>
+ actions next, and execute the first one that succeeds.
If <command>MERGE</command> attempts an <command>INSERT</command>
and a unique index is present and a duplicate row is concurrently
inserted, then a uniqueness violation error is raised;
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0995fe0..8ef121a
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -33,7 +33,8 @@ USING <replaceable class="parameter">dat
<phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>
{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
- WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }
+ WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
+ WHEN NOT MATCHED [BY TARGET] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }
<phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase>
@@ -70,7 +71,9 @@ DELETE
from <replaceable class="parameter">data_source</replaceable> to
<replaceable class="parameter">target_table_name</replaceable>
producing zero or more candidate change rows. For each candidate change
- row, the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal>
+ row, the status of <literal>MATCHED</literal>,
+ <literal>NOT MATCHED BY SOURCE</literal>,
+ or <literal>NOT MATCHED [BY TARGET]</literal>
is set just once, after which <literal>WHEN</literal> clauses are evaluated
in the order specified. For each candidate change row, the first clause to
evaluate as true is executed. No more than one <literal>WHEN</literal>
@@ -226,16 +229,37 @@ DELETE
At least one <literal>WHEN</literal> clause is required.
</para>
<para>
+ The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>,
+ <literal>WHEN NOT MATCHED BY SOURCE</literal>, or
+ <literal>WHEN NOT MATCHED [BY TARGET]</literal>.
+ Note that the <acronym>SQL</acronym> standard only defines
+ <literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal>
+ (which is defined to mean no matching target row).
+ <literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the
+ <acronym>SQL</acronym> standard, as is the option to append
+ <literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to
+ make its meaning more explicit.
+ </para>
+ <para>
If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>
- and the candidate change row matches a row in the
+ and the candidate change row matches a row in the source to a row in the
<replaceable class="parameter">target_table_name</replaceable>,
the <literal>WHEN</literal> clause is executed if the
<replaceable class="parameter">condition</replaceable> is
absent or it evaluates to <literal>true</literal>.
</para>
<para>
- Conversely, if the <literal>WHEN</literal> clause specifies
- <literal>WHEN NOT MATCHED</literal>
+ If the <literal>WHEN</literal> clause specifies
+ <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change
+ row represents a row in the
+ <replaceable class="parameter">target_table_name</replaceable> that does
+ not match a source row, the <literal>WHEN</literal> clause is executed
+ if the <replaceable class="parameter">condition</replaceable> is
+ absent or it evaluates to <literal>true</literal>.
+ </para>
+ <para>
+ If the <literal>WHEN</literal> clause specifies
+ <literal>WHEN NOT MATCHED [BY TARGET]</literal>
and the candidate change row does not match a row in the
<replaceable class="parameter">target_table_name</replaceable>,
the <literal>WHEN</literal> clause is executed if the
@@ -257,7 +281,10 @@ DELETE
<para>
A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns
in both the source and the target relations. A condition on a
- <literal>WHEN NOT MATCHED</literal> clause can only refer to columns from
+ <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to
+ columns from the target relation, since by definition there is no matching
+ source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal>
+ clause can only refer to columns from
the source relation, since by definition there is no matching target row.
Only the system attributes from the target table are accessible.
</para>
@@ -382,8 +409,10 @@ DELETE
<literal>WHEN MATCHED</literal> clause, the expression can use values
from the original row in the target table, and values from the
<literal>data_source</literal> row.
- If used in a <literal>WHEN NOT MATCHED</literal> clause, the
- expression can use values from the <literal>data_source</literal>.
+ If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the
+ expression can only use values from the original row in the target table.
+ If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the
+ expression can only use values from the <literal>data_source</literal>.
</para>
</listitem>
</varlistentry>
@@ -452,8 +481,9 @@ MERGE <replaceable class="parameter">tot
<orderedlist>
<listitem>
<para>
- Evaluate whether each row is <literal>MATCHED</literal> or
- <literal>NOT MATCHED</literal>.
+ Evaluate whether each row is <literal>MATCHED</literal>,
+ <literal>NOT MATCHED BY SOURCE</literal>, or
+ <literal>NOT MATCHED [BY TARGET]</literal>.
</para>
</listitem>
<listitem>
@@ -528,7 +558,8 @@ MERGE <replaceable class="parameter">tot
<para>
If a <literal>WHEN</literal> clause omits an <literal>AND</literal>
sub-clause, it becomes the final reachable clause of that
- kind (<literal>MATCHED</literal> or <literal>NOT MATCHED</literal>).
+ kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>,
+ or <literal>NOT MATCHED [BY TARGET]</literal>).
If a later <literal>WHEN</literal> clause of that kind
is specified it would be provably unreachable and an error is raised.
If no final reachable clause is specified of either kind, it is
@@ -619,6 +650,23 @@ WHEN MATCHED THEN
temporary table recently loaded into the database.
</para>
+ <para>
+ Update <literal>wine</literal> based on a replacement wine list, inserting
+ rows for any new stock, updating modified stock entries, and deleting any
+ wines not present in the new list.
+<programlisting>
+MERGE INTO wines w
+USING new_wine_list s
+ON s.winename = w.winename
+WHEN NOT MATCHED BY TARGET THEN
+ INSERT VALUES(s.winename, s.stock)
+WHEN MATCHED AND w.stock != s.stock THEN
+ UPDATE SET stock = s.stock
+WHEN NOT MATCHED BY SOURCE THEN
+ DELETE;
+</programlisting>
+ </para>
+
</refsect1>
<refsect1>
@@ -627,7 +675,10 @@ WHEN MATCHED THEN
This command conforms to the <acronym>SQL</acronym> standard.
</para>
<para>
- The WITH clause and <literal>DO NOTHING</literal> action are extensions to
+ The WITH clause, <literal>BY SOURCE</literal> and
+ <literal>BY TARGET</literal> qualifiers to
+ <literal>WHEN NOT MATCHED</literal>, and
+ <literal>DO NOTHING</literal> action are extensions to
the <acronym>SQL</acronym> standard.
</para>
</refsect1>
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index 651ad24..005da8d
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -898,8 +898,16 @@ ExecInitPartitionInfo(ModifyTableState *
action_state = makeNode(MergeActionState);
action_state->mas_action = action;
- /* And put the action in the appropriate list */
- if (action->matched)
+ /*
+ * Put the action in the appropriate list.
+ *
+ * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+ * actions in exactly the same way as WHEN MATCHED actions, since
+ * they both match the target (see ExecMerge). Thus both types go
+ * in the "matched" list. Only WHEN NOT MATCHED BY TARGET actions
+ * go in the "not matched" list.
+ */
+ if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
list = &leaf_part_rri->ri_matchedMergeAction;
else
list = &leaf_part_rri->ri_notMatchedMergeAction;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 1ac6517..5384406
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -24,13 +24,15 @@
* values plus row-locating info for UPDATE and MERGE cases, or just the
* row-locating info for DELETE cases.
*
- * MERGE runs a join between the source relation and the target
- * table; if any WHEN NOT MATCHED clauses are present, then the
- * join is an outer join. In this case, any unmatched tuples will
- * have NULL row-locating info, and only INSERT can be run. But for
- * matched tuples, then row-locating info is used to determine the
- * tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED,
- * then an inner join is used, so all tuples contain row-locating info.
+ * MERGE runs a join between the source relation and the target table.
+ * If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join
+ * is an outer join that might output tuples without a matching target
+ * tuple. In this case, any unmatched target tuples will have NULL
+ * row-locating info, and only INSERT can be run. But for matched
+ * target tuples, the row-locating info is used to determine the tuple
+ * to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT
+ * MATCHED BY SOURCE, all tuples produced by the join will include a
+ * matching target tuple, so all tuples contain row-locating info.
*
* If the query specifies RETURNING, then the ModifyTable returns a
* RETURNING tuple after completing each row insert, update, or delete.
@@ -2695,6 +2697,20 @@ ExecMerge(ModifyTableContext *context, R
bool matched;
/*-----
+ * Note that as far as the executor is concerned, WHEN NOT MATCHED BY
+ * SOURCE actions are treated exactly the same as WHEN MATCHED actions,
+ * since both match target tuples. They are distinguished from one
+ * another by a qual that tests if the source tuple is NULL, but the
+ * executor knows nothing about the contents of the merge action quals.
+ * Thus WHEN MATCHED and WHEN NOT MATCHED BY SOURCE actions are stored
+ * together in the same "matched" list. In the case of concurrent updates
+ * and rechecks discussed below, the target tuple might be modified, but
+ * not the source tuple, and so there is no danger of a WHEN MATCHED case
+ * becoming a WHEN NOT MATCHED BY SOURCE case, or vice versa. Thus, in
+ * the dicussion that follows "MATCHED" means "matched by target", and
+ * should be taken to include both WHEN MATCHED and WHEN NOT MATCHED BY
+ * SOURCE, while "NOT MATCHED" means NOT MATCHED BY TARGET.
+ *
* If we are dealing with a WHEN MATCHED case (tupleid is valid), we
* execute the first action for which the additional WHEN MATCHED AND
* quals pass. If an action without quals is found, that action is
@@ -3257,8 +3273,14 @@ ExecInitMerge(ModifyTableState *mtstate,
* We create two lists - one for WHEN MATCHED actions and one for
* WHEN NOT MATCHED actions - and stick the MergeActionState into
* the appropriate list.
+ *
+ * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+ * actions in exactly the same way as WHEN MATCHED actions, since
+ * they both match the target (see ExecMerge). Thus both types go
+ * in the "matched" list. Only WHEN NOT MATCHED BY TARGET actions
+ * go in the "not matched" list.
*/
- if (action_state->mas_action->matched)
+ if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
list = &resultRelInfo->ri_matchedMergeAction;
else
list = &resultRelInfo->ri_notMatchedMergeAction;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 870d84b..3b32c8d
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -153,6 +153,9 @@ transform_MERGE_to_join(Query *parse)
{
RangeTblEntry *joinrte;
JoinExpr *joinexpr;
+ ListCell *lc;
+ bool src_only_tuples;
+ bool tgt_only_tuples;
JoinType jointype;
int joinrti;
List *vars;
@@ -164,12 +167,32 @@ transform_MERGE_to_join(Query *parse)
vars = NIL;
/*
- * When any WHEN NOT MATCHED THEN INSERT clauses exist, we need to use an
- * outer join so that we process all unmatched tuples from the source
- * relation. If none exist, we can use an inner join.
+ * Work out what kind of join is required. If there any WHEN NOT MATCHED
+ * BY SOURCE/TARGET clauses, an outer join is required so that we process
+ * all unmatched tuples from the source and/or target relations.
+ * Otherwise, we can use an inner join.
*/
- if (parse->mergeUseOuterJoin)
+ src_only_tuples = false;
+ tgt_only_tuples = false;
+ foreach(lc, parse->mergeActionList)
+ {
+ MergeAction *action = lfirst_node(MergeAction, lc);
+
+ if (action->commandType != CMD_NOTHING)
+ {
+ if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+ tgt_only_tuples = true;
+ if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET)
+ src_only_tuples = true;
+ }
+ }
+
+ if (src_only_tuples && tgt_only_tuples)
+ jointype = JOIN_FULL;
+ else if (src_only_tuples)
jointype = JOIN_RIGHT;
+ else if (tgt_only_tuples)
+ jointype = JOIN_LEFT;
else
jointype = JOIN_INNER;
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
new file mode 100644
index c6d747b..027d2be
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -157,15 +157,14 @@ preprocess_targetlist(PlannerInfo *root)
/*
* Add resjunk entries for any Vars used in each action's
* targetlist and WHEN condition that belong to relations other
- * than target. Note that aggregates, window functions and
- * placeholder vars are not possible anywhere in MERGE's WHEN
- * clauses. (PHVs may be added later, but they don't concern us
- * here.)
+ * than target. Note that aggregates and window functions are not
+ * possible anywhere in MERGE's WHEN clauses, but PlaceHolderVars
+ * may have been added by subquery pullup.
*/
vars = pull_var_clause((Node *)
list_concat_copy((List *) action->qual,
action->targetList),
- 0);
+ PVC_INCLUDE_PLACEHOLDERS);
foreach(l2, vars)
{
Var *var = (Var *) lfirst(l2);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index a013838..dbe0089
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -273,6 +273,7 @@ static Node *makeRecursiveViewSelect(cha
struct SelectLimit *selectlimit;
SetQuantifier setquantifier;
struct GroupClause *groupclause;
+ MergeMatchKind mergematch;
MergeWhenClause *mergewhen;
struct KeyActions *keyactions;
struct KeyAction *keyaction;
@@ -512,6 +513,7 @@ static Node *makeRecursiveViewSelect(cha
%type <onconflict> opt_on_conflict
%type <mergewhen> merge_insert merge_update merge_delete
+%type <mergematch> merge_when_tgt_matched merge_when_tgt_not_matched
%type <node> merge_when_clause opt_merge_when_condition
%type <list> merge_when_list
@@ -740,11 +742,11 @@ static Node *makeRecursiveViewSelect(cha
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
- SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
+ SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SOURCE SQL_P STABLE STANDALONE_P
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
- TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
+ TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
@@ -12260,49 +12262,59 @@ merge_when_list:
;
merge_when_clause:
- WHEN MATCHED opt_merge_when_condition THEN merge_update
+ merge_when_tgt_matched opt_merge_when_condition THEN merge_update
{
- $5->matched = true;
- $5->condition = $3;
+ $4->matchKind = $1;
+ $4->condition = $2;
- $$ = (Node *) $5;
+ $$ = (Node *) $4;
}
- | WHEN MATCHED opt_merge_when_condition THEN merge_delete
+ | merge_when_tgt_matched opt_merge_when_condition THEN merge_delete
{
- $5->matched = true;
- $5->condition = $3;
+ $4->matchKind = $1;
+ $4->condition = $2;
- $$ = (Node *) $5;
+ $$ = (Node *) $4;
}
- | WHEN NOT MATCHED opt_merge_when_condition THEN merge_insert
+ | merge_when_tgt_not_matched opt_merge_when_condition THEN merge_insert
{
- $6->matched = false;
- $6->condition = $4;
+ $4->matchKind = $1;
+ $4->condition = $2;
- $$ = (Node *) $6;
+ $$ = (Node *) $4;
}
- | WHEN MATCHED opt_merge_when_condition THEN DO NOTHING
+ | merge_when_tgt_matched opt_merge_when_condition THEN DO NOTHING
{
MergeWhenClause *m = makeNode(MergeWhenClause);
- m->matched = true;
+ m->matchKind = $1;
m->commandType = CMD_NOTHING;
- m->condition = $3;
+ m->condition = $2;
$$ = (Node *) m;
}
- | WHEN NOT MATCHED opt_merge_when_condition THEN DO NOTHING
+ | merge_when_tgt_not_matched opt_merge_when_condition THEN DO NOTHING
{
MergeWhenClause *m = makeNode(MergeWhenClause);
- m->matched = false;
+ m->matchKind = $1;
m->commandType = CMD_NOTHING;
- m->condition = $4;
+ m->condition = $2;
$$ = (Node *) m;
}
;
+merge_when_tgt_matched:
+ WHEN MATCHED { $$ = MERGE_WHEN_MATCHED; }
+ | WHEN NOT MATCHED BY SOURCE { $$ = MERGE_WHEN_NOT_MATCHED_BY_SOURCE; }
+ ;
+
+merge_when_tgt_not_matched:
+ WHEN NOT MATCHED { $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; }
+ | WHEN NOT MATCHED BY TARGET { $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; }
+ ;
+
opt_merge_when_condition:
AND a_expr { $$ = $2; }
| { $$ = NULL; }
@@ -16961,6 +16973,7 @@ unreserved_keyword:
| SIMPLE
| SKIP
| SNAPSHOT
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -16979,6 +16992,7 @@ unreserved_keyword:
| SYSTEM_P
| TABLES
| TABLESPACE
+ | TARGET
| TEMP
| TEMPLATE
| TEMPORARY
@@ -17555,6 +17569,7 @@ bare_label_keyword:
| SMALLINT
| SNAPSHOT
| SOME
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -17578,6 +17593,7 @@ bare_label_keyword:
| TABLES
| TABLESAMPLE
| TABLESPACE
+ | TARGET
| TEMP
| TEMPLATE
| TEMPORARY
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
new file mode 100644
index d886637..fca2bd9
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -43,9 +43,9 @@ static void setNamespaceVisibilityForRTE
* Make appropriate changes to the namespace visibility while transforming
* individual action's quals and targetlist expressions. In particular, for
* INSERT actions we must only see the source relation (since INSERT action is
- * invoked for NOT MATCHED tuples and hence there is no target tuple to deal
- * with). On the other hand, UPDATE and DELETE actions can see both source and
- * target relations.
+ * invoked for NOT MATCHED [BY TARGET] tuples and hence there is no target
+ * tuple to deal with). On the other hand, UPDATE and DELETE actions can see
+ * both source and target relations, unless invoked for NOT MATCH BY SOURCE.
*
* Also, since the internal join node can hide the source and target
* relations, we must explicitly make the respective relation as visible so
@@ -61,7 +61,7 @@ setNamespaceForMergeWhen(ParseState *pst
targetRelRTE = rt_fetch(targetRTI, pstate->p_rtable);
sourceRelRTE = rt_fetch(sourceRTI, pstate->p_rtable);
- if (mergeWhenClause->matched)
+ if (mergeWhenClause->matchKind == MERGE_WHEN_MATCHED)
{
Assert(mergeWhenClause->commandType == CMD_UPDATE ||
mergeWhenClause->commandType == CMD_DELETE ||
@@ -73,11 +73,25 @@ setNamespaceForMergeWhen(ParseState *pst
setNamespaceVisibilityForRTE(pstate->p_namespace,
sourceRelRTE, true, true);
}
- else
+ else if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
{
/*
- * NOT MATCHED actions can't see target relation, but they can see
- * source relation.
+ * NOT MATCHED BY SOURCE actions can see the target relation, but they
+ * can't see the source relation.
+ */
+ Assert(mergeWhenClause->commandType == CMD_UPDATE ||
+ mergeWhenClause->commandType == CMD_DELETE ||
+ mergeWhenClause->commandType == CMD_NOTHING);
+ setNamespaceVisibilityForRTE(pstate->p_namespace,
+ targetRelRTE, true, true);
+ setNamespaceVisibilityForRTE(pstate->p_namespace,
+ sourceRelRTE, false, false);
+ }
+ else /* mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET */
+ {
+ /*
+ * NOT MATCHED [BY TARGET] actions can't see target relation, but they
+ * can see source relation.
*/
Assert(mergeWhenClause->commandType == CMD_INSERT ||
mergeWhenClause->commandType == CMD_NOTHING);
@@ -98,7 +112,8 @@ transformMergeStmt(ParseState *pstate, M
Query *qry = makeNode(Query);
ListCell *l;
AclMode targetPerms = ACL_NO_RIGHTS;
- bool is_terminal[2];
+ bool is_terminal[3];
+ bool tgt_only_tuples;
Index sourceRTI;
List *mergeActionList;
Node *joinExpr;
@@ -125,12 +140,13 @@ transformMergeStmt(ParseState *pstate, M
/*
* Check WHEN clauses for permissions and sanity
*/
- is_terminal[0] = false;
- is_terminal[1] = false;
+ is_terminal[MERGE_WHEN_MATCHED] = false;
+ is_terminal[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = false;
+ is_terminal[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = false;
+ tgt_only_tuples = false;
foreach(l, stmt->mergeWhenClauses)
{
MergeWhenClause *mergeWhenClause = (MergeWhenClause *) lfirst(l);
- int when_type = (mergeWhenClause->matched ? 0 : 1);
/*
* Collect action types so we can check target permissions
@@ -155,12 +171,22 @@ transformMergeStmt(ParseState *pstate, M
/*
* Check for unreachable WHEN clauses
*/
- if (is_terminal[when_type])
+ if (is_terminal[mergeWhenClause->matchKind])
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("unreachable WHEN clause specified after unconditional WHEN clause")));
if (mergeWhenClause->condition == NULL)
- is_terminal[when_type] = true;
+ is_terminal[mergeWhenClause->matchKind] = true;
+
+ /*
+ * Test if we have a WHEN NOT MATCHED BY SOURCE action that processes
+ * unmatched tuples from the target relation. If so, additional WHEN
+ * conditions are added below to differentiate the various match
+ * kinds.
+ */
+ if (mergeWhenClause->commandType != CMD_NOTHING &&
+ mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+ tgt_only_tuples = true;
}
/*
@@ -255,11 +281,7 @@ transformMergeStmt(ParseState *pstate, M
action = makeNode(MergeAction);
action->commandType = mergeWhenClause->commandType;
- action->matched = mergeWhenClause->matched;
-
- /* Use an outer join if any INSERT actions exist in the command. */
- if (action->commandType == CMD_INSERT)
- qry->mergeUseOuterJoin = true;
+ action->matchKind = mergeWhenClause->matchKind;
/*
* Set namespace for the specific action. This must be done before
@@ -280,6 +302,51 @@ transformMergeStmt(ParseState *pstate, M
EXPR_KIND_MERGE_WHEN, "WHEN");
/*
+ * If there any WHEN NOT MATCHED BY SOURCE actions that require
+ * unmatched tuples from the target relation to be processed, add
+ * additional WHEN conditions to every action to check that tuples
+ * from the source match or do not match, as necessary.
+ *
+ * This distinguishes NOT MATCHED BY SOURCE actions (identified by a
+ * "source IS NOT DISTINCT FROM NULL" clause) from MATCHED actions
+ * (identified by a "source IS DISTINCT FROM NULL" clause).
+ *
+ * Additionally, a "source IS DISTINCT FROM NULL" clause is required
+ * for NOT MATCHED [BY TARGET] actions in case the executor attempts
+ * to invoke such an action for a concurrently deleted target row that
+ * ends up matching neither source nor target.
+ */
+ if (tgt_only_tuples)
+ {
+ bool src_null;
+ NullTest *ntest;
+
+ /* Make a "source IS [NOT] DISTINCT FROM NULL" clause */
+ src_null = action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE;
+
+ ntest = makeNode(NullTest);
+
+ ntest->arg = (Expr *) makeVar(sourceRTI,
+ InvalidAttrNumber,
+ RECORDOID,
+ -1,
+ InvalidOid,
+ 0);
+ ntest->nulltesttype = src_null ? IS_NULL : IS_NOT_NULL;
+ ntest->argisrow = false;
+ ntest->location = -1;
+
+ /* Combine it with the action's WHEN condition */
+ if (action->qual == NULL)
+ action->qual = (Node *) ntest;
+ else
+ action->qual =
+ (Node *) makeBoolExpr(AND_EXPR,
+ list_make2(ntest, action->qual),
+ -1);
+ }
+
+ /*
* Transform target lists for each INSERT and UPDATE action stmt
*/
switch (action->commandType)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
new file mode 100644
index 5e1882e..e202fcd
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4237,17 +4237,35 @@ psql_completion(const char *text, int st
TailMatches("USING", MatchAny, MatchAny, "ON", MatchAny, MatchAny, MatchAny, "WHEN"))
COMPLETE_WITH("MATCHED", "NOT MATCHED");
- /* Complete ... WHEN [NOT] MATCHED with THEN/AND */
+ /*
+ * Complete ... WHEN MATCHED and WHEN NOT MATCHED BY SOURCE|TARGET with
+ * THEN/AND
+ */
else if (TailMatches("WHEN", "MATCHED") ||
- TailMatches("WHEN", "NOT", "MATCHED"))
+ TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE|TARGET"))
COMPLETE_WITH("THEN", "AND");
- /* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO NOTHING */
- else if (TailMatches("WHEN", "MATCHED", "THEN"))
+ /* Complete ... WHEN NOT MATCHED with BY/THEN/AND */
+ else if (TailMatches("WHEN", "NOT", "MATCHED"))
+ COMPLETE_WITH("BY", "THEN", "AND");
+
+ /* Complete ... WHEN NOT MATCHED BY with SOURCE/TARGET */
+ else if (TailMatches("WHEN", "NOT", "MATCHED", "BY"))
+ COMPLETE_WITH("SOURCE", "TARGET");
+
+ /*
+ * Complete ... WHEN MATCHED THEN and WHEN NOT MATCHED BY SOURCE THEN with
+ * UPDATE SET/DELETE/DO NOTHING
+ */
+ else if (TailMatches("WHEN", "MATCHED", "THEN") ||
+ TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE", "THEN"))
COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING");
- /* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */
- else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN"))
+ /*
+ * Complete ... WHEN NOT MATCHED [BY TARGET] THEN with INSERT/DO NOTHING
+ */
+ else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN") ||
+ TailMatches("WHEN", "NOT", "MATCHED", "BY", "TARGET", "THEN"))
COMPLETE_WITH("INSERT", "DO NOTHING");
/* NOTIFY --- can be inside EXPLAIN, RULE, etc */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 855da99..d9d3a18
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -184,8 +184,6 @@ typedef struct Query
* also USING clause for MERGE */
List *mergeActionList; /* list of actions for MERGE (only) */
- /* whether to use outer join */
- bool mergeUseOuterJoin pg_node_attr(query_jumble_ignore);
List *targetList; /* target list (of TargetEntry) */
@@ -1662,6 +1660,14 @@ typedef struct CommonTableExpr
((Query *) (cte)->ctequery)->targetList : \
((Query *) (cte)->ctequery)->returningList)
+/* MATCHED/NOT MATCHED specification for WHEN clauses in a MERGE statement */
+typedef enum MergeMatchKind
+{
+ MERGE_WHEN_MATCHED,
+ MERGE_WHEN_NOT_MATCHED_BY_SOURCE,
+ MERGE_WHEN_NOT_MATCHED_BY_TARGET
+} MergeMatchKind;
+
/*
* MergeWhenClause -
* raw parser representation of a WHEN clause in a MERGE statement
@@ -1671,7 +1677,7 @@ typedef struct CommonTableExpr
typedef struct MergeWhenClause
{
NodeTag type;
- bool matched; /* true=MATCHED, false=NOT MATCHED */
+ MergeMatchKind matchKind; /* MATCHED/NOT MATCHED BY SOURCE/TARGET */
CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */
OverridingKind override; /* OVERRIDING clause */
Node *condition; /* WHEN conditions (raw parser) */
@@ -1687,7 +1693,7 @@ typedef struct MergeWhenClause
typedef struct MergeAction
{
NodeTag type;
- bool matched; /* true=MATCHED, false=NOT MATCHED */
+ MergeMatchKind matchKind; /* MATCHED/NOT MATCHED BY SOURCE/TARGET */
CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */
/* OVERRIDING clause */
OverridingKind override pg_node_attr(query_jumble_ignore);
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
new file mode 100644
index bb36213..a398153
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -391,6 +391,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
PG_KEYWORD("smallint", SMALLINT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("snapshot", SNAPSHOT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("some", SOME, RESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("source", SOURCE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sql", SQL_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("stable", STABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("standalone", STANDALONE_P, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -414,6 +415,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW
PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("target", TARGET, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index bc53b21..00cdbe9
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -71,6 +71,15 @@ WHEN MATCHED THEN
ERROR: syntax error at or near "INSERT"
LINE 5: INSERT DEFAULT VALUES;
^
+-- NOT MATCHED BY SOURCE/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+ INSERT DEFAULT VALUES;
+ERROR: syntax error at or near "INSERT"
+LINE 5: INSERT DEFAULT VALUES;
+ ^
-- incorrectly specifying INTO target
MERGE INTO target t
USING source AS s
@@ -107,6 +116,15 @@ WHEN NOT MATCHED THEN
ERROR: syntax error at or near "UPDATE"
LINE 5: UPDATE SET balance = 0;
^
+-- NOT MATCHED BY TARGET/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY TARGET THEN
+ UPDATE SET balance = 0;
+ERROR: syntax error at or near "UPDATE"
+LINE 5: UPDATE SET balance = 0;
+ ^
-- UPDATE tablename
MERGE INTO target t
USING source AS s
@@ -271,6 +289,22 @@ SELECT * FROM target ORDER BY tid;
(4 rows)
ROLLBACK;
+-- DELETE/INSERT not matched by source/target
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+ DELETE
+WHEN NOT MATCHED BY TARGET THEN
+ INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 4 | 40
+(1 row)
+
+ROLLBACK;
-- index plans
INSERT INTO target SELECT generate_series(1000,2500), 0;
ALTER TABLE target ADD PRIMARY KEY (tid);
@@ -738,6 +772,19 @@ SELECT * FROM wq_target;
1 | -1
(1 row)
+-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+ DELETE;
+ERROR: invalid reference to FROM-clause entry for table "s"
+LINE 3: WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+ ^
+DETAIL: There is an entry for table "s", but it cannot be referenced from this part of the query.
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN
+ DELETE;
-- conditions in MATCHED clause can refer to both source and target
SELECT * FROM wq_source;
balance | sid
@@ -923,6 +970,45 @@ SELECT * FROM target ORDER BY tid;
(3 rows)
ROLLBACK;
+-- UPSERT with UPDATE/DELETE when not matched by source
+BEGIN;
+DELETE FROM SOURCE WHERE sid = 2;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND t.balance > s.delta THEN
+ UPDATE SET balance = t.balance - s.delta
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED BY SOURCE THEN
+ DELETE;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE UPDATE STATEMENT trigger
+NOTICE: BEFORE DELETE STATEMENT trigger
+NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE: BEFORE INSERT ROW trigger row: (4,40)
+NOTICE: BEFORE DELETE ROW trigger row: (2,20)
+NOTICE: BEFORE UPDATE ROW trigger row: (1,10) -> (1,0)
+NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE: AFTER INSERT ROW trigger row: (4,40)
+NOTICE: AFTER DELETE ROW trigger row: (2,20)
+NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,0)
+NOTICE: AFTER DELETE STATEMENT trigger
+NOTICE: AFTER UPDATE STATEMENT trigger
+NOTICE: AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance
+-----+---------
+ 1 | 0
+ 3 | 10
+ 4 | 40
+(3 rows)
+
+ROLLBACK;
-- Test behavior of triggers that turn UPDATE/DELETE into no-ops
create or replace function skip_merge_op() returns trigger
language plpgsql as
@@ -1436,6 +1522,50 @@ WHEN NOT MATCHED AND s.a < 20 THEN
-> Seq Scan on ex_mtarget t (actual rows=49 loops=1)
(12 rows)
+-- not matched by source
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN
+ DELETE');
+ explain_merge
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+ Tuples: skipped=54
+ -> Merge Left Join (actual rows=54 loops=1)
+ Merge Cond: (t.a = s.a)
+ -> Sort (actual rows=54 loops=1)
+ Sort Key: t.a
+ Sort Method: quicksort Memory: xxx
+ -> Seq Scan on ex_mtarget t (actual rows=54 loops=1)
+ -> Sort (actual rows=100 loops=1)
+ Sort Key: s.a
+ Sort Method: quicksort Memory: xxx
+ -> Seq Scan on ex_msource s (actual rows=100 loops=1)
+(12 rows)
+
+-- not matched by source and target
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN
+ DELETE
+WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN
+ INSERT VALUES (a, b)');
+ explain_merge
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+ Tuples: skipped=100
+ -> Merge Full Join (actual rows=100 loops=1)
+ Merge Cond: (t.a = s.a)
+ -> Sort (actual rows=54 loops=1)
+ Sort Key: t.a
+ Sort Method: quicksort Memory: xxx
+ -> Seq Scan on ex_mtarget t (actual rows=54 loops=1)
+ -> Sort (actual rows=100 loops=1)
+ Sort Key: s.a
+ Sort Method: quicksort Memory: xxx
+ -> Seq Scan on ex_msource s (actual rows=100 loops=1)
+(12 rows)
+
-- nothing
SELECT explain_merge('
MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index fdbcd70..f5dfb2a
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -51,6 +51,12 @@ USING source AS s
ON t.tid = s.sid
WHEN MATCHED THEN
INSERT DEFAULT VALUES;
+-- NOT MATCHED BY SOURCE/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+ INSERT DEFAULT VALUES;
-- incorrectly specifying INTO target
MERGE INTO target t
USING source AS s
@@ -75,6 +81,12 @@ USING source AS s
ON t.tid = s.sid
WHEN NOT MATCHED THEN
UPDATE SET balance = 0;
+-- NOT MATCHED BY TARGET/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY TARGET THEN
+ UPDATE SET balance = 0;
-- UPDATE tablename
MERGE INTO target t
USING source AS s
@@ -213,6 +225,18 @@ WHEN NOT MATCHED THEN
SELECT * FROM target ORDER BY tid;
ROLLBACK;
+-- DELETE/INSERT not matched by source/target
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+ DELETE
+WHEN NOT MATCHED BY TARGET THEN
+ INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
-- index plans
INSERT INTO target SELECT generate_series(1000,2500), 0;
ALTER TABLE target ADD PRIMARY KEY (tid);
@@ -497,6 +521,17 @@ WHEN NOT MATCHED AND s.balance = 100 THE
INSERT (tid) VALUES (s.sid);
SELECT * FROM wq_target;
+-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN
+ DELETE;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN
+ DELETE;
+
-- conditions in MATCHED clause can refer to both source and target
SELECT * FROM wq_source;
MERGE INTO wq_target t
@@ -624,6 +659,25 @@ WHEN NOT MATCHED THEN
SELECT * FROM target ORDER BY tid;
ROLLBACK;
+-- UPSERT with UPDATE/DELETE when not matched by source
+BEGIN;
+DELETE FROM SOURCE WHERE sid = 2;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND t.balance > s.delta THEN
+ UPDATE SET balance = t.balance - s.delta
+WHEN MATCHED THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s.sid, s.delta)
+WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN
+ UPDATE SET balance = 0
+WHEN NOT MATCHED BY SOURCE THEN
+ DELETE;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
-- Test behavior of triggers that turn UPDATE/DELETE into no-ops
create or replace function skip_merge_op() returns trigger
language plpgsql as
@@ -916,6 +970,20 @@ WHEN MATCHED AND t.a >= 30 AND t.a <= 40
WHEN NOT MATCHED AND s.a < 20 THEN
INSERT VALUES (a, b)');
+-- not matched by source
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN
+ DELETE');
+
+-- not matched by source and target
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN
+ DELETE
+WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN
+ INSERT VALUES (a, b)');
+
-- nothing
SELECT explain_merge('
MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
