MERGE fails to reject source references in NOT MATCHED BY SOURCE actions
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
Hi Tender,
Thanks for the patch. I reviewed
0001-Fix-source-visibility-in-MERGE-NOT-MATCHED-BY-SOURCE.patch.
Setup: master at debfb3c8a8, and the same commit with the patch applied
(the only version on the CF page, 2026-11-05). The patch applied
cleanly. Both were built with --without-icu, and the build showed no
warnings.
What I tested
1. Your report case (unaliased join, "WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET balance = s.delta"): master returns "MERGE 2". The patched
build returns "invalid reference to FROM-clause entry for table "s""
with the usual DETAIL. The same happens for the WHEN condition ("AND
s.delta > 0", "AND s2.k > 0"), for both inputs of a CROSS JOIN, for a
subquery that references the source ("(SELECT s.delta)"), for a
nested join (s JOIN (s2 JOIN s3 ON true) ON true), and for a
partitioned target. On master all of these were accepted.
2. Valid cases: target-only references in NOT MATCHED BY SOURCE
(qualified, unqualified, with a target alias, in a scalar subquery,
in a DELETE action), MATCHED actions that use both source and target,
NOT MATCHED INSERT that uses join inputs, a NOT MATCHED BY SOURCE
action placed before the other actions, and RETURNING that references
the source. For these statements the output is the same on master and
on the patched build. NOT MATCHED INSERT still cannot see the target,
on both.
3. Regression tests: "make check" failed for me in the temp-install
step (initdb-template). I did not find the cause. I was building on a
Windows-mounted filesystem under WSL, so I suspect my setup and not
the patch. So I ran "make installcheck" against a separately
initialized cluster with the patched build instead: 243/243 tests
passed, including merge with your new cases.
Review comments
a) Behavior difference you may want to mention. For an unqualified
source column in a NOT MATCHED BY SOURCE action, master prints 'HINT:
To reference that column, you must use a table-qualified name.' The
patched build no longer prints it (for example "UPDATE SET balance =
delta" on a join). I think dropping it is right, because a qualified
name does not work there either, so the hint was misleading. The
error text and DETAIL are the same.
b) The patch deletes the three Assert()s on commandType that were in
setNamespaceForMergeWhen() (for example, NOT MATCHED BY SOURCE must
be UPDATE, DELETE or DO NOTHING). Maybe keep them in the new switch,
or mention in the commit message that they are dropped on purpose.
c) parse_merge.c now ends with an extra blank line after the closing
brace of transformMergeStmt(), left over from removing
setNamespaceVisibilityForRTE(). pgindent or git diff --check would
probably point at it.
d) In the new tests, the first two failing statements have "EXPLAIN
(COSTS OFF)" in front. It is not needed, since the statements fail
during parse analysis. Plain MERGE statements would be simpler.
Overall the patch looks good to me and fixes the problem as
described. The simplification (no flag mutation, a temporary
namespace per action, restored afterwards) is easier to read than the
old code.
What I did not test: other platforms, the docs (I did not look for text
that needs updating), and the TAP tests.
Regards,
Joao Rito
Hi,
Joao Rito <joaovictor.rito@gmail.com> 于2026年11月19日周四 03:47写道:
c) parse_merge.c now ends with an extra blank line after the closing
brace of transformMergeStmt(), left over from removing
setNamespaceVisibilityForRTE(). pgindent or git diff --check would
probably point at it.
Done.
Overall the patch looks good to me and fixes the problem as
described. The simplification (no flag mutation, a temporary
namespace per action, restored afterwards) is easier to read than the
old code.
Thanks for the review.
I've cc'd Dean since this issue dates back to the introduction of WHEN
NOT MATCHED BY SOURCE.
Dean, could you take a look at the namespace handling in v2 when you
have a chance?
--
Tender Wang