/ Check-in [0a7f2051]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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: 0a7f2051b2cc9b4e060fd6081587502124e16c9e
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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;