0001-isolation-test.patch

From aafb53357d612e9d25443891fb5dc2c815bc7ad2 Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumarb@google.com>
Date: Sun, 16 Nov 2025 18:38:38 +0530
Subject: [PATCH] isolation test

---
 .../expected/merge-match-to-not-matched.out   | 40 +++++++++++++
 src/test/isolation/isolation_schedule         |  1 +
 .../specs/merge-match-to-not-matched.spec     | 57 +++++++++++++++++++
 3 files changed, 98 insertions(+)
 create mode 100644 src/test/isolation/expected/merge-match-to-not-matched.out
 create mode 100644 src/test/isolation/specs/merge-match-to-not-matched.spec

diff --git a/src/test/isolation/expected/merge-match-to-not-matched.out b/src/test/isolation/expected/merge-match-to-not-matched.out
new file mode 100644
index 00000000000..11d50210132
--- /dev/null
+++ b/src/test/isolation/expected/merge-match-to-not-matched.out
@@ -0,0 +1,40 @@
+Parsed test spec with 2 sessions
+
+starting permutation: merge_update explain_analyze_merge c1 c2
+step merge_update: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+  INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+  UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step explain_analyze_merge: 
+  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+    WHEN NOT MATCHED THEN
+        INSERT VALUES (s.key, s.val)
+    WHEN MATCHED THEN
+        UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+    WHEN NOT MATCHED BY SOURCE THEN
+        UPDATE set key = t.key + 1, val = t.val || ' source not matched by
+  merge2a'
+    RETURNING merge_action(), t.*;
+ <waiting ...>
+step c1: COMMIT;
+step explain_analyze_merge: <... completed>
+QUERY PLAN                                                 
+-----------------------------------------------------------
+Merge on target t (actual rows=2.00 loops=1)               
+  Tuples: inserted=1 updated=1                             
+  ->  Hash Full Join (actual rows=2.00 loops=1)            
+        Hash Cond: (t.key = (1))                           
+        ->  Seq Scan on target t (actual rows=1.00 loops=1)
+        ->  Hash (actual rows=1.00 loops=1)                
+              Buckets: 1024  Batches: 1  Memory Usage: 9kB 
+              ->  Result (actual rows=1.00 loops=1)        
+(8 rows)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..a0cf34489c3 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -59,6 +59,7 @@ test: merge-delete
 test: merge-update
 test: merge-match-recheck
 test: merge-join
+test: merge-match-to-not-matched
 test: delete-abort-savept
 test: delete-abort-savept-2
 test: aborted-keyrevoke
diff --git a/src/test/isolation/specs/merge-match-to-not-matched.spec b/src/test/isolation/specs/merge-match-to-not-matched.spec
new file mode 100644
index 00000000000..97738985687
--- /dev/null
+++ b/src/test/isolation/specs/merge-match-to-not-matched.spec
@@ -0,0 +1,57 @@
+# MERGE MATCHED TO NOT MATCHED
+#
+# This test looks at what happens when we have WHEN MATCHED
+# WHEN NOT MATCHED BY SOURCE and NOT MATCHED BY TARGET and a concurrent UPDATE
+# causes a MATCHED to MATCHED BY SOURCE and NOT MATCHED BY TARGET
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+  INSERT INTO target VALUES (1, 'setup');
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge_update"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+  INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+  UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "c1" { COMMIT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "explain_analyze_merge"
+{
+  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+    WHEN NOT MATCHED THEN
+        INSERT VALUES (s.key, s.val)
+    WHEN MATCHED THEN
+        UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+    WHEN NOT MATCHED BY SOURCE THEN
+        UPDATE set key = t.key + 1, val = t.val || ' source not matched by
+  merge2a'
+    RETURNING merge_action(), t.*;
+}
+step "c2" { COMMIT; }
+
+# merge_status sees concurrently updated row and changed matched to not matched by source and not matched by target
+permutation "merge_update" "explain_analyze_merge" "c1" "c2"
-- 
2.49.0