From 71ccfc3217f0aa50d981f2b55adae1c2b8d96b2e Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 20 Mar 2015 20:30:26 +0000 Subject: [PATCH] Where possible insert the set of new keys for each index in sorted order within "INSERT INTO ... SELECT" statements. FossilOrigin-Name: 0a7f2051b2cc9b4e060fd6081587502124e16c9e --- manifest | 28 ++-- manifest.uuid | 2 +- src/insert.c | 106 +++++++++++-- src/sqliteInt.h | 4 +- src/update.c | 4 +- src/vdbe.c | 10 +- test/e_vacuum.test | 18 +-- test/insert6.test | 387 +++++++++++++++++++++++++++++++++++++++++++++ test/stat.test | 10 +- test/wal.test | 10 +- 10 files changed, 529 insertions(+), 50 deletions(-) create mode 100644 test/insert6.test diff --git a/manifest b/manifest index 6ee57b552e..6c3f7aadda 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sa\sproblem\scausing\scollation\ssequence\snames\sto\sbe\sdequoted\smultiple\stimes\sunder\ssome\scircumstances. -D 2015-03-20T08:43:59.683 +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 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 5b9243a33726008cc4132897d2be371db12a13be +F src/insert.c 9e5d41e7001a6f62e7ea8d76966892a34b003c6b F src/journal.c b4124532212b6952f42eb2c12fa3c25701d8ba8d F src/legacy.c ba1863ea58c4c840335a84ec276fc2b25e22bc4e F src/lempar.c 7274c97d24bb46631e504332ccd3bd1b37841770 @@ -235,7 +235,7 @@ F src/shell.c d1ecce877f899abc97cabdf6a0b8323b8c5a0b69 F src/sqlite.h.in 2d48e05677d0f9b06b7757662eef3cebea02d837 F src/sqlite3.rc 992c9f5fb8285ae285d6be28240a7e8d3a7f2bad F src/sqlite3ext.h 17d487c3c91b0b8c584a32fbeb393f6f795eea7d -F src/sqliteInt.h 0f36b72dbaee2306cd4df055e5e6125b3d7a2420 +F src/sqliteInt.h 07402857442948cc95c3a2f98a405cd9b118658b F src/sqliteLimit.h 216557999cb45f2e3578ed53ebefe228d779cb46 F src/status.c 81712116e826b0089bb221b018929536b2b5406f F src/table.c e7a09215315a978057fb42c640f890160dbcc45e @@ -289,11 +289,11 @@ F src/test_wsd.c 41cadfd9d97fe8e3e4e44f61a4a8ccd6f7ca8fe9 F src/threads.c 6bbcc9fe50c917864d48287b4792d46d6e873481 F src/tokenize.c 05e52378c46efbc1fd63cbbbf7f3c555f840f4bf F src/trigger.c 25571661fdeae8c7f975ff40ffec205520a3f92f -F src/update.c 3c4ecc282accf12d39edb8d524cf089645e55a13 +F src/update.c 250cefb96cfc7ca8f86f92ecf6b6b6c7e289daf7 F src/utf.c fc6b889ba0779b7722634cdeaa25f1930d93820c F src/util.c 98a7627ca48ad3265b6940915a1d08355eb3fc7e F src/vacuum.c 9460b9de7b2d4e34b0d374894aa6c8a0632be8ec -F src/vdbe.c b32dc2efe94a2c31ab4584066d47af2b56391f39 +F src/vdbe.c f50f3663b8a88e39609b944776b811106f999a74 F src/vdbe.h 6fc69d9c5e146302c56e163cb4b31d1ee64a18c3 F src/vdbeInt.h bb56fd199d8af1a2c1b9639ee2f70724b4338e3a F src/vdbeapi.c 583d56b129dd27f12bed518270de9ebe521e6a75 @@ -481,7 +481,7 @@ F test/e_select2.test aceb80ab927d46fba5ce7586ebabf23e2bb0604f F test/e_totalchanges.test b12ee5809d3e63aeb83238dd501a7bca7fd72c10 F test/e_update.test 312cb8f5ccfe41515a6bb092f8ea562a9bd54d52 F test/e_uri.test 5ae33760fb2039c61aa2d90886f1664664173585 -F test/e_vacuum.test 5bfbdc21b65c0abf24398d0ba31dc88d93ca77a9 +F test/e_vacuum.test a83cbb0b1f52cfda735909609f9f6422d1655bc3 F test/e_wal.test ae9a593207a77d711443ee69ffe081fda9243625 F test/e_walauto.test ca70cf75c07a6cb1874ced101dd426da76625649 F test/e_walckpt.test 65e29b6631e51f210f83e4ff11571e647ba93608 @@ -666,6 +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/instr.test 737bbf80685232033f3abedc6ae92f75860b5dd2 F test/intarray.test 066b7d7ac38d25bf96f87f1b017bfc687551cdd4 F test/interrupt.test dfe9a67a94b0b2d8f70545ba1a6cca10780d71cc @@ -896,7 +897,7 @@ F test/speed4p.test 0e51908951677de5a969b723e03a27a1c45db38b F test/speedtest1.c 2b416dca3a155fcaa849540b2e7fc1df12896c23 F test/spellfix.test 24f676831acddd2f4056a598fd731a72c6311f49 F test/sqllimits1.test e05786eaed7950ff6a2d00031d001d8a26131e68 -F test/stat.test 76fd746b85459e812a0193410fb599f0531f22de +F test/stat.test 2120916b1e79d6041b53888d53a0e58db4d0b003 F test/stmt.test 25d64e3dbf9a3ce89558667d7f39d966fe2a71b9 F test/subquery.test 666fdecceac258f5fd84bed09a64e49d9f37edd9 F test/subquery2.test 438f8a7da1457277b22e4176510f7659b286995f @@ -1129,7 +1130,7 @@ F test/vtabF.test fd5ad376f5a34fe0891df1f3cddb4fe7c3eb077e F test/vtab_alter.test 9e374885248f69e251bdaacf480b04a197f125e5 F test/vtab_err.test 0d4d8eb4def1d053ac7c5050df3024fd47a3fbd8 F test/vtab_shared.test ea8778d5b0df200adef2ca7c00c3c37d4375f772 -F test/wal.test 885f32b2b390b30b4aa3dbb0e568f8f78d40f5cc +F test/wal.test d4b6ab9c5449af3f28ff070649cd18784ff71f32 F test/wal2.test 1f841d2048080d32f552942e333fd99ce541dada F test/wal3.test b22eb662bcbc148c5f6d956eaf94b047f7afe9c0 F test/wal4.test 4744e155cd6299c6bd99d3eab1c82f77db9cdb3c @@ -1246,7 +1247,10 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P b74cb0a92bba69f8ea705adf4695d03ea4470984 -R 0d4dc41d235c1c2ad9a38ee6dff0fe2f +P eddc05e7bb31fae74daa86e0504a3478b99fa0f2 +R a72a78e4c229b46f08cda5f8e6da7f3d +T *branch * insert-select-opt +T *sym-insert-select-opt * +T -sym-trunk * U dan -Z 56b5996883845a15ff92eed0154203c2 +Z ce2c6acfee6969d3739af7eba6d8fa6c diff --git a/manifest.uuid b/manifest.uuid index d98d8ad752..ebf2eec353 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -eddc05e7bb31fae74daa86e0504a3478b99fa0f2 \ No newline at end of file +0a7f2051b2cc9b4e060fd6081587502124e16c9e \ No newline at end of file diff --git a/src/insert.c b/src/insert.c index a5c3f3e92d..c0e4f5b05c 100644 --- a/src/insert.c +++ b/src/insert.c @@ -339,6 +339,23 @@ static int xferOptimization( int iDbDest /* The database of pDest */ ); +/* +** Return the conflict handling mode that should be used for index pIdx +** if the statement specified conflict mode overrideError. +** +** If the index is not a UNIQUE index, then the conflict handling mode is +** always OE_None. Otherwise, it is one of OE_Abort, OE_Rollback, OE_Fail, +** OE_Ignore or OE_Replace. +*/ +static u8 idxConflictMode(Index *pIdx, u8 overrideError){ + u8 ret = pIdx->onError; + if( ret!=OE_None ){ + if( overrideError!=OE_Default ) ret = overrideError; + if( ret==OE_Default ) ret = OE_Abort; + } + return ret; +} + /* ** This routine is called to handle SQL of the following forms: ** @@ -451,6 +468,7 @@ void sqlite3Insert( int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ int iDataCur = 0; /* VDBE cursor that is the main data repository */ int iIdxCur = 0; /* First index cursor */ + int iSortCur = 0; /* First sorter cursor (for INSERT INTO ... SELECT) */ int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ int endOfLoop; /* Label for the end of the insertion loop */ int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ @@ -755,6 +773,40 @@ void sqlite3Insert( for(i=0; inMem; } + + /* If this is an INSERT INTO ... SELECT statement on a non-virtual table, + ** check if it is possible to defer updating any indexes until after + ** all rows have been processed. If it is, the index keys can be sorted + ** before they are inserted into the index b-tree, which is more efficient + ** for large inserts. It is possible to defer updating the indexes if: + ** + ** * 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. + */ + if( pSelect + && !IsVirtual(pTab) + && pTrigger==0 + && 0==sqlite3FkRequired(pParse, pTab, 0, 0) + ){ + 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 ); + } + if( pIdx==0 ){ + /* This statement can sort the set of new keys for each index before + ** writing them into the b-tree on disk. So open a sorter for each + ** index on the table. */ + iSortCur = pParse->nTab; + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + sqlite3VdbeAddOp1(v, OP_SorterOpen, pParse->nTab++); + sqlite3VdbeSetP4KeyInfo(pParse, pIdx); + } + assert( iSortCur>0 ); + } + } } /* This is the top of the main insertion loop */ @@ -956,12 +1008,20 @@ void sqlite3Insert( #endif { int isReplace; /* Set to true if constraints may cause a replace */ + int iIdxBase = iIdxCur; + int op = OP_IdxInsert; sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, - regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace + regIns, 0, ipkColumn>=0, onError, endOfLoop, iSortCur!=0, &isReplace ); + if( iSortCur ){ + iIdxBase = iSortCur; + isReplace = 1; + op = OP_SorterInsert; + } sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); - sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, - regIns, aRegIdx, 0, appendFlag, isReplace==0); + sqlite3CompleteInsertion(pParse, pTab, + iDataCur, iIdxBase, regIns, op, aRegIdx, 0, appendFlag, isReplace==0 + ); } } @@ -991,6 +1051,31 @@ void sqlite3Insert( } if( !IsVirtual(pTab) && !isView ){ + /* If new index keys were written into sorter objects instead of + ** directly to the index b-trees, copy them from the sorters into the + ** indexes now. And close all the sorters. */ + if( iSortCur ){ + int iTmp = sqlite3GetTempReg(pParse); + for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ + int oe = idxConflictMode(pIdx, onError); + int iCur = iSortCur + idx; + int iIdx = iIdxCur + idx; + int addr = sqlite3VdbeAddOp1(v, OP_SorterSort, iCur); + sqlite3VdbeAddOp3(v, OP_SorterData, iCur, iTmp, iIdx); + if( oe!=OE_None ){ + int nField = -1 * pIdx->nKeyCol; + int jmp = sqlite3VdbeCurrentAddr(v)+2; + sqlite3VdbeAddOp4Int(v, OP_NoConflict, iIdx, jmp, iTmp, nField); + sqlite3UniqueConstraint(pParse, oe, pIdx); + } + sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, iTmp); + sqlite3VdbeAddOp2(v, OP_SorterNext, iCur, addr+1); VdbeCoverage(v); + sqlite3VdbeJumpHere(v, addr); + sqlite3VdbeAddOp1(v, OP_Close, iCur); + } + sqlite3ReleaseTempReg(pParse, iTmp); + } + /* Close all tables opened */ if( iDataCurpIndex; pIdx; pIdx=pIdx->pNext, idx++){ @@ -1133,6 +1218,7 @@ void sqlite3GenerateConstraintChecks( u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */ u8 overrideError, /* Override onError to this if not OE_Default */ int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ + int ignoreUnique, /* Do not enforce UNIQUE constraints */ int *pbMayReplace /* OUT: Set to true if constraint may cause a replace */ ){ Vdbe *v; /* VDBE under constrution */ @@ -1413,17 +1499,12 @@ void sqlite3GenerateConstraintChecks( } /* Find out what action to take in case there is a uniqueness conflict */ - onError = pIdx->onError; - if( onError==OE_None ){ + onError = idxConflictMode(pIdx, overrideError); + if( onError==OE_None || ignoreUnique ){ sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn); sqlite3VdbeResolveLabel(v, addrUniqueOk); continue; /* pIdx is not a UNIQUE index */ } - if( overrideError!=OE_Default ){ - onError = overrideError; - }else if( onError==OE_Default ){ - onError = OE_Abort; - } /* Check to see if the new index entry will be unique */ sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, @@ -1538,6 +1619,7 @@ void sqlite3CompleteInsertion( int iDataCur, /* Cursor of the canonical data source */ int iIdxCur, /* First index cursor */ int regNewData, /* Range of content */ + int idxop, /* Opcode to use to write to "indexes" */ int *aRegIdx, /* Register used by each index. 0 for unused indices */ int isUpdate, /* True for UPDATE, False for INSERT */ int appendBias, /* True if this is likely to be an append */ @@ -1551,6 +1633,8 @@ void sqlite3CompleteInsertion( int i; /* Loop counter */ u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */ + assert( idxop==OP_IdxInsert || idxop==OP_SorterInsert ); + v = sqlite3GetVdbe(pParse); assert( v!=0 ); assert( pTab->pSelect==0 ); /* This table is not a VIEW */ @@ -1561,7 +1645,7 @@ void sqlite3CompleteInsertion( sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2); VdbeCoverage(v); } - sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]); + sqlite3VdbeAddOp2(v, idxop, iIdxCur+i, aRegIdx[i]); pik_flags = 0; if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT; if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 7a4afd2240..092fe32ae0 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3331,8 +3331,8 @@ void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*); int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int); void sqlite3ResolvePartIdxLabel(Parse*,int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int, - u8,u8,int,int*); -void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int); + u8,u8,int,int,int*); +void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int,int*,int,int,int); int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*); void sqlite3BeginWriteOperation(Parse*, int, int); void sqlite3MultiWrite(Parse*); diff --git a/src/update.c b/src/update.c index 3af4017f1b..c4ab726de9 100644 --- a/src/update.c +++ b/src/update.c @@ -567,7 +567,7 @@ void sqlite3Update( /* Do constraint checks. */ assert( regOldRowid>0 ); sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, - regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace); + regNewRowid, regOldRowid, chngKey, onError, labelContinue, 0,&bReplace); /* Do FK constraint checks. */ if( hasFK ){ @@ -599,7 +599,7 @@ void sqlite3Update( /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, - regNewRowid, aRegIdx, 1, 0, 0); + regNewRowid, OP_IdxInsert, aRegIdx, 1, 0, 0); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key diff --git a/src/vdbe.c b/src/vdbe.c index 82c3269e73..a13a89affb 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -164,7 +164,7 @@ int sqlite3_found_count = 0; if( ((P)->flags&MEM_Ephem)!=0 \ && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;} -/* Return true if the cursor was opened using the OP_OpenSorter opcode. */ +/* Return true if the cursor was opened using the OP_SorterOpen opcode. */ #define isSorter(x) ((x)->pSorter!=0) /* @@ -3769,7 +3769,8 @@ case OP_Seek: { /* in2 */ ** ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If ** P4>0 then register P3 is the first of P4 registers that form an unpacked -** record. +** record. If P4<0, then P3 holds a blob constructed by MakeRecord, but +** only the first |P4| fields should be considered. ** ** Cursor P1 is on an index btree. If the record identified by P3 and P4 ** contains any NULL value, jump immediately to P2. If all terms of the @@ -3834,6 +3835,9 @@ case OP_Found: { /* jump, in3 */ assert( pIn3->flags & MEM_Blob ); ExpandBlob(pIn3); sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey); + if( pOp->p4.i<0 ){ + pIdxKey->nField = pOp->p4.i * -1; + } } pIdxKey->default_rc = 0; if( pOp->opcode==OP_NoConflict ){ @@ -3848,7 +3852,7 @@ case OP_Found: { /* jump, in3 */ } } rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res); - if( pOp->p4.i==0 ){ + if( pOp->p4.i<=0 ){ sqlite3DbFree(db, pFree); } if( rc!=SQLITE_OK ){ diff --git a/test/e_vacuum.test b/test/e_vacuum.test index 99b31aaca4..b555316fb0 100644 --- a/test/e_vacuum.test +++ b/test/e_vacuum.test @@ -29,16 +29,16 @@ proc create_db {{sql ""}} { execsql { CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); INSERT INTO t1 VALUES(1, randomblob(400)); - INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1; - INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+1, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+2, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+4, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+8, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+16, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+32, randomblob(400) FROM t1; + INSERT OR FAIL INTO t1 SELECT a+64, randomblob(400) FROM t1; CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); - INSERT INTO t2 SELECT * FROM t1; + INSERT OR FAIL INTO t2 SELECT * FROM t1; } } @@ -127,7 +127,7 @@ ifcapable vtab&&compound { register_dbstat_vtab db do_execsql_test e_vacuum-1.2.1 { DELETE FROM t1 WHERE a%2; - INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2; + INSERT OR REPLACE INTO t1 SELECT b, a FROM t2 WHERE a%2; UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0; } {} diff --git a/test/insert6.test b/test/insert6.test new file mode 100644 index 0000000000..f716e16945 --- /dev/null +++ b/test/insert6.test @@ -0,0 +1,387 @@ +# 2015 March 20 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# The tests in this file ensure that sorter objects are used by +# "INSERT INTO ... SELECT ..." statements when possible. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix insert6 + +# Return the number of OP_SorterOpen instructions in the SQL passed as +# the only argument if it is compiled using connection [db]. +# +proc sorter_count {sql} { + set res 0 + db cache flush + db eval "EXPLAIN $sql" x { + if {$x(opcode) == "SorterOpen"} { incr res } + } + return $res +} + + +#------------------------------------------------------------------------- +# Warm body test. This verifies that the simplest case works for both +# regular and WITHOUT ROWID tables. +# +do_execsql_test 1.1 { + CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 ) + INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt; +} + +foreach {tn nSort schema} { + 1 3 { CREATE TABLE t1(a, b, c) } + 2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID } +} { + + do_test 1.$tn.1 { + execsql { DROP TABLE IF EXISTS t1 } + execsql $schema + } {} + + do_execsql_test 1.$tn.2 { + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); + CREATE INDEX t1c ON t1(c); + } + + do_execsql_test 1.$tn.3 { + INSERT INTO t1 SELECT x, y, z FROM t2; + PRAGMA integrity_check; + SELECT count(*) FROM t1; + } {ok 100} + + do_execsql_test 1.$tn.4 { + INSERT INTO t1 SELECT -x, y, z FROM t2; + PRAGMA integrity_check; + } {ok} + + do_execsql_test 1.$tn.5 { + SELECT count(*) FROM t1; + } {200} + + do_test 1.$tn.6 { + sorter_count { INSERT INTO t1 SELECT * FROM t2 } + } $nSort +} + +#------------------------------------------------------------------------- +# 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.2: The statement does not explicitly specify a conflict mode and +# there are one or more PRIMARY KEY or UNIQUE constraints with +# "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling +# mode. +# +# 2.3: There are one or more INSERT triggers on the target table. +# +# 2.4: The target table is the parent or child of an FK constraint. +# + +do_execsql_test 2.1.1 { + CREATE TABLE x1(a, b, c); + CREATE INDEX x1a ON x1(a); + + CREATE TABLE x2(a, b, c); + CREATE UNIQUE INDEX x2a ON x2(a); + + CREATE TABLE x3(a PRIMARY KEY, b, c); + 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.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.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.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 + +do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1 +do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1 +do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1 +do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1 + +do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1 +do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1 +do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1 +do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1 + + +foreach {tn scount schema} { + 2.1 0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) } + 2.2 0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) } + 2.3 0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) } + 2.4 0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) } + 2.5 0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) } + 2.6 0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) } + 2.7 0 { + CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID + } + 2.8 0 { + CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID + } + 2.9 0 { + CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID + } + + 3.1 1 { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a); + } + 3.2 0 { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a); + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END; + } + 3.3 0 { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a); + CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END; + } + + 4.1 2 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(a); + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); + PRAGMA foreign_keys = 0; + } + 4.2 0 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(a); + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); + PRAGMA foreign_keys = 1; + } + + 4.3 1 { + CREATE TABLE p1(x, y UNIQUE); + CREATE TABLE t1(a, b, c REFERENCES p1(y)); + CREATE INDEX i1 ON t1(a); + PRAGMA foreign_keys = 0; + } + 4.4 0 { + CREATE TABLE p1(x, y UNIQUE); + CREATE TABLE t1(a, b, c REFERENCES p1(y)); + CREATE INDEX i1 ON t1(a); + PRAGMA foreign_keys = 1; + } + +} { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS c1; + DROP TABLE IF EXISTS p1; + } + + do_test 2.2.$tn { + execsql $schema + sorter_count { INSERT INTO t1 SELECT * FROM t2 } + } $scount +} + +#------------------------------------------------------------------------- +# Test that if a UNIQUE constraint is violated and the on conflict mode +# is either ABORT or ROLLBACK, the conflict is handled correctly. +# +# 3.2: Check that conflicts are actually detected. +# 3.3: Check that OR ROLLBACK really does rollback the transaction. +# 3.4: Check that OR ABORT does not. +# +do_execsql_test 3.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c)); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + CREATE TABLE src(a, b, c); +} + +do_catchsql_test 3.2.1 { + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); + INSERT INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.a}} + +do_catchsql_test 3.2.2 { + DELETE FROM src; + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); + INSERT INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.b, t1.c}} + +do_catchsql_test 3.2.3.1 { + CREATE TABLE t3(a); + CREATE UNIQUE INDEX t3a ON t3(a); + + CREATE TABLE t3src(a); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 ) + INSERT INTO t3src SELECT 'abc' FROM cnt; +} {0 {}} + +# execsql { PRAGMA vdbe_trace = 1 } +do_catchsql_test 3.2.3.2 { + INSERT INTO t3 SELECT * FROM t3src; +} {1 {UNIQUE constraint failed: t3.a}} + +do_catchsql_test 3.3.1 { + DELETE FROM src; + BEGIN; + INSERT INTO src VALUES (10, 11, 12), (7, 13, 14); + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.a}} +do_catchsql_test 3.3.2 { + DELETE FROM src; + BEGIN; + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.b, t1.c}} +do_test 3.3.3 { + sqlite3_get_autocommit db +} 1 + +do_catchsql_test 3.4.1 { + DELETE FROM src; + BEGIN; + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); + INSERT OR ABORT INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.a}} +do_catchsql_test 3.4.2 { + ROLLBACK; + DELETE FROM src; + BEGIN; + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); + INSERT OR ABORT INTO t1 SELECT * FROM src; +} {1 {UNIQUE constraint failed: t1.b, t1.c}} +do_test 3.4.3 { + sqlite3_get_autocommit db +} 0 +do_execsql_test 3.4.4 { ROLLBACK } + +#------------------------------------------------------------------------- +# The following tests - 4.* - check that this optimization is actually +# doing something helpful. They do this by executing a big +# "INSERT INTO SELECT" statement in wal mode with a small pager cache. +# Once with "OR FAIL" (so that the sorters are not used) and once with +# the default "OR ABORT" (so that they are). +# +# If the sorters are doing their job, the wal file generated by the +# "OR ABORT" case should be much smaller than the "OR FAIL" trial. +# + +proc odd_collate {lhs rhs} { + string compare [string range $lhs 6 end] [string range $rhs 6 end] +} + +proc do_insert6_4_test {tn sql} { + + reset_db + db collate odd_collate odd_collate + execsql $sql + db_save_and_close + + foreach {tn2 ::onerror ::var} { + 1 "OR ABORT" ::sz1 + 2 "OR FAIL" ::sz2 + } { + do_test $tn.$tn2 { + db_restore_and_reopen + db collate odd_collate odd_collate + execsql " + PRAGMA journal_mode = wal; + PRAGMA cache_size = 5; + PRAGMA wal_autocheckpoint = 0; + INSERT $onerror INTO t1 SELECT * FROM src; + " + set $var [file size test.db-wal] + db close + } {} + } + + do_test $tn.3.($::sz1<$::sz2) { + expr {$sz1 < ($sz2/2)} + } 1 + + sqlite3 db test.db + db collate odd_collate odd_collate + integrity_check $tn.4 +} + +do_insert6_4_test 4.1 { + CREATE TABLE t1(a, b, c); + CREATE UNIQUE INDEX t1a ON t1(a); + CREATE UNIQUE INDEX t1bc ON t1(b, c); + + CREATE TABLE src(x, y, z); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) + INSERT INTO src + SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt; +} + +do_insert6_4_test 4.2 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x); + CREATE UNIQUE INDEX t1b ON t1(b); + CREATE INDEX t1x1 ON t1(x); + CREATE INDEX t1x2 ON t1(x); + CREATE INDEX t1x3 ON t1(x); + CREATE INDEX t1x4 ON t1(x); + + CREATE TABLE src(a, b, x); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) + INSERT INTO src + SELECT random(), x, zeroblob(50) FROM cnt; +} + +do_insert6_4_test 4.3 { + CREATE TABLE t1(a, b, c); + CREATE UNIQUE INDEX t1ab ON t1(a, b); + CREATE UNIQUE INDEX t1ac ON t1(a, c); + + CREATE TABLE src(a, b, c); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) + INSERT INTO src + SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt; +} + +db collate odd_collate odd_collate +do_insert6_4_test 4.5 { + CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate); + CREATE UNIQUE INDEX t1t ON t1(t); + CREATE UNIQUE INDEX t1v ON t1(v); + + CREATE TABLE src(t, v); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) + INSERT INTO src + SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt; +} + +db collate odd_collate odd_collate +do_insert6_4_test 4.6 { + CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE src(t); + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) + INSERT INTO src + SELECT hex(randomblob(50)) FROM cnt; +} + +finish_test + diff --git a/test/stat.test b/test/stat.test index f0447e4509..06c9e3b383 100644 --- a/test/stat.test +++ b/test/stat.test @@ -76,15 +76,15 @@ do_test stat-1.4 { do_execsql_test stat-2.1 { CREATE TABLE t3(a PRIMARY KEY, b); INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222)); - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 ORDER BY rowid; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name != 'sqlite_master'; diff --git a/test/wal.test b/test/wal.test index 675be73791..a508b2a300 100644 --- a/test/wal.test +++ b/test/wal.test @@ -695,10 +695,10 @@ do_test wal-11.3 { do_test wal-11.4 { execsql { BEGIN; - INSERT INTO t1 SELECT blob(900) FROM t1; -- 2 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 4 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 8 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 16 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 2 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 4 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 8 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 16 } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 3 [wal_file_size 32 1024]] @@ -734,7 +734,7 @@ do_test wal-11.10 { execsql { PRAGMA cache_size = 10; BEGIN; - INSERT INTO t1 SELECT blob(900) FROM t1; -- 32 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 32 SELECT count(*) FROM t1; } list [expr [file size test.db]/1024] [file size test.db-wal] -- 2.47.2