Fix a problem in the sessions extension allowing changesets containing foreign key violations to be committed under some circumstances.

FossilOrigin-Name: e09a0c022903d88d5d6de34b2527565ef60c6bb049f2fb42f037e1520abf0f93

7 files changed, 101 insertions, 25 deletions

diff --git a/ext/session/session1.test b/ext/session/session1.test
index bcd7b03d5..dfc1aa895 100644
--- a/ext/session/session1.test
+++ b/ext/session/session1.test
@@ -204,7 +204,9 @@ proc do_conflict_test {tn args} {
   foreach t $O(-tables) { S attach $t }
   execsql $O(-sql)
   set ::xConflict [list]
-  sqlite3changeset_apply db2 [S changeset] xConflict
+  catch {
+    sqlite3changeset_apply db2 [S changeset] xConflict
+  }
 
   set conflicts [list]
   foreach c $O(-conflicts) {
@@ -283,7 +285,7 @@ do_conflict_test $tn.3.2.3 -tables t2 -sql {
   {FOREIGN_KEY 1}
 }
 do_execsql_test $tn.3.2.4 "SELECT * FROM t2" {}
-do_db2_test $tn.3.2.5 "SELECT * FROM t2" {4 five}
+do_db2_test $tn.3.2.5 "SELECT * FROM t2" {1 one 2 two 4 five}
 
 # Test UPDATE changesets.
 #
diff --git a/ext/session/session9.test b/ext/session/session9.test
index ebb88ffad..6b7d1648b 100644
--- a/ext/session/session9.test
+++ b/ext/session/session9.test
@@ -80,8 +80,10 @@ foreach {tn delrow trans conflictargs conflictret} {
   8   3 1 {FOREIGN_KEY 1} ABORT
 } {
 
-  set A(OMIT)  {0 {}}
-  set A(ABORT) {1 SQLITE_CONSTRAINT}
+  set A(OMIT,0)  {1 SQLITE_CONSTRAINT}
+  set A(OMIT,1)  {0 {}}
+  set A(ABORT,0) {1 SQLITE_CONSTRAINT}
+  set A(ABORT,1) {1 SQLITE_CONSTRAINT}
   do_test 1.2.$tn.1 {
     populate_db
     execsql { DELETE FROM p1 WHERE a=($delrow+0) }
@@ -89,20 +91,24 @@ foreach {tn delrow trans conflictargs conflictret} {
 
     set ::xConflict [list]
     list [catch {sqlite3changeset_apply db $::cc xConflict} msg] $msg
-  } $A($conflictret)
+  } $A($conflictret,$trans)
 
   do_test 1.2.$tn.2 { set ::xConflict } $conflictargs
 
-  set A(OMIT)  {1 1}
-  set A(ABORT) {0 0}
+  set A(OMIT,0)  {0 0}
+  set A(OMIT,1)  {1 1}
+  set A(ABORT,0) {0 0}
+  set A(ABORT,1) {0 0}
+
   do_test 1.2.$tn.3 {
     execsql { SELECT count(*) FROM c1 UNION ALL SELECT count(*) FROM c2 }
-  } $A($conflictret)
+  } $A($conflictret,$trans)
 
   do_test 1.2.$tn.4 { expr ![sqlite3_get_autocommit db] } $trans
   do_test 1.2.$tn.5 {
-    if { $trans } { execsql COMMIT }
+    if { $trans && $conflictret=="ABORT" } { execsql COMMIT }
   } {}
+  catchsql ROLLBACK
 }
 
 #--------------------------------------------------------------------
diff --git a/ext/session/sessionnoact.test b/ext/session/sessionnoact.test
index aa1cde474..f605e6108 100644
--- a/ext/session/sessionnoact.test
+++ b/ext/session/sessionnoact.test
@@ -82,7 +82,6 @@ do_execsql_test 1.5 {
   UPDATE p1 SET c=12345 WHERE a = 45;
 }
 
-breakpoint
 sqlite3changeset_apply_v2 -noaction db $C conflict
 do_execsql_test 1.6 {
   SELECT * FROM c1
@@ -108,4 +107,47 @@ do_execsql_test 1.8 {
   PRAGMA foreign_key_check
 }
 
+#-------------------------------------------------------------------------
+# Check that a changeset that causes an FK violation may not be applied,
+# even if SQLITE_CHANGESETAPPLY_FKNOACTION is specified.
+#
+reset_db
+do_execsql_test 2.0 {
+  CREATE TABLE p1(a INTEGER PRIMARY KEY, b, c UNIQUE);
+  INSERT INTO p1 VALUES(1, 1, 'one');
+  INSERT INTO p1 VALUES(2, 2, 'two');
+
+  CREATE TABLE c1(x REFERENCES p1(c) ON DELETE CASCADE);
+  INSERT INTO c1 VALUES('two');
+}
+
+db_save
+
+set C [changeset_from_sql {
+  DELETE FROM p1 WHERE a=2;
+}]
+
+db_restore_and_reopen
+
+do_test 2.1 {
+  sqlite3changeset_apply_v2 -noaction db $C conflict
+} {}
+do_execsql_test 2.2 {
+  SELECT * FROM p1
+} {1 1 one}
+
+db_restore_and_reopen
+db eval { PRAGMA foreign_keys = 1 }
+
+do_test 2.3 {
+  list [catch { sqlite3changeset_apply_v2 -noaction db $C conflict } msg] $msg
+} {1 SQLITE_CONSTRAINT}
+do_execsql_test 2.4 {
+  SELECT * FROM p1;
+} {1 1 one 2 2 two}
+do_execsql_test 2.5 {
+  SELECT * FROM c1;
+} {two}
+
 finish_test
+
diff --git a/ext/session/sqlite3session.c b/ext/session/sqlite3session.c
index f2eb942e6..a3f132add 100644
--- a/ext/session/sqlite3session.c
+++ b/ext/session/sqlite3session.c
@@ -5283,12 +5283,12 @@ static int sessionChangesetApply(
       }
     }
   }
-  sqlite3_exec(db, "PRAGMA defer_foreign_keys = 0", 0, 0, 0);
 
   if( (flags & SQLITE_CHANGESETAPPLY_NOSAVEPOINT)==0 ){
     if( rc==SQLITE_OK ){
       rc = sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0);
-    }else{
+    }
+    if( rc!=SQLITE_OK ){
       sqlite3_exec(db, "ROLLBACK TO changeset_apply", 0, 0, 0);
       sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0);
     }
diff --git a/manifest b/manifest
index 3cdc3c386..1063bced5 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sthe\sdocument\sdescribing\stest\sprocedures\sfor\sthe\sTCL\sextension\sbuild\nprocess.\s\sUpdate\sthe\sWindows\smakefile\sso\sthat\sit\sbuilds\sthe\sTCL\sextensions\nsuccessfully\swith\sa\sdefault\sinstallation\sof\sTcl8.6.
-D 2025-01-04T15:52:40.069
+C Fix\sa\sproblem\sin\sthe\ssessions\sextension\sallowing\schangesets\scontaining\sforeign\skey\sviolations\sto\sbe\scommitted\sunder\ssome\scircumstances.
+D 2025-01-04T16:30:05.359
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md e108e1e69ae8e8a59e93c455654b8ac9356a11720d3345df2a4743e9590fb20d
@@ -576,14 +576,14 @@ F ext/rtree/visual01.txt e9c2564083bcd30ec51b07f881bffbf0e12b50a3f6fced0c222c5c1
 F ext/session/changeset.c 7a1e6a14c7e92d36ca177e92e88b5281acd709f3b726298dc34ec0fb58869cb5
 F ext/session/changesetfuzz.c 227076ab0ae4447d742c01ee88a564da6478bbf26b65108bf8fac9cd8b0b24aa
 F ext/session/changesetfuzz1.test 15b629004e58d5ffcc852e6842a603775bb64b1ce51254831f3d12b113b616cd
-F ext/session/session1.test e94f764fbfb672147c0ef7026b195988133b371dc8cf9e52423eba6cad69717e
+F ext/session/session1.test 8d0509cd3fcfdee6a33422d5fe5c95a9770d62a0b8588adb0177ecdf79b2c345
 F ext/session/session2.test ee83bb973b9ce17ccce4db931cdcdae65eb40bbb22089b2fe6aa4f6be3b9303f
 F ext/session/session3.test 2cc1629cfb880243aec1a7251145e07b78411d851b39b2aa1390704550db8e6a
 F ext/session/session4.test 823f6f018fcbb8dacf61e2960f8b3b848d492b094f8b495eae1d9407d9ab7219
 F ext/session/session5.test 716bc6fafd625ce60dfa62ae128971628c1a1169
 F ext/session/session6.test 35279f2ec45448cd2e24a61688219dc6cf7871757716063acf4a8b5455e1e926
 F ext/session/session8.test 326f3273abf9d5d2d7d559eee8f5994c4ea74a5d935562454605e6607ee29904
-F ext/session/session9.test 5409d90d8141881d08285ed1c2c0d8d10fb92069
+F ext/session/session9.test be090b1420f3824a573da9e56ff542b1e1c2a4f772118e9ab2f75774e66d25d0
 F ext/session/sessionA.test 1feeab0b8e03527f08f2f1defb442da25480138f
 F ext/session/sessionB.test c4fb7f8a688787111606e123a555f18ee04f65bb9f2a4bb2aa71d55ce4e6d02c
 F ext/session/sessionC.test f8a5508bc059ae646e5ec9bdbca66ad24bc92fe99fda5790ac57e1f59fce2fdf
@@ -606,7 +606,7 @@ F ext/session/sessionfault2.test b0d6a7c1d7398a7e800d84657404909c7d385965ea8576d
 F ext/session/sessionfault3.test ce0b5d182133935c224d72507dbf1c5be1a1febf7e85d0b0fbd6d2f724b32b96
 F ext/session/sessioninvert.test 04075517a9497a80d39c495ba6b44f3982c7371129b89e2c52219819bc105a25
 F ext/session/sessionmem.test f2a735db84a3e9e19f571033b725b0b2daf847f3f28b1da55a0c1a4e74f1de09
-F ext/session/sessionnoact.test 2563dff62a2a80dc7c88002241b2fd1578c3e5438735e180fb7e941ebbc66214
+F ext/session/sessionnoact.test 1ea34324b7be2fa9d63870d44969e6bb5290a6d1603ddfd4151c51df73fad291
 F ext/session/sessionnoop.test a9366a36a95ef85f8a3687856ebef46983df399541174cb1ede2ee53b8011bc7
 F ext/session/sessionnoop2.test de4672dce88464396ec9f30ed08c6c01643a69c53ae540fadbbf6d30642d64e8
 F ext/session/sessionrebase.test 702378bdcb5062f1106e74457beca8797d09c113a81768734a58b197b5b334e2
@@ -614,7 +614,7 @@ F ext/session/sessionrowid.test 85187c2f1b38861a5844868126f69f9ec62223a03449a98a
 F ext/session/sessionsize.test 8fcf4685993c3dbaa46a24183940ab9f5aa9ed0d23e5fb63bfffbdb56134b795
 F ext/session/sessionstat1.test 5e718d5888c0c49bbb33a7a4f816366db85f59f6a4f97544a806421b85dc2dec
 F ext/session/sessionwor.test 6fd9a2256442cebde5b2284936ae9e0d54bde692d0f5fd009ecef8511f4cf3fc
-F ext/session/sqlite3session.c 3d0a7f0f7a1c946e01818c716a55a40ae30542a29a9045cb05daf7fb658cdafa
+F ext/session/sqlite3session.c d6f5e3e83b9b0bbc4a8db4837284f0ecc6af5321d4c8e7136380b456b278c46a
 F ext/session/sqlite3session.h 683ccbf16e2c2521661fc4c1cf918ce57002039efbcabcd8097fa4bca569104b
 F ext/session/test_session.c aa29abdcc9011ac02f4fa38e8ede226106eaeee7c3ea7d8b2b999a124e0c368c
 F ext/wasm/EXPORTED_FUNCTIONS.fiddle.in 27450c8b8c70875a260aca55435ec927068b34cef801a96205adb81bdcefc65c
@@ -1153,7 +1153,7 @@ F test/fkey2.test 1063d65e5923c054cfb8f0555a92a3ae0fa8c067275a33ee1715bd856cdb30
 F test/fkey3.test 76d475c80b84ee7a5d062e56ccb6ea68882e2b49
 F test/fkey4.test 86446017011273aad8f9a99c1a65019e7bd9ca9d
 F test/fkey5.test 6727452e163a427147e84e739da18713da553d79f9783559b04fdcd36d5c7421
-F test/fkey6.test d078a1e323a740062bed38df32b8a736fd320dc0
+F test/fkey6.test bdb9c808349a149575b87cf4bfd82d4c81612f0c4d954d27b3f42f043a385396
 F test/fkey7.test 64fb28da03da5dfe3cdef5967aa7e832c2507bf7fb8f0780cacbca1f2338d031
 F test/fkey8.test 51deda7f1a1448bca95875e4a6e1a3a75b4bd7215e924e845bd60de60e4d84bf
 F test/fkey_malloc.test 594a7ea1fbab553c036c70813cd8bd9407d63749
@@ -2205,9 +2205,8 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350
 F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7
 F tool/warnings.sh 49a486c5069de041aedcbde4de178293e0463ae9918ecad7539eedf0ec77a139
 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P 9ee57a30a49d9813bf2669a5d8346f7e018e3fbf1792739951311a8d3a249d45 0c2cdc632f26d6acac2d508def4ecfcc97462ebcddc20c84f8847e02e42b3a1d
-R c39856b8b1c55486d94efa9f4d6e65f7
-T +closed 0c2cdc632f26d6acac2d508def4ecfcc97462ebcddc20c84f8847e02e42b3a1d
-U drh
-Z 0b107a7817bd289dd55ee148db24c6d9
+P 3263db9249444203b7a9a9f2b0be309c74944315dde7ed192366b709fff93f1b
+R dd67c57d38ae3918a7498e739dc242d4
+U dan
+Z 0beeea9f83c0978752782d269e0f987a
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index f286bc29e..613d99cd9 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-3263db9249444203b7a9a9f2b0be309c74944315dde7ed192366b709fff93f1b
+e09a0c022903d88d5d6de34b2527565ef60c6bb049f2fb42f037e1520abf0f93
diff --git a/test/fkey6.test b/test/fkey6.test
index b658f20fe..865875952 100644
--- a/test/fkey6.test
+++ b/test/fkey6.test
@@ -225,5 +225,32 @@ do_execsql_test 3.3.4 {
   SELECT * FROM p2;
 } {0 one 1 deleted!}
 
+#-------------------------------------------------------------------------
+# Verify that, even with "PRAGMA defer_foreign_keys", a transaction cannot
+# be committed if there are outstanding foreign key violations.
+#
+reset_db
+do_execsql_test 4.0 {
+  CREATE TABLE p1(a INTEGER PRIMARY KEY, b UNIQUE);
+  CREATE TABLE c1(x REFERENCES p1(b));
+
+  INSERT INTO p1 VALUES(1, 'one'), (2, 'two'), (3, 'three');
+  INSERT INTO c1 VALUES('two');
+
+  PRAGMA foreign_keys = 1;
+  PRAGMA defer_foreign_keys = 1;
+}
+
+do_execsql_test 4.1 {
+  BEGIN;
+    DELETE FROM p1 WHERE a=2;
+}
+
+do_catchsql_test 4.2 {
+  COMMIT;
+} {1 {FOREIGN KEY constraint failed}}
+
+
+
 
 finish_test