Hi,
I noticed that MERGE allows source references in NOT MATCHED BY SOURCE
actions when the source is an unaliased join. For example:
postgres=# CREATE TABLE t (id int, balance int);
CREATE TABLE s (id int, delta int);
CREATE TABLE
CREATE TABLE
postgres=# MERGE INTO t
USING s CROSS JOIN (SELECT 1 AS k) j ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = 1
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
MERGE 0
This should reject s.delta, as it does when USING contains only s.
Source references in WHEN conditions are also incorrectly accepted.
postgres=# MERGE INTO t
USING s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = 1
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
ERROR: invalid reference to FROM-clause entry for table "s"
LINE 4: ...HEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
^
DETAIL: There is an entry for table "s", but it cannot be referenced
from this part of the query.
setNamespaceForMergeWhen() hides only the top-level source RTE. For an
unaliased join, its input relations remain visible by name.
The attached patch temporarily selects the appropriate namespace for
each action and restores it afterward, following transformJoinOnClause().
It preserves the source visibility flags established by
transformFromClause(). Regression tests are included.
Thoughts?
--
Thanks,
Tender Wang
From bc2f84e0718a9f1f2b2b99df8bd16b7ec3f1bd68 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Thu, 5 Nov 2026 11:27:15 +0800
Subject: [PATCH] Fix source visibility in MERGE NOT MATCHED BY SOURCE actions.
When the MERGE source is an unaliased join, hiding only its top-level
RTE leaves the input relations visible by name. This allows
NOT MATCHED BY SOURCE actions to reference source columns in their
conditions and UPDATE expressions, although such references should
be rejected.
Temporarily restrict the namespace for each action according to its
match kind, and restore it afterwards, following the pattern used by
transformJoinOnClause(). Use the complete source namespace to preserve
the visibility rules for join inputs and aliases.
---
src/backend/parser/parse_merge.c | 122 ++++++----------------------
src/test/regress/expected/merge.out | 33 ++++++++
src/test/regress/sql/merge.sql | 28 +++++++
3 files changed, 88 insertions(+), 95 deletions(-)
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
index 0a70d48fd4c..ec7f357801c 100644
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -28,77 +28,6 @@
#include "parser/parsetree.h"
#include "utils/rel.h"
-static void setNamespaceForMergeWhen(ParseState *pstate,
-
MergeWhenClause *mergeWhenClause,
- Index
targetRTI,
- Index
sourceRTI);
-static void setNamespaceVisibilityForRTE(List *namespace, RangeTblEntry *rte,
-
bool rel_visible,
-
bool cols_visible);
-
-/*
- * 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 [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 MATCHED 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
- * that columns can be referenced unqualified from these relations.
- */
-static void
-setNamespaceForMergeWhen(ParseState *pstate, MergeWhenClause *mergeWhenClause,
- Index targetRTI, Index
sourceRTI)
-{
- RangeTblEntry *targetRelRTE,
- *sourceRelRTE;
-
- targetRelRTE = rt_fetch(targetRTI, pstate->p_rtable);
- sourceRelRTE = rt_fetch(sourceRTI, pstate->p_rtable);
-
- if (mergeWhenClause->matchKind == MERGE_WHEN_MATCHED)
- {
- Assert(mergeWhenClause->commandType == CMD_UPDATE ||
- mergeWhenClause->commandType == CMD_DELETE ||
- mergeWhenClause->commandType == CMD_NOTHING);
-
- /* MATCHED actions can see both target and source relations. */
- setNamespaceVisibilityForRTE(pstate->p_namespace,
-
targetRelRTE, true, true);
- setNamespaceVisibilityForRTE(pstate->p_namespace,
-
sourceRelRTE, true, true);
- }
- else if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
- {
- /*
- * 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 /*
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);
- setNamespaceVisibilityForRTE(pstate->p_namespace,
-
targetRelRTE, false, false);
- setNamespaceVisibilityForRTE(pstate->p_namespace,
-
sourceRelRTE, true, true);
- }
-}
-
/*
* transformMergeStmt -
* transforms a MERGE statement
@@ -112,6 +41,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
bool is_terminal[NUM_MERGE_MATCH_KINDS];
Index sourceRTI;
List *mergeActionList;
+ List *sourceNamespace;
ParseNamespaceItem *nsitem;
/* There can't be any outer WITH to worry about */
@@ -208,6 +138,8 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
transformFromClause(pstate,
list_make1(stmt->sourceRelation));
sourceRTI = list_length(pstate->p_rtable);
+ /* Save the source namespace before adding the target below. */
+ sourceNamespace = list_copy(pstate->p_namespace);
nsitem = GetNSItemByRangeTablePosn(pstate, sourceRTI, 0);
/*
@@ -269,18 +201,34 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
{
MergeWhenClause *mergeWhenClause = lfirst_node(MergeWhenClause,
l);
MergeAction *action;
+ List *save_namespace;
action = makeNode(MergeAction);
action->commandType = mergeWhenClause->commandType;
action->matchKind = mergeWhenClause->matchKind;
/*
- * Set namespace for the specific action. This must be done
before
- * analyzing the WHEN quals and the action targetlist.
+ * Temporarily restrict the namespace to the relations visible
to this
+ * action. Use the complete source namespace, rather than just
its
+ * top-level RTE, so that inputs of an unaliased source join are
+ * handled too. Preserve the visibility flags established by
+ * transformFromClause().
*/
- setNamespaceForMergeWhen(pstate, mergeWhenClause,
-
qry->resultRelation,
- sourceRTI);
+ save_namespace = pstate->p_namespace;
+ switch (mergeWhenClause->matchKind)
+ {
+ case MERGE_WHEN_MATCHED:
+ /* Both source and target are already visible.
*/
+ break;
+ case MERGE_WHEN_NOT_MATCHED_BY_SOURCE:
+ pstate->p_namespace =
list_make1(pstate->p_target_nsitem);
+ break;
+ case MERGE_WHEN_NOT_MATCHED_BY_TARGET:
+ pstate->p_namespace = sourceNamespace;
+ break;
+ default:
+ elog(ERROR, "unknown match kind in MERGE WHEN
clause");
+ }
/*
* Transform the WHEN condition.
@@ -393,6 +341,9 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
elog(ERROR, "unknown action in MERGE WHEN
clause");
}
+ /* Restore the namespace for subsequent actions. */
+ pstate->p_namespace = save_namespace;
+
mergeActionList = lappend(mergeActionList, action);
}
@@ -406,22 +357,3 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
return qry;
}
-static void
-setNamespaceVisibilityForRTE(List *namespace, RangeTblEntry *rte,
- bool rel_visible,
- bool cols_visible)
-{
- ListCell *lc;
-
- foreach(lc, namespace)
- {
- ParseNamespaceItem *nsitem = (ParseNamespaceItem *) lfirst(lc);
-
- if (nsitem->p_rte == rte)
- {
- nsitem->p_rel_visible = rel_visible;
- nsitem->p_cols_visible = cols_visible;
- break;
- }
- }
-}
diff --git a/src/test/regress/expected/merge.out
b/src/test/regress/expected/merge.out
index 9cb1d87066a..fc34c2ac83a 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -2466,6 +2466,39 @@ WHEN NOT MATCHED THEN
INSERT VALUES (sid2, delta, sval)
WHEN MATCHED THEN
DELETE;
+-- NOT MATCHED BY SOURCE must not see either input of a source join
+EXPLAIN (COSTS OFF)
+MERGE INTO cj_target t
+USING cj_source1 s1 CROSS JOIN cj_source2 s2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE THEN
+ UPDATE SET balance = s1.delta;
+ERROR: invalid reference to FROM-clause entry for table "s1"
+LINE 6: UPDATE SET balance = s1.delta;
+ ^
+DETAIL: There is an entry for table "s1", but it cannot be referenced from
this part of the query.
+EXPLAIN (COSTS OFF)
+MERGE INTO cj_target t
+USING cj_source1 s1 CROSS JOIN cj_source2 s2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE AND s2.sval IS NULL THEN
+ DELETE;
+ERROR: invalid reference to FROM-clause entry for table "s2"
+LINE 5: WHEN NOT MATCHED BY SOURCE AND s2.sval IS NULL THEN
+ ^
+DETAIL: There is an entry for table "s2", but it cannot be referenced from
this part of the query.
+-- A later action must still be able to see the source join inputs
+BEGIN;
+MERGE INTO cj_target t
+USING cj_source1 s1 JOIN cj_source2 s2 ON s1.sid1 = s2.sid2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE THEN
+ UPDATE SET balance = 0
+WHEN MATCHED AND s1.scat = 10 THEN
+ UPDATE SET val = s2.sval
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s1.sid1, s1.delta, s2.sval);
+ROLLBACK;
-- some simple expressions in INSERT targetlist
MERGE INTO cj_target t
USING cj_source2 s2
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index 2660b19f238..39913eb21c0 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -1544,6 +1544,34 @@ WHEN NOT MATCHED THEN
WHEN MATCHED THEN
DELETE;
+-- NOT MATCHED BY SOURCE must not see either input of a source join
+EXPLAIN (COSTS OFF)
+MERGE INTO cj_target t
+USING cj_source1 s1 CROSS JOIN cj_source2 s2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE THEN
+ UPDATE SET balance = s1.delta;
+
+EXPLAIN (COSTS OFF)
+MERGE INTO cj_target t
+USING cj_source1 s1 CROSS JOIN cj_source2 s2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE AND s2.sval IS NULL THEN
+ DELETE;
+
+-- A later action must still be able to see the source join inputs
+BEGIN;
+MERGE INTO cj_target t
+USING cj_source1 s1 JOIN cj_source2 s2 ON s1.sid1 = s2.sid2
+ON t.tid = s1.sid1
+WHEN NOT MATCHED BY SOURCE THEN
+ UPDATE SET balance = 0
+WHEN MATCHED AND s1.scat = 10 THEN
+ UPDATE SET val = s2.sval
+WHEN NOT MATCHED THEN
+ INSERT VALUES (s1.sid1, s1.delta, s2.sval);
+ROLLBACK;
+
-- some simple expressions in INSERT targetlist
MERGE INTO cj_target t
USING cj_source2 s2
--
2.43.0
