Ensure that all ON CONFLICT REPLACE indexes are sorted to the end of the list

of indexes for a table, even for weird cases where the same UNIQUE constraint
occurs twice with the ON CONFLICT REPLACE clause only on the second one.  This
avoids an out-of-order contraint processing problem that can arise due
to the optimization of check-in [469a62ca33081854].

FossilOrigin-Name: 1e3918ca2f2c1cfcfa44249b1d7b847d52cbb8d302a8d4a335c090cfdf22d7a1

5 files changed, 52 insertions, 28 deletions

diff --git a/manifest b/manifest
index f7497eb85..007d5cbfb 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Abort\sthe\sOP_Savepoint\sopcode\searly\sif\sa\svirtual\stable\sxSync\smethod\sfails,\nperhaps\sdue\sto\san\sinterrupt.
-D 2019-12-15T00:36:33.807
+C Ensure\sthat\sall\sON\sCONFLICT\sREPLACE\sindexes\sare\ssorted\sto\sthe\send\sof\sthe\slist\nof\sindexes\sfor\sa\stable,\seven\sfor\sweird\scases\swhere\sthe\ssame\sUNIQUE\sconstraint\noccurs\stwice\swith\sthe\sON\sCONFLICT\sREPLACE\sclause\sonly\son\sthe\ssecond\sone.\s\sThis\navoids\san\sout-of-order\scontraint\sprocessing\sproblem\sthat\scan\sarise\sdue\nto\sthe\soptimization\sof\scheck-in\s[469a62ca33081854].
+D 2019-12-15T02:49:32.455
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -471,7 +471,7 @@ F src/btmutex.c 8acc2f464ee76324bf13310df5692a262b801808984c1b79defb2503bbafadb6
 F src/btree.c d22498af716953400e314d2d98d1dac3ea5c3b01e0fd243ef9e9b132c74114ec
 F src/btree.h f27a33c49280209a93385e218306c4ee5f46ba8d7649d2f81a7166b282232484
 F src/btreeInt.h 91806f01fd1145a9a86ba3042f25c38d8faf6002701bf5e780742cf88bcff437
-F src/build.c 9a08209f438b5cbc9795ed34430553288944593aa1221325529e67e3c3449143
+F src/build.c 17f97c9917e84b978f9ec549518f8ec90151c659c3e37058276edb7659c36c44
 F src/callback.c 88615dfc0a82167b65b452b4b305dbf86be77200b3343c6ffc6d03e92a01d181
 F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e
 F src/ctime.c 1b0724e66f95f33b160b1af85caaf9cceb325d22abf39bd24df4f54a73982251
@@ -488,7 +488,7 @@ F src/hash.c 8d7dda241d0ebdafb6ffdeda3149a412d7df75102cecfc1021c98d6219823b19
 F src/hash.h 9d56a9079d523b648774c1784b74b89bd93fac7b365210157482e4319a468f38
 F src/hwtime.h cb1d7e3e1ed94b7aa6fde95ae2c2daccc3df826be26fc9ed7fd90d1750ae6144
 F src/in-operator.md 10cd8f4bcd225a32518407c2fb2484089112fd71
-F src/insert.c 5de3fd62bd1a692353c8f9c7bbde658f0b579f195620ca3cde1948a53015dddd
+F src/insert.c 831408b14a146e93a4e02ddba54dcdfd8097463b9c00ca2ed9daed790c5d452a
 F src/legacy.c d7874bc885906868cd51e6c2156698f2754f02d9eee1bae2d687323c3ca8e5aa
 F src/loadext.c d74f5e7bd51f3c9d283442473eb65aef359664efd6513591c03f01881c4ae2da
 F src/main.c 868ae7db7a54fe859bf2ca8b7a4f24e9fa03a6134abfb7c9801d08411ef5dacb
@@ -747,7 +747,7 @@ F test/collateA.test b8218ab90d1fa5c59dcf156efabb1b2599c580d6
 F test/collateB.test 1e68906951b846570f29f20102ed91d29e634854ee47454d725f2151ecac0b95
 F test/colmeta.test 2c765ea61ee37bc43bbe6d6047f89004e6508eb1
 F test/colname.test 87ad5458bb8709312dac0d6755fd30e8e4ca83298d0a9ef6e5c24277a3c3390e
-F test/conflict.test c7cc007e2af151516ddf38f7412fe10d473a694f55e3df437e2c7b31c2590e8d
+F test/conflict.test 58857e2533fb9f2e0358ea7cb191215657846be1dd9da3b3d6df3e750c02ae03
 F test/conflict2.test bb0b94cf7196c64a3cbd815c66d3ee98c2fecd9c
 F test/conflict3.test f62a2d0cad9162a60e6458fc913dff3a2208feca924120c21737cfee65a6a74a
 F test/contrib01.test 2a1cbc0f2f48955d7d073f725765da6fbceda6b4
@@ -1852,7 +1852,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P a89b38605661e36d192df1d074383b704b34a959fcbc169837c0d4f6848f3208
-R e301f47ec9c8eebaafac7a0537e37181
+P 672e749aef7351de3c69b365c1f80c756fda4e261b5d2ac1faa01d3a7d5a4c49
+R 5b4ddda083685148b74be75770b01033
 U drh
-Z 8f656184411b5cc9de90e32f691f0d2f
+Z d0a5e430475096c2441f59bd7d88b411
diff --git a/manifest.uuid b/manifest.uuid
index f6db7a615..be67899e0 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-672e749aef7351de3c69b365c1f80c756fda4e261b5d2ac1faa01d3a7d5a4c49
\ No newline at end of file
+1e3918ca2f2c1cfcfa44249b1d7b847d52cbb8d302a8d4a335c090cfdf22d7a1
\ No newline at end of file
diff --git a/src/build.c b/src/build.c
index f84eda2a8..f904198fe 100644
--- a/src/build.c
+++ b/src/build.c
@@ -3921,26 +3921,9 @@ void sqlite3CreateIndex(
       sqlite3VdbeJumpHere(v, pIndex->tnum);
     }
   }
-
-  /* When adding an index to the list of indices for a table, make
-  ** sure all indices labeled OE_Replace come after all those labeled
-  ** OE_Ignore.  This is necessary for the correct constraint check
-  ** processing (in sqlite3GenerateConstraintChecks()) as part of
-  ** UPDATE and INSERT statements.  
-  */
   if( db->init.busy || pTblName==0 ){
-    if( onError!=OE_Replace || pTab->pIndex==0
-         || pTab->pIndex->onError==OE_Replace){
-      pIndex->pNext = pTab->pIndex;
-      pTab->pIndex = pIndex;
-    }else{
-      Index *pOther = pTab->pIndex;
-      while( pOther->pNext && pOther->pNext->onError!=OE_Replace ){
-        pOther = pOther->pNext;
-      }
-      pIndex->pNext = pOther->pNext;
-      pOther->pNext = pIndex;
-    }
+    pIndex->pNext = pTab->pIndex;
+    pTab->pIndex = pIndex;
     pIndex = 0;
   }
   else if( IN_RENAME_OBJECT ){
@@ -3952,6 +3935,21 @@ void sqlite3CreateIndex(
   /* Clean up before exiting */
 exit_create_index:
   if( pIndex ) sqlite3FreeIndex(db, pIndex);
+  if( pTab ){  /* Ensure all REPLACE indexes are at the end of the list */
+    Index **ppFrom = &pTab->pIndex;
+    Index *pThis;
+    for(ppFrom=&pTab->pIndex; (pThis = *ppFrom)!=0; ppFrom=&pThis->pNext){
+      Index *pNext;
+      if( pThis->onError!=OE_Replace ) continue;
+      while( (pNext = pThis->pNext)!=0 && pNext->onError!=OE_Replace ){
+        *ppFrom = pNext;
+        pThis->pNext = pNext->pNext;
+        pNext->pNext = pThis;
+        ppFrom = &pNext->pNext;
+      }
+      break;
+    }
+  }
   sqlite3ExprDelete(db, pPIWhere);
   sqlite3ExprListDelete(db, pList);
   sqlite3SrcListDelete(db, pTblName);
diff --git a/src/insert.c b/src/insert.c
index 5915f4fa9..019dec42f 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -2250,6 +2250,10 @@ void sqlite3CompleteInsertion(
   assert( v!=0 );
   assert( pTab->pSelect==0 );  /* This table is not a VIEW */
   for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
+    /* All REPLACE indexes are at the end of the list */
+    assert( pIdx->onError!=OE_Replace
+         || pIdx->pNext==0
+         || pIdx->pNext->onError==OE_Replace );
     if( aRegIdx[i]==0 ) continue;
     if( pIdx->pPartIdxWhere ){
       sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
diff --git a/test/conflict.test b/test/conflict.test
index 136bc3fec..b86f86022 100644
--- a/test/conflict.test
+++ b/test/conflict.test
@@ -834,5 +834,27 @@ do_catchsql_test conflict-14.1 {
   REPLACE INTO t1 DEFAULT VALUES;
 } {1 {NOT NULL constraint failed: t1.x}}
 
+# 2019-12-15 gramfuzz1 find
+# Three UNIQUE constraints, where the third would is a duplicate except
+# that it adds ON CONFLICT REPLACE.  Verify that the indexes end up
+# sorted in the correct order (REPLACE last) so that constraint processing
+# works correctly.
+#
+reset_db
+do_execsql_test conflict-15.10 {
+  CREATE TABLE t1(
+    x PRIMARY KEY,
+    UNIQUE(x,x),
+    UNIQUE(x,x) ON CONFLICT REPLACE
+  );
+  INSERT INTO t1(x) VALUES(1);
+  SELECT * FROM t1;
+} {1}
+do_catchsql_test conflict-15.20 {
+  INSERT INTO t1(x) VALUES(1);
+} {1 {UNIQUE constraint failed: t1.x}}
+do_execsql_test conflict-15.30 {
+  SELECT * FROM t1;
+} {1}
 
 finish_test