Message-ID: <ccdab5ba02c65af195b5a6d2d744a01d9de47cd3.camel@j-davis.com>
Subject: wrong results: merge when not matched by source
From: Jeff Davis <pgsql@j-davis.com>
To: pgsql-bugs@postgresql.org
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Thu, 17 Sep 2026 12:17:56 -0700
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1.1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ccdab5ba02c65af195b5a6d2d744a01d9de47cd3.camel%40j-davis.com>
Precedence: bulk


AI-discovered bug report appended to this email.

Regards,
	Jeff Davis


-- MERGE WHEN NOT MATCHED BY SOURCE + concurrent DELETE inserts a
-- null-source row.  Two sessions, READ COMMITTED.  Affects 17+.
--
-- Setup (either session):

DROP TABLE IF EXISTS target;
CREATE TABLE target (key int, val text);
INSERT INTO target VALUES (1, 'matched'), (2, 'nms');

-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
DELETE FROM target WHERE key =3D 2;   -- holds the NMS row

-- Session 2 (blocks on the NMS row):
BEGIN ISOLATION LEVEL READ COMMITTED;
MERGE INTO target t
USING (SELECT 1 AS key, 'src' AS val) s
ON t.key =3D s.key
WHEN MATCHED THEN UPDATE SET val =3D s.val
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET val =3D 'nms-action'
WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val)
RETURNING merge_action(), t.*;

-- Session 1:
COMMIT;   -- unblocks session 2

-- Session 2 then returns:
--
--  merge_action | key | val
-- --------------+-----+-----
--  INSERT       |     |
--  UPDATE       |   1 | src
--
-- Expected: only UPDATE of key=3D1; table is {(1, src)}.
-- Actual: also INSERT of (NULL, NULL).