Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch onepass-update Excluding Merge-Ins
This is equivalent to a diff from e500c15a9f to 62257eb53c
2017-01-11
| ||
21:03 | Changes to allow some multi-row UPDATE statements to avoid the two-pass approach. (check-in: 7ae6104a3e 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: 62257eb53c user: dan tags: onepass-update) | |
19:03 | Fix a problem with single-pass multi-row UPDATE statements that invoke REPLACE conflict handling. (check-in: 0a2b8e1b9d user: dan tags: onepass-update) | |
14:15 | In the STAT4 computations, ensure that the aAvgEq values do not go negative. (check-in: f58f75b5a0 user: drh tags: trunk) | |
2017-01-10
| ||
20:04 | Changes to allow some multi-row UPDATE statements to avoid the two-pass approach. (check-in: 46db23ccd1 user: dan tags: onepass-update) | |
17:37 | Add a test case for ticket [25e335f802dd]. (check-in: e500c15a9f user: dan tags: trunk) | |
17:33 | Throw an error if the ON clause of a LEFT JOIN references tables to the right of the ON clause. Fix for ticket [25e335f802dd]. (check-in: c92ecff2ec user: drh tags: trunk) | |
Changes to src/btree.c.
︙ | ︙ | |||
7944 7945 7946 7947 7948 7949 7950 | ** if pX->nMem is non-zero, then pX->aMem contains pointers to the unpacked ** key values and pX->aMem can be used instead of pX->pKey to avoid having ** to decode the key. */ int sqlite3BtreeInsert( BtCursor *pCur, /* Insert data into the table of this cursor */ const BtreePayload *pX, /* Content of the row to be inserted */ | | > > | 7944 7945 7946 7947 7948 7949 7950 7951 7952 7953 7954 7955 7956 7957 7958 7959 7960 7961 7962 7963 7964 7965 7966 7967 7968 7969 7970 7971 | ** if pX->nMem is non-zero, then pX->aMem contains pointers to the unpacked ** key values and pX->aMem can be used instead of pX->pKey to avoid having ** to decode the key. */ int sqlite3BtreeInsert( BtCursor *pCur, /* Insert data into the table of this cursor */ const BtreePayload *pX, /* Content of the row to be inserted */ int flags, /* True if this is likely an append */ int seekResult /* Result of prior MovetoUnpacked() call */ ){ int rc; int loc = seekResult; /* -1: before desired location +1: after */ int szNew = 0; int idx; MemPage *pPage; Btree *p = pCur->pBtree; BtShared *pBt = p->pBt; unsigned char *oldCell; unsigned char *newCell = 0; assert( (flags & (BTREE_SAVEPOSITION|BTREE_APPEND))==flags ); if( pCur->eState==CURSOR_FAULT ){ assert( pCur->skipNext!=SQLITE_OK ); return pCur->skipNext; } assert( cursorOwnsBtShared(pCur) ); |
︙ | ︙ | |||
7997 7998 7999 8000 8001 8002 8003 8004 8005 8006 8007 8008 8009 8010 8011 8012 | if( pCur->pKeyInfo==0 ){ assert( pX->pKey==0 ); /* If this is an insert into a table b-tree, invalidate any incrblob ** cursors open on the row being replaced */ invalidateIncrblobCursors(p, pX->nKey, 0); /* If the cursor is currently on the last row and we are appending a ** new row onto the end, set the "loc" to avoid an unnecessary ** btreeMoveto() call */ if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey==pCur->info.nKey ){ loc = 0; }else if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey>0 && pCur->info.nKey==pX->nKey-1 ){ loc = -1; }else if( loc==0 ){ | > > > > > | | | | | 7999 8000 8001 8002 8003 8004 8005 8006 8007 8008 8009 8010 8011 8012 8013 8014 8015 8016 8017 8018 8019 8020 8021 8022 8023 8024 8025 8026 8027 8028 8029 8030 8031 8032 8033 8034 8035 8036 8037 8038 8039 8040 8041 8042 8043 | if( pCur->pKeyInfo==0 ){ assert( pX->pKey==0 ); /* If this is an insert into a table b-tree, invalidate any incrblob ** cursors open on the row being replaced */ invalidateIncrblobCursors(p, pX->nKey, 0); /* If BTREE_SAVEPOSITION is set, the cursor must already be pointing ** to a row with the same key as the new entry being inserted. */ assert( (flags & BTREE_SAVEPOSITION)==0 || ((pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey==pCur->info.nKey) ); /* If the cursor is currently on the last row and we are appending a ** new row onto the end, set the "loc" to avoid an unnecessary ** btreeMoveto() call */ if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey==pCur->info.nKey ){ loc = 0; }else if( (pCur->curFlags&BTCF_ValidNKey)!=0 && pX->nKey>0 && pCur->info.nKey==pX->nKey-1 ){ loc = -1; }else if( loc==0 ){ rc = sqlite3BtreeMovetoUnpacked(pCur, 0, pX->nKey, flags!=0, &loc); if( rc ) return rc; } }else if( loc==0 && (flags & BTREE_SAVEPOSITION)==0 ){ if( pX->nMem ){ UnpackedRecord r; r.pKeyInfo = pCur->pKeyInfo; r.aMem = pX->aMem; r.nField = pX->nMem; r.default_rc = 0; r.errCode = 0; r.r1 = 0; r.r2 = 0; r.eqSeen = 0; rc = sqlite3BtreeMovetoUnpacked(pCur, &r, 0, flags!=0, &loc); }else{ rc = btreeMoveto(pCur, pX->pKey, pX->nKey, flags!=0, &loc); } if( rc ) return rc; } assert( pCur->eState==CURSOR_VALID || (pCur->eState==CURSOR_INVALID && loc) ); pPage = pCur->apPage[pCur->iPage]; assert( pPage->intKey || pX->nKey>=0 ); |
︙ | ︙ | |||
8110 8111 8112 8113 8114 8115 8116 8117 8118 8119 8120 8121 8122 8123 | /* Must make sure nOverflow is reset to zero even if the balance() ** fails. Internal data structure corruption will result otherwise. ** Also, set the cursor state to invalid. This stops saveCursorPosition() ** from trying to save the current position of the cursor. */ pCur->apPage[pCur->iPage]->nOverflow = 0; pCur->eState = CURSOR_INVALID; } assert( pCur->apPage[pCur->iPage]->nOverflow==0 ); end_insert: return rc; } | > > > > > > > > > > > > > > | 8117 8118 8119 8120 8121 8122 8123 8124 8125 8126 8127 8128 8129 8130 8131 8132 8133 8134 8135 8136 8137 8138 8139 8140 8141 8142 8143 8144 | /* Must make sure nOverflow is reset to zero even if the balance() ** fails. Internal data structure corruption will result otherwise. ** Also, set the cursor state to invalid. This stops saveCursorPosition() ** from trying to save the current position of the cursor. */ pCur->apPage[pCur->iPage]->nOverflow = 0; pCur->eState = CURSOR_INVALID; if( (flags & BTREE_SAVEPOSITION) && rc==SQLITE_OK ){ rc = moveToRoot(pCur); if( pCur->pKeyInfo && rc==SQLITE_OK ){ assert( pCur->pKey==0 ); pCur->pKey = sqlite3Malloc( pX->nKey ); if( pCur->pKey==0 ){ rc = SQLITE_NOMEM; }else{ memcpy(pCur->pKey, pX->pKey, pX->nKey); } } pCur->eState = CURSOR_REQUIRESEEK; pCur->nKey = pX->nKey; } } assert( pCur->apPage[pCur->iPage]->nOverflow==0 ); end_insert: return rc; } |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
245 246 247 248 249 250 251 | int bias, int *pRes ); int sqlite3BtreeCursorHasMoved(BtCursor*); int sqlite3BtreeCursorRestore(BtCursor*, int*); int sqlite3BtreeDelete(BtCursor*, u8 flags); | | > | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 | int bias, int *pRes ); int sqlite3BtreeCursorHasMoved(BtCursor*); int sqlite3BtreeCursorRestore(BtCursor*, int*); int sqlite3BtreeDelete(BtCursor*, u8 flags); /* Allowed flags for sqlite3BtreeDelete() and sqlite3BtreeInsert() */ #define BTREE_SAVEPOSITION 0x02 /* Leave cursor pointing at NEXT or PREV */ #define BTREE_AUXDELETE 0x04 /* not the primary delete operation */ #define BTREE_APPEND 0x08 /* Insert is likely an append */ /* An instance of the BtreePayload object describes the content of a single ** entry in either an index or table btree. ** ** Index btrees (used for indexes and also WITHOUT ROWID tables) contain ** an arbitrary key and no data. These btrees have pKey,nKey set to their ** key and pData,nData,nZero set to zero. |
︙ | ︙ | |||
278 279 280 281 282 283 284 | struct Mem *aMem; /* First of nMem value in the unpacked pKey */ u16 nMem; /* Number of aMem[] value. Might be zero */ int nData; /* Size of pData. 0 if none. */ int nZero; /* Extra zero data appended after pData,nData */ }; int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload, | | | 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 | struct Mem *aMem; /* First of nMem value in the unpacked pKey */ u16 nMem; /* Number of aMem[] value. Might be zero */ int nData; /* Size of pData. 0 if none. */ int nZero; /* Extra zero data appended after pData,nData */ }; int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload, int flags, int seekResult); int sqlite3BtreeFirst(BtCursor*, int *pRes); int sqlite3BtreeLast(BtCursor*, int *pRes); int sqlite3BtreeNext(BtCursor*, int *pRes); int sqlite3BtreeEof(BtCursor*); int sqlite3BtreePrevious(BtCursor*, int *pRes); i64 sqlite3BtreeIntegerKey(BtCursor*); int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*); |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
1680 1681 1682 1683 1684 1685 1686 | void sqlite3CompleteInsertion( Parse *pParse, /* The parser context */ Table *pTab, /* the table into which we are inserting */ int iDataCur, /* Cursor of the canonical data source */ int iIdxCur, /* First index cursor */ int regNewData, /* Range of content */ int *aRegIdx, /* Register used by each index. 0 for unused indices */ | | > > > > > > | | 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 | void sqlite3CompleteInsertion( Parse *pParse, /* The parser context */ Table *pTab, /* the table into which we are inserting */ int iDataCur, /* Cursor of the canonical data source */ int iIdxCur, /* First index cursor */ int regNewData, /* Range of content */ int *aRegIdx, /* Register used by each index. 0 for unused indices */ int update_flags, /* True for UPDATE, False for INSERT */ int appendBias, /* True if this is likely to be an append */ int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */ ){ Vdbe *v; /* Prepared statements under construction */ Index *pIdx; /* An index being inserted or updated */ u8 pik_flags; /* flag values passed to the btree insert */ int regData; /* Content registers (after the rowid) */ int regRec; /* Register holding assembled record for the table */ int i; /* Loop counter */ u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */ assert( update_flags==0 || update_flags==OPFLAG_ISUPDATE || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION) ); v = sqlite3GetVdbe(pParse); assert( v!=0 ); assert( pTab->pSelect==0 ); /* This table is not a VIEW */ for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( aRegIdx[i]==0 ) continue; bAffinityDone = 1; if( pIdx->pPartIdxWhere ){ sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2); VdbeCoverage(v); } sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i], aRegIdx[i]+1, pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn); pik_flags = 0; if( useSeekResult ) pik_flags = OPFLAG_USESEEKRESULT; if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ assert( pParse->nested==0 ); pik_flags |= OPFLAG_NCHANGE; pik_flags |= (update_flags & OPFLAG_SAVEPOSITION); } sqlite3VdbeChangeP5(v, pik_flags); } if( !HasRowid(pTab) ) return; regData = regNewData + 1; regRec = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec); if( !bAffinityDone ){ sqlite3TableAffinity(v, pTab, 0); sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol); } if( pParse->nested ){ pik_flags = 0; }else{ pik_flags = OPFLAG_NCHANGE; pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID); } if( appendBias ){ pik_flags |= OPFLAG_APPEND; } if( useSeekResult ){ pik_flags |= OPFLAG_USESEEKRESULT; } |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
5648 5649 5650 5651 5652 5653 5654 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); | | | 5648 5649 5650 5651 5652 5653 5654 5655 5656 5657 5658 5659 5660 5661 5662 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax, 0,flag,0); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); if( sqlite3WhereIsOrdered(pWInfo)>0 ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3037 3038 3039 3040 3041 3042 3043 | ** OPFLAG_FORDELETE == BTREE_FORDELETE ** OPFLAG_SAVEPOSITION == BTREE_SAVEPOSITION ** OPFLAG_AUXDELETE == BTREE_AUXDELETE */ #define OPFLAG_NCHANGE 0x01 /* OP_Insert: Set to update db->nChange */ /* Also used in P2 (not P5) of OP_Delete */ #define OPFLAG_EPHEM 0x01 /* OP_Column: Ephemeral output is ok */ | | | | 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 | ** OPFLAG_FORDELETE == BTREE_FORDELETE ** OPFLAG_SAVEPOSITION == BTREE_SAVEPOSITION ** OPFLAG_AUXDELETE == BTREE_AUXDELETE */ #define OPFLAG_NCHANGE 0x01 /* OP_Insert: Set to update db->nChange */ /* Also used in P2 (not P5) of OP_Delete */ #define OPFLAG_EPHEM 0x01 /* OP_Column: Ephemeral output is ok */ #define OPFLAG_LASTROWID 0x20 /* Set to update db->lastRowid */ #define OPFLAG_ISUPDATE 0x04 /* This OP_Insert is an sql UPDATE */ #define OPFLAG_APPEND 0x08 /* This is likely to be an append */ #define OPFLAG_USESEEKRESULT 0x10 /* Try to avoid a seek in BtreeInsert() */ #ifdef SQLITE_ENABLE_PREUPDATE_HOOK #define OPFLAG_ISNOOP 0x40 /* OP_Delete does pre-update-hook only */ #endif #define OPFLAG_LENGTHARG 0x40 /* OP_Column only used for length() */ #define OPFLAG_TYPEOFARG 0x80 /* OP_Column only used for typeof() */ #define OPFLAG_BULKCSR 0x01 /* OP_Open** used to open bulk cursor */ #define OPFLAG_SEEKEQ 0x02 /* OP_Open** cursor uses EQ seek only */ #define OPFLAG_FORDELETE 0x08 /* OP_Open should use BTREE_FORDELETE */ #define OPFLAG_P2ISREG 0x10 /* P2 to OP_Open** is a register number */ #define OPFLAG_PERMUTE 0x01 /* OP_Compare: use the permutation */ #define OPFLAG_SAVEPOSITION 0x02 /* OP_Delete/Insert: save cursor pos */ #define OPFLAG_AUXDELETE 0x04 /* OP_Delete: index in a DELETE op */ /* * Each trigger present in the database schema is stored as an instance of * struct Trigger. * * Pointers to instances of struct Trigger are stored in two ways. |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
101 102 103 104 105 106 107 | Index *pIdx; /* For looping over indices */ Index *pPk; /* The PRIMARY KEY index for WITHOUT ROWID tables */ int nIdx; /* Number of indices that need updating */ int iBaseCur; /* Base cursor number */ int iDataCur; /* Cursor for the canonical data btree */ int iIdxCur; /* Cursor for the first index */ sqlite3 *db; /* The database structure */ | | | > > > > > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | Index *pIdx; /* For looping over indices */ Index *pPk; /* The PRIMARY KEY index for WITHOUT ROWID tables */ int nIdx; /* Number of indices that need updating */ int iBaseCur; /* Base cursor number */ int iDataCur; /* Cursor for the canonical data btree */ int iIdxCur; /* Cursor for the first index */ sqlite3 *db; /* The database structure */ int *aRegIdx = 0; /* First register in array assigned to each index */ int *aXRef = 0; /* aXRef[i] is the index in pChanges->a[] of the ** an expression for the i-th column of the table. ** aXRef[i]==-1 if the i-th column is not changed. */ u8 *aToOpen; /* 1 for tables and indices to be opened */ u8 chngPk; /* PRIMARY KEY changed in a WITHOUT ROWID table */ u8 chngRowid; /* Rowid changed in a normal table */ u8 chngKey; /* Either chngPk or chngRowid */ Expr *pRowidExpr = 0; /* Expression defining the new record number */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int eOnePass; /* ONEPASS_XXX value from where.c */ int hasFK; /* True if foreign key processing is required */ int labelBreak; /* Jump here to break out of UPDATE loop */ int labelContinue; /* Jump here to continue next step of UPDATE loop */ int flags; /* Flags for sqlite3WhereBegin() */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True when updating a view (INSTEAD OF trigger) */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */ #endif int newmask; /* Mask of NEW.* columns accessed by BEFORE triggers */ int iEph = 0; /* Ephemeral table holding all primary key values */ int nKey = 0; /* Number of elements in regKey for WITHOUT ROWID */ int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ int addrOpen; /* Address of OP_OpenEphemeral */ int iPk; /* First of nPk cells holding PRIMARY KEY value */ i16 nPk; /* Number of components of the PRIMARY KEY */ int bReplace = 0; /* True if REPLACE conflict resolution might happen */ /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid = 0; /* The old rowid */ int regNewRowid = 0; /* The new rowid */ int regNew = 0; /* Content of the NEW.* table in triggers */ int regOld = 0; /* Content of OLD.* table in triggers */ |
︙ | ︙ | |||
286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 | }else{ reg = 0; for(i=0; i<pIdx->nKeyCol; i++){ i16 iIdxCol = pIdx->aiColumn[i]; if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){ reg = ++pParse->nMem; pParse->nMem += pIdx->nColumn; break; } } } if( reg==0 ) aToOpen[j+1] = 0; aRegIdx[j] = reg; } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); | > > > > > > > > > > | 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 | }else{ reg = 0; for(i=0; i<pIdx->nKeyCol; i++){ i16 iIdxCol = pIdx->aiColumn[i]; if( iIdxCol<0 || aXRef[iIdxCol]>=0 ){ reg = ++pParse->nMem; pParse->nMem += pIdx->nColumn; if( (onError==OE_Replace) || (onError==OE_Default && pIdx->onError==OE_Replace) ){ bReplace = 1; } break; } } } if( reg==0 ) aToOpen[j+1] = 0; aRegIdx[j] = reg; } if( bReplace ){ /* If REPLACE conflict resolution might be invoked, open cursors on all ** indexes in case they are needed to delete records. */ memset(aToOpen, 1, nIdx+1); } /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto update_cleanup; if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); |
︙ | ︙ | |||
345 346 347 348 349 350 351 | if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif | | < < < < | < < < < | < < | < < | | < < > | < < < < > > > > > > > > > > > > > > | < | > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > | < | < < < < < > | > | | < < < < | | < < < | < | | < < < | > > | | > | > > > | 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 | if( IsVirtual(pTab) ){ updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif /* Initialize the count of updated rows */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); }else{ assert( pPk!=0 ); nPk = pPk->nKeyCol; iPk = pParse->nMem+1; pParse->nMem += nPk; regKey = ++pParse->nMem; iEph = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_Null, 0, iPk); addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } /* Begin the database scan. ** ** Do not consider a single-pass strategy for a multi-row update if ** there are any triggers or foreign keys to process, or rows may ** be deleted as a result of REPLACE conflict handling. Any of these ** things might disturb a cursor being used to scan through the table ** or index, causing a single-pass approach to malfunction. */ flags = WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE; if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){ flags |= WHERE_ONEPASS_MULTIROW; } pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, flags, iIdxCur); if( pWInfo==0 ) goto update_cleanup; /* A one-pass strategy that might update more than one row may not ** be used if any column of the index used for the scan is being ** updated. Otherwise, if there is an index on "b", statements like ** the following could create an infinite loop: ** ** UPDATE t1 SET b=b+1 WHERE b>? ** ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI ** strategy that uses an index for which one or more columns are being ** updated. */ eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); if( eOnePass==ONEPASS_MULTI ){ int iCur = aiCurOnePass[1]; if( iCur>=0 && iCur!=iDataCur && aToOpen[iCur-iBaseCur] ){ eOnePass = ONEPASS_OFF; } assert( iCur!=iDataCur || !HasRowid(pTab) ); } if( HasRowid(pTab) ){ /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF ** mode, write the rowid into the FIFO. In either of the one-pass modes, ** leave it in register regOldRowid. */ sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid); if( eOnePass==ONEPASS_OFF ){ sqlite3VdbeAddOp2(v, OP_RowSetAdd, regRowSet, regOldRowid); } }else{ /* Read the PK of the current row into an array of registers. In ** ONEPASS_OFF mode, serialize the array into a record and store it in ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table ** is not required) and leave the PK fields in the array of registers. */ for(i=0; i<nPk; i++){ assert( pPk->aiColumn[i]>=0 ); sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur,pPk->aiColumn[i],iPk+i); } if( eOnePass ){ sqlite3VdbeChangeToNoop(v, addrOpen); nKey = nPk; regKey = iPk; }else{ sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, sqlite3IndexAffinityStr(db, pPk), nPk); sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk); } } if( eOnePass!=ONEPASS_MULTI ){ sqlite3WhereEnd(pWInfo); } labelBreak = sqlite3VdbeMakeLabel(v); if( !isView ){ int addrOnce = 0; /* Open every index that needs updating. */ if( eOnePass!=ONEPASS_OFF ){ if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0; if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0; } if( eOnePass==ONEPASS_MULTI && (nIdx-(aiCurOnePass[1]>=0))>0 ){ addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); } sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, iBaseCur, aToOpen, 0, 0); if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce); } /* Top of the update loop */ if( eOnePass!=ONEPASS_OFF ){ if( !isView && aiCurOnePass[0]!=iDataCur && aiCurOnePass[1]!=iDataCur ){ assert( pPk ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey); VdbeCoverageNeverTaken(v); } if( eOnePass==ONEPASS_SINGLE ){ labelContinue = labelBreak; }else{ labelContinue = sqlite3VdbeMakeLabel(v); } sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); VdbeCoverageIf(v, pPk==0); VdbeCoverageIf(v, pPk!=0); }else if( pPk ){ labelContinue = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v); addrTop = sqlite3VdbeAddOp2(v, OP_RowData, iEph, regKey); |
︙ | ︙ | |||
566 567 568 569 570 571 572 | sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i); } } } if( !isView ){ int addr1 = 0; /* Address of jump instruction */ | < | 601 602 603 604 605 606 607 608 609 610 611 612 613 614 | sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, regNew+i); } } } if( !isView ){ int addr1 = 0; /* Address of jump instruction */ /* Do constraint checks. */ assert( regOldRowid>0 ); sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace, aXRef); |
︙ | ︙ | |||
602 603 604 605 606 607 608 | ** pre-update hook. If the caller invokes preupdate_new(), the returned ** value is copied from memory cell (regNewRowid+1+iCol), where iCol ** is the column index supplied by the user. */ assert( regNew==regNewRowid+1 ); #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeAddOp3(v, OP_Delete, iDataCur, | | > > > > | | | > > > | 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 | ** pre-update hook. If the caller invokes preupdate_new(), the returned ** value is copied from memory cell (regNewRowid+1+iCol), where iCol ** is the column index supplied by the user. */ assert( regNew==regNewRowid+1 ); #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeAddOp3(v, OP_Delete, iDataCur, OPFLAG_ISUPDATE | ((hasFK || chngKey) ? 0 : OPFLAG_ISNOOP), regNewRowid ); if( eOnePass==ONEPASS_MULTI ){ assert( hasFK==0 && chngKey==0 ); sqlite3VdbeChangeP5(v, OPFLAG_SAVEPOSITION); } if( !pParse->nested ){ sqlite3VdbeAppendP4(v, pTab, P4_TABLE); } #else if( hasFK || chngKey ){ sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, 0); } #endif if( bReplace || chngKey ){ sqlite3VdbeJumpHere(v, addr1); } if( hasFK ){ sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngKey); } /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion( pParse, pTab, iDataCur, iIdxCur, regNewRowid, aRegIdx, OPFLAG_ISUPDATE | (eOnePass==ONEPASS_MULTI ? OPFLAG_SAVEPOSITION : 0), 0, 0 ); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); } |
︙ | ︙ | |||
645 646 647 648 649 650 651 | sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue); /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ | | > > > | 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 | sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue); /* Repeat the above with the next record to be updated, until ** all record selected by the WHERE clause have been updated. */ if( eOnePass==ONEPASS_SINGLE ){ /* Nothing to do at end-of-loop for a single-pass */ }else if( eOnePass==ONEPASS_MULTI ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3WhereEnd(pWInfo); }else if( pPk ){ sqlite3VdbeResolveLabel(v, labelContinue); sqlite3VdbeAddOp2(v, OP_Next, iEph, addrTop); VdbeCoverage(v); }else{ sqlite3VdbeGoto(v, labelContinue); } sqlite3VdbeResolveLabel(v, labelBreak); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
4417 4418 4419 4420 4421 4422 4423 | if( pData->flags & MEM_Zero ){ x.nZero = pData->u.nZero; }else{ x.nZero = 0; } x.pKey = 0; rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, | | | 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 | if( pData->flags & MEM_Zero ){ x.nZero = pData->u.nZero; }else{ x.nZero = 0; } x.pKey = 0; rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), seekResult ); pC->deferredMoveto = 0; pC->cacheStatus = CACHE_STALE; /* Invoke the update-hook if required. */ if( rc ) goto abort_due_to_error; if( db->xUpdateCallback && op ){ |
︙ | ︙ | |||
5082 5083 5084 5085 5086 5087 5088 | rc = sqlite3VdbeSorterWrite(pC, pIn2); }else{ x.nKey = pIn2->n; x.pKey = pIn2->z; x.aMem = aMem + pOp->p3; x.nMem = (u16)pOp->p4.i; rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, | | | 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 | rc = sqlite3VdbeSorterWrite(pC, pIn2); }else{ x.nKey = pIn2->n; x.pKey = pIn2->z; x.aMem = aMem + pOp->p3; x.nMem = (u16)pOp->p4.i; rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0) ); assert( pC->deferredMoveto==0 ); pC->cacheStatus = CACHE_STALE; } if( rc) goto abort_due_to_error; break; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
4945 4946 4947 4948 4949 4950 4951 | assert( x>=0 ); } x = sqlite3ColumnOfIndex(pIdx, x); if( x>=0 ){ pOp->p2 = x; pOp->p1 = pLevel->iIdxCur; } | | > | 4945 4946 4947 4948 4949 4950 4951 4952 4953 4954 4955 4956 4957 4958 4959 4960 | assert( x>=0 ); } x = sqlite3ColumnOfIndex(pIdx, x); if( x>=0 ){ pOp->p2 = x; pOp->p1 = pLevel->iIdxCur; } assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 || pWInfo->eOnePass ); }else if( pOp->opcode==OP_Rowid ){ pOp->p1 = pLevel->iIdxCur; pOp->opcode = OP_IdxRowid; } } } } |
︙ | ︙ |
Added test/update2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 | # 2017 January 9 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix update2 db func repeat [list string repeat] #------------------------------------------------------------------------- # 1.1.* A one-pass UPDATE that does balance() operations on the IPK index # that it is scanning. # # 1.2.* Same again, but with a WITHOUT ROWID table. # set nrow [expr 10] do_execsql_test 1.1.0 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(a INTEGER PRIMARY KEY, b); WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow ) INSERT INTO t1(b) SELECT char((i % 26) + 65) FROM s; INSERT INTO t2 SELECT * FROM t1; } do_execsql_test 1.1.1 { UPDATE t1 SET b = repeat(b, 100) } do_execsql_test 1.1.2 { SELECT * FROM t1; } [db eval { SELECT a, repeat(b, 100) FROM t2 }] do_execsql_test 1.2.0 { DROP TABLE t1; CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID; WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow ) INSERT INTO t1(a, b) SELECT i+1, char((i % 26) + 65) FROM s; } #explain_i { UPDATE t1 SET b = repeat(b, 100) } do_execsql_test 1.2.1 { UPDATE t1 SET b = repeat(b, 100) } do_execsql_test 1.2.2 { SELECT * FROM t1; } [db eval { SELECT a, repeat(b, 100) FROM t2 }] #------------------------------------------------------------------------- # A one-pass UPDATE that does balance() operations on the IPK index # that it is scanning. # do_execsql_test 2.1 { CREATE TABLE t3(a PRIMARY KEY, b, c); CREATE INDEX t3i ON t3(b); } {} do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=? } {} do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=? } {} #------------------------------------------------------------------------- # do_execsql_test 3.0 { CREATE TABLE t4(a PRIMARY KEY, b, c) WITHOUT ROWID; CREATE INDEX t4c ON t4(c); INSERT INTO t4 VALUES(1, 2, 3); INSERT INTO t4 VALUES(2, 3, 4); } do_execsql_test 3.1 { UPDATE t4 SET c=c+2 WHERE c>2; SELECT a, c FROM t4 ORDER BY a; } {1 5 2 6} #------------------------------------------------------------------------- # foreach {tn sql} { 1 { CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c); CREATE TABLE c1(a INTEGER PRIMARY KEY, b, c, d) } 2 { CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; CREATE TABLE c1(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID; } } { execsql { DROP TABLE IF EXISTS b1; DROP TABLE IF EXISTS c1; } execsql $sql do_execsql_test 4.$tn.0 { CREATE UNIQUE INDEX b1c ON b1(c); INSERT INTO b1 VALUES(1, 'a', 1); INSERT INTO b1 VALUES(2, 'b', 15); INSERT INTO b1 VALUES(3, 'c', 3); INSERT INTO b1 VALUES(4, 'd', 4); INSERT INTO b1 VALUES(5, 'e', 5); INSERT INTO b1 VALUES(6, 'f', 6); INSERT INTO b1 VALUES(7, 'g', 7); } do_execsql_test 4.$tn.1 { UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7; SELECT * FROM b1 ORDER BY a; } { 1 a 1 3 c 3 4 d 14 5 e 15 6 f 16 7 g 17 } do_execsql_test 4.$tn.2 { CREATE INDEX c1d ON c1(d, b); CREATE UNIQUE INDEX c1c ON c1(c, b); INSERT INTO c1 VALUES(1, 'a', 1, 1); INSERT INTO c1 VALUES(2, 'a', 15, 2); INSERT INTO c1 VALUES(3, 'a', 3, 3); INSERT INTO c1 VALUES(4, 'a', 4, 4); INSERT INTO c1 VALUES(5, 'a', 5, 5); INSERT INTO c1 VALUES(6, 'a', 6, 6); INSERT INTO c1 VALUES(7, 'a', 7, 7); } do_execsql_test 4.$tn.3 { UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7; SELECT * FROM c1 ORDER BY a; } { 1 a 1 1 3 a 3 3 4 a 14 4 5 a 15 5 6 a 16 6 7 a 17 7 } do_execsql_test 4.$tn.4 { PRAGMA integrity_check } ok do_execsql_test 4.$tn.5 { DROP INDEX c1d; DROP INDEX c1c; DELETE FROM c1; INSERT INTO c1 VALUES(1, 'a', 1, 1); INSERT INTO c1 VALUES(2, 'a', 15, 2); INSERT INTO c1 VALUES(3, 'a', 3, 3); INSERT INTO c1 VALUES(4, 'a', 4, 4); INSERT INTO c1 VALUES(5, 'a', 5, 5); INSERT INTO c1 VALUES(6, 'a', 6, 6); INSERT INTO c1 VALUES(7, 'a', 7, 7); CREATE INDEX c1d ON c1(d); CREATE UNIQUE INDEX c1c ON c1(c); } do_execsql_test 4.$tn.6 { UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7; SELECT * FROM c1 ORDER BY a; } { 1 a 1 1 3 a 3 3 4 a 14 4 5 a 15 5 6 a 16 6 7 a 17 7 } } finish_test |