Correctly handle the case of a multi-column UNIQUE constraint that contains

the ROWID as one of it columns, and then the columns of that UNIQUE are
used in a row-value IN operator as a WHERE clause constraint.  Reported by
[forum:/forumpost/b9647a113b465950|forum post b9647a113b].  Problem
introduced by [723f1be3d4a905a6], part of ticket [da78413751863].

FossilOrigin-Name: d22475b81c4e26ccc50f3b5626d43b32f7a2de34e5a764539554665bdda735d5

1 files changed, 23 insertions, 0 deletions

diff --git a/test/rowvalue.test b/test/rowvalue.test
index 875fe1c60..e2688e903 100644
--- a/test/rowvalue.test
+++ b/test/rowvalue.test
@@ -782,4 +782,27 @@ do_execsql_test 33.3 {
   SELECT * FROM t1 WHERE (a,b) BETWEEN (2,99) AND (4,0);
 } {3 100}
 
+# 2025-04-15 https://sqlite.org/forum/forumpost/b9647a113b465950
+# Incorrect result when the schema includes a table with a UNIQUE
+# constraint and one of the columns in the UNIQUE constraint is the
+# INTEGER PRIMARY KEY, and the columns that UNIQUE constraint are
+# used in a rowvalue-IN operator constraint.
+#
+reset_db
+do_execsql_test 34.1 {
+  CREATE TABLE items (
+    Id INTEGER  /* rowid alias */,
+    Item INTEGER  /* any type */,
+    Test TEXT  /* TEXT or BLOB */,
+    Filler,  /* any type */
+    PRIMARY KEY(Id),
+    UNIQUE(Item, Id)
+  );
+  INSERT INTO items (Id, Item)
+    VALUES (1, 2), (2, 2), (3, 3), (4, 5);
+  UPDATE items SET test='ok'
+    WHERE (Id, Item) IN (SELECT Id, Item FROM items);
+  SELECT Id, Item, test FROM items ORDER BY id;
+} {1 2 ok 2 2 ok 3 3 ok 4 5 ok}
+
 finish_test