From cf287e2742bb430cbe86c874e39abab86d4981b4 Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 21 Mar 2015 07:03:55 +0000 Subject: [PATCH] Disable the sorter optimization used by INSERT INTO SELECT statements if the statement explicitly specifies REPLACE, IGNORE or FAIL conflict handling. FossilOrigin-Name: d42159429735c4a03f1d66887135487a9513ea29 --- manifest | 17 +++++++---------- manifest.uuid | 2 +- src/insert.c | 7 ++++--- test/insert6.test | 32 +++++++++++++++++++++++++++----- 4 files changed, 39 insertions(+), 19 deletions(-) diff --git a/manifest b/manifest index 6c3f7aadda..c5457ca941 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Where\spossible\sinsert\sthe\sset\sof\snew\skeys\sfor\seach\sindex\sin\ssorted\sorder\swithin\s"INSERT\sINTO\s...\sSELECT"\sstatements. -D 2015-03-20T20:30:26.758 +C Disable\sthe\ssorter\soptimization\sused\sby\sINSERT\sINTO\sSELECT\sstatements\sif\sthe\sstatement\sexplicitly\sspecifies\sREPLACE,\sIGNORE\sor\sFAIL\sconflict\shandling. +D 2015-03-21T07:03:55.769 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 88a3e6261286db378fdffa1124cad11b3c05f5bb F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -190,7 +190,7 @@ F src/global.c 4f77cadbc5427d00139ba43d0f3979804cbb700e F src/hash.c 4263fbc955f26c2e8cdc0cf214bc42435aa4e4f5 F src/hash.h c8f3c31722cf3277d03713909761e152a5b81094 F src/hwtime.h d32741c8f4df852c7d959236615444e2b1063b08 -F src/insert.c 9e5d41e7001a6f62e7ea8d76966892a34b003c6b +F src/insert.c 004d1f12366be4b07cec2c61fd632cca917b1c0d F src/journal.c b4124532212b6952f42eb2c12fa3c25701d8ba8d F src/legacy.c ba1863ea58c4c840335a84ec276fc2b25e22bc4e F src/lempar.c 7274c97d24bb46631e504332ccd3bd1b37841770 @@ -666,7 +666,7 @@ F test/insert2.test 4f3a04d168c728ed5ec2c88842e772606c7ce435 F test/insert3.test 1b7db95a03ad9c5013fdf7d6722b6cd66ee55e30 F test/insert4.test 4791662c50518bdd37d394cae9a7a8014e845bb3 F test/insert5.test 394f96728d1258f406fe5f5aeb0aaf29487c39a6 -F test/insert6.test a855a23aea575a540232abbd68664184287aac77 +F test/insert6.test d75c3869f1d8249d54e67774e7a811391e8d2a9e F test/instr.test 737bbf80685232033f3abedc6ae92f75860b5dd2 F test/intarray.test 066b7d7ac38d25bf96f87f1b017bfc687551cdd4 F test/interrupt.test dfe9a67a94b0b2d8f70545ba1a6cca10780d71cc @@ -1247,10 +1247,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P eddc05e7bb31fae74daa86e0504a3478b99fa0f2 -R a72a78e4c229b46f08cda5f8e6da7f3d -T *branch * insert-select-opt -T *sym-insert-select-opt * -T -sym-trunk * +P 0a7f2051b2cc9b4e060fd6081587502124e16c9e +R 12c0ae072dce7a1a3217ff6d1ecb09c8 U dan -Z ce2c6acfee6969d3739af7eba6d8fa6c +Z 5e5cc540ee592a20a0076ae277f0733e diff --git a/manifest.uuid b/manifest.uuid index ebf2eec353..8b14097370 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -0a7f2051b2cc9b4e060fd6081587502124e16c9e \ No newline at end of file +d42159429735c4a03f1d66887135487a9513ea29 \ No newline at end of file diff --git a/src/insert.c b/src/insert.c index c0e4f5b05c..5bbe40fcf1 100644 --- a/src/insert.c +++ b/src/insert.c @@ -782,10 +782,11 @@ void sqlite3Insert( ** ** * there are no triggers to fire, and ** * no foreign key processing to perform, and - ** * the on-conflict mode used for all UNIQUE indexes is either - ** ROLLBACK or ABORT. + ** * the on-conflict mode used for all UNIQUE and PRIMARY KEY indexes, + ** including INTEGER PRIMARY KEYs, is either ROLLBACK or ABORT. */ if( pSelect + && onError!=OE_Fail && onError!=OE_Replace && onError!=OE_Ignore && !IsVirtual(pTab) && pTrigger==0 && 0==sqlite3FkRequired(pParse, pTab, 0, 0) @@ -793,7 +794,7 @@ void sqlite3Insert( for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ u8 oe = idxConflictMode(pIdx, onError); if( oe==OE_Fail || oe==OE_Replace || oe==OE_Ignore ) break; - assert( oe==OE_None||oe==OE_Abort||oe==OE_Rollback ); + assert( oe==OE_None || oe==OE_Abort || oe==OE_Rollback ); } if( pIdx==0 ){ /* This statement can sort the set of new keys for each index before diff --git a/test/insert6.test b/test/insert6.test index f716e16945..b8602f9521 100644 --- a/test/insert6.test +++ b/test/insert6.test @@ -80,8 +80,7 @@ foreach {tn nSort schema} { # The following test cases check that the sorters are disabled if any # of the following are true: # -# 2.1: There are one or more UNIQUE constraints or indexes and the -# statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE". +# 2.1: The statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE". # # 2.2: The statement does not explicitly specify a conflict mode and # there are one or more PRIMARY KEY or UNIQUE constraints with @@ -104,17 +103,17 @@ do_execsql_test 2.1.1 { CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID; } -do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 1 +do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 0 do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0 -do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 1 +do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 0 do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0 -do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 1 +do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 0 do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0 @@ -383,5 +382,28 @@ do_insert6_4_test 4.6 { SELECT hex(randomblob(50)) FROM cnt; } +#------------------------------------------------------------------------- +# At one point the sorters were used for INSERT statements that specify +# "OR FAIL", "REPLACE" or "IGNORE" if there were no PRIMARY KEY or +# UNIQUE indexes. This is incorrect, as all such tables have an implicit +# IPK column. So using the sorters can cause corruption. This test checks +# that that problem no longer exists. +# +reset_db +do_execsql_test 5.1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX t1b ON t1(b); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); +} + +do_catchsql_test 5.2 { + INSERT OR FAIL INTO t1 + SELECT 2, 'x', 'x' UNION ALL SELECT 3, 'x', 'x' UNION ALL SELECT 4, 'x', 'x'; +} {1 {UNIQUE constraint failed: t1.a}} + +integrity_check 5.3 + + finish_test -- 2.47.2