/ Check-in [8f479a72]
Login

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

Overview
Comment:Enhance the DELETE logic so that it can make use of WHERE_ONEPASS_DESIRED for rowid tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | optimize-delete
Files: files | file ages | folders
SHA1:8f479a72758ab6fedb171ada612b1963143c32fa
User & Date: drh 2013-11-16 20:13:39
Context
2013-11-16
20:45
The one-pass optimization is now working for DELETE on WITHOUT ROWID tables. check-in: e4d220a3 user: drh tags: optimize-delete
20:13
Enhance the DELETE logic so that it can make use of WHERE_ONEPASS_DESIRED for rowid tables. check-in: 8f479a72 user: drh tags: optimize-delete
15:35
Fully constraint the ORDER BY on the top-10 line of the --summary output from the wordcount test program. Add the run-wordcount.bash script for running wordcount in various configurations. check-in: 7edf39eb user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   230    230     int end, addr = 0;     /* A couple addresses of generated code */
   231    231     int i;                 /* Loop counter */
   232    232     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   233    233     Index *pIdx;           /* For looping over indices of the table */
   234    234     int iTabCur;           /* Cursor number for the table */
   235    235     int iDataCur;          /* VDBE cursor for the canonical data source */
   236    236     int iIdxCur;           /* Cursor number of the first index */
          237  +  int nIdx;              /* Number of indices */
   237    238     sqlite3 *db;           /* Main database structure */
   238    239     AuthContext sContext;  /* Authorization context */
   239    240     NameContext sNC;       /* Name context to resolve expressions in */
   240    241     int iDb;               /* Database number */
   241    242     int memCnt = -1;       /* Memory cell used for change counting */
   242    243     int rcauth;            /* Value returned by authorization callback */
          244  +  int okOnePass;         /* True for one-pass algorithm without the FIFO */
          245  +  int aiCurOnePass[2];   /* The write cursors opened by WHERE_ONEPASS */
          246  +  u8 *aToOpen = 0;       /* Open cursor iTabCur+j if aToOpen[j] is true */
   243    247   
   244    248   #ifndef SQLITE_OMIT_TRIGGER
   245    249     int isView;                  /* True if attempting to delete from a view */
   246    250     Trigger *pTrigger;           /* List of table triggers, if required */
   247    251   #endif
   248    252   
   249    253     memset(&sContext, 0, sizeof(sContext));
................................................................................
   291    295     rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb);
   292    296     assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
   293    297     if( rcauth==SQLITE_DENY ){
   294    298       goto delete_from_cleanup;
   295    299     }
   296    300     assert(!isView || pTrigger);
   297    301   
   298         -  /* Assign  cursor number to the table and all its indices.
          302  +  /* Assign cursor numbers to the table and all its indices.
   299    303     */
   300    304     assert( pTabList->nSrc==1 );
   301    305     iTabCur = pTabList->a[0].iCursor = pParse->nTab++;
   302         -  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          306  +  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
   303    307       pParse->nTab++;
   304    308     }
   305    309   
   306    310     /* Start the view context
   307    311     */
   308    312     if( isView ){
   309    313       sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
................................................................................
   395    399       if( db->flags & SQLITE_CountRows ){
   396    400         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   397    401       }
   398    402       sqlite3WhereEnd(pWInfo);
   399    403   
   400    404       /* Open cursors for all indices of the table.
   401    405       */
   402         -    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite,
   403         -                               iTabCur, &iDataCur, &iIdxCur);
          406  +    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
          407  +                               &iDataCur, &iIdxCur);
   404    408   
   405    409       /* Loop over the primary keys to be deleted. */
   406    410       addr = sqlite3VdbeAddOp1(v, OP_Rewind, iEph);
   407    411       sqlite3VdbeAddOp2(v, OP_RowKey, iEph, iPk);
   408    412   
   409    413       /* Delete the row */
   410    414       sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
................................................................................
   420    424         sqlite3VdbeAddOp1(v, OP_Close, iIdxCur+i);
   421    425       }
   422    426     }else{
   423    427       /* There is a WHERE clause on a rowid table.  Run a loop that extracts
   424    428       ** all rowids to be deleted into a RowSet.
   425    429       */
   426    430       int iRowSet = ++pParse->nMem;   /* Register for rowset of rows to delete */
   427         -    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
   428    431       int regRowid;                   /* Actual register containing rowids */
   429    432   
   430    433       /* Collect rowids of every row to be deleted.
   431    434       */
   432    435       sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
   433    436       pWInfo = sqlite3WhereBegin(
   434         -        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0
          437  +        pParse, pTabList, pWhere, 0, 0,
          438  +        WHERE_DUPLICATES_OK|WHERE_ONEPASS_DESIRED, iTabCur+1
   435    439       );
   436    440       if( pWInfo==0 ) goto delete_from_cleanup;
   437         -    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iRowid, 0);
   438         -    sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
          441  +    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   439    442       if( db->flags & SQLITE_CountRows ){
   440    443         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   441    444       }
          445  +    regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur,
          446  +                                        pParse->nMem+1, 0);
          447  +    if( regRowid>pParse->nMem ) pParse->nMem = regRowid;
          448  +    if( okOnePass ){
          449  +      aToOpen = sqlite3DbMallocRaw(db, nIdx+2);
          450  +      if( aToOpen==0 ) goto delete_from_cleanup;
          451  +      memset(aToOpen, 1, nIdx+1);
          452  +      aToOpen[nIdx+1] = 0;
          453  +      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
          454  +      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
          455  +      addr = sqlite3VdbeAddOp0(v, OP_Goto);
          456  +    }else{
          457  +      sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
          458  +    }
   442    459       sqlite3WhereEnd(pWInfo);
          460  +    if( okOnePass ){
          461  +      sqlite3VdbeAddOp0(v, OP_Halt);
          462  +      sqlite3VdbeJumpHere(v, addr);
          463  +    }
   443    464   
   444    465       /* Delete every item whose key was written to the list during the
   445    466       ** database scan.  We have to delete items after the scan is complete
   446    467       ** because deleting an item can change the scan order.  */
   447    468       end = sqlite3VdbeMakeLabel(v);
   448    469   
   449    470       /* Unless this is a view, open cursors for the table we are 
   450    471       ** deleting from and all its indices. If this is a view, then the
   451    472       ** only effect this statement has is to fire the INSTEAD OF 
   452    473       ** triggers.  */
   453    474       if( !isView ){
   454         -      sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur,
          475  +      sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
   455    476                                    &iDataCur, &iIdxCur);
   456    477         assert( iDataCur==iTabCur );
   457    478         assert( iIdxCur==iDataCur+1 );
   458    479       }
   459    480   
   460         -    addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid);
          481  +    if( !okOnePass ){    
          482  +      addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, regRowid);
          483  +    }
   461    484   
   462    485       /* Delete the row */
   463    486   #ifndef SQLITE_OMIT_VIRTUALTABLE
   464    487       if( IsVirtual(pTab) ){
   465    488         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
   466    489         sqlite3VtabMakeWritable(pParse, pTab);
   467         -      sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB);
          490  +      sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, regRowid, pVTab, P4_VTAB);
   468    491         sqlite3VdbeChangeP5(v, OE_Abort);
   469    492         sqlite3MayAbort(pParse);
   470    493       }else
   471    494   #endif
   472    495       {
   473    496         int count = (pParse->nested==0);    /* True to count changes */
   474    497         sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
   475         -                               iRowid, 1, count, OE_Default, 0);
          498  +                               regRowid, 1, count, OE_Default, okOnePass);
   476    499       }
   477    500   
   478    501       /* End of the delete loop */
   479         -    sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          502  +    if( !okOnePass ){
          503  +      sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          504  +    }
   480    505       sqlite3VdbeResolveLabel(v, end);
   481    506   
   482    507       /* Close the cursors open on the table and its indexes. */
   483    508       if( !isView && !IsVirtual(pTab) ){
   484    509         sqlite3VdbeAddOp1(v, OP_Close, iDataCur);
   485    510         for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
   486    511           sqlite3VdbeAddOp1(v, OP_Close, iIdxCur + i);
................................................................................
   506    531       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
   507    532     }
   508    533   
   509    534   delete_from_cleanup:
   510    535     sqlite3AuthContextPop(&sContext);
   511    536     sqlite3SrcListDelete(db, pTabList);
   512    537     sqlite3ExprDelete(db, pWhere);
          538  +  sqlite3DbFree(db, aToOpen);
   513    539     return;
   514    540   }
   515    541   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   516    542   ** thely may interfere with compilation of other functions in this file
   517    543   ** (or in another file, if this file becomes part of the amalgamation).  */
   518    544   #ifdef isView
   519    545    #undef isView

Changes to src/insert.c.

   816    816       regRowCount = ++pParse->nMem;
   817    817       sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
   818    818     }
   819    819   
   820    820     /* If this is not a view, open the table and and all indices */
   821    821     if( !isView ){
   822    822       int nIdx;
   823         -    nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, -1,
          823  +    nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, -1, 0,
   824    824                                         &iDataCur, &iIdxCur);
   825    825       aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1));
   826    826       if( aRegIdx==0 ){
   827    827         goto insert_cleanup;
   828    828       }
   829    829       for(i=0; i<nIdx; i++){
   830    830         aRegIdx[i] = ++pParse->nMem;
................................................................................
  1676   1676   ** pTab->pIndex list.
  1677   1677   */
  1678   1678   int sqlite3OpenTableAndIndices(
  1679   1679     Parse *pParse,   /* Parsing context */
  1680   1680     Table *pTab,     /* Table to be opened */
  1681   1681     int op,          /* OP_OpenRead or OP_OpenWrite */
  1682   1682     int iBase,       /* Use this for the table cursor, if there is one */
         1683  +  u8 *aToOpen,     /* If not NULL: boolean for each table and index */
  1683   1684     int *piDataCur,  /* Write the database source cursor number here */
  1684   1685     int *piIdxCur    /* Write the first index cursor number here */
  1685   1686   ){
  1686   1687     int i;
  1687   1688     int iDb;
         1689  +  int iDataCur;
  1688   1690     Index *pIdx;
  1689   1691     Vdbe *v;
  1690   1692   
  1691   1693     assert( op==OP_OpenRead || op==OP_OpenWrite );
  1692   1694     if( IsVirtual(pTab) ){
         1695  +    assert( aToOpen==0 );
  1693   1696       *piDataCur = 0;
  1694   1697       *piIdxCur = 1;
  1695   1698       return 0;
  1696   1699     }
  1697   1700     iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
  1698   1701     v = sqlite3GetVdbe(pParse);
  1699   1702     assert( v!=0 );
  1700   1703     if( iBase<0 ) iBase = pParse->nTab;
  1701         -  if( HasRowid(pTab) ){
  1702         -    *piDataCur = iBase++;
  1703         -    sqlite3OpenTable(pParse, *piDataCur, iDb, pTab, op);
         1704  +  iDataCur = iBase++;
         1705  +  if( piDataCur ) *piDataCur = iDataCur;
         1706  +  if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){
         1707  +    sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op);
  1704   1708     }else{
  1705   1709       sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
  1706   1710     }
  1707         -  *piIdxCur = iBase;
         1711  +  if( piIdxCur ) *piIdxCur = iBase;
  1708   1712     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1709   1713       int iIdxCur = iBase++;
  1710   1714       assert( pIdx->pSchema==pTab->pSchema );
  1711         -    if( pIdx->autoIndex==2 && !HasRowid(pTab) ) *piDataCur = iIdxCur;
  1712         -    sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
  1713         -    sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1714         -    VdbeComment((v, "%s", pIdx->zName));
         1715  +    if( pIdx->autoIndex==2 && !HasRowid(pTab) && piDataCur ){
         1716  +      *piDataCur = iIdxCur;
         1717  +    }
         1718  +    if( aToOpen==0 || aToOpen[i+1] ){
         1719  +      sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
         1720  +      sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
         1721  +      VdbeComment((v, "%s", pIdx->zName));
         1722  +    }
  1715   1723     }
  1716   1724     if( iBase>pParse->nTab ) pParse->nTab = iBase;
  1717   1725     return i;
  1718   1726   }
  1719   1727   
  1720   1728   
  1721   1729   #ifdef SQLITE_TEST

Changes to src/pragma.c.

  1887   1887           if( pTab->pIndex==0 ) continue;
  1888   1888           pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
  1889   1889           addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1);  /* Stop if out of errors */
  1890   1890           sqlite3VdbeAddOp2(v, OP_Halt, 0, 0);
  1891   1891           sqlite3VdbeJumpHere(v, addr);
  1892   1892           sqlite3ExprCacheClear(pParse);
  1893   1893           sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenRead,
  1894         -                                   1, &iDataCur, &iIdxCur);
         1894  +                                   1, 0, &iDataCur, &iIdxCur);
  1895   1895           sqlite3VdbeAddOp2(v, OP_Integer, 0, 7);
  1896   1896           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1897   1897             sqlite3VdbeAddOp2(v, OP_Integer, 0, 8+j); /* index entries counter */
  1898   1898           }
  1899   1899           pParse->nMem = MAX(pParse->nMem, 8+j);
  1900   1900           sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0);
  1901   1901           loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1);

Changes to src/sqliteInt.h.

  2938   2938   int sqlite3IsRowid(const char*);
  2939   2939   void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
  2940   2940   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
  2941   2941   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*);
  2942   2942   void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
  2943   2943                                        u8,u8,int,int*);
  2944   2944   void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
  2945         -int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, int*, int*);
         2945  +int sqlite3OpenTableAndIndices(Parse*, Table*, int, int, u8*, int*, int*);
  2946   2946   void sqlite3BeginWriteOperation(Parse*, int, int);
  2947   2947   void sqlite3MultiWrite(Parse*);
  2948   2948   void sqlite3MayAbort(Parse*);
  2949   2949   void sqlite3HaltConstraint(Parse*, int, int, char*, i8, u8);
  2950   2950   void sqlite3UniqueConstraint(Parse*, int, Index*);
  2951   2951   void sqlite3RowidConstraint(Parse*, int, Table*);
  2952   2952   Expr *sqlite3ExprDup(sqlite3*,Expr*,int);

Changes to src/update.c.

    97     97     Table *pTab;           /* The table to be updated */
    98     98     int addrTop = 0;       /* VDBE instruction address of the start of the loop */
    99     99     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   100    100     Vdbe *v;               /* The virtual database engine */
   101    101     Index *pIdx;           /* For looping over indices */
   102    102     Index *pPk;            /* The PRIMARY KEY index for WITHOUT ROWID tables */
   103    103     int nIdx;              /* Number of indices that need updating */
          104  +  int iBaseCur;          /* Base cursor number */
   104    105     int iDataCur;          /* Cursor for the canonical data btree */
   105    106     int iIdxCur;           /* Cursor for the first index */
   106    107     sqlite3 *db;           /* The database structure */
   107    108     int *aRegIdx = 0;      /* One register assigned to each index to be updated */
   108    109     int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
   109    110                            ** an expression for the i-th column of the table.
   110    111                            ** aXRef[i]==-1 if the i-th column is not changed. */
          112  +  u8 *aToOpen;           /* 1 for tables and indices to be opened */
   111    113     u8 chngPk;             /* PRIMARY KEY changed in a WITHOUT ROWID table */
   112    114     u8 chngRowid;          /* Rowid changed in a normal table */
   113    115     u8 chngKey;            /* Either chngPk or chngRowid */
   114    116     Expr *pRowidExpr = 0;  /* Expression defining the new record number */
   115    117     int openAll = 0;       /* True if all indices need to be opened */
   116    118     AuthContext sContext;  /* The authorization context */
   117    119     NameContext sNC;       /* The name-context to resolve expressions in */
................................................................................
   172    174   
   173    175     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   174    176       goto update_cleanup;
   175    177     }
   176    178     if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
   177    179       goto update_cleanup;
   178    180     }
   179         -  aXRef = sqlite3DbMallocRaw(db, sizeof(int) * pTab->nCol );
   180         -  if( aXRef==0 ) goto update_cleanup;
   181         -  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
   182    181   
   183    182     /* Allocate a cursors for the main database table and for all indices.
   184    183     ** The index cursors might not be used, but if they are used they
   185    184     ** need to occur right after the database cursor.  So go ahead and
   186    185     ** allocate enough space, just in case.
   187    186     */
   188         -  pTabList->a[0].iCursor = iDataCur = pParse->nTab++;
          187  +  pTabList->a[0].iCursor = iBaseCur = iDataCur = pParse->nTab++;
   189    188     iIdxCur = iDataCur+1;
   190    189     pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
   191    190     for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
   192    191       if( pIdx->autoIndex==2 && pPk!=0 ){
   193    192         iDataCur = pParse->nTab;
   194    193         pTabList->a[0].iCursor = iDataCur;
   195    194       }
   196    195       pParse->nTab++;
   197    196     }
          197  +
          198  +  /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
          199  +  ** Initialize aXRef[] and aToOpen[] to their default values.
          200  +  */
          201  +  aXRef = sqlite3DbMallocRaw(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 );
          202  +  if( aXRef==0 ) goto update_cleanup;
          203  +  aRegIdx = aXRef+pTab->nCol;
          204  +  aToOpen = (u8*)(aRegIdx+nIdx);
          205  +  memset(aToOpen, 1, nIdx+1);
          206  +  aToOpen[nIdx+1] = 0;
          207  +  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
   198    208   
   199    209     /* Initialize the name-context */
   200    210     memset(&sNC, 0, sizeof(sNC));
   201    211     sNC.pParse = pParse;
   202    212     sNC.pSrcList = pTabList;
   203    213   
   204    214     /* Resolve the column names in all the expressions of the
................................................................................
   252    262     assert( (chngRowid & chngPk)==0 );
   253    263     assert( chngRowid==0 || chngRowid==1 );
   254    264     assert( chngPk==0 || chngPk==1 );
   255    265     chngKey = chngRowid + chngPk;
   256    266   
   257    267     hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);
   258    268   
   259         -  /* Allocate memory for the array aRegIdx[].  There is one entry in the
   260         -  ** array for each index associated with table being updated.  Fill in
   261         -  ** the value with a register number for indices that are to be used
   262         -  ** and with zero for unused indices.
          269  +  /* There is one entry in the aRegIdx[] array for each index on the table
          270  +  ** being updated.  Fill in aRegIdx[] with a register number that will hold
          271  +  ** the key for accessing each index.  
   263    272     */
   264         -  if( nIdx>0 ){
   265         -    aRegIdx = sqlite3DbMallocRaw(db, sizeof(Index*) * nIdx );
   266         -    if( aRegIdx==0 ) goto update_cleanup;
   267         -  }
   268    273     for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
   269    274       int reg;
   270    275       if( chngKey || hasFK || pIdx->pPartIdxWhere || pIdx==pPk ){
   271    276         reg = ++pParse->nMem;
   272    277       }else{
   273    278         reg = 0;
   274    279         for(i=0; i<pIdx->nKeyCol; i++){
   275    280           if( aXRef[pIdx->aiColumn[i]]>=0 ){
   276    281             reg = ++pParse->nMem;
   277    282             break;
   278    283           }
   279    284         }
   280    285       }
          286  +    if( reg==0 ) aToOpen[j+1] = 0;
   281    287       aRegIdx[j] = reg;
   282    288     }
   283    289   
   284    290     /* Begin generating code. */
   285    291     v = sqlite3GetVdbe(pParse);
   286    292     if( v==0 ) goto update_cleanup;
   287    293     if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
................................................................................
   397    403     if( !isView ){
   398    404       /* 
   399    405       ** Open every index that needs updating.  Note that if any
   400    406       ** index could potentially invoke a REPLACE conflict resolution 
   401    407       ** action, then we need to open all indices because we might need
   402    408       ** to be deleting some records.
   403    409       */
   404         -    if( !okOnePass && HasRowid(pTab) ){
   405         -      sqlite3OpenTable(pParse, iDataCur, iDb, pTab, OP_OpenWrite); 
   406         -    }
   407         -    sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName);
   408    410       if( onError==OE_Replace ){
   409         -      openAll = 1;
          411  +      memset(aToOpen, 1, nIdx+1);
   410    412       }else{
   411         -      openAll = 0;
   412    413         for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   413    414           if( pIdx->onError==OE_Replace ){
   414         -          openAll = 1;
          415  +          memset(aToOpen, 1, nIdx+1);
   415    416             break;
   416    417           }
   417    418         }
   418    419       }
   419         -    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
   420         -      int iThisCur = iIdxCur+i;
   421         -      assert( aRegIdx );
   422         -      if( (openAll || aRegIdx[i]>0)
   423         -       && iThisCur!=aiCurOnePass[1]
   424         -      ){
   425         -        assert( iThisCur!=aiCurOnePass[0] );
   426         -        sqlite3VdbeAddOp3(v, OP_OpenWrite, iThisCur, pIdx->tnum, iDb);
   427         -        sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
   428         -        assert( pParse->nTab>iThisCur );
   429         -        VdbeComment((v, "%s", pIdx->zName));
   430         -        if( okOnePass && pPk && iThisCur==iDataCur ){
   431         -          sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak,
   432         -                               regKey, nKey);
   433         -        }
   434         -      }
          420  +    if( okOnePass ){
          421  +      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
          422  +      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
   435    423       }
          424  +    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iBaseCur, aToOpen,
          425  +                               0, 0);
   436    426     }
   437    427   
   438    428     /* Top of the update loop */
   439    429     if( okOnePass ){
          430  +    if( pPk ){
          431  +      sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
          432  +    }
   440    433       labelContinue = labelBreak;
   441    434       sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
   442    435     }else if( pPk ){
   443    436       labelContinue = sqlite3VdbeMakeLabel(v);
   444    437       sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak);
   445    438       addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey);
   446    439       sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0);
................................................................................
   652    645       sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
   653    646       sqlite3VdbeSetNumCols(v, 1);
   654    647       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC);
   655    648     }
   656    649   
   657    650   update_cleanup:
   658    651     sqlite3AuthContextPop(&sContext);
   659         -  sqlite3DbFree(db, aRegIdx);
   660         -  sqlite3DbFree(db, aXRef);
          652  +  sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */
   661    653     sqlite3SrcListDelete(db, pTabList);
   662    654     sqlite3ExprListDelete(db, pChanges);
   663    655     sqlite3ExprDelete(db, pWhere);
   664    656     return;
   665    657   }
   666    658   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   667    659   ** thely may interfere with compilation of other functions in this file