/ Check-in [7ae6104a]
Login

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

Overview
Comment:Changes to allow some multi-row UPDATE statements to avoid the two-pass approach.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7ae6104a3e0d1d2cacfe2be732f0220a53908132
User & Date: dan 2017-01-11 21:03:53
References
2018-01-16
12:54 New ticket [47b2581a] Infinite loop on UPDATE. artifact: 0e79b53b user: drh
Context
2017-01-12
11:50
Make sure Tcl_AppendResult() always has a NULL-pointer argument at the end. check-in: c07aef6f user: drh tags: trunk
2017-01-11
21:03
Changes to allow some multi-row UPDATE statements to avoid the two-pass approach. check-in: 7ae6104a user: dan tags: trunk
20:10
Fix a problem causing the pre-update hook to be passed an incorrect rowid value in some single-pass multi-row updates. Closed-Leaf check-in: 62257eb5 user: dan tags: onepass-update
14:15
In the STAT4 computations, ensure that the aAvgEq values do not go negative. check-in: f58f75b5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  7944   7944   ** if pX->nMem is non-zero, then pX->aMem contains pointers to the unpacked
  7945   7945   ** key values and pX->aMem can be used instead of pX->pKey to avoid having
  7946   7946   ** to decode the key.
  7947   7947   */
  7948   7948   int sqlite3BtreeInsert(
  7949   7949     BtCursor *pCur,                /* Insert data into the table of this cursor */
  7950   7950     const BtreePayload *pX,        /* Content of the row to be inserted */
  7951         -  int appendBias,                /* True if this is likely an append */
         7951  +  int flags,                     /* True if this is likely an append */
  7952   7952     int seekResult                 /* Result of prior MovetoUnpacked() call */
  7953   7953   ){
  7954   7954     int rc;
  7955   7955     int loc = seekResult;          /* -1: before desired location  +1: after */
  7956   7956     int szNew = 0;
  7957   7957     int idx;
  7958   7958     MemPage *pPage;
  7959   7959     Btree *p = pCur->pBtree;
  7960   7960     BtShared *pBt = p->pBt;
  7961   7961     unsigned char *oldCell;
  7962   7962     unsigned char *newCell = 0;
         7963  +
         7964  +  assert( (flags & (BTREE_SAVEPOSITION|BTREE_APPEND))==flags );
  7963   7965   
  7964   7966     if( pCur->eState==CURSOR_FAULT ){
  7965   7967       assert( pCur->skipNext!=SQLITE_OK );
  7966   7968       return pCur->skipNext;
  7967   7969     }
  7968   7970   
  7969   7971     assert( cursorOwnsBtShared(pCur) );
................................................................................
  7997   7999   
  7998   8000     if( pCur->pKeyInfo==0 ){
  7999   8001       assert( pX->pKey==0 );
  8000   8002       /* If this is an insert into a table b-tree, invalidate any incrblob 
  8001   8003       ** cursors open on the row being replaced */
  8002   8004       invalidateIncrblobCursors(p, pX->nKey, 0);
  8003   8005   
         8006  +    /* If BTREE_SAVEPOSITION is set, the cursor must already be pointing 
         8007  +    ** to a row with the same key as the new entry being inserted.  */
         8008  +    assert( (flags & BTREE_SAVEPOSITION)==0 || 
         8009  +            ((pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey==pCur->info.nKey) );
         8010  +
  8004   8011       /* If the cursor is currently on the last row and we are appending a
  8005   8012       ** new row onto the end, set the "loc" to avoid an unnecessary
  8006   8013       ** btreeMoveto() call */
  8007   8014       if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey==pCur->info.nKey ){
  8008   8015         loc = 0;
  8009   8016       }else if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey>0
  8010   8017                  && pCur->info.nKey==pX->nKey-1 ){
  8011   8018         loc = -1;
  8012   8019       }else if( loc==0 ){
  8013         -      rc = sqlite3BtreeMovetoUnpacked(pCur, 0, pX->nKey, appendBias, &loc);
         8020  +      rc = sqlite3BtreeMovetoUnpacked(pCur, 0, pX->nKey, flags!=0, &loc);
  8014   8021         if( rc ) return rc;
  8015   8022       }
  8016         -  }else if( loc==0 ){
         8023  +  }else if( loc==0 && (flags & BTREE_SAVEPOSITION)==0 ){
  8017   8024       if( pX->nMem ){
  8018   8025         UnpackedRecord r;
  8019   8026         r.pKeyInfo = pCur->pKeyInfo;
  8020   8027         r.aMem = pX->aMem;
  8021   8028         r.nField = pX->nMem;
  8022   8029         r.default_rc = 0;
  8023   8030         r.errCode = 0;
  8024   8031         r.r1 = 0;
  8025   8032         r.r2 = 0;
  8026   8033         r.eqSeen = 0;
  8027         -      rc = sqlite3BtreeMovetoUnpacked(pCur, &r, 0, appendBias, &loc);
         8034  +      rc = sqlite3BtreeMovetoUnpacked(pCur, &r, 0, flags!=0, &loc);
  8028   8035       }else{
  8029         -      rc = btreeMoveto(pCur, pX->pKey, pX->nKey, appendBias, &loc);
         8036  +      rc = btreeMoveto(pCur, pX->pKey, pX->nKey, flags!=0, &loc);
  8030   8037       }
  8031   8038       if( rc ) return rc;
  8032   8039     }
  8033   8040     assert( pCur->eState==CURSOR_VALID || (pCur->eState==CURSOR_INVALID && loc) );
  8034   8041   
  8035   8042     pPage = pCur->apPage[pCur->iPage];
  8036   8043     assert( pPage->intKey || pX->nKey>=0 );
................................................................................
  8110   8117   
  8111   8118       /* Must make sure nOverflow is reset to zero even if the balance()
  8112   8119       ** fails. Internal data structure corruption will result otherwise. 
  8113   8120       ** Also, set the cursor state to invalid. This stops saveCursorPosition()
  8114   8121       ** from trying to save the current position of the cursor.  */
  8115   8122       pCur->apPage[pCur->iPage]->nOverflow = 0;
  8116   8123       pCur->eState = CURSOR_INVALID;
         8124  +    if( (flags & BTREE_SAVEPOSITION) && rc==SQLITE_OK ){
         8125  +      rc = moveToRoot(pCur);
         8126  +      if( pCur->pKeyInfo && rc==SQLITE_OK ){
         8127  +        assert( pCur->pKey==0 );
         8128  +        pCur->pKey = sqlite3Malloc( pX->nKey );
         8129  +        if( pCur->pKey==0 ){
         8130  +          rc = SQLITE_NOMEM;
         8131  +        }else{
         8132  +          memcpy(pCur->pKey, pX->pKey, pX->nKey);
         8133  +        }
         8134  +      }
         8135  +      pCur->eState = CURSOR_REQUIRESEEK;
         8136  +      pCur->nKey = pX->nKey;
         8137  +    }
  8117   8138     }
  8118   8139     assert( pCur->apPage[pCur->iPage]->nOverflow==0 );
  8119   8140   
  8120   8141   end_insert:
  8121   8142     return rc;
  8122   8143   }
  8123   8144   

Changes to src/btree.h.

   245    245     int bias,
   246    246     int *pRes
   247    247   );
   248    248   int sqlite3BtreeCursorHasMoved(BtCursor*);
   249    249   int sqlite3BtreeCursorRestore(BtCursor*, int*);
   250    250   int sqlite3BtreeDelete(BtCursor*, u8 flags);
   251    251   
   252         -/* Allowed flags for the 2nd argument to sqlite3BtreeDelete() */
          252  +/* Allowed flags for sqlite3BtreeDelete() and sqlite3BtreeInsert() */
   253    253   #define BTREE_SAVEPOSITION 0x02  /* Leave cursor pointing at NEXT or PREV */
   254    254   #define BTREE_AUXDELETE    0x04  /* not the primary delete operation */
          255  +#define BTREE_APPEND       0x08  /* Insert is likely an append */
   255    256   
   256    257   /* An instance of the BtreePayload object describes the content of a single
   257    258   ** entry in either an index or table btree.
   258    259   **
   259    260   ** Index btrees (used for indexes and also WITHOUT ROWID tables) contain
   260    261   ** an arbitrary key and no data.  These btrees have pKey,nKey set to their
   261    262   ** key and pData,nData,nZero set to zero.
................................................................................
   278    279     struct Mem *aMem;       /* First of nMem value in the unpacked pKey */
   279    280     u16 nMem;               /* Number of aMem[] value.  Might be zero */
   280    281     int nData;              /* Size of pData.  0 if none. */
   281    282     int nZero;              /* Extra zero data appended after pData,nData */
   282    283   };
   283    284   
   284    285   int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
   285         -                       int bias, int seekResult);
          286  +                       int flags, int seekResult);
   286    287   int sqlite3BtreeFirst(BtCursor*, int *pRes);
   287    288   int sqlite3BtreeLast(BtCursor*, int *pRes);
   288    289   int sqlite3BtreeNext(BtCursor*, int *pRes);
   289    290   int sqlite3BtreeEof(BtCursor*);
   290    291   int sqlite3BtreePrevious(BtCursor*, int *pRes);
   291    292   i64 sqlite3BtreeIntegerKey(BtCursor*);
   292    293   int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);

Changes to src/insert.c.

  1680   1680   void sqlite3CompleteInsertion(
  1681   1681     Parse *pParse,      /* The parser context */
  1682   1682     Table *pTab,        /* the table into which we are inserting */
  1683   1683     int iDataCur,       /* Cursor of the canonical data source */
  1684   1684     int iIdxCur,        /* First index cursor */
  1685   1685     int regNewData,     /* Range of content */
  1686   1686     int *aRegIdx,       /* Register used by each index.  0 for unused indices */
  1687         -  int isUpdate,       /* True for UPDATE, False for INSERT */
         1687  +  int update_flags,   /* True for UPDATE, False for INSERT */
  1688   1688     int appendBias,     /* True if this is likely to be an append */
  1689   1689     int useSeekResult   /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
  1690   1690   ){
  1691   1691     Vdbe *v;            /* Prepared statements under construction */
  1692   1692     Index *pIdx;        /* An index being inserted or updated */
  1693   1693     u8 pik_flags;       /* flag values passed to the btree insert */
  1694   1694     int regData;        /* Content registers (after the rowid) */
  1695   1695     int regRec;         /* Register holding assembled record for the table */
  1696   1696     int i;              /* Loop counter */
  1697   1697     u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */
         1698  +
         1699  +  assert( update_flags==0
         1700  +       || update_flags==OPFLAG_ISUPDATE
         1701  +       || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION)
         1702  +  );
  1698   1703   
  1699   1704     v = sqlite3GetVdbe(pParse);
  1700   1705     assert( v!=0 );
  1701   1706     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1702   1707     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1703   1708       if( aRegIdx[i]==0 ) continue;
  1704   1709       bAffinityDone = 1;
................................................................................
  1710   1715                            aRegIdx[i]+1,
  1711   1716                            pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
  1712   1717       pik_flags = 0;
  1713   1718       if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT;
  1714   1719       if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
  1715   1720         assert( pParse->nested==0 );
  1716   1721         pik_flags |= OPFLAG_NCHANGE;
         1722  +      pik_flags |= (update_flags & OPFLAG_SAVEPOSITION);
  1717   1723       }
  1718   1724       sqlite3VdbeChangeP5(v, pik_flags);
  1719   1725     }
  1720   1726     if( !HasRowid(pTab) ) return;
  1721   1727     regData = regNewData + 1;
  1722   1728     regRec = sqlite3GetTempReg(pParse);
  1723   1729     sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
................................................................................
  1725   1731       sqlite3TableAffinity(v, pTab, 0);
  1726   1732       sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol);
  1727   1733     }
  1728   1734     if( pParse->nested ){
  1729   1735       pik_flags = 0;
  1730   1736     }else{
  1731   1737       pik_flags = OPFLAG_NCHANGE;
  1732         -    pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
         1738  +    pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID);
  1733   1739     }
  1734   1740     if( appendBias ){
  1735   1741       pik_flags |= OPFLAG_APPEND;
  1736   1742     }
  1737   1743     if( useSeekResult ){
  1738   1744       pik_flags |= OPFLAG_USESEEKRESULT;
  1739   1745     }

Changes to src/select.c.

  5648   5648           }
  5649   5649     
  5650   5650           /* This case runs if the aggregate has no GROUP BY clause.  The
  5651   5651           ** processing is much simpler since there is only a single row
  5652   5652           ** of output.
  5653   5653           */
  5654   5654           resetAccumulator(pParse, &sAggInfo);
  5655         -        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0);
         5655  +        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax, 0,flag,0);
  5656   5656           if( pWInfo==0 ){
  5657   5657             sqlite3ExprListDelete(db, pDel);
  5658   5658             goto select_end;
  5659   5659           }
  5660   5660           updateAccumulator(pParse, &sAggInfo);
  5661   5661           assert( pMinMax==0 || pMinMax->nExpr==1 );
  5662   5662           if( sqlite3WhereIsOrdered(pWInfo)>0 ){

Changes to src/sqliteInt.h.

  3037   3037   **    OPFLAG_FORDELETE    == BTREE_FORDELETE
  3038   3038   **    OPFLAG_SAVEPOSITION == BTREE_SAVEPOSITION
  3039   3039   **    OPFLAG_AUXDELETE    == BTREE_AUXDELETE
  3040   3040   */
  3041   3041   #define OPFLAG_NCHANGE       0x01    /* OP_Insert: Set to update db->nChange */
  3042   3042                                        /* Also used in P2 (not P5) of OP_Delete */
  3043   3043   #define OPFLAG_EPHEM         0x01    /* OP_Column: Ephemeral output is ok */
  3044         -#define OPFLAG_LASTROWID     0x02    /* Set to update db->lastRowid */
         3044  +#define OPFLAG_LASTROWID     0x20    /* Set to update db->lastRowid */
  3045   3045   #define OPFLAG_ISUPDATE      0x04    /* This OP_Insert is an sql UPDATE */
  3046   3046   #define OPFLAG_APPEND        0x08    /* This is likely to be an append */
  3047   3047   #define OPFLAG_USESEEKRESULT 0x10    /* Try to avoid a seek in BtreeInsert() */
  3048   3048   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  3049   3049   #define OPFLAG_ISNOOP        0x40    /* OP_Delete does pre-update-hook only */
  3050   3050   #endif
  3051   3051   #define OPFLAG_LENGTHARG     0x40    /* OP_Column only used for length() */
  3052   3052   #define OPFLAG_TYPEOFARG     0x80    /* OP_Column only used for typeof() */
  3053   3053   #define OPFLAG_BULKCSR       0x01    /* OP_Open** used to open bulk cursor */
  3054   3054   #define OPFLAG_SEEKEQ        0x02    /* OP_Open** cursor uses EQ seek only */
  3055   3055   #define OPFLAG_FORDELETE     0x08    /* OP_Open should use BTREE_FORDELETE */
  3056   3056   #define OPFLAG_P2ISREG       0x10    /* P2 to OP_Open** is a register number */
  3057   3057   #define OPFLAG_PERMUTE       0x01    /* OP_Compare: use the permutation */
  3058         -#define OPFLAG_SAVEPOSITION  0x02    /* OP_Delete: keep cursor position */
         3058  +#define OPFLAG_SAVEPOSITION  0x02    /* OP_Delete/Insert: save cursor pos */
  3059   3059   #define OPFLAG_AUXDELETE     0x04    /* OP_Delete: index in a DELETE op */
  3060   3060   
  3061   3061   /*
  3062   3062    * Each trigger present in the database schema is stored as an instance of
  3063   3063    * struct Trigger.
  3064   3064    *
  3065   3065    * Pointers to instances of struct Trigger are stored in two ways.

Changes to src/update.c.

   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    104     int iBaseCur;          /* Base cursor number */
   105    105     int iDataCur;          /* Cursor for the canonical data btree */
   106    106     int iIdxCur;           /* Cursor for the first index */
   107    107     sqlite3 *db;           /* The database structure */
   108         -  int *aRegIdx = 0;      /* One register assigned to each index to be updated */
          108  +  int *aRegIdx = 0;      /* First register in array assigned to each index */
   109    109     int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
   110    110                            ** an expression for the i-th column of the table.
   111    111                            ** aXRef[i]==-1 if the i-th column is not changed. */
   112    112     u8 *aToOpen;           /* 1 for tables and indices to be opened */
   113    113     u8 chngPk;             /* PRIMARY KEY changed in a WITHOUT ROWID table */
   114    114     u8 chngRowid;          /* Rowid changed in a normal table */
   115    115     u8 chngKey;            /* Either chngPk or chngRowid */
   116    116     Expr *pRowidExpr = 0;  /* Expression defining the new record number */
   117    117     AuthContext sContext;  /* The authorization context */
   118    118     NameContext sNC;       /* The name-context to resolve expressions in */
   119    119     int iDb;               /* Database containing the table being updated */
   120         -  int okOnePass;         /* True for one-pass algorithm without the FIFO */
          120  +  int eOnePass;          /* ONEPASS_XXX value from where.c */
   121    121     int hasFK;             /* True if foreign key processing is required */
   122    122     int labelBreak;        /* Jump here to break out of UPDATE loop */
   123    123     int labelContinue;     /* Jump here to continue next step of UPDATE loop */
          124  +  int flags;             /* Flags for sqlite3WhereBegin() */
   124    125   
   125    126   #ifndef SQLITE_OMIT_TRIGGER
   126    127     int isView;            /* True when updating a view (INSTEAD OF trigger) */
   127    128     Trigger *pTrigger;     /* List of triggers on pTab, if required */
   128    129     int tmask;             /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
   129    130   #endif
   130    131     int newmask;           /* Mask of NEW.* columns accessed by BEFORE triggers */
   131    132     int iEph = 0;          /* Ephemeral table holding all primary key values */
   132    133     int nKey = 0;          /* Number of elements in regKey for WITHOUT ROWID */
   133    134     int aiCurOnePass[2];   /* The write cursors opened by WHERE_ONEPASS */
          135  +  int addrOpen;          /* Address of OP_OpenEphemeral */
          136  +  int iPk;               /* First of nPk cells holding PRIMARY KEY value */
          137  +  i16 nPk;               /* Number of components of the PRIMARY KEY */
          138  +  int bReplace = 0;      /* True if REPLACE conflict resolution might happen */
   134    139   
   135    140     /* Register Allocations */
   136    141     int regRowCount = 0;   /* A count of rows changed */
   137    142     int regOldRowid = 0;   /* The old rowid */
   138    143     int regNewRowid = 0;   /* The new rowid */
   139    144     int regNew = 0;        /* Content of the NEW.* table in triggers */
   140    145     int regOld = 0;        /* Content of OLD.* table in triggers */
................................................................................
   286    291       }else{
   287    292         reg = 0;
   288    293         for(i=0; i<pIdx->nKeyCol; i++){
   289    294           i16 iIdxCol = pIdx->aiColumn[i];
   290    295           if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){
   291    296             reg = ++pParse->nMem;
   292    297             pParse->nMem += pIdx->nColumn;
          298  +          if( (onError==OE_Replace)
          299  +           || (onError==OE_Default && pIdx->onError==OE_Replace) 
          300  +          ){
          301  +            bReplace = 1;
          302  +          }
   293    303             break;
   294    304           }
   295    305         }
   296    306       }
   297    307       if( reg==0 ) aToOpen[j+1] = 0;
   298    308       aRegIdx[j] = reg;
   299    309     }
          310  +  if( bReplace ){
          311  +    /* If REPLACE conflict resolution might be invoked, open cursors on all 
          312  +    ** indexes in case they are needed to delete records.  */
          313  +    memset(aToOpen, 1, nIdx+1);
          314  +  }
   300    315   
   301    316     /* Begin generating code. */
   302    317     v = sqlite3GetVdbe(pParse);
   303    318     if( v==0 ) goto update_cleanup;
   304    319     if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
   305    320     sqlite3BeginWriteOperation(pParse, 1, iDb);
   306    321   
................................................................................
   345    360     if( IsVirtual(pTab) ){
   346    361       updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
   347    362                          pWhere, onError);
   348    363       goto update_cleanup;
   349    364     }
   350    365   #endif
   351    366   
   352         -  /* Begin the database scan
   353         -  */
          367  +  /* Initialize the count of updated rows */
          368  +  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
          369  +    regRowCount = ++pParse->nMem;
          370  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
          371  +  }
          372  +
   354    373     if( HasRowid(pTab) ){
   355    374       sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   356         -    pWInfo = sqlite3WhereBegin(
   357         -        pParse, pTabList, pWhere, 0, 0,
   358         -            WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE, iIdxCur
   359         -    );
   360         -    if( pWInfo==0 ) goto update_cleanup;
   361         -    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   362         -  
   363         -    /* Remember the rowid of every item to be updated.
   364         -    */
   365         -    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
   366         -    if( !okOnePass ){
   367         -      sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid);
   368         -    }
   369         -  
   370         -    /* End the database scan loop.
   371         -    */
   372         -    sqlite3WhereEnd(pWInfo);
   373    375     }else{
   374         -    int iPk;         /* First of nPk memory cells holding PRIMARY KEY value */
   375         -    i16 nPk;         /* Number of components of the PRIMARY KEY */
   376         -    int addrOpen;    /* Address of the OpenEphemeral instruction */
   377         -
   378    376       assert( pPk!=0 );
   379    377       nPk = pPk->nKeyCol;
   380    378       iPk = pParse->nMem+1;
   381    379       pParse->nMem += nPk;
   382    380       regKey = ++pParse->nMem;
   383    381       iEph = pParse->nTab++;
          382  +
   384    383       sqlite3VdbeAddOp2(v, OP_Null, 0, iPk);
   385    384       addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
   386    385       sqlite3VdbeSetP4KeyInfo(pParse, pPk);
   387         -    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
   388         -                               WHERE_ONEPASS_DESIRED, iIdxCur);
   389         -    if( pWInfo==0 ) goto update_cleanup;
   390         -    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
          386  +  }
          387  +
          388  +  /* Begin the database scan. 
          389  +  **
          390  +  ** Do not consider a single-pass strategy for a multi-row update if
          391  +  ** there are any triggers or foreign keys to process, or rows may
          392  +  ** be deleted as a result of REPLACE conflict handling. Any of these
          393  +  ** things might disturb a cursor being used to scan through the table
          394  +  ** or index, causing a single-pass approach to malfunction.  */
          395  +  flags = WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE;
          396  +  if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
          397  +    flags |= WHERE_ONEPASS_MULTIROW;
          398  +  }
          399  +  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur);
          400  +  if( pWInfo==0 ) goto update_cleanup;
          401  +
          402  +  /* A one-pass strategy that might update more than one row may not
          403  +  ** be used if any column of the index used for the scan is being
          404  +  ** updated. Otherwise, if there is an index on "b", statements like
          405  +  ** the following could create an infinite loop:
          406  +  **
          407  +  **   UPDATE t1 SET b=b+1 WHERE b>?
          408  +  **
          409  +  ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
          410  +  ** strategy that uses an index for which one or more columns are being
          411  +  ** updated.  */
          412  +  eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
          413  +  if( eOnePass==ONEPASS_MULTI ){
          414  +    int iCur = aiCurOnePass[1];
          415  +    if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){
          416  +      eOnePass = ONEPASS_OFF;
          417  +    }
          418  +    assert( iCur!=iDataCur || !HasRowid(pTab) );
          419  +  }
          420  +  
          421  +  if( HasRowid(pTab) ){
          422  +    /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
          423  +    ** mode, write the rowid into the FIFO. In either of the one-pass modes,
          424  +    ** leave it in register regOldRowid.  */
          425  +    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
          426  +    if( eOnePass==ONEPASS_OFF ){
          427  +      sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid);
          428  +    }
          429  +  }else{
          430  +    /* Read the PK of the current row into an array of registers. In
          431  +    ** ONEPASS_OFF mode, serialize the array into a record and store it in
          432  +    ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change
          433  +    ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table 
          434  +    ** is not required) and leave the PK fields in the array of registers.  */
   391    435       for(i=0; i<nPk; i++){
   392    436         assert( pPk->aiColumn[i]>=0 );
   393         -      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
   394         -                                      iPk+i);
          437  +      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur,pPk->aiColumn[i],iPk+i);
   395    438       }
   396         -    if( okOnePass ){
          439  +    if( eOnePass ){
   397    440         sqlite3VdbeChangeToNoop(v, addrOpen);
   398    441         nKey = nPk;
   399    442         regKey = iPk;
   400    443       }else{
   401    444         sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey,
   402    445                           sqlite3IndexAffinityStr(db, pPk), nPk);
   403    446         sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk);
   404    447       }
   405         -    sqlite3WhereEnd(pWInfo);
   406    448     }
   407    449   
   408         -  /* Initialize the count of updated rows
   409         -  */
   410         -  if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
   411         -    regRowCount = ++pParse->nMem;
   412         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
          450  +  if( eOnePass!=ONEPASS_MULTI ){
          451  +    sqlite3WhereEnd(pWInfo);
   413    452     }
   414    453   
   415    454     labelBreak = sqlite3VdbeMakeLabel(v);
   416    455     if( !isView ){
   417         -    /* 
   418         -    ** Open every index that needs updating.  Note that if any
   419         -    ** index could potentially invoke a REPLACE conflict resolution 
   420         -    ** action, then we need to open all indices because we might need
   421         -    ** to be deleting some records.
   422         -    */
   423         -    if( onError==OE_Replace ){
   424         -      memset(aToOpen, 1, nIdx+1);
   425         -    }else{
   426         -      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   427         -        if( pIdx->onError==OE_Replace ){
   428         -          memset(aToOpen, 1, nIdx+1);
   429         -          break;
   430         -        }
   431         -      }
   432         -    }
   433         -    if( okOnePass ){
          456  +    int addrOnce = 0;
          457  +
          458  +    /* Open every index that needs updating. */
          459  +    if( eOnePass!=ONEPASS_OFF ){
   434    460         if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
   435    461         if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
   436    462       }
          463  +
          464  +    if( eOnePass==ONEPASS_MULTI && (nIdx-(aiCurOnePass[1]>=0))>0 ){
          465  +      addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
          466  +    }
   437    467       sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur, aToOpen,
   438    468                                  0, 0);
          469  +    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
   439    470     }
   440    471   
   441    472     /* Top of the update loop */
   442         -  if( okOnePass ){
   443         -    if( aToOpen[iDataCur-iBaseCur] && !isView ){
          473  +  if( eOnePass!=ONEPASS_OFF ){
          474  +    if( !isView && aiCurOnePass[0]!=iDataCur && aiCurOnePass[1]!=iDataCur ){
   444    475         assert( pPk );
   445    476         sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey);
   446    477         VdbeCoverageNeverTaken(v);
   447    478       }
   448         -    labelContinue = labelBreak;
          479  +    if( eOnePass==ONEPASS_SINGLE ){
          480  +      labelContinue = labelBreak;
          481  +    }else{
          482  +      labelContinue = sqlite3VdbeMakeLabel(v);
          483  +    }
   449    484       sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak);
   450    485       VdbeCoverageIf(v, pPk==0);
   451    486       VdbeCoverageIf(v, pPk!=0);
   452    487     }else if( pPk ){
   453    488       labelContinue = sqlite3VdbeMakeLabel(v);
   454    489       sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v);
   455    490       addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey);
................................................................................
   566    601           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i);
   567    602         }
   568    603       }
   569    604     }
   570    605   
   571    606     if( !isView ){
   572    607       int addr1 = 0;        /* Address of jump instruction */
   573         -    int bReplace = 0;     /* True if REPLACE conflict resolution might happen */
   574    608   
   575    609       /* Do constraint checks. */
   576    610       assert( regOldRowid>0 );
   577    611       sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
   578    612           regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace,
   579    613           aXRef);
   580    614   
................................................................................
   602    636       ** pre-update hook. If the caller invokes preupdate_new(), the returned
   603    637       ** value is copied from memory cell (regNewRowid+1+iCol), where iCol
   604    638       ** is the column index supplied by the user.
   605    639       */
   606    640       assert( regNew==regNewRowid+1 );
   607    641   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   608    642       sqlite3VdbeAddOp3(v, OP_Delete, iDataCur,
   609         -        OPFLAG_ISUPDATE | ((hasFK || chngKey || pPk!=0) ? 0 : OPFLAG_ISNOOP),
          643  +        OPFLAG_ISUPDATE | ((hasFK || chngKey) ? 0 : OPFLAG_ISNOOP),
   610    644           regNewRowid
   611    645       );
          646  +    if( eOnePass==ONEPASS_MULTI ){
          647  +      assert( hasFK==0 && chngKey==0 );
          648  +      sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION);
          649  +    }
   612    650       if( !pParse->nested ){
   613    651         sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
   614    652       }
   615    653   #else
   616         -    if( hasFK || chngKey || pPk!=0 ){
          654  +    if( hasFK || chngKey ){
   617    655         sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, 0);
   618    656       }
   619    657   #endif
   620    658       if( bReplace || chngKey ){
   621    659         sqlite3VdbeJumpHere(v, addr1);
   622    660       }
   623    661   
   624    662       if( hasFK ){
   625    663         sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey);
   626    664       }
   627    665     
   628    666       /* Insert the new index entries and the new record. */
   629         -    sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
   630         -                             regNewRowid, aRegIdx, 1, 0, 0);
          667  +    sqlite3CompleteInsertion(
          668  +        pParse, pTab, iDataCur, iIdxCur, regNewRowid, aRegIdx, 
          669  +        OPFLAG_ISUPDATE | (eOnePass==ONEPASS_MULTI ? OPFLAG_SAVEPOSITION : 0), 
          670  +        0, 0
          671  +    );
   631    672   
   632    673       /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   633    674       ** handle rows (possibly in other tables) that refer via a foreign key
   634    675       ** to the row just updated. */ 
   635    676       if( hasFK ){
   636    677         sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
   637    678       }
................................................................................
   645    686   
   646    687     sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, 
   647    688         TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue);
   648    689   
   649    690     /* Repeat the above with the next record to be updated, until
   650    691     ** all record selected by the WHERE clause have been updated.
   651    692     */
   652         -  if( okOnePass ){
          693  +  if( eOnePass==ONEPASS_SINGLE ){
   653    694       /* Nothing to do at end-of-loop for a single-pass */
          695  +  }else if( eOnePass==ONEPASS_MULTI ){
          696  +    sqlite3VdbeResolveLabel(v, labelContinue);
          697  +    sqlite3WhereEnd(pWInfo);
   654    698     }else if( pPk ){
   655    699       sqlite3VdbeResolveLabel(v, labelContinue);
   656    700       sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop); VdbeCoverage(v);
   657    701     }else{
   658    702       sqlite3VdbeGoto(v, labelContinue);
   659    703     }
   660    704     sqlite3VdbeResolveLabel(v, labelBreak);

Changes to src/vdbe.c.

  4417   4417     if( pData->flags & MEM_Zero ){
  4418   4418       x.nZero = pData->u.nZero;
  4419   4419     }else{
  4420   4420       x.nZero = 0;
  4421   4421     }
  4422   4422     x.pKey = 0;
  4423   4423     rc = sqlite3BtreeInsert(pC->uc.pCursor, &x,
  4424         -                          (pOp->p5 & OPFLAG_APPEND)!=0, seekResult
         4424  +      (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), seekResult
  4425   4425     );
  4426   4426     pC->deferredMoveto = 0;
  4427   4427     pC->cacheStatus = CACHE_STALE;
  4428   4428   
  4429   4429     /* Invoke the update-hook if required. */
  4430   4430     if( rc ) goto abort_due_to_error;
  4431   4431     if( db->xUpdateCallback && op ){
................................................................................
  5082   5082       rc = sqlite3VdbeSorterWrite(pC, pIn2);
  5083   5083     }else{
  5084   5084       x.nKey = pIn2->n;
  5085   5085       x.pKey = pIn2->z;
  5086   5086       x.aMem = aMem + pOp->p3;
  5087   5087       x.nMem = (u16)pOp->p4.i;
  5088   5088       rc = sqlite3BtreeInsert(pC->uc.pCursor, &x,
  5089         -         (pOp->p5 & OPFLAG_APPEND)!=0, 
         5089  +         (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), 
  5090   5090           ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0)
  5091   5091           );
  5092   5092       assert( pC->deferredMoveto==0 );
  5093   5093       pC->cacheStatus = CACHE_STALE;
  5094   5094     }
  5095   5095     if( rc) goto abort_due_to_error;
  5096   5096     break;

Changes to src/where.c.

  4945   4945               assert( x>=0 );
  4946   4946             }
  4947   4947             x = sqlite3ColumnOfIndex(pIdx, x);
  4948   4948             if( x>=0 ){
  4949   4949               pOp->p2 = x;
  4950   4950               pOp->p1 = pLevel->iIdxCur;
  4951   4951             }
  4952         -          assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 );
         4952  +          assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 
         4953  +              || pWInfo->eOnePass );
  4953   4954           }else if( pOp->opcode==OP_Rowid ){
  4954   4955             pOp->p1 = pLevel->iIdxCur;
  4955   4956             pOp->opcode = OP_IdxRowid;
  4956   4957           }
  4957   4958         }
  4958   4959       }
  4959   4960     }

Added test/update2.test.

            1  +# 2017 January 9
            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  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix update2
           16  +
           17  +db func repeat [list string repeat]
           18  +
           19  +#-------------------------------------------------------------------------
           20  +# 1.1.* A one-pass UPDATE that does balance() operations on the IPK index
           21  +#       that it is scanning.
           22  +#
           23  +# 1.2.* Same again, but with a WITHOUT ROWID table.
           24  +#
           25  +set nrow [expr 10]
           26  +do_execsql_test 1.1.0 {
           27  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
           28  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
           29  +  WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
           30  +  INSERT INTO t1(b) SELECT char((i % 26) + 65) FROM s;
           31  +  INSERT INTO t2 SELECT * FROM t1;
           32  +}
           33  +
           34  +do_execsql_test 1.1.1 {
           35  +  UPDATE t1 SET b = repeat(b, 100)
           36  +}
           37  +
           38  +do_execsql_test 1.1.2 {
           39  +  SELECT * FROM t1;
           40  +} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
           41  +
           42  +do_execsql_test 1.2.0 {
           43  +  DROP TABLE t1;
           44  +  CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID;
           45  +  WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
           46  +  INSERT INTO t1(a, b) SELECT i+1, char((i % 26) + 65) FROM s;
           47  +}
           48  +
           49  +#explain_i { UPDATE t1 SET b = repeat(b, 100) }
           50  +do_execsql_test 1.2.1 {
           51  +  UPDATE t1 SET b = repeat(b, 100)
           52  +}
           53  +
           54  +do_execsql_test 1.2.2 {
           55  +  SELECT * FROM t1;
           56  +} [db eval { SELECT a, repeat(b, 100) FROM t2 }]
           57  +
           58  +
           59  +#-------------------------------------------------------------------------
           60  +# A one-pass UPDATE that does balance() operations on the IPK index
           61  +# that it is scanning.
           62  +#
           63  +do_execsql_test 2.1 {
           64  +  CREATE TABLE t3(a PRIMARY KEY, b, c);
           65  +  CREATE INDEX t3i ON t3(b);
           66  +} {}
           67  +do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=?      } {}
           68  +do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=?  } {}
           69  +
           70  +#-------------------------------------------------------------------------
           71  +#
           72  +do_execsql_test 3.0 {
           73  +  CREATE TABLE t4(a PRIMARY KEY, b, c) WITHOUT ROWID;
           74  +  CREATE INDEX t4c ON t4(c);
           75  +  INSERT INTO t4 VALUES(1, 2, 3);
           76  +  INSERT INTO t4 VALUES(2, 3, 4);
           77  +}
           78  +
           79  +do_execsql_test 3.1 {
           80  +  UPDATE t4 SET c=c+2 WHERE c>2;
           81  +  SELECT a, c FROM t4 ORDER BY a;
           82  +} {1 5 2 6}
           83  +
           84  +#-------------------------------------------------------------------------
           85  +#
           86  +foreach {tn sql} {
           87  +  1 { 
           88  +    CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c);
           89  +    CREATE TABLE c1(a INTEGER PRIMARY KEY, b, c, d)
           90  +  }
           91  +  2 { 
           92  +    CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
           93  +    CREATE TABLE c1(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
           94  +  }
           95  +} {
           96  +  execsql { DROP TABLE IF EXISTS b1; DROP TABLE IF EXISTS c1; }
           97  +  execsql $sql
           98  +
           99  +  do_execsql_test 4.$tn.0 {
          100  +    CREATE UNIQUE INDEX b1c ON b1(c);
          101  +    INSERT INTO b1 VALUES(1, 'a', 1);
          102  +    INSERT INTO b1 VALUES(2, 'b', 15);
          103  +    INSERT INTO b1 VALUES(3, 'c', 3);
          104  +    INSERT INTO b1 VALUES(4, 'd', 4);
          105  +    INSERT INTO b1 VALUES(5, 'e', 5);
          106  +    INSERT INTO b1 VALUES(6, 'f', 6);
          107  +    INSERT INTO b1 VALUES(7, 'g', 7);
          108  +  }
          109  +
          110  +  do_execsql_test 4.$tn.1 {
          111  +    UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7;
          112  +    SELECT * FROM b1 ORDER BY a;
          113  +  } {
          114  +    1 a 1
          115  +    3 c 3
          116  +    4 d 14
          117  +    5 e 15
          118  +    6 f 16
          119  +    7 g 17
          120  +  }
          121  +
          122  +  do_execsql_test 4.$tn.2 {
          123  +    CREATE INDEX c1d ON c1(d, b);
          124  +    CREATE UNIQUE INDEX c1c ON c1(c, b);
          125  +
          126  +    INSERT INTO c1 VALUES(1, 'a', 1,  1);
          127  +    INSERT INTO c1 VALUES(2, 'a', 15, 2);
          128  +    INSERT INTO c1 VALUES(3, 'a', 3,  3);
          129  +    INSERT INTO c1 VALUES(4, 'a', 4,  4);
          130  +    INSERT INTO c1 VALUES(5, 'a', 5,  5);
          131  +    INSERT INTO c1 VALUES(6, 'a', 6,  6);
          132  +    INSERT INTO c1 VALUES(7, 'a', 7,  7);
          133  +  }
          134  +
          135  +  do_execsql_test 4.$tn.3 {
          136  +    UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
          137  +    SELECT * FROM c1 ORDER BY a;
          138  +  } {
          139  +    1 a 1 1
          140  +    3 a 3 3
          141  +    4 a 14 4
          142  +    5 a 15 5
          143  +    6 a 16 6
          144  +    7 a 17 7
          145  +  }
          146  +
          147  +  do_execsql_test 4.$tn.4 { PRAGMA integrity_check } ok
          148  +
          149  +  do_execsql_test 4.$tn.5 {
          150  +    DROP INDEX c1d;
          151  +    DROP INDEX c1c;
          152  +    DELETE FROM c1;
          153  +
          154  +    INSERT INTO c1 VALUES(1, 'a', 1,  1);
          155  +    INSERT INTO c1 VALUES(2, 'a', 15, 2);
          156  +    INSERT INTO c1 VALUES(3, 'a', 3,  3);
          157  +    INSERT INTO c1 VALUES(4, 'a', 4,  4);
          158  +    INSERT INTO c1 VALUES(5, 'a', 5,  5);
          159  +    INSERT INTO c1 VALUES(6, 'a', 6,  6);
          160  +    INSERT INTO c1 VALUES(7, 'a', 7,  7);
          161  +
          162  +    CREATE INDEX c1d ON c1(d);
          163  +    CREATE UNIQUE INDEX c1c ON c1(c);
          164  +  }
          165  +
          166  +  do_execsql_test 4.$tn.6 {
          167  +    UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
          168  +    SELECT * FROM c1 ORDER BY a;
          169  +  } {
          170  +    1 a 1 1
          171  +    3 a 3 3
          172  +    4 a 14 4
          173  +    5 a 15 5
          174  +    6 a 16 6
          175  +    7 a 17 7
          176  +  }
          177  +}
          178  +
          179  +finish_test
          180  +