Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Where possible insert the set of new keys for each index in sorted order within "INSERT INTO ... SELECT" statements. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | insert-select-opt |
| Files: | files | file ages | folders |
| SHA1: |
0a7f2051b2cc9b4e060fd60815875021 |
| User & Date: | dan 2015-03-20 20:30:26 |
Context
|
2015-03-21
| ||
| 07:03 | Disable the sorter optimization used by INSERT INTO SELECT statements if the statement explicitly specifies REPLACE, IGNORE or FAIL conflict handling. check-in: d4215942 user: dan tags: insert-select-opt | |
|
2015-03-20
| ||
| 20:30 | Where possible insert the set of new keys for each index in sorted order within "INSERT INTO ... SELECT" statements. check-in: 0a7f2051 user: dan tags: insert-select-opt | |
| 08:43 | Fix a problem causing collation sequence names to be dequoted multiple times under some circumstances. check-in: eddc05e7 user: dan tags: trunk | |
Changes
Changes to src/insert.c.
334 334 static int xferOptimization( 335 335 Parse *pParse, /* Parser context */ 336 336 Table *pDest, /* The table we are inserting into */ 337 337 Select *pSelect, /* A SELECT statement to use as the data source */ 338 338 int onError, /* How to handle constraint errors */ 339 339 int iDbDest /* The database of pDest */ 340 340 ); 341 + 342 +/* 343 +** Return the conflict handling mode that should be used for index pIdx 344 +** if the statement specified conflict mode overrideError. 345 +** 346 +** If the index is not a UNIQUE index, then the conflict handling mode is 347 +** always OE_None. Otherwise, it is one of OE_Abort, OE_Rollback, OE_Fail, 348 +** OE_Ignore or OE_Replace. 349 +*/ 350 +static u8 idxConflictMode(Index *pIdx, u8 overrideError){ 351 + u8 ret = pIdx->onError; 352 + if( ret!=OE_None ){ 353 + if( overrideError!=OE_Default ) ret = overrideError; 354 + if( ret==OE_Default ) ret = OE_Abort; 355 + } 356 + return ret; 357 +} 341 358 342 359 /* 343 360 ** This routine is called to handle SQL of the following forms: 344 361 ** 345 362 ** insert into TABLE (IDLIST) values(EXPRLIST) 346 363 ** insert into TABLE (IDLIST) select 347 364 ** ................................................................................ 447 464 int i, j, idx; /* Loop counters */ 448 465 Vdbe *v; /* Generate code into this virtual machine */ 449 466 Index *pIdx; /* For looping over indices of the table */ 450 467 int nColumn; /* Number of columns in the data */ 451 468 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ 452 469 int iDataCur = 0; /* VDBE cursor that is the main data repository */ 453 470 int iIdxCur = 0; /* First index cursor */ 471 + int iSortCur = 0; /* First sorter cursor (for INSERT INTO ... SELECT) */ 454 472 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ 455 473 int endOfLoop; /* Label for the end of the insertion loop */ 456 474 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ 457 475 int addrInsTop = 0; /* Jump to label "D" */ 458 476 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */ 459 477 SelectDest dest; /* Destination for SELECT on rhs of INSERT */ 460 478 int iDb; /* Index of database holding TABLE */ ................................................................................ 751 769 aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1)); 752 770 if( aRegIdx==0 ){ 753 771 goto insert_cleanup; 754 772 } 755 773 for(i=0; i<nIdx; i++){ 756 774 aRegIdx[i] = ++pParse->nMem; 757 775 } 776 + 777 + /* If this is an INSERT INTO ... SELECT statement on a non-virtual table, 778 + ** check if it is possible to defer updating any indexes until after 779 + ** all rows have been processed. If it is, the index keys can be sorted 780 + ** before they are inserted into the index b-tree, which is more efficient 781 + ** for large inserts. It is possible to defer updating the indexes if: 782 + ** 783 + ** * there are no triggers to fire, and 784 + ** * no foreign key processing to perform, and 785 + ** * the on-conflict mode used for all UNIQUE indexes is either 786 + ** ROLLBACK or ABORT. 787 + */ 788 + if( pSelect 789 + && !IsVirtual(pTab) 790 + && pTrigger==0 791 + && 0==sqlite3FkRequired(pParse, pTab, 0, 0) 792 + ){ 793 + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 794 + u8 oe = idxConflictMode(pIdx, onError); 795 + if( oe==OE_Fail || oe==OE_Replace || oe==OE_Ignore ) break; 796 + assert( oe==OE_None||oe==OE_Abort||oe==OE_Rollback ); 797 + } 798 + if( pIdx==0 ){ 799 + /* This statement can sort the set of new keys for each index before 800 + ** writing them into the b-tree on disk. So open a sorter for each 801 + ** index on the table. */ 802 + iSortCur = pParse->nTab; 803 + for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 804 + sqlite3VdbeAddOp1(v, OP_SorterOpen, pParse->nTab++); 805 + sqlite3VdbeSetP4KeyInfo(pParse, pIdx); 806 + } 807 + assert( iSortCur>0 ); 808 + } 809 + } 758 810 } 759 811 760 812 /* This is the top of the main insertion loop */ 761 813 if( useTempTable ){ 762 814 /* This block codes the top of loop only. The complete loop is the 763 815 ** following pseudocode (template 4): 764 816 ** ................................................................................ 952 1004 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB); 953 1005 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError); 954 1006 sqlite3MayAbort(pParse); 955 1007 }else 956 1008 #endif 957 1009 { 958 1010 int isReplace; /* Set to true if constraints may cause a replace */ 1011 + int iIdxBase = iIdxCur; 1012 + int op = OP_IdxInsert; 959 1013 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, 960 - regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace 1014 + regIns, 0, ipkColumn>=0, onError, endOfLoop, iSortCur!=0, &isReplace 961 1015 ); 1016 + if( iSortCur ){ 1017 + iIdxBase = iSortCur; 1018 + isReplace = 1; 1019 + op = OP_SorterInsert; 1020 + } 962 1021 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); 963 - sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, 964 - regIns, aRegIdx, 0, appendFlag, isReplace==0); 1022 + sqlite3CompleteInsertion(pParse, pTab, 1023 + iDataCur, iIdxBase, regIns, op, aRegIdx, 0, appendFlag, isReplace==0 1024 + ); 965 1025 } 966 1026 } 967 1027 968 1028 /* Update the count of rows that are inserted 969 1029 */ 970 1030 if( (db->flags & SQLITE_CountRows)!=0 ){ 971 1031 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); ................................................................................ 987 1047 sqlite3VdbeAddOp1(v, OP_Close, srcTab); 988 1048 }else if( pSelect ){ 989 1049 sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont); 990 1050 sqlite3VdbeJumpHere(v, addrInsTop); 991 1051 } 992 1052 993 1053 if( !IsVirtual(pTab) && !isView ){ 1054 + /* If new index keys were written into sorter objects instead of 1055 + ** directly to the index b-trees, copy them from the sorters into the 1056 + ** indexes now. And close all the sorters. */ 1057 + if( iSortCur ){ 1058 + int iTmp = sqlite3GetTempReg(pParse); 1059 + for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 1060 + int oe = idxConflictMode(pIdx, onError); 1061 + int iCur = iSortCur + idx; 1062 + int iIdx = iIdxCur + idx; 1063 + int addr = sqlite3VdbeAddOp1(v, OP_SorterSort, iCur); 1064 + sqlite3VdbeAddOp3(v, OP_SorterData, iCur, iTmp, iIdx); 1065 + if( oe!=OE_None ){ 1066 + int nField = -1 * pIdx->nKeyCol; 1067 + int jmp = sqlite3VdbeCurrentAddr(v)+2; 1068 + sqlite3VdbeAddOp4Int(v, OP_NoConflict, iIdx, jmp, iTmp, nField); 1069 + sqlite3UniqueConstraint(pParse, oe, pIdx); 1070 + } 1071 + sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, iTmp); 1072 + sqlite3VdbeAddOp2(v, OP_SorterNext, iCur, addr+1); VdbeCoverage(v); 1073 + sqlite3VdbeJumpHere(v, addr); 1074 + sqlite3VdbeAddOp1(v, OP_Close, iCur); 1075 + } 1076 + sqlite3ReleaseTempReg(pParse, iTmp); 1077 + } 1078 + 994 1079 /* Close all tables opened */ 995 1080 if( iDataCur<iIdxCur ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur); 996 1081 for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ 997 1082 sqlite3VdbeAddOp1(v, OP_Close, idx+iIdxCur); 998 1083 } 999 1084 } 1000 1085 ................................................................................ 1129 1214 int iDataCur, /* Canonical data cursor (main table or PK index) */ 1130 1215 int iIdxCur, /* First index cursor */ 1131 1216 int regNewData, /* First register in a range holding values to insert */ 1132 1217 int regOldData, /* Previous content. 0 for INSERTs */ 1133 1218 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */ 1134 1219 u8 overrideError, /* Override onError to this if not OE_Default */ 1135 1220 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ 1221 + int ignoreUnique, /* Do not enforce UNIQUE constraints */ 1136 1222 int *pbMayReplace /* OUT: Set to true if constraint may cause a replace */ 1137 1223 ){ 1138 1224 Vdbe *v; /* VDBE under constrution */ 1139 1225 Index *pIdx; /* Pointer to one of the indices */ 1140 1226 Index *pPk = 0; /* The PRIMARY KEY index */ 1141 1227 sqlite3 *db; /* Database connection */ 1142 1228 int i; /* loop counter */ ................................................................................ 1409 1495 ** logic below can all be skipped. */ 1410 1496 if( isUpdate && pPk==pIdx && pkChng==0 ){ 1411 1497 sqlite3VdbeResolveLabel(v, addrUniqueOk); 1412 1498 continue; 1413 1499 } 1414 1500 1415 1501 /* Find out what action to take in case there is a uniqueness conflict */ 1416 - onError = pIdx->onError; 1417 - if( onError==OE_None ){ 1502 + onError = idxConflictMode(pIdx, overrideError); 1503 + if( onError==OE_None || ignoreUnique ){ 1418 1504 sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn); 1419 1505 sqlite3VdbeResolveLabel(v, addrUniqueOk); 1420 1506 continue; /* pIdx is not a UNIQUE index */ 1421 1507 } 1422 - if( overrideError!=OE_Default ){ 1423 - onError = overrideError; 1424 - }else if( onError==OE_Default ){ 1425 - onError = OE_Abort; 1426 - } 1427 1508 1428 1509 /* Check to see if the new index entry will be unique */ 1429 1510 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, 1430 1511 regIdx, pIdx->nKeyCol); VdbeCoverage(v); 1431 1512 1432 1513 /* Generate code to handle collisions */ 1433 1514 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField); ................................................................................ 1534 1615 */ 1535 1616 void sqlite3CompleteInsertion( 1536 1617 Parse *pParse, /* The parser context */ 1537 1618 Table *pTab, /* the table into which we are inserting */ 1538 1619 int iDataCur, /* Cursor of the canonical data source */ 1539 1620 int iIdxCur, /* First index cursor */ 1540 1621 int regNewData, /* Range of content */ 1622 + int idxop, /* Opcode to use to write to "indexes" */ 1541 1623 int *aRegIdx, /* Register used by each index. 0 for unused indices */ 1542 1624 int isUpdate, /* True for UPDATE, False for INSERT */ 1543 1625 int appendBias, /* True if this is likely to be an append */ 1544 1626 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */ 1545 1627 ){ 1546 1628 Vdbe *v; /* Prepared statements under construction */ 1547 1629 Index *pIdx; /* An index being inserted or updated */ 1548 1630 u8 pik_flags; /* flag values passed to the btree insert */ 1549 1631 int regData; /* Content registers (after the rowid) */ 1550 1632 int regRec; /* Register holding assembled record for the table */ 1551 1633 int i; /* Loop counter */ 1552 1634 u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */ 1553 1635 1636 + assert( idxop==OP_IdxInsert || idxop==OP_SorterInsert ); 1637 + 1554 1638 v = sqlite3GetVdbe(pParse); 1555 1639 assert( v!=0 ); 1556 1640 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1557 1641 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 1558 1642 if( aRegIdx[i]==0 ) continue; 1559 1643 bAffinityDone = 1; 1560 1644 if( pIdx->pPartIdxWhere ){ 1561 1645 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2); 1562 1646 VdbeCoverage(v); 1563 1647 } 1564 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]); 1648 + sqlite3VdbeAddOp2(v, idxop, iIdxCur+i, aRegIdx[i]); 1565 1649 pik_flags = 0; 1566 1650 if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT; 1567 1651 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ 1568 1652 assert( pParse->nested==0 ); 1569 1653 pik_flags |= OPFLAG_NCHANGE; 1570 1654 } 1571 1655 if( pik_flags ) sqlite3VdbeChangeP5(v, pik_flags);
Changes to src/sqliteInt.h.
3327 3327 int sqlite3ExprNeedsNoAffinityChange(const Expr*, char); 3328 3328 int sqlite3IsRowid(const char*); 3329 3329 void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8); 3330 3330 void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*); 3331 3331 int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int); 3332 3332 void sqlite3ResolvePartIdxLabel(Parse*,int); 3333 3333 void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int, 3334 - u8,u8,int,int*); 3335 -void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int); 3334 + u8,u8,int,int,int*); 3335 +void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int,int*,int,int,int); 3336 3336 int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*); 3337 3337 void sqlite3BeginWriteOperation(Parse*, int, int); 3338 3338 void sqlite3MultiWrite(Parse*); 3339 3339 void sqlite3MayAbort(Parse*); 3340 3340 void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8); 3341 3341 void sqlite3UniqueConstraint(Parse*, int, Index*); 3342 3342 void sqlite3RowidConstraint(Parse*, int, Table*);
Changes to src/update.c.
563 563 if( !isView ){ 564 564 int j1 = 0; /* Address of jump instruction */ 565 565 int bReplace = 0; /* True if REPLACE conflict resolution might happen */ 566 566 567 567 /* Do constraint checks. */ 568 568 assert( regOldRowid>0 ); 569 569 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, 570 - regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace); 570 + regNewRowid, regOldRowid, chngKey, onError, labelContinue, 0,&bReplace); 571 571 572 572 /* Do FK constraint checks. */ 573 573 if( hasFK ){ 574 574 sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey); 575 575 } 576 576 577 577 /* Delete the index entries associated with the current record. */ ................................................................................ 595 595 596 596 if( hasFK ){ 597 597 sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey); 598 598 } 599 599 600 600 /* Insert the new index entries and the new record. */ 601 601 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, 602 - regNewRowid, aRegIdx, 1, 0, 0); 602 + regNewRowid, OP_IdxInsert, aRegIdx, 1, 0, 0); 603 603 604 604 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 605 605 ** handle rows (possibly in other tables) that refer via a foreign key 606 606 ** to the row just updated. */ 607 607 if( hasFK ){ 608 608 sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); 609 609 }
Changes to src/vdbe.c.
160 160 ** string that the register itself controls. In other words, it 161 161 ** converts an MEM_Ephem string into a string with P.z==P.zMalloc. 162 162 */ 163 163 #define Deephemeralize(P) \ 164 164 if( ((P)->flags&MEM_Ephem)!=0 \ 165 165 && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;} 166 166 167 -/* Return true if the cursor was opened using the OP_OpenSorter opcode. */ 167 +/* Return true if the cursor was opened using the OP_SorterOpen opcode. */ 168 168 #define isSorter(x) ((x)->pSorter!=0) 169 169 170 170 /* 171 171 ** Allocate VdbeCursor number iCur. Return a pointer to it. Return NULL 172 172 ** if we run out of memory. 173 173 */ 174 174 static VdbeCursor *allocateCursor( ................................................................................ 3765 3765 ** See also: Found, NotExists, NoConflict 3766 3766 */ 3767 3767 /* Opcode: NoConflict P1 P2 P3 P4 * 3768 3768 ** Synopsis: key=r[P3@P4] 3769 3769 ** 3770 3770 ** If P4==0 then register P3 holds a blob constructed by MakeRecord. If 3771 3771 ** P4>0 then register P3 is the first of P4 registers that form an unpacked 3772 -** record. 3772 +** record. If P4<0, then P3 holds a blob constructed by MakeRecord, but 3773 +** only the first |P4| fields should be considered. 3773 3774 ** 3774 3775 ** Cursor P1 is on an index btree. If the record identified by P3 and P4 3775 3776 ** contains any NULL value, jump immediately to P2. If all terms of the 3776 3777 ** record are not-NULL then a check is done to determine if any row in the 3777 3778 ** P1 index btree has a matching key prefix. If there are no matches, jump 3778 3779 ** immediately to P2. If there is a match, fall through and leave the P1 3779 3780 ** cursor pointing to the matching row. ................................................................................ 3830 3831 pIdxKey = sqlite3VdbeAllocUnpackedRecord( 3831 3832 pC->pKeyInfo, aTempRec, sizeof(aTempRec), &pFree 3832 3833 ); 3833 3834 if( pIdxKey==0 ) goto no_mem; 3834 3835 assert( pIn3->flags & MEM_Blob ); 3835 3836 ExpandBlob(pIn3); 3836 3837 sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey); 3838 + if( pOp->p4.i<0 ){ 3839 + pIdxKey->nField = pOp->p4.i * -1; 3840 + } 3837 3841 } 3838 3842 pIdxKey->default_rc = 0; 3839 3843 if( pOp->opcode==OP_NoConflict ){ 3840 3844 /* For the OP_NoConflict opcode, take the jump if any of the 3841 3845 ** input fields are NULL, since any key with a NULL will not 3842 3846 ** conflict */ 3843 3847 for(ii=0; ii<pIdxKey->nField; ii++){ ................................................................................ 3844 3848 if( pIdxKey->aMem[ii].flags & MEM_Null ){ 3845 3849 pc = pOp->p2 - 1; VdbeBranchTaken(1,2); 3846 3850 break; 3847 3851 } 3848 3852 } 3849 3853 } 3850 3854 rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res); 3851 - if( pOp->p4.i==0 ){ 3855 + if( pOp->p4.i<=0 ){ 3852 3856 sqlite3DbFree(db, pFree); 3853 3857 } 3854 3858 if( rc!=SQLITE_OK ){ 3855 3859 break; 3856 3860 } 3857 3861 pC->seekResult = res; 3858 3862 alreadyExists = (res==0);
Changes to test/e_vacuum.test.
25 25 26 26 db transaction { 27 27 execsql { PRAGMA page_size = 1024; } 28 28 execsql $sql 29 29 execsql { 30 30 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); 31 31 INSERT INTO t1 VALUES(1, randomblob(400)); 32 - INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1; 33 - INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1; 34 - INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1; 35 - INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1; 36 - INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1; 37 - INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1; 38 - INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1; 32 + INSERT OR FAIL INTO t1 SELECT a+1, randomblob(400) FROM t1; 33 + INSERT OR FAIL INTO t1 SELECT a+2, randomblob(400) FROM t1; 34 + INSERT OR FAIL INTO t1 SELECT a+4, randomblob(400) FROM t1; 35 + INSERT OR FAIL INTO t1 SELECT a+8, randomblob(400) FROM t1; 36 + INSERT OR FAIL INTO t1 SELECT a+16, randomblob(400) FROM t1; 37 + INSERT OR FAIL INTO t1 SELECT a+32, randomblob(400) FROM t1; 38 + INSERT OR FAIL INTO t1 SELECT a+64, randomblob(400) FROM t1; 39 39 40 40 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); 41 - INSERT INTO t2 SELECT * FROM t1; 41 + INSERT OR FAIL INTO t2 SELECT * FROM t1; 42 42 } 43 43 } 44 44 45 45 return [expr {[file size test.db] / 1024}] 46 46 } 47 47 48 48 # This proc returns the number of contiguous blocks of pages that make up ................................................................................ 123 123 # less fragmented. 124 124 # 125 125 ifcapable vtab&&compound { 126 126 create_db 127 127 register_dbstat_vtab db 128 128 do_execsql_test e_vacuum-1.2.1 { 129 129 DELETE FROM t1 WHERE a%2; 130 - INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2; 130 + INSERT OR REPLACE INTO t1 SELECT b, a FROM t2 WHERE a%2; 131 131 UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0; 132 132 } {} 133 133 134 134 do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1 135 135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1 136 136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1 137 137
Added test/insert6.test.
1 +# 2015 March 20 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# The tests in this file ensure that sorter objects are used by 13 +# "INSERT INTO ... SELECT ..." statements when possible. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 +set testprefix insert6 19 + 20 +# Return the number of OP_SorterOpen instructions in the SQL passed as 21 +# the only argument if it is compiled using connection [db]. 22 +# 23 +proc sorter_count {sql} { 24 + set res 0 25 + db cache flush 26 + db eval "EXPLAIN $sql" x { 27 + if {$x(opcode) == "SorterOpen"} { incr res } 28 + } 29 + return $res 30 +} 31 + 32 + 33 +#------------------------------------------------------------------------- 34 +# Warm body test. This verifies that the simplest case works for both 35 +# regular and WITHOUT ROWID tables. 36 +# 37 +do_execsql_test 1.1 { 38 + CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z); 39 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 ) 40 + INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt; 41 +} 42 + 43 +foreach {tn nSort schema} { 44 + 1 3 { CREATE TABLE t1(a, b, c) } 45 + 2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID } 46 +} { 47 + 48 + do_test 1.$tn.1 { 49 + execsql { DROP TABLE IF EXISTS t1 } 50 + execsql $schema 51 + } {} 52 + 53 + do_execsql_test 1.$tn.2 { 54 + CREATE INDEX t1a ON t1(a); 55 + CREATE INDEX t1b ON t1(b); 56 + CREATE INDEX t1c ON t1(c); 57 + } 58 + 59 + do_execsql_test 1.$tn.3 { 60 + INSERT INTO t1 SELECT x, y, z FROM t2; 61 + PRAGMA integrity_check; 62 + SELECT count(*) FROM t1; 63 + } {ok 100} 64 + 65 + do_execsql_test 1.$tn.4 { 66 + INSERT INTO t1 SELECT -x, y, z FROM t2; 67 + PRAGMA integrity_check; 68 + } {ok} 69 + 70 + do_execsql_test 1.$tn.5 { 71 + SELECT count(*) FROM t1; 72 + } {200} 73 + 74 + do_test 1.$tn.6 { 75 + sorter_count { INSERT INTO t1 SELECT * FROM t2 } 76 + } $nSort 77 +} 78 + 79 +#------------------------------------------------------------------------- 80 +# The following test cases check that the sorters are disabled if any 81 +# of the following are true: 82 +# 83 +# 2.1: There are one or more UNIQUE constraints or indexes and the 84 +# statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE". 85 +# 86 +# 2.2: The statement does not explicitly specify a conflict mode and 87 +# there are one or more PRIMARY KEY or UNIQUE constraints with 88 +# "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling 89 +# mode. 90 +# 91 +# 2.3: There are one or more INSERT triggers on the target table. 92 +# 93 +# 2.4: The target table is the parent or child of an FK constraint. 94 +# 95 + 96 +do_execsql_test 2.1.1 { 97 + CREATE TABLE x1(a, b, c); 98 + CREATE INDEX x1a ON x1(a); 99 + 100 + CREATE TABLE x2(a, b, c); 101 + CREATE UNIQUE INDEX x2a ON x2(a); 102 + 103 + CREATE TABLE x3(a PRIMARY KEY, b, c); 104 + CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID; 105 +} 106 + 107 +do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 1 108 +do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0 109 +do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0 110 +do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0 111 + 112 +do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 1 113 +do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0 114 +do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0 115 +do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0 116 + 117 +do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 1 118 +do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0 119 +do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0 120 +do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0 121 + 122 +do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1 123 +do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1 124 +do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1 125 +do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1 126 + 127 +do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1 128 +do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1 129 +do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1 130 +do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1 131 + 132 + 133 +foreach {tn scount schema} { 134 + 2.1 0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) } 135 + 2.2 0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) } 136 + 2.3 0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) } 137 + 2.4 0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) } 138 + 2.5 0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) } 139 + 2.6 0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) } 140 + 2.7 0 { 141 + CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID 142 + } 143 + 2.8 0 { 144 + CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID 145 + } 146 + 2.9 0 { 147 + CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID 148 + } 149 + 150 + 3.1 1 { 151 + CREATE TABLE t1(a, b, c); 152 + CREATE INDEX i1 ON t1(a); 153 + } 154 + 3.2 0 { 155 + CREATE TABLE t1(a, b, c); 156 + CREATE INDEX i1 ON t1(a); 157 + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END; 158 + } 159 + 3.3 0 { 160 + CREATE TABLE t1(a, b, c); 161 + CREATE INDEX i1 ON t1(a); 162 + CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END; 163 + } 164 + 165 + 4.1 2 { 166 + CREATE TABLE t1(a PRIMARY KEY, b, c); 167 + CREATE INDEX i1 ON t1(a); 168 + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); 169 + PRAGMA foreign_keys = 0; 170 + } 171 + 4.2 0 { 172 + CREATE TABLE t1(a PRIMARY KEY, b, c); 173 + CREATE INDEX i1 ON t1(a); 174 + CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); 175 + PRAGMA foreign_keys = 1; 176 + } 177 + 178 + 4.3 1 { 179 + CREATE TABLE p1(x, y UNIQUE); 180 + CREATE TABLE t1(a, b, c REFERENCES p1(y)); 181 + CREATE INDEX i1 ON t1(a); 182 + PRAGMA foreign_keys = 0; 183 + } 184 + 4.4 0 { 185 + CREATE TABLE p1(x, y UNIQUE); 186 + CREATE TABLE t1(a, b, c REFERENCES p1(y)); 187 + CREATE INDEX i1 ON t1(a); 188 + PRAGMA foreign_keys = 1; 189 + } 190 + 191 +} { 192 + execsql { 193 + DROP TABLE IF EXISTS t1; 194 + DROP TABLE IF EXISTS c1; 195 + DROP TABLE IF EXISTS p1; 196 + } 197 + 198 + do_test 2.2.$tn { 199 + execsql $schema 200 + sorter_count { INSERT INTO t1 SELECT * FROM t2 } 201 + } $scount 202 +} 203 + 204 +#------------------------------------------------------------------------- 205 +# Test that if a UNIQUE constraint is violated and the on conflict mode 206 +# is either ABORT or ROLLBACK, the conflict is handled correctly. 207 +# 208 +# 3.2: Check that conflicts are actually detected. 209 +# 3.3: Check that OR ROLLBACK really does rollback the transaction. 210 +# 3.4: Check that OR ABORT does not. 211 +# 212 +do_execsql_test 3.1 { 213 + DROP TABLE IF EXISTS t1; 214 + CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c)); 215 + INSERT INTO t1 VALUES(1, 2, 3); 216 + INSERT INTO t1 VALUES(4, 5, 6); 217 + INSERT INTO t1 VALUES(7, 8, 9); 218 + CREATE TABLE src(a, b, c); 219 +} 220 + 221 +do_catchsql_test 3.2.1 { 222 + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); 223 + INSERT INTO t1 SELECT * FROM src; 224 +} {1 {UNIQUE constraint failed: t1.a}} 225 + 226 +do_catchsql_test 3.2.2 { 227 + DELETE FROM src; 228 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 229 + INSERT INTO t1 SELECT * FROM src; 230 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 231 + 232 +do_catchsql_test 3.2.3.1 { 233 + CREATE TABLE t3(a); 234 + CREATE UNIQUE INDEX t3a ON t3(a); 235 + 236 + CREATE TABLE t3src(a); 237 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 ) 238 + INSERT INTO t3src SELECT 'abc' FROM cnt; 239 +} {0 {}} 240 + 241 +# execsql { PRAGMA vdbe_trace = 1 } 242 +do_catchsql_test 3.2.3.2 { 243 + INSERT INTO t3 SELECT * FROM t3src; 244 +} {1 {UNIQUE constraint failed: t3.a}} 245 + 246 +do_catchsql_test 3.3.1 { 247 + DELETE FROM src; 248 + BEGIN; 249 + INSERT INTO src VALUES (10, 11, 12), (7, 13, 14); 250 + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; 251 +} {1 {UNIQUE constraint failed: t1.a}} 252 +do_catchsql_test 3.3.2 { 253 + DELETE FROM src; 254 + BEGIN; 255 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 256 + INSERT OR ROLLBACK INTO t1 SELECT * FROM src; 257 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 258 +do_test 3.3.3 { 259 + sqlite3_get_autocommit db 260 +} 1 261 + 262 +do_catchsql_test 3.4.1 { 263 + DELETE FROM src; 264 + BEGIN; 265 + INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); 266 + INSERT OR ABORT INTO t1 SELECT * FROM src; 267 +} {1 {UNIQUE constraint failed: t1.a}} 268 +do_catchsql_test 3.4.2 { 269 + ROLLBACK; 270 + DELETE FROM src; 271 + BEGIN; 272 + INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); 273 + INSERT OR ABORT INTO t1 SELECT * FROM src; 274 +} {1 {UNIQUE constraint failed: t1.b, t1.c}} 275 +do_test 3.4.3 { 276 + sqlite3_get_autocommit db 277 +} 0 278 +do_execsql_test 3.4.4 { ROLLBACK } 279 + 280 +#------------------------------------------------------------------------- 281 +# The following tests - 4.* - check that this optimization is actually 282 +# doing something helpful. They do this by executing a big 283 +# "INSERT INTO SELECT" statement in wal mode with a small pager cache. 284 +# Once with "OR FAIL" (so that the sorters are not used) and once with 285 +# the default "OR ABORT" (so that they are). 286 +# 287 +# If the sorters are doing their job, the wal file generated by the 288 +# "OR ABORT" case should be much smaller than the "OR FAIL" trial. 289 +# 290 + 291 +proc odd_collate {lhs rhs} { 292 + string compare [string range $lhs 6 end] [string range $rhs 6 end] 293 +} 294 + 295 +proc do_insert6_4_test {tn sql} { 296 + 297 + reset_db 298 + db collate odd_collate odd_collate 299 + execsql $sql 300 + db_save_and_close 301 + 302 + foreach {tn2 ::onerror ::var} { 303 + 1 "OR ABORT" ::sz1 304 + 2 "OR FAIL" ::sz2 305 + } { 306 + do_test $tn.$tn2 { 307 + db_restore_and_reopen 308 + db collate odd_collate odd_collate 309 + execsql " 310 + PRAGMA journal_mode = wal; 311 + PRAGMA cache_size = 5; 312 + PRAGMA wal_autocheckpoint = 0; 313 + INSERT $onerror INTO t1 SELECT * FROM src; 314 + " 315 + set $var [file size test.db-wal] 316 + db close 317 + } {} 318 + } 319 + 320 + do_test $tn.3.($::sz1<$::sz2) { 321 + expr {$sz1 < ($sz2/2)} 322 + } 1 323 + 324 + sqlite3 db test.db 325 + db collate odd_collate odd_collate 326 + integrity_check $tn.4 327 +} 328 + 329 +do_insert6_4_test 4.1 { 330 + CREATE TABLE t1(a, b, c); 331 + CREATE UNIQUE INDEX t1a ON t1(a); 332 + CREATE UNIQUE INDEX t1bc ON t1(b, c); 333 + 334 + CREATE TABLE src(x, y, z); 335 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 336 + INSERT INTO src 337 + SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt; 338 +} 339 + 340 +do_insert6_4_test 4.2 { 341 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x); 342 + CREATE UNIQUE INDEX t1b ON t1(b); 343 + CREATE INDEX t1x1 ON t1(x); 344 + CREATE INDEX t1x2 ON t1(x); 345 + CREATE INDEX t1x3 ON t1(x); 346 + CREATE INDEX t1x4 ON t1(x); 347 + 348 + CREATE TABLE src(a, b, x); 349 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 350 + INSERT INTO src 351 + SELECT random(), x, zeroblob(50) FROM cnt; 352 +} 353 + 354 +do_insert6_4_test 4.3 { 355 + CREATE TABLE t1(a, b, c); 356 + CREATE UNIQUE INDEX t1ab ON t1(a, b); 357 + CREATE UNIQUE INDEX t1ac ON t1(a, c); 358 + 359 + CREATE TABLE src(a, b, c); 360 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 361 + INSERT INTO src 362 + SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt; 363 +} 364 + 365 +db collate odd_collate odd_collate 366 +do_insert6_4_test 4.5 { 367 + CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate); 368 + CREATE UNIQUE INDEX t1t ON t1(t); 369 + CREATE UNIQUE INDEX t1v ON t1(v); 370 + 371 + CREATE TABLE src(t, v); 372 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 373 + INSERT INTO src 374 + SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt; 375 +} 376 + 377 +db collate odd_collate odd_collate 378 +do_insert6_4_test 4.6 { 379 + CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID; 380 + CREATE TABLE src(t); 381 + WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) 382 + INSERT INTO src 383 + SELECT hex(randomblob(50)) FROM cnt; 384 +} 385 + 386 +finish_test 387 +
Changes to test/stat.test.
72 72 DROP TABLE t1; 73 73 } 74 74 } {} 75 75 76 76 do_execsql_test stat-2.1 { 77 77 CREATE TABLE t3(a PRIMARY KEY, b); 78 78 INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222)); 79 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 79 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 80 + ORDER BY rowid; 81 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 80 82 ORDER BY rowid; 81 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 83 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 82 84 ORDER BY rowid; 83 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 85 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 84 86 ORDER BY rowid; 85 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 86 - ORDER BY rowid; 87 - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 87 + INSERT OR FAIL INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 88 88 ORDER BY rowid; 89 89 SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload 90 90 FROM stat WHERE name != 'sqlite_master'; 91 91 } [list \ 92 92 sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \ 93 93 sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \ 94 94 sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \
Changes to test/wal.test.
691 691 execsql { INSERT INTO t1 VALUES( blob(900) ) } 692 692 list [expr [file size test.db]/1024] [file size test.db-wal] 693 693 } [list 3 [wal_file_size 4 1024]] 694 694 695 695 do_test wal-11.4 { 696 696 execsql { 697 697 BEGIN; 698 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 2 699 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 4 700 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 8 701 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 16 698 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 2 699 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 4 700 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 8 701 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 16 702 702 } 703 703 list [expr [file size test.db]/1024] [file size test.db-wal] 704 704 } [list 3 [wal_file_size 32 1024]] 705 705 do_test wal-11.5 { 706 706 execsql { 707 707 SELECT count(*) FROM t1; 708 708 PRAGMA integrity_check; ................................................................................ 730 730 set nWal 39 731 731 if {[permutation]!="mmap"} {set nWal 37} 732 732 ifcapable !mmap {set nWal 37} 733 733 do_test wal-11.10 { 734 734 execsql { 735 735 PRAGMA cache_size = 10; 736 736 BEGIN; 737 - INSERT INTO t1 SELECT blob(900) FROM t1; -- 32 737 + INSERT OR FAIL INTO t1 SELECT blob(900) FROM t1; -- 32 738 738 SELECT count(*) FROM t1; 739 739 } 740 740 list [expr [file size test.db]/1024] [file size test.db-wal] 741 741 } [list 37 [wal_file_size $nWal 1024]] 742 742 do_test wal-11.11 { 743 743 execsql { 744 744 SELECT count(*) FROM t1;