/ Changes On Branch insert-select-opt
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch insert-select-opt Excluding Merge-Ins

This is equivalent to a diff from 0a72726da2 to 08c0b19b89

2015-05-21
01:04
Do not allow virtual table constructors to be called recursively. Cherrypick [0a72726da21581ab] (check-in: 0f0694e424 user: drh tags: branch-3.7.11)
2015-05-20
19:50
Do not allow virtual table constructors to be called recursively. Cherrypick of [0a72726da215] (check-in: 023a29baf0 user: dan tags: branch-3.8.6)
2015-04-10
12:04
Fix foreign key CASCADE for cases where the parent key is an INTEGER PRIMARY KEY and the parent table contains other columns named "rowid", "_rowid_", and "oid". (check-in: ed3cbaab6a user: drh tags: trunk)
08:28
Update this branch with the latest changes from sorter-opt. (Leaf check-in: 08c0b19b89 user: dan tags: insert-select-opt)
08:20
Update this branch with latest trunk changes. (check-in: 60be9c1c1a user: dan tags: sorter-opt)
07:55
Do not allow virtual table constructors to be called recursively. (check-in: 0a72726da2 user: dan tags: trunk)
2015-04-09
19:39
Fix incorrect column names in UPDATE statements generated by the sqldiff utility. (check-in: ee53b46011 user: drh tags: trunk)
2015-03-30
15:45
Merge sorter optimizations with this branch. (check-in: 9bf1cfb4d9 user: dan tags: insert-select-opt)

Changes to src/build.c.

  2759   2759       sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord,
  2760   2760                            pIndex->nKeyCol); VdbeCoverage(v);
  2761   2761       sqlite3UniqueConstraint(pParse, OE_Abort, pIndex);
  2762   2762     }else{
  2763   2763       addr2 = sqlite3VdbeCurrentAddr(v);
  2764   2764     }
  2765   2765     sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx);
  2766         -  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 1);
         2766  +  sqlite3VdbeAddOp3(v, OP_Last, iIdx, 0, -1);
         2767  +  sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 0);
  2767   2768     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  2768   2769     sqlite3ReleaseTempReg(pParse, regRecord);
  2769   2770     sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v);
  2770   2771     sqlite3VdbeJumpHere(v, addr1);
  2771   2772   
  2772   2773     sqlite3VdbeAddOp1(v, OP_Close, iTab);
  2773   2774     sqlite3VdbeAddOp1(v, OP_Close, iIdx);

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 and PRIMARY KEY indexes, 
          786  +    **      including INTEGER PRIMARY KEYs, is either ROLLBACK or ABORT.
          787  +    */
          788  +    if( pSelect 
          789  +     && 0==(pSelect->selFlags & SF_Values)
          790  +     && onError!=OE_Fail && onError!=OE_Replace && onError!=OE_Ignore
          791  +     && !IsVirtual(pTab) 
          792  +     && pTrigger==0 
          793  +     && 0==sqlite3FkRequired(pParse, pTab, 0, 0) 
          794  +    ){
          795  +      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          796  +        u8 oe = idxConflictMode(pIdx, onError);
          797  +        if( oe==OE_Fail || oe==OE_Replace || oe==OE_Ignore ) break;
          798  +        assert( oe==OE_None || oe==OE_Abort || oe==OE_Rollback );
          799  +      }
          800  +      if( pIdx==0 ){
          801  +        /* This statement can sort the set of new keys for each index before
          802  +        ** writing them into the b-tree on disk. So open a sorter for each
          803  +        ** index on the table. */
          804  +        iSortCur = pParse->nTab;
          805  +        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          806  +          sqlite3VdbeAddOp1(v, OP_SorterOpen, pParse->nTab++);
          807  +          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          808  +        }
          809  +        assert( iSortCur>0 );
          810  +      }
          811  +    }
   758    812     }
   759    813   
   760    814     /* This is the top of the main insertion loop */
   761    815     if( useTempTable ){
   762    816       /* This block codes the top of loop only.  The complete loop is the
   763    817       ** following pseudocode (template 4):
   764    818       **
................................................................................
   952   1006         sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
   953   1007         sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
   954   1008         sqlite3MayAbort(pParse);
   955   1009       }else
   956   1010   #endif
   957   1011       {
   958   1012         int isReplace;    /* Set to true if constraints may cause a replace */
         1013  +      int iIdxBase = iIdxCur;
         1014  +      int op = OP_IdxInsert;
   959   1015         sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   960         -          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace
         1016  +          regIns, 0, ipkColumn>=0, onError, endOfLoop, iSortCur!=0, &isReplace
   961   1017         );
         1018  +      if( iSortCur ){
         1019  +        iIdxBase = iSortCur;
         1020  +        isReplace = 1;
         1021  +        op = OP_SorterInsert;
         1022  +      }
   962   1023         sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
   963         -      sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
   964         -                               regIns, aRegIdx, 0, appendFlag, isReplace==0);
         1024  +      sqlite3CompleteInsertion(pParse, pTab, 
         1025  +          iDataCur, iIdxBase, regIns, op, aRegIdx, 0, appendFlag, isReplace==0
         1026  +      );
   965   1027       }
   966   1028     }
   967   1029   
   968   1030     /* Update the count of rows that are inserted
   969   1031     */
   970   1032     if( (db->flags & SQLITE_CountRows)!=0 ){
   971   1033       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
................................................................................
   987   1049       sqlite3VdbeAddOp1(v, OP_Close, srcTab);
   988   1050     }else if( pSelect ){
   989   1051       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont);
   990   1052       sqlite3VdbeJumpHere(v, addrInsTop);
   991   1053     }
   992   1054   
   993   1055     if( !IsVirtual(pTab) && !isView ){
         1056  +    /* If new index keys were written into sorter objects instead of
         1057  +    ** directly to the index b-trees, copy them from the sorters into the
         1058  +    ** indexes now. And close all the sorters. */
         1059  +    if( iSortCur ){
         1060  +      int iTmp = sqlite3GetTempReg(pParse);
         1061  +      for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
         1062  +        int oe = idxConflictMode(pIdx, onError);
         1063  +        int iCur = iSortCur + idx;
         1064  +        int iIdx = iIdxCur + idx;
         1065  +        int addr = sqlite3VdbeAddOp1(v, OP_SorterSort, iCur);
         1066  +        sqlite3VdbeAddOp3(v, OP_SorterData, iCur, iTmp, iIdx);
         1067  +        if( oe!=OE_None ){
         1068  +          int nField = -1 * pIdx->nKeyCol;
         1069  +          int jmp = sqlite3VdbeCurrentAddr(v)+2;
         1070  +          sqlite3VdbeAddOp4Int(v, OP_NoConflict, iIdx, jmp, iTmp, nField);
         1071  +          sqlite3UniqueConstraint(pParse, oe, pIdx);
         1072  +        }
         1073  +        sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, iTmp); 
         1074  +        if( oe!=OE_None ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
         1075  +        sqlite3VdbeAddOp2(v, OP_SorterNext, iCur, addr+1); VdbeCoverage(v);
         1076  +        sqlite3VdbeJumpHere(v, addr);
         1077  +        sqlite3VdbeAddOp1(v, OP_Close, iCur);
         1078  +      }
         1079  +      sqlite3ReleaseTempReg(pParse, iTmp);
         1080  +    }
         1081  +
   994   1082       /* Close all tables opened */
   995   1083       if( iDataCur<iIdxCur ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur);
   996   1084       for(idx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
   997   1085         sqlite3VdbeAddOp1(v, OP_Close, idx+iIdxCur);
   998   1086       }
   999   1087     }
  1000   1088   
................................................................................
  1129   1217     int iDataCur,        /* Canonical data cursor (main table or PK index) */
  1130   1218     int iIdxCur,         /* First index cursor */
  1131   1219     int regNewData,      /* First register in a range holding values to insert */
  1132   1220     int regOldData,      /* Previous content.  0 for INSERTs */
  1133   1221     u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  1134   1222     u8 overrideError,    /* Override onError to this if not OE_Default */
  1135   1223     int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
         1224  +  int ignoreUnique,    /* Do not enforce UNIQUE constraints */
  1136   1225     int *pbMayReplace    /* OUT: Set to true if constraint may cause a replace */
  1137   1226   ){
  1138   1227     Vdbe *v;             /* VDBE under constrution */
  1139   1228     Index *pIdx;         /* Pointer to one of the indices */
  1140   1229     Index *pPk = 0;      /* The PRIMARY KEY index */
  1141   1230     sqlite3 *db;         /* Database connection */
  1142   1231     int i;               /* loop counter */
................................................................................
  1409   1498       ** logic below can all be skipped. */
  1410   1499       if( isUpdate && pPk==pIdx && pkChng==0 ){
  1411   1500         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1412   1501         continue;
  1413   1502       }
  1414   1503   
  1415   1504       /* Find out what action to take in case there is a uniqueness conflict */
  1416         -    onError = pIdx->onError;
  1417         -    if( onError==OE_None ){ 
         1505  +    onError = idxConflictMode(pIdx, overrideError);
         1506  +    if( onError==OE_None || ignoreUnique ){ 
  1418   1507         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);
  1419   1508         sqlite3VdbeResolveLabel(v, addrUniqueOk);
  1420   1509         continue;  /* pIdx is not a UNIQUE index */
  1421   1510       }
  1422         -    if( overrideError!=OE_Default ){
  1423         -      onError = overrideError;
  1424         -    }else if( onError==OE_Default ){
  1425         -      onError = OE_Abort;
  1426         -    }
  1427   1511       
  1428   1512       /* Check to see if the new index entry will be unique */
  1429   1513       sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
  1430   1514                            regIdx, pIdx->nKeyCol); VdbeCoverage(v);
  1431   1515   
  1432   1516       /* Generate code to handle collisions */
  1433   1517       regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
................................................................................
  1534   1618   */
  1535   1619   void sqlite3CompleteInsertion(
  1536   1620     Parse *pParse,      /* The parser context */
  1537   1621     Table *pTab,        /* the table into which we are inserting */
  1538   1622     int iDataCur,       /* Cursor of the canonical data source */
  1539   1623     int iIdxCur,        /* First index cursor */
  1540   1624     int regNewData,     /* Range of content */
         1625  +  int idxop,          /* Opcode to use to write to "indexes" */
  1541   1626     int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  1542   1627     int isUpdate,       /* True for UPDATE, False for INSERT */
  1543   1628     int appendBias,     /* True if this is likely to be an append */
  1544   1629     int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
  1545   1630   ){
  1546   1631     Vdbe *v;            /* Prepared statements under construction */
  1547   1632     Index *pIdx;        /* An index being inserted or updated */
  1548   1633     u8 pik_flags;       /* flag values passed to the btree insert */
  1549   1634     int regData;        /* Content registers (after the rowid) */
  1550   1635     int regRec;         /* Register holding assembled record for the table */
  1551   1636     int i;              /* Loop counter */
  1552   1637     u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */
  1553   1638   
         1639  +  assert( idxop==OP_IdxInsert || idxop==OP_SorterInsert );
         1640  +
  1554   1641     v = sqlite3GetVdbe(pParse);
  1555   1642     assert( v!=0 );
  1556   1643     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1557   1644     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1558   1645       if( aRegIdx[i]==0 ) continue;
  1559   1646       bAffinityDone = 1;
  1560   1647       if( pIdx->pPartIdxWhere ){
  1561   1648         sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
  1562   1649         VdbeCoverage(v);
  1563   1650       }
  1564         -    sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
         1651  +    sqlite3VdbeAddOp2(v, idxop, iIdxCur+i, aRegIdx[i]);
  1565   1652       pik_flags = 0;
  1566   1653       if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
  1567   1654       if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
  1568   1655         assert( pParse->nested==0 );
  1569   1656         pik_flags |= OPFLAG_NCHANGE;
  1570   1657       }
  1571   1658       if( pik_flags )  sqlite3VdbeChangeP5(v, pik_flags);
................................................................................
  1761   1848   static int xferOptimization(
  1762   1849     Parse *pParse,        /* Parser context */
  1763   1850     Table *pDest,         /* The table we are inserting into */
  1764   1851     Select *pSelect,      /* A SELECT statement to use as the data source */
  1765   1852     int onError,          /* How to handle constraint errors */
  1766   1853     int iDbDest           /* The database of pDest */
  1767   1854   ){
         1855  +  sqlite3 *db = pParse->db;
  1768   1856     ExprList *pEList;                /* The result set of the SELECT */
  1769   1857     Table *pSrc;                     /* The table in the FROM clause of SELECT */
  1770   1858     Index *pSrcIdx, *pDestIdx;       /* Source and destination indices */
  1771   1859     struct SrcList_item *pItem;      /* An element of pSelect->pSrc */
  1772   1860     int i;                           /* Loop counter */
  1773   1861     int iDbSrc;                      /* The database of pSrc */
  1774   1862     int iSrc, iDest;                 /* Cursors from source and destination */
................................................................................
  1908   1996     /* Disallow the transfer optimization if the destination table constains
  1909   1997     ** any foreign key constraints.  This is more restrictive than necessary.
  1910   1998     ** But the main beneficiary of the transfer optimization is the VACUUM 
  1911   1999     ** command, and the VACUUM command disables foreign key constraints.  So
  1912   2000     ** the extra complication to make this rule less restrictive is probably
  1913   2001     ** not worth the effort.  Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
  1914   2002     */
  1915         -  if( (pParse->db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
         2003  +  if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
  1916   2004       return 0;
  1917   2005     }
  1918   2006   #endif
  1919         -  if( (pParse->db->flags & SQLITE_CountRows)!=0 ){
         2007  +  if( (db->flags & SQLITE_CountRows)!=0 ){
  1920   2008       return 0;  /* xfer opt does not play well with PRAGMA count_changes */
  1921   2009     }
  1922   2010   
  1923   2011     /* If we get this far, it means that the xfer optimization is at
  1924   2012     ** least a possibility, though it might only work if the destination
  1925   2013     ** table (tab1) is initially empty.
  1926   2014     */
  1927   2015   #ifdef SQLITE_TEST
  1928   2016     sqlite3_xferopt_count++;
  1929   2017   #endif
  1930         -  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
         2018  +  iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema);
  1931   2019     v = sqlite3GetVdbe(pParse);
  1932   2020     sqlite3CodeVerifySchema(pParse, iDbSrc);
  1933   2021     iSrc = pParse->nTab++;
  1934   2022     iDest = pParse->nTab++;
  1935   2023     regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
  1936   2024     regData = sqlite3GetTempReg(pParse);
  1937   2025     regRowid = sqlite3GetTempReg(pParse);
  1938   2026     sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  1939   2027     assert( HasRowid(pDest) || destHasUniqueIdx );
  1940         -  if( (pDest->iPKey<0 && pDest->pIndex!=0)          /* (1) */
         2028  +  if( (db->flags & SQLITE_Vacuum)==0 && (
         2029  +      (pDest->iPKey<0 && pDest->pIndex!=0)          /* (1) */
  1941   2030      || destHasUniqueIdx                              /* (2) */
  1942   2031      || (onError!=OE_Abort && onError!=OE_Rollback)   /* (3) */
  1943         -  ){
         2032  +  )){
  1944   2033       /* In some circumstances, we are able to run the xfer optimization
  1945         -    ** only if the destination table is initially empty.  This code makes
  1946         -    ** that determination.  Conditions under which the destination must
  1947         -    ** be empty:
         2034  +    ** only if the destination table is initially empty. Unless the
         2035  +    ** SQLITE_Vacuum flag is set, this block generates code to make
         2036  +    ** that determination. If SQLITE_Vacuum is set, then the destination
         2037  +    ** table is always empty.
         2038  +    **
         2039  +    ** Conditions under which the destination must be empty:
  1948   2040       **
  1949   2041       ** (1) There is no INTEGER PRIMARY KEY but there are indices.
  1950   2042       **     (If the destination is not initially empty, the rowid fields
  1951   2043       **     of index entries might need to change.)
  1952   2044       **
  1953   2045       ** (2) The destination has a unique index.  (The xfer optimization 
  1954   2046       **     is unable to test uniqueness.)
................................................................................
  1983   2075       sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
  1984   2076       sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
  1985   2077     }else{
  1986   2078       sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);
  1987   2079       sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName);
  1988   2080     }
  1989   2081     for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
         2082  +    u8 useSeekResult = 0;
  1990   2083       for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
  1991   2084         if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
  1992   2085       }
  1993   2086       assert( pSrcIdx );
  1994   2087       sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc);
  1995   2088       sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);
  1996   2089       VdbeComment((v, "%s", pSrcIdx->zName));
  1997   2090       sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest);
  1998   2091       sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
  1999   2092       sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
  2000   2093       VdbeComment((v, "%s", pDestIdx->zName));
  2001   2094       addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
  2002   2095       sqlite3VdbeAddOp2(v, OP_RowKey, iSrc, regData);
         2096  +    if( db->flags & SQLITE_Vacuum ){
         2097  +      /* This INSERT command is part of a VACUUM operation, which guarantees
         2098  +      ** that the destination table is empty. If all indexed columns use
         2099  +      ** collation sequence BINARY, then it can also be assumed that the
         2100  +      ** index will be populated by inserting keys in strictly sorted 
         2101  +      ** order. In this case, instead of seeking within the b-tree as part
         2102  +      ** of every OP_IdxInsert opcode, an OP_Last is added before the
         2103  +      ** OP_IdxInsert to seek to the point within the b-tree where each key 
         2104  +      ** should be inserted. This is faster.
         2105  +      **
         2106  +      ** If any of the indexed columns use a collation sequence other than
         2107  +      ** BINARY, this optimization is disabled. This is because the user 
         2108  +      ** might change the definition of a collation sequence and then run
         2109  +      ** a VACUUM command. In that case keys may not be written in strictly
         2110  +      ** sorted order.  */
         2111  +      int i;
         2112  +      for(i=0; i<pSrcIdx->nColumn; i++){
         2113  +        char *zColl = pSrcIdx->azColl[i];
         2114  +        if( zColl && sqlite3_stricmp("BINARY", zColl) ) break;
         2115  +      }
         2116  +      if( i==pSrcIdx->nColumn ){
         2117  +        useSeekResult = OPFLAG_USESEEKRESULT;
         2118  +        sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1);
         2119  +      }
         2120  +    }
  2003   2121       sqlite3VdbeAddOp3(v, OP_IdxInsert, iDest, regData, 1);
         2122  +    sqlite3VdbeChangeP5(v, useSeekResult);
  2004   2123       sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
  2005   2124       sqlite3VdbeJumpHere(v, addr1);
  2006   2125       sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
  2007   2126       sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
  2008   2127     }
  2009   2128     if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest);
  2010   2129     sqlite3ReleaseTempReg(pParse, regRowid);

Changes to src/sqliteInt.h.

  1222   1222   #define SQLITE_AutoIndex      0x00100000  /* Enable automatic indexes */
  1223   1223   #define SQLITE_PreferBuiltin  0x00200000  /* Preference to built-in funcs */
  1224   1224   #define SQLITE_LoadExtension  0x00400000  /* Enable load_extension */
  1225   1225   #define SQLITE_EnableTrigger  0x00800000  /* True to enable triggers */
  1226   1226   #define SQLITE_DeferFKs       0x01000000  /* Defer all FK constraints */
  1227   1227   #define SQLITE_QueryOnly      0x02000000  /* Disable database changes */
  1228   1228   #define SQLITE_VdbeEQP        0x04000000  /* Debug EXPLAIN QUERY PLAN */
         1229  +#define SQLITE_Vacuum         0x08000000  /* Currently in a VACUUM */
  1229   1230   
  1230   1231   
  1231   1232   /*
  1232   1233   ** Bits of the sqlite3.dbOptFlags field that are used by the
  1233   1234   ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
  1234   1235   ** selectively disable various optimizations.
  1235   1236   */
................................................................................
  3349   3350   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
  3350   3351   int sqlite3IsRowid(const char*);
  3351   3352   void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
  3352   3353   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
  3353   3354   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
  3354   3355   void sqlite3ResolvePartIdxLabel(Parse*,int);
  3355   3356   void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
  3356         -                                     u8,u8,int,int*);
  3357         -void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
         3357  +                                     u8,u8,int,int,int*);
         3358  +void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int,int*,int,int,int);
  3358   3359   int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*);
  3359   3360   void sqlite3BeginWriteOperation(Parse*, int, int);
  3360   3361   void sqlite3MultiWrite(Parse*);
  3361   3362   void sqlite3MayAbort(Parse*);
  3362   3363   void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
  3363   3364   void sqlite3UniqueConstraint(Parse*, int, Index*);
  3364   3365   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/vacuum.c.

   246    246         "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
   247    247     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   248    248   
   249    249     /* Loop through the tables in the main database. For each, do
   250    250     ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
   251    251     ** the contents to the temporary database.
   252    252     */
          253  +  assert( (db->flags & SQLITE_Vacuum)==0 );
          254  +  db->flags |= SQLITE_Vacuum;
   253    255     rc = execExecSql(db, pzErrMsg,
   254    256         "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
   255    257         "|| ' SELECT * FROM main.' || quote(name) || ';'"
   256    258         "FROM main.sqlite_master "
   257    259         "WHERE type = 'table' AND name!='sqlite_sequence' "
   258    260         "  AND coalesce(rootpage,1)>0"
   259    261     );
          262  +  assert( (db->flags & SQLITE_Vacuum)!=0 );
          263  +  db->flags &= ~SQLITE_Vacuum;
   260    264     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   261    265   
   262    266     /* Copy over the sequence table
   263    267     */
   264    268     rc = execExecSql(db, pzErrMsg,
   265    269         "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
   266    270         "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "

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(
................................................................................
  3788   3788   ** See also: Found, NotExists, NoConflict
  3789   3789   */
  3790   3790   /* Opcode: NoConflict P1 P2 P3 P4 *
  3791   3791   ** Synopsis: key=r[P3@P4]
  3792   3792   **
  3793   3793   ** If P4==0 then register P3 holds a blob constructed by MakeRecord.  If
  3794   3794   ** P4>0 then register P3 is the first of P4 registers that form an unpacked
  3795         -** record.
         3795  +** record. If P4<0, then P3 holds a blob constructed by MakeRecord, but
         3796  +** only the first |P4| fields should be considered.
  3796   3797   ** 
  3797   3798   ** Cursor P1 is on an index btree.  If the record identified by P3 and P4
  3798   3799   ** contains any NULL value, jump immediately to P2.  If all terms of the
  3799   3800   ** record are not-NULL then a check is done to determine if any row in the
  3800   3801   ** P1 index btree has a matching key prefix.  If there are no matches, jump
  3801   3802   ** immediately to P2.  If there is a match, fall through and leave the P1
  3802   3803   ** cursor pointing to the matching row.
................................................................................
  3853   3854       pIdxKey = sqlite3VdbeAllocUnpackedRecord(
  3854   3855           pC->pKeyInfo, aTempRec, sizeof(aTempRec), &pFree
  3855   3856       );
  3856   3857       if( pIdxKey==0 ) goto no_mem;
  3857   3858       assert( pIn3->flags & MEM_Blob );
  3858   3859       ExpandBlob(pIn3);
  3859   3860       sqlite3VdbeRecordUnpack(pC->pKeyInfo, pIn3->n, pIn3->z, pIdxKey);
         3861  +    if( pOp->p4.i<0 ){
         3862  +      pIdxKey->nField = pOp->p4.i * -1;
         3863  +    }
  3860   3864     }
  3861   3865     pIdxKey->default_rc = 0;
  3862   3866     if( pOp->opcode==OP_NoConflict ){
  3863   3867       /* For the OP_NoConflict opcode, take the jump if any of the
  3864   3868       ** input fields are NULL, since any key with a NULL will not
  3865   3869       ** conflict */
  3866   3870       for(ii=0; ii<pIdxKey->nField; ii++){
................................................................................
  3867   3871         if( pIdxKey->aMem[ii].flags & MEM_Null ){
  3868   3872           pc = pOp->p2 - 1; VdbeBranchTaken(1,2);
  3869   3873           break;
  3870   3874         }
  3871   3875       }
  3872   3876     }
  3873   3877     rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, pIdxKey, 0, 0, &res);
  3874         -  if( pOp->p4.i==0 ){
         3878  +  if( pOp->p4.i<=0 ){
  3875   3879       sqlite3DbFree(db, pFree);
  3876   3880     }
  3877   3881     if( rc!=SQLITE_OK ){
  3878   3882       break;
  3879   3883     }
  3880   3884     pC->seekResult = res;
  3881   3885     alreadyExists = (res==0);
................................................................................
  4482   4486     pC->cacheStatus = CACHE_STALE;
  4483   4487     if( pC->pCursor ){
  4484   4488       sqlite3BtreeClearCursor(pC->pCursor);
  4485   4489     }
  4486   4490     break;
  4487   4491   }
  4488   4492   
  4489         -/* Opcode: Last P1 P2 * * *
         4493  +/* Opcode: Last P1 P2 P3 * *
  4490   4494   **
  4491   4495   ** The next use of the Rowid or Column or Prev instruction for P1 
  4492   4496   ** will refer to the last entry in the database table or index.
  4493   4497   ** If the table or index is empty and P2>0, then jump immediately to P2.
  4494   4498   ** If P2 is 0 or if the table or index is not empty, fall through
  4495   4499   ** to the following instruction.
  4496   4500   **
................................................................................
  4509   4513     pCrsr = pC->pCursor;
  4510   4514     res = 0;
  4511   4515     assert( pCrsr!=0 );
  4512   4516     rc = sqlite3BtreeLast(pCrsr, &res);
  4513   4517     pC->nullRow = (u8)res;
  4514   4518     pC->deferredMoveto = 0;
  4515   4519     pC->cacheStatus = CACHE_STALE;
         4520  +  pC->seekResult = pOp->p3;
  4516   4521   #ifdef SQLITE_DEBUG
  4517   4522     pC->seekOp = OP_Last;
  4518   4523   #endif
  4519   4524     if( pOp->p2>0 ){
  4520   4525       VdbeBranchTaken(res!=0,2);
  4521   4526       if( res ) pc = pOp->p2 - 1;
  4522   4527     }

Changes to src/vdbe.h.

   209    209   #ifndef SQLITE_OMIT_TRACE
   210    210     char *sqlite3VdbeExpandSql(Vdbe*, const char*);
   211    211   #endif
   212    212   int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*);
   213    213   
   214    214   void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*);
   215    215   int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*);
          216  +int sqlite3VdbeRecordCompareWithSkip(int, const void *, UnpackedRecord *, int);
   216    217   UnpackedRecord *sqlite3VdbeAllocUnpackedRecord(KeyInfo *, char *, int, char **);
   217    218   
   218    219   typedef int (*RecordCompare)(int,const void*,UnpackedRecord*);
   219    220   RecordCompare sqlite3VdbeFindCompare(UnpackedRecord*);
   220    221   
   221    222   #ifndef SQLITE_OMIT_TRIGGER
   222    223   void sqlite3VdbeLinkSubProgram(Vdbe *, SubProgram *);

Changes to src/vdbeaux.c.

  3581   3581   ** returned.
  3582   3582   **
  3583   3583   ** If database corruption is discovered, set pPKey2->errCode to 
  3584   3584   ** SQLITE_CORRUPT and return 0. If an OOM error is encountered, 
  3585   3585   ** pPKey2->errCode is set to SQLITE_NOMEM and, if it is not NULL, the
  3586   3586   ** malloc-failed flag set on database handle (pPKey2->pKeyInfo->db).
  3587   3587   */
  3588         -static int vdbeRecordCompareWithSkip(
         3588  +int sqlite3VdbeRecordCompareWithSkip(
  3589   3589     int nKey1, const void *pKey1,   /* Left key */
  3590   3590     UnpackedRecord *pPKey2,         /* Right key */
  3591   3591     int bSkip                       /* If true, skip the first field */
  3592   3592   ){
  3593   3593     u32 d1;                         /* Offset into aKey[] of next data element */
  3594   3594     int i;                          /* Index of next field to compare */
  3595   3595     u32 szHdr1;                     /* Size of record header in bytes */
................................................................................
  3767   3767     );
  3768   3768     return pPKey2->default_rc;
  3769   3769   }
  3770   3770   int sqlite3VdbeRecordCompare(
  3771   3771     int nKey1, const void *pKey1,   /* Left key */
  3772   3772     UnpackedRecord *pPKey2          /* Right key */
  3773   3773   ){
  3774         -  return vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0);
         3774  +  return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0);
  3775   3775   }
  3776   3776   
  3777   3777   
  3778   3778   /*
  3779   3779   ** This function is an optimized version of sqlite3VdbeRecordCompare() 
  3780   3780   ** that (a) the first field of pPKey2 is an integer, and (b) the 
  3781   3781   ** size-of-header varint at the start of (pKey1/nKey1) fits in a single
................................................................................
  3855   3855     if( v>lhs ){
  3856   3856       res = pPKey2->r1;
  3857   3857     }else if( v<lhs ){
  3858   3858       res = pPKey2->r2;
  3859   3859     }else if( pPKey2->nField>1 ){
  3860   3860       /* The first fields of the two keys are equal. Compare the trailing 
  3861   3861       ** fields.  */
  3862         -    res = vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
         3862  +    res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
  3863   3863     }else{
  3864   3864       /* The first fields of the two keys are equal and there are no trailing
  3865   3865       ** fields. Return pPKey2->default_rc in this case. */
  3866   3866       res = pPKey2->default_rc;
  3867   3867     }
  3868   3868   
  3869   3869     assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) );
................................................................................
  3903   3903       nCmp = MIN( pPKey2->aMem[0].n, nStr );
  3904   3904       res = memcmp(&aKey1[szHdr], pPKey2->aMem[0].z, nCmp);
  3905   3905   
  3906   3906       if( res==0 ){
  3907   3907         res = nStr - pPKey2->aMem[0].n;
  3908   3908         if( res==0 ){
  3909   3909           if( pPKey2->nField>1 ){
  3910         -          res = vdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
         3910  +          res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
  3911   3911           }else{
  3912   3912             res = pPKey2->default_rc;
  3913   3913           }
  3914   3914         }else if( res>0 ){
  3915   3915           res = pPKey2->r2;
  3916   3916         }else{
  3917   3917           res = pPKey2->r1;

Changes to src/vdbesort.c.

   287    287   **      to sqlite3ThreadJoin() is likely to block. Cases that are likely to
   288    288   **      block provoke debugging output.
   289    289   **
   290    290   ** In both cases, the effects of the main thread seeing (bDone==0) even
   291    291   ** after the thread has finished are not dire. So we don't worry about
   292    292   ** memory barriers and such here.
   293    293   */
          294  +typedef int (*SorterCompare)(SortSubtask*,int*,const void*,int,const void*,int);
   294    295   struct SortSubtask {
   295    296     SQLiteThread *pThread;          /* Background thread, if any */
   296    297     int bDone;                      /* Set if thread is finished but not joined */
   297    298     VdbeSorter *pSorter;            /* Sorter that owns this sub-task */
   298    299     UnpackedRecord *pUnpacked;      /* Space to unpack a record */
   299    300     SorterList list;                /* List for thread to write to a PMA */
   300    301     int nPMA;                       /* Number of PMAs currently in file */
          302  +  SorterCompare xCompare;         /* Compare function to use */
   301    303     SorterFile file;                /* Temp file for level-0 PMAs */
   302    304     SorterFile file2;               /* Space for other PMAs */
   303    305   };
          306  +
   304    307   
   305    308   /*
   306    309   ** Main sorter structure. A single instance of this is allocated for each 
   307    310   ** sorter cursor created by the VDBE.
   308    311   **
   309    312   ** mxKeysize:
   310    313   **   As records are added to the sorter by calls to sqlite3VdbeSorterWrite(),
................................................................................
   324    327     SorterList list;                /* List of in-memory records */
   325    328     int iMemory;                    /* Offset of free space in list.aMemory */
   326    329     int nMemory;                    /* Size of list.aMemory allocation in bytes */
   327    330     u8 bUsePMA;                     /* True if one or more PMAs created */
   328    331     u8 bUseThreads;                 /* True to use background threads */
   329    332     u8 iPrev;                       /* Previous thread used to flush PMA */
   330    333     u8 nTask;                       /* Size of aTask[] array */
          334  +  u8 typeMask;
   331    335     SortSubtask aTask[1];           /* One or more subtasks */
   332    336   };
          337  +
          338  +#define SORTER_TYPE_INTEGER 0x01
          339  +#define SORTER_TYPE_TEXT    0x02
   333    340   
   334    341   /*
   335    342   ** An instance of the following object is used to read records out of a
   336    343   ** PMA, in sorted order.  The next key to be read is cached in nKey/aKey.
   337    344   ** aKey might point into aMap or into aBuffer.  If neither of those locations
   338    345   ** contain a contiguous representation of the key, then aAlloc is allocated
   339    346   ** and the key is copied into aAlloc and aKey is made to poitn to aAlloc.
................................................................................
   738    745   
   739    746     if( rc==SQLITE_OK ){
   740    747       rc = vdbePmaReaderNext(pReadr);
   741    748     }
   742    749     return rc;
   743    750   }
   744    751   
          752  +/*
          753  +** A version of vdbeSorterCompare() that assumes that it has already been
          754  +** determined that the first field of key1 is equal to the first field of 
          755  +** key2.
          756  +*/
          757  +static int vdbeSorterCompareTail(
          758  +  SortSubtask *pTask,             /* Subtask context (for pKeyInfo) */
          759  +  int *pbKey2Cached,              /* True if pTask->pUnpacked is pKey2 */
          760  +  const void *pKey1, int nKey1,   /* Left side of comparison */
          761  +  const void *pKey2, int nKey2    /* Right side of comparison */
          762  +){
          763  +  UnpackedRecord *r2 = pTask->pUnpacked;
          764  +  if( *pbKey2Cached==0 ){
          765  +    sqlite3VdbeRecordUnpack(pTask->pSorter->pKeyInfo, nKey2, pKey2, r2);
          766  +    *pbKey2Cached = 1;
          767  +  }
          768  +  return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, r2, 1);
          769  +}
   745    770   
   746    771   /*
   747    772   ** Compare key1 (buffer pKey1, size nKey1 bytes) with key2 (buffer pKey2, 
   748    773   ** size nKey2 bytes). Use (pTask->pKeyInfo) for the collation sequences
   749    774   ** used by the comparison. Return the result of the comparison.
   750    775   **
   751         -** Before returning, object (pTask->pUnpacked) is populated with the
   752         -** unpacked version of key2. Or, if pKey2 is passed a NULL pointer, then it 
   753         -** is assumed that the (pTask->pUnpacked) structure already contains the 
   754         -** unpacked key to use as key2.
          776  +** If IN/OUT parameter *pbKey2Cached is true when this function is called,
          777  +** it is assumed that (pTask->pUnpacked) contains the unpacked version
          778  +** of key2. If it is false, (pTask->pUnpacked) is populated with the unpacked
          779  +** version of key2 and *pbKey2Cached set to true before returning.
   755    780   **
   756    781   ** If an OOM error is encountered, (pTask->pUnpacked->error_rc) is set
   757    782   ** to SQLITE_NOMEM.
   758    783   */
   759    784   static int vdbeSorterCompare(
   760    785     SortSubtask *pTask,             /* Subtask context (for pKeyInfo) */
          786  +  int *pbKey2Cached,              /* True if pTask->pUnpacked is pKey2 */
   761    787     const void *pKey1, int nKey1,   /* Left side of comparison */
   762    788     const void *pKey2, int nKey2    /* Right side of comparison */
   763    789   ){
   764    790     UnpackedRecord *r2 = pTask->pUnpacked;
   765         -  if( pKey2 ){
          791  +  if( !*pbKey2Cached ){
   766    792       sqlite3VdbeRecordUnpack(pTask->pSorter->pKeyInfo, nKey2, pKey2, r2);
          793  +    *pbKey2Cached = 1;
   767    794     }
   768    795     return sqlite3VdbeRecordCompare(nKey1, pKey1, r2);
   769    796   }
          797  +
          798  +/*
          799  +** A specially optimized version of vdbeSorterCompare() that assumes that
          800  +** the first field of each key is a TEXT value and that the collation
          801  +** sequence to compare them with is BINARY.
          802  +*/
          803  +static int vdbeSorterCompareText(
          804  +  SortSubtask *pTask,             /* Subtask context (for pKeyInfo) */
          805  +  int *pbKey2Cached,              /* True if pTask->pUnpacked is pKey2 */
          806  +  const void *pKey1, int nKey1,   /* Left side of comparison */
          807  +  const void *pKey2, int nKey2    /* Right side of comparison */
          808  +){
          809  +  const u8 * const p1 = (const u8 * const)pKey1;
          810  +  const u8 * const p2 = (const u8 * const)pKey2;
          811  +  const u8 * const v1 = &p1[ p1[0] ];   /* Pointer to value 1 */
          812  +  const u8 * const v2 = &p2[ p2[0] ];   /* Pointer to value 2 */
          813  +
          814  +  int n1;
          815  +  int n2;
          816  +  int res;
          817  +
          818  +  getVarint32(&p1[1], n1); n1 = (n1 - 13) / 2;
          819  +  getVarint32(&p2[1], n2); n2 = (n2 - 13) / 2;
          820  +  res = memcmp(v1, v2, MIN(n1, n2));
          821  +  if( res==0 ){
          822  +    res = n1 - n2;
          823  +  }
          824  +
          825  +  if( res==0 ){
          826  +    if( pTask->pSorter->pKeyInfo->nField>1 ){
          827  +      res = vdbeSorterCompareTail(
          828  +          pTask, pbKey2Cached, pKey1, nKey1, pKey2, nKey2
          829  +      );
          830  +    }
          831  +  }else{
          832  +    if( pTask->pSorter->pKeyInfo->aSortOrder[0] ){
          833  +      res = res * -1;
          834  +    }
          835  +  }
          836  +
          837  +  return res;
          838  +}
          839  +
          840  +/*
          841  +** A specially optimized version of vdbeSorterCompare() that assumes that
          842  +** the first field of each key is an INTEGER value.
          843  +*/
          844  +static int vdbeSorterCompareInt(
          845  +  SortSubtask *pTask,             /* Subtask context (for pKeyInfo) */
          846  +  int *pbKey2Cached,              /* True if pTask->pUnpacked is pKey2 */
          847  +  const void *pKey1, int nKey1,   /* Left side of comparison */
          848  +  const void *pKey2, int nKey2    /* Right side of comparison */
          849  +){
          850  +  const u8 * const p1 = (const u8 * const)pKey1;
          851  +  const u8 * const p2 = (const u8 * const)pKey2;
          852  +  const int s1 = p1[1];                 /* Left hand serial type */
          853  +  const int s2 = p2[1];                 /* Right hand serial type */
          854  +  const u8 * const v1 = &p1[ p1[0] ];   /* Pointer to value 1 */
          855  +  const u8 * const v2 = &p2[ p2[0] ];   /* Pointer to value 2 */
          856  +  int res;                              /* Return value */
          857  +
          858  +  assert( (s1>0 && s1<7) || s1==8 || s1==9 );
          859  +  assert( (s2>0 && s2<7) || s2==8 || s2==9 );
          860  +
          861  +  if( s1>7 && s2>7 ){
          862  +    res = s1 - s2;
          863  +  }else{
          864  +    if( s1==s2 ){
          865  +      if( (*v1 ^ *v2) & 0x80 ){
          866  +        /* The two values have different signs */
          867  +        res = (*v1 & 0x80) ? -1 : +1;
          868  +      }else{
          869  +        /* The two values have the same sign. Compare using memcmp(). */
          870  +        static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 };
          871  +        int i;
          872  +        res = 0;
          873  +        for(i=0; i<aLen[s1]; i++){
          874  +          if( (res = v1[i] - v2[i]) ) break;
          875  +        }
          876  +      }
          877  +    }else{
          878  +      if( s2>7 ){
          879  +        res = +1;
          880  +      }else if( s1>7 ){
          881  +        res = -1;
          882  +      }else{
          883  +        res = s1 - s2;
          884  +      }
          885  +
          886  +      if( res>0 ){
          887  +        if( *v1 & 0x80 ) res = -1;
          888  +      }else if( res<0 ){
          889  +        if( *v2 & 0x80 ) res = +1;
          890  +      }
          891  +    }
          892  +  }
          893  +
          894  +  if( res==0 ){
          895  +    if( pTask->pSorter->pKeyInfo->nField>1 ){
          896  +      res = vdbeSorterCompareTail(
          897  +          pTask, pbKey2Cached, pKey1, nKey1, pKey2, nKey2
          898  +      );
          899  +    }
          900  +  }else if( pTask->pSorter->pKeyInfo->aSortOrder[0] ){
          901  +    res = res * -1;
          902  +  }
          903  +
          904  +  return res;
          905  +}
   770    906   
   771    907   /*
   772    908   ** Initialize the temporary index cursor just opened as a sorter cursor.
   773    909   **
   774    910   ** Usually, the sorter module uses the value of (pCsr->pKeyInfo->nField)
   775    911   ** to determine the number of fields that should be compared from the
   776    912   ** records being sorted. However, if the value passed as argument nField
................................................................................
   831    967     pCsr->pSorter = pSorter;
   832    968     if( pSorter==0 ){
   833    969       rc = SQLITE_NOMEM;
   834    970     }else{
   835    971       pSorter->pKeyInfo = pKeyInfo = (KeyInfo*)((u8*)pSorter + sz);
   836    972       memcpy(pKeyInfo, pCsr->pKeyInfo, szKeyInfo);
   837    973       pKeyInfo->db = 0;
   838         -    if( nField && nWorker==0 ) pKeyInfo->nField = nField;
          974  +    if( nField && nWorker==0 ){
          975  +      pKeyInfo->nXField += (pKeyInfo->nField - nField);
          976  +      pKeyInfo->nField = nField;
          977  +    }
   839    978       pSorter->pgsz = pgsz = sqlite3BtreeGetPageSize(db->aDb[0].pBt);
   840    979       pSorter->nTask = nWorker + 1;
          980  +    pSorter->iPrev = nWorker-1;
   841    981       pSorter->bUseThreads = (pSorter->nTask>1);
   842    982       pSorter->db = db;
   843    983       for(i=0; i<pSorter->nTask; i++){
   844    984         SortSubtask *pTask = &pSorter->aTask[i];
   845    985         pTask->pSorter = pSorter;
   846    986       }
   847    987   
................................................................................
   859    999         if( sqlite3GlobalConfig.pScratch==0 ){
   860   1000           assert( pSorter->iMemory==0 );
   861   1001           pSorter->nMemory = pgsz;
   862   1002           pSorter->list.aMemory = (u8*)sqlite3Malloc(pgsz);
   863   1003           if( !pSorter->list.aMemory ) rc = SQLITE_NOMEM;
   864   1004         }
   865   1005       }
         1006  +
         1007  +    if( (pKeyInfo->nField+pKeyInfo->nXField)<13 
         1008  +     && (pKeyInfo->aColl[0]==0 || pKeyInfo->aColl[0]==db->pDfltColl)
         1009  +    ){
         1010  +      pSorter->typeMask = SORTER_TYPE_INTEGER | SORTER_TYPE_TEXT;
         1011  +    }
   866   1012     }
   867   1013   
   868   1014     return rc;
   869   1015   }
   870   1016   #undef nWorker   /* Defined at the top of this function */
   871   1017   
   872   1018   /*
................................................................................
  1195   1341     SortSubtask *pTask,             /* Calling thread context */
  1196   1342     SorterRecord *p1,               /* First list to merge */
  1197   1343     SorterRecord *p2,               /* Second list to merge */
  1198   1344     SorterRecord **ppOut            /* OUT: Head of merged list */
  1199   1345   ){
  1200   1346     SorterRecord *pFinal = 0;
  1201   1347     SorterRecord **pp = &pFinal;
  1202         -  void *pVal2 = p2 ? SRVAL(p2) : 0;
         1348  +  int bCached = 0;
  1203   1349   
  1204   1350     while( p1 && p2 ){
  1205   1351       int res;
  1206         -    res = vdbeSorterCompare(pTask, SRVAL(p1), p1->nVal, pVal2, p2->nVal);
         1352  +    res = pTask->xCompare(
         1353  +        pTask, &bCached, SRVAL(p1), p1->nVal, SRVAL(p2), p2->nVal
         1354  +    );
         1355  +
  1207   1356       if( res<=0 ){
  1208   1357         *pp = p1;
  1209   1358         pp = &p1->u.pNext;
  1210   1359         p1 = p1->u.pNext;
  1211         -      pVal2 = 0;
  1212   1360       }else{
  1213   1361         *pp = p2;
  1214         -       pp = &p2->u.pNext;
         1362  +      pp = &p2->u.pNext;
  1215   1363         p2 = p2->u.pNext;
  1216         -      if( p2==0 ) break;
  1217         -      pVal2 = SRVAL(p2);
         1364  +      bCached = 0;
  1218   1365       }
  1219   1366     }
  1220   1367     *pp = p1 ? p1 : p2;
  1221   1368     *ppOut = pFinal;
  1222   1369   }
         1370  +
         1371  +/*
         1372  +** Return the SorterCompare function to compare values collected by the
         1373  +** sorter object passed as the only argument.
         1374  +*/
         1375  +static SorterCompare vdbeSorterGetCompare(VdbeSorter *p){
         1376  +  if( p->typeMask==SORTER_TYPE_INTEGER ){
         1377  +    return vdbeSorterCompareInt;
         1378  +  }else if( p->typeMask==SORTER_TYPE_TEXT ){
         1379  +    return vdbeSorterCompareText; 
         1380  +  }
         1381  +  return vdbeSorterCompare;
         1382  +}
  1223   1383   
  1224   1384   /*
  1225   1385   ** Sort the linked list of records headed at pTask->pList. Return 
  1226   1386   ** SQLITE_OK if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if 
  1227   1387   ** an error occurs.
  1228   1388   */
  1229   1389   static int vdbeSorterSort(SortSubtask *pTask, SorterList *pList){
................................................................................
  1231   1391     SorterRecord **aSlot;
  1232   1392     SorterRecord *p;
  1233   1393     int rc;
  1234   1394   
  1235   1395     rc = vdbeSortAllocUnpacked(pTask);
  1236   1396     if( rc!=SQLITE_OK ) return rc;
  1237   1397   
         1398  +  p = pList->pList;
         1399  +  pTask->xCompare = vdbeSorterGetCompare(pTask->pSorter);
         1400  +
  1238   1401     aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *));
  1239   1402     if( !aSlot ){
  1240   1403       return SQLITE_NOMEM;
  1241   1404     }
  1242   1405   
  1243         -  p = pList->pList;
  1244   1406     while( p ){
  1245   1407       SorterRecord *pNext;
  1246   1408       if( pList->aMemory ){
  1247   1409         if( (u8*)p==pList->aMemory ){
  1248   1410           pNext = 0;
  1249   1411         }else{
  1250   1412           assert( p->u.iNext<sqlite3MallocSize(pList->aMemory) );
................................................................................
  1450   1612     rc = vdbePmaReaderNext(&pMerger->aReadr[iPrev]);
  1451   1613   
  1452   1614     /* Update contents of aTree[] */
  1453   1615     if( rc==SQLITE_OK ){
  1454   1616       int i;                      /* Index of aTree[] to recalculate */
  1455   1617       PmaReader *pReadr1;         /* First PmaReader to compare */
  1456   1618       PmaReader *pReadr2;         /* Second PmaReader to compare */
  1457         -    u8 *pKey2;                  /* To pReadr2->aKey, or 0 if record cached */
         1619  +    int bCached = 0;
  1458   1620   
  1459   1621       /* Find the first two PmaReaders to compare. The one that was just
  1460   1622       ** advanced (iPrev) and the one next to it in the array.  */
  1461   1623       pReadr1 = &pMerger->aReadr[(iPrev & 0xFFFE)];
  1462   1624       pReadr2 = &pMerger->aReadr[(iPrev | 0x0001)];
  1463         -    pKey2 = pReadr2->aKey;
  1464   1625   
  1465   1626       for(i=(pMerger->nTree+iPrev)/2; i>0; i=i/2){
  1466   1627         /* Compare pReadr1 and pReadr2. Store the result in variable iRes. */
  1467   1628         int iRes;
  1468   1629         if( pReadr1->pFd==0 ){
  1469   1630           iRes = +1;
  1470   1631         }else if( pReadr2->pFd==0 ){
  1471   1632           iRes = -1;
  1472   1633         }else{
  1473         -        iRes = vdbeSorterCompare(pTask, 
  1474         -            pReadr1->aKey, pReadr1->nKey, pKey2, pReadr2->nKey
         1634  +        iRes = pTask->xCompare(pTask, &bCached,
         1635  +            pReadr1->aKey, pReadr1->nKey, pReadr2->aKey, pReadr2->nKey
  1475   1636           );
  1476   1637         }
  1477   1638   
  1478   1639         /* If pReadr1 contained the smaller value, set aTree[i] to its index.
  1479   1640         ** Then set pReadr2 to the next PmaReader to compare to pReadr1. In this
  1480   1641         ** case there is no cache of pReadr2 in pTask->pUnpacked, so set
  1481   1642         ** pKey2 to point to the record belonging to pReadr2.
................................................................................
  1489   1650         ** If the two values were equal, then the value from the oldest
  1490   1651         ** PMA should be considered smaller. The VdbeSorter.aReadr[] array
  1491   1652         ** is sorted from oldest to newest, so pReadr1 contains older values
  1492   1653         ** than pReadr2 iff (pReadr1<pReadr2).  */
  1493   1654         if( iRes<0 || (iRes==0 && pReadr1<pReadr2) ){
  1494   1655           pMerger->aTree[i] = (int)(pReadr1 - pMerger->aReadr);
  1495   1656           pReadr2 = &pMerger->aReadr[ pMerger->aTree[i ^ 0x0001] ];
  1496         -        pKey2 = pReadr2->aKey;
         1657  +        bCached = 0;
  1497   1658         }else{
  1498         -        if( pReadr1->pFd ) pKey2 = 0;
         1659  +        if( pReadr1->pFd ) bCached = 0;
  1499   1660           pMerger->aTree[i] = (int)(pReadr2 - pMerger->aReadr);
  1500   1661           pReadr1 = &pMerger->aReadr[ pMerger->aTree[i ^ 0x0001] ];
  1501   1662         }
  1502   1663       }
  1503   1664       *pbEof = (pMerger->aReadr[pMerger->aTree[1]].pFd==0);
  1504   1665     }
  1505   1666   
................................................................................
  1598   1759     VdbeSorter *pSorter = pCsr->pSorter;
  1599   1760     int rc = SQLITE_OK;             /* Return Code */
  1600   1761     SorterRecord *pNew;             /* New list element */
  1601   1762   
  1602   1763     int bFlush;                     /* True to flush contents of memory to PMA */
  1603   1764     int nReq;                       /* Bytes of memory required */
  1604   1765     int nPMA;                       /* Bytes of PMA space required */
         1766  +  int t;                          /* serial type of first record field */
         1767  +
         1768  +  getVarint32((const u8*)&pVal->z[1], t);
         1769  +  if( t>0 && t<10 && t!=7 ){
         1770  +    pSorter->typeMask &= SORTER_TYPE_INTEGER;
         1771  +  }else if( t>10 && (t & 0x01) ){
         1772  +    pSorter->typeMask &= SORTER_TYPE_TEXT;
         1773  +  }else{
         1774  +    pSorter->typeMask = 0;
         1775  +  }
  1605   1776   
  1606   1777     assert( pSorter );
  1607   1778   
  1608   1779     /* Figure out whether or not the current contents of memory should be
  1609   1780     ** flushed to a PMA before continuing. If so, do so.
  1610   1781     **
  1611   1782     ** If using the single large allocation mode (pSorter->aMemory!=0), then
................................................................................
  1863   2034     p2 = &pMerger->aReadr[i2];
  1864   2035   
  1865   2036     if( p1->pFd==0 ){
  1866   2037       iRes = i2;
  1867   2038     }else if( p2->pFd==0 ){
  1868   2039       iRes = i1;
  1869   2040     }else{
         2041  +    SortSubtask *pTask = pMerger->pTask;
         2042  +    int bCached = 0;
  1870   2043       int res;
  1871         -    assert( pMerger->pTask->pUnpacked!=0 );  /* from vdbeSortSubtaskMain() */
  1872         -    res = vdbeSorterCompare(
  1873         -        pMerger->pTask, p1->aKey, p1->nKey, p2->aKey, p2->nKey
         2044  +    assert( pTask->pUnpacked!=0 );  /* from vdbeSortSubtaskMain() */
         2045  +    res = pTask->xCompare(
         2046  +        pTask, &bCached, p1->aKey, p1->nKey, p2->aKey, p2->nKey
  1874   2047       );
  1875   2048       if( res<=0 ){
  1876   2049         iRes = i1;
  1877   2050       }else{
  1878   2051         iRes = i2;
  1879   2052       }
  1880   2053     }
................................................................................
  2284   2457   */
  2285   2458   static int vdbeSorterSetupMerge(VdbeSorter *pSorter){
  2286   2459     int rc;                         /* Return code */
  2287   2460     SortSubtask *pTask0 = &pSorter->aTask[0];
  2288   2461     MergeEngine *pMain = 0;
  2289   2462   #if SQLITE_MAX_WORKER_THREADS
  2290   2463     sqlite3 *db = pTask0->pSorter->db;
         2464  +  int i;
         2465  +  SorterCompare xCompare = vdbeSorterGetCompare(pSorter);
         2466  +  for(i=0; i<pSorter->nTask; i++){
         2467  +    pSorter->aTask[i].xCompare = xCompare;
         2468  +  }
  2291   2469   #endif
  2292   2470   
  2293   2471     rc = vdbeSorterMergeTreeBuild(pSorter, &pMain);
  2294   2472     if( rc==SQLITE_OK ){
  2295   2473   #if SQLITE_MAX_WORKER_THREADS
  2296   2474       assert( pSorter->bUseThreads==0 || pSorter->nTask>1 );
  2297   2475       if( pSorter->bUseThreads ){

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: The statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE".
           84  +#
           85  +#   2.2: The statement does not explicitly specify a conflict mode and 
           86  +#        there are one or more PRIMARY KEY or UNIQUE constraints with 
           87  +#        "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling 
           88  +#        mode.
           89  +#
           90  +#   2.3: There are one or more INSERT triggers on the target table.
           91  +#
           92  +#   2.4: The target table is the parent or child of an FK constraint.
           93  +#
           94  +
           95  +do_execsql_test 2.1.1 {
           96  +  CREATE TABLE x1(a, b, c);
           97  +  CREATE INDEX x1a ON x1(a);
           98  +
           99  +  CREATE TABLE x2(a, b, c);
          100  +  CREATE UNIQUE INDEX x2a ON x2(a);
          101  +
          102  +  CREATE TABLE x3(a PRIMARY KEY, b, c);
          103  +  CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID;
          104  +}
          105  +
          106  +do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 0
          107  +do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0
          108  +do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0
          109  +do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0
          110  +
          111  +do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 0
          112  +do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0
          113  +do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0
          114  +do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0
          115  +
          116  +do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 0
          117  +do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0
          118  +do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0
          119  +do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0
          120  +
          121  +do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1
          122  +do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1
          123  +do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1
          124  +do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1
          125  +
          126  +do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1
          127  +do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1
          128  +do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1
          129  +do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1
          130  +
          131  +
          132  +foreach {tn scount schema} {
          133  +  2.1   0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) }
          134  +  2.2   0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) }
          135  +  2.3   0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) }
          136  +  2.4   0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) }
          137  +  2.5   0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) }
          138  +  2.6   0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) }
          139  +  2.7   0 { 
          140  +    CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID
          141  +  }
          142  +  2.8   0 { 
          143  +    CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID
          144  +  }
          145  +  2.9   0 { 
          146  +    CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID
          147  +  }
          148  +
          149  +  3.1   1 {
          150  +    CREATE TABLE t1(a, b, c);
          151  +    CREATE INDEX i1 ON t1(a);
          152  +  }
          153  +  3.2   0 {
          154  +    CREATE TABLE t1(a, b, c);
          155  +    CREATE INDEX i1 ON t1(a);
          156  +    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END;
          157  +  }
          158  +  3.3   0 {
          159  +    CREATE TABLE t1(a, b, c);
          160  +    CREATE INDEX i1 ON t1(a);
          161  +    CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
          162  +  }
          163  +
          164  +  4.1   2 {
          165  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
          166  +    CREATE INDEX i1 ON t1(a);
          167  +    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
          168  +    PRAGMA foreign_keys = 0;
          169  +  }
          170  +  4.2   0 {
          171  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
          172  +    CREATE INDEX i1 ON t1(a);
          173  +    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
          174  +    PRAGMA foreign_keys = 1;
          175  +  }
          176  +
          177  +  4.3   1 {
          178  +    CREATE TABLE p1(x, y UNIQUE);
          179  +    CREATE TABLE t1(a, b, c REFERENCES p1(y));
          180  +    CREATE INDEX i1 ON t1(a);
          181  +    PRAGMA foreign_keys = 0;
          182  +  }
          183  +  4.4   0 {
          184  +    CREATE TABLE p1(x, y UNIQUE);
          185  +    CREATE TABLE t1(a, b, c REFERENCES p1(y));
          186  +    CREATE INDEX i1 ON t1(a);
          187  +    PRAGMA foreign_keys = 1;
          188  +  }
          189  +
          190  +} {
          191  +  execsql { 
          192  +    DROP TABLE IF EXISTS t1;
          193  +    DROP TABLE IF EXISTS c1;
          194  +    DROP TABLE IF EXISTS p1;
          195  +  }
          196  +
          197  +  do_test 2.2.$tn {
          198  +    execsql $schema
          199  +    sorter_count { INSERT INTO t1 SELECT * FROM t2 }
          200  +  } $scount
          201  +}
          202  +
          203  +#-------------------------------------------------------------------------
          204  +# Test that if a UNIQUE constraint is violated and the on conflict mode
          205  +# is either ABORT or ROLLBACK, the conflict is handled correctly.
          206  +#
          207  +#   3.2: Check that conflicts are actually detected. 
          208  +#   3.3: Check that OR ROLLBACK really does rollback the transaction.
          209  +#   3.4: Check that OR ABORT does not.
          210  +#
          211  +do_execsql_test 3.1 {
          212  +  DROP TABLE IF EXISTS t1;
          213  +  CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c));
          214  +  INSERT INTO t1 VALUES(1, 2, 3);
          215  +  INSERT INTO t1 VALUES(4, 5, 6);
          216  +  INSERT INTO t1 VALUES(7, 8, 9);
          217  +  CREATE TABLE src(a, b, c);
          218  +}
          219  +
          220  +do_catchsql_test 3.2.1 {
          221  +  INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
          222  +  INSERT INTO t1 SELECT * FROM src;
          223  +} {1 {UNIQUE constraint failed: t1.a}}
          224  +
          225  +do_catchsql_test 3.2.2 {
          226  +  DELETE FROM src;
          227  +  INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
          228  +  INSERT INTO t1 SELECT * FROM src;
          229  +} {1 {UNIQUE constraint failed: t1.b, t1.c}}
          230  +
          231  +do_catchsql_test 3.2.3.1 {
          232  +  CREATE TABLE t3(a);
          233  +  CREATE UNIQUE INDEX t3a ON t3(a);
          234  +
          235  +  CREATE TABLE t3src(a);
          236  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 )
          237  +  INSERT INTO t3src SELECT 'abc' FROM cnt;
          238  +} {0 {}}
          239  +
          240  +#  execsql { PRAGMA vdbe_trace = 1 }
          241  +do_catchsql_test 3.2.3.2 {
          242  +  INSERT INTO t3 SELECT * FROM t3src;
          243  +} {1 {UNIQUE constraint failed: t3.a}}
          244  +
          245  +do_catchsql_test 3.3.1 {
          246  +  DELETE FROM src;
          247  +  BEGIN;
          248  +    INSERT INTO src VALUES (10, 11, 12), (7, 13, 14);
          249  +    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
          250  +} {1 {UNIQUE constraint failed: t1.a}}
          251  +do_catchsql_test 3.3.2 {
          252  +  DELETE FROM src;
          253  +  BEGIN;
          254  +    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
          255  +    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
          256  +} {1 {UNIQUE constraint failed: t1.b, t1.c}}
          257  +do_test 3.3.3 {
          258  +  sqlite3_get_autocommit db
          259  +} 1
          260  +
          261  +do_catchsql_test 3.4.1 {
          262  +  DELETE FROM src;
          263  +  BEGIN;
          264  +    INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
          265  +    INSERT OR ABORT INTO t1 SELECT * FROM src;
          266  +} {1 {UNIQUE constraint failed: t1.a}}
          267  +do_catchsql_test 3.4.2 {
          268  +  ROLLBACK;
          269  +  DELETE FROM src;
          270  +  BEGIN;
          271  +    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
          272  +    INSERT OR ABORT INTO t1 SELECT * FROM src;
          273  +} {1 {UNIQUE constraint failed: t1.b, t1.c}}
          274  +do_test 3.4.3 {
          275  +  sqlite3_get_autocommit db
          276  +} 0
          277  +do_execsql_test 3.4.4 { ROLLBACK }
          278  +
          279  +#-------------------------------------------------------------------------
          280  +# The following tests - 4.* - check that this optimization is actually
          281  +# doing something helpful. They do this by executing a big 
          282  +# "INSERT INTO SELECT" statement in wal mode with a small pager cache.
          283  +# Once with "OR FAIL" (so that the sorters are not used) and once with
          284  +# the default "OR ABORT" (so that they are).
          285  +#
          286  +# If the sorters are doing their job, the wal file generated by the 
          287  +# "OR ABORT" case should be much smaller than the "OR FAIL" trial.
          288  +#
          289  +
          290  +proc odd_collate {lhs rhs} {
          291  +  string compare [string range $lhs 6 end] [string range $rhs 6 end]
          292  +}
          293  +
          294  +proc do_insert6_4_test {tn sql} {
          295  +
          296  +  reset_db
          297  +  db collate odd_collate odd_collate
          298  +  execsql $sql
          299  +  db_save_and_close
          300  +
          301  +  foreach {tn2 ::onerror ::var} {
          302  +    1 "OR ABORT" ::sz1
          303  +    2 "OR FAIL"  ::sz2
          304  +  } {
          305  +    do_test $tn.$tn2 {
          306  +      db_restore_and_reopen
          307  +      db collate odd_collate odd_collate
          308  +      execsql "
          309  +        PRAGMA journal_mode = wal;
          310  +        PRAGMA cache_size = 5;
          311  +        PRAGMA wal_autocheckpoint = 0;
          312  +        INSERT $onerror INTO t1 SELECT * FROM src;
          313  +      "
          314  +      set $var [file size test.db-wal]
          315  +      db close
          316  +    } {}
          317  +  }
          318  +
          319  +  do_test $tn.3.($::sz1<$::sz2) {
          320  +    expr {$sz1 < ($sz2/2)}
          321  +  } 1
          322  +
          323  +  sqlite3 db test.db
          324  +  db collate odd_collate odd_collate
          325  +  integrity_check $tn.4 
          326  +}
          327  +
          328  +do_insert6_4_test 4.1 {
          329  +  CREATE TABLE t1(a, b, c);
          330  +  CREATE UNIQUE INDEX t1a ON t1(a);
          331  +  CREATE UNIQUE INDEX t1bc ON t1(b, c);
          332  +
          333  +  CREATE TABLE src(x, y, z);
          334  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
          335  +  INSERT INTO src 
          336  +  SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt;
          337  +}
          338  +
          339  +do_insert6_4_test 4.2 {
          340  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x);
          341  +  CREATE UNIQUE INDEX t1b ON t1(b);
          342  +  CREATE INDEX t1x1 ON t1(x);
          343  +  CREATE INDEX t1x2 ON t1(x);
          344  +  CREATE INDEX t1x3 ON t1(x);
          345  +  CREATE INDEX t1x4 ON t1(x);
          346  +
          347  +  CREATE TABLE src(a, b, x);
          348  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
          349  +  INSERT INTO src 
          350  +  SELECT random(), x, zeroblob(50) FROM cnt;
          351  +}
          352  +
          353  +do_insert6_4_test 4.3 {
          354  +  CREATE TABLE t1(a, b, c);
          355  +  CREATE UNIQUE INDEX t1ab ON t1(a, b);
          356  +  CREATE UNIQUE INDEX t1ac ON t1(a, c);
          357  +
          358  +  CREATE TABLE src(a, b, c);
          359  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
          360  +  INSERT INTO src 
          361  +  SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt;
          362  +}
          363  +
          364  +db collate odd_collate odd_collate
          365  +do_insert6_4_test 4.5 {
          366  +  CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate);
          367  +  CREATE UNIQUE INDEX t1t ON t1(t);
          368  +  CREATE UNIQUE INDEX t1v ON t1(v);
          369  +
          370  +  CREATE TABLE src(t, v);
          371  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
          372  +  INSERT INTO src 
          373  +  SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt;
          374  +}
          375  +
          376  +db collate odd_collate odd_collate
          377  +do_insert6_4_test 4.6 {
          378  +  CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID;
          379  +  CREATE TABLE src(t);
          380  +  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
          381  +  INSERT INTO src 
          382  +  SELECT hex(randomblob(50)) FROM cnt;
          383  +}
          384  +
          385  +#-------------------------------------------------------------------------
          386  +# At one point the sorters were used for INSERT statements that specify
          387  +# "OR FAIL", "REPLACE" or "IGNORE" if there were no PRIMARY KEY or
          388  +# UNIQUE indexes. This is incorrect, as all such tables have an implicit
          389  +# IPK column. So using the sorters can cause corruption. This test checks
          390  +# that that problem no longer exists.
          391  +#
          392  +reset_db
          393  +do_execsql_test 5.1 {
          394  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          395  +  CREATE INDEX t1b ON t1(b);
          396  +  INSERT INTO t1 VALUES(1, 2, 3);
          397  +  INSERT INTO t1 VALUES(4, 5, 6);
          398  +}
          399  +
          400  +do_catchsql_test 5.2 {
          401  +  INSERT OR FAIL INTO t1 
          402  +  SELECT 2, 'x', 'x' UNION ALL SELECT 3, 'x', 'x' UNION ALL SELECT 4, 'x', 'x';
          403  +} {1 {UNIQUE constraint failed: t1.a}}
          404  +
          405  +integrity_check 5.3
          406  +
          407  +
          408  +finish_test
          409  +

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;