wrong results: merge when not matched by source - Mailing list pgsql-bugs

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 = 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 = s.key
WHEN MATCHED THEN UPDATE SET val = s.val
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET val = '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=1; table is {(1, src)}.
-- Actual: also INSERT of (NULL, NULL).