Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -589,10 +589,53 @@ pCur->apPage[i] = 0; } pCur->iPage = -1; } +/* +** The cursor passed as the only argument must point to a valid entry +** when this function is called (i.e. have eState==CURSOR_VALID). This +** function saves the current cursor key in variables pCur->nKey and +** pCur->pKey. SQLITE_OK is returned if successful or an SQLite error +** code otherwise. +** +** If the cursor is open on an intkey table, then the integer key +** (the rowid) is stored in pCur->nKey and pCur->pKey is left set to +** NULL. If the cursor is open on a non-intkey table, then pCur->pKey is +** set to point to a malloced buffer pCur->nKey bytes in size containing +** the key. +*/ +static int saveCursorKey(BtCursor *pCur){ + int rc; + assert( CURSOR_VALID==pCur->eState ); + assert( 0==pCur->pKey ); + assert( cursorHoldsMutex(pCur) ); + + rc = sqlite3BtreeKeySize(pCur, &pCur->nKey); + assert( rc==SQLITE_OK ); /* KeySize() cannot fail */ + + /* If this is an intKey table, then the above call to BtreeKeySize() + ** stores the integer key in pCur->nKey. In this case this value is + ** all that is required. Otherwise, if pCur is not open on an intKey + ** table, then malloc space for and store the pCur->nKey bytes of key + ** data. */ + if( 0==pCur->curIntKey ){ + void *pKey = sqlite3Malloc( pCur->nKey ); + if( pKey ){ + rc = sqlite3BtreeKey(pCur, 0, (int)pCur->nKey, pKey); + if( rc==SQLITE_OK ){ + pCur->pKey = pKey; + }else{ + sqlite3_free(pKey); + } + }else{ + rc = SQLITE_NOMEM; + } + } + assert( !pCur->curIntKey || !pCur->pKey ); + return rc; +} /* ** Save the current cursor position in the variables BtCursor.nKey ** and BtCursor.pKey. The cursor's state is set to CURSOR_REQUIRESEEK. ** @@ -609,34 +652,12 @@ if( pCur->eState==CURSOR_SKIPNEXT ){ pCur->eState = CURSOR_VALID; }else{ pCur->skipNext = 0; } - rc = sqlite3BtreeKeySize(pCur, &pCur->nKey); - assert( rc==SQLITE_OK ); /* KeySize() cannot fail */ - - /* If this is an intKey table, then the above call to BtreeKeySize() - ** stores the integer key in pCur->nKey. In this case this value is - ** all that is required. Otherwise, if pCur is not open on an intKey - ** table, then malloc space for and store the pCur->nKey bytes of key - ** data. - */ - if( 0==pCur->curIntKey ){ - void *pKey = sqlite3Malloc( pCur->nKey ); - if( pKey ){ - rc = sqlite3BtreeKey(pCur, 0, (int)pCur->nKey, pKey); - if( rc==SQLITE_OK ){ - pCur->pKey = pKey; - }else{ - sqlite3_free(pKey); - } - }else{ - rc = SQLITE_NOMEM; - } - } - assert( !pCur->curIntKey || !pCur->pKey ); - + + rc = saveCursorKey(pCur); if( rc==SQLITE_OK ){ btreeReleaseAllCursorPages(pCur); pCur->eState = CURSOR_REQUIRESEEK; } @@ -8024,22 +8045,28 @@ end_insert: return rc; } /* -** Delete the entry that the cursor is pointing to. The cursor -** is left pointing at an arbitrary location. +** Delete the entry that the cursor is pointing to. +** +** If the second parameter is zero, then the cursor is left pointing at an +** arbitrary location after the delete. If it is non-zero, then the cursor +** is left in a state such that the next call to BtreeNext() or BtreePrev() +** moves it to the same row as it would if the call to BtreeDelete() had +** been omitted. */ -int sqlite3BtreeDelete(BtCursor *pCur){ +int sqlite3BtreeDelete(BtCursor *pCur, int bPreserve){ Btree *p = pCur->pBtree; BtShared *pBt = p->pBt; int rc; /* Return code */ MemPage *pPage; /* Page to delete cell from */ unsigned char *pCell; /* Pointer to cell to delete */ int iCellIdx; /* Index of cell to delete */ int iCellDepth; /* Depth of node containing pCell */ u16 szCell; /* Size of the cell being deleted */ + int bSkipnext = 0; /* Leaf cursor in SKIPNEXT state */ assert( cursorHoldsMutex(pCur) ); assert( pBt->inTransaction==TRANS_WRITE ); assert( (pBt->btsFlags & BTS_READ_ONLY)==0 ); assert( pCur->curFlags & BTCF_WriteFlag ); @@ -8065,14 +8092,11 @@ rc = sqlite3BtreePrevious(pCur, ¬Used); if( rc ) return rc; } /* Save the positions of any other cursors open on this table before - ** making any modifications. Make the page containing the entry to be - ** deleted writable. Then free any overflow pages associated with the - ** entry and finally remove the cell itself from within the page. - */ + ** making any modifications. */ if( pCur->curFlags & BTCF_Multiple ){ rc = saveAllCursors(pBt, pCur->pgnoRoot, pCur); if( rc ) return rc; } @@ -8080,10 +8104,35 @@ ** invalidate any incrblob cursors open on the row being deleted. */ if( pCur->pKeyInfo==0 ){ invalidateIncrblobCursors(p, pCur->info.nKey, 0); } + /* If the bPreserve flag is set to true, then the cursor position must + ** be preserved following this delete operation. If the current delete + ** will cause a b-tree rebalance, then this is done by saving the cursor + ** key and leaving the cursor in CURSOR_REQUIRESEEK state before + ** returning. + ** + ** Or, if the current delete will not cause a rebalance, then the cursor + ** will be left in CURSOR_SKIPNEXT state pointing to the entry immediately + ** before or after the deleted entry. In this case set bSkipnext to true. */ + if( bPreserve ){ + if( !pPage->leaf + || (pPage->nFree + cellSizePtr(pPage, pCell) + 2)>(pBt->usableSize*2/3) + ){ + /* A b-tree rebalance will be required after deleting this entry. + ** Save the cursor key. */ + rc = saveCursorKey(pCur); + if( rc ) return rc; + }else{ + bSkipnext = 1; + } + } + + /* Make the page containing the entry to be deleted writable. Then free any + ** overflow pages associated with the entry and finally remove the cell + ** itself from within the page. */ rc = sqlite3PagerWrite(pPage->pDbPage); if( rc ) return rc; rc = clearCell(pPage, pCell, &szCell); dropCell(pPage, iCellIdx, szCell, &rc); if( rc ) return rc; @@ -8133,11 +8182,26 @@ } rc = balance(pCur); } if( rc==SQLITE_OK ){ - moveToRoot(pCur); + if( bSkipnext ){ + assert( bPreserve && pCur->iPage==iCellDepth ); + assert( pPage->nCell>0 && iCellIdx<=pPage->nCell ); + pCur->eState = CURSOR_SKIPNEXT; + if( iCellIdx>=pPage->nCell ){ + pCur->skipNext = -1; + pCur->aiIdx[iCellDepth] = pPage->nCell-1; + }else{ + pCur->skipNext = 1; + } + }else{ + rc = moveToRoot(pCur); + if( bPreserve ){ + pCur->eState = CURSOR_REQUIRESEEK; + } + } } return rc; } /* Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -183,11 +183,11 @@ int bias, int *pRes ); int sqlite3BtreeCursorHasMoved(BtCursor*); int sqlite3BtreeCursorRestore(BtCursor*, int*); -int sqlite3BtreeDelete(BtCursor*); +int sqlite3BtreeDelete(BtCursor*, int); int sqlite3BtreeInsert(BtCursor*, const void *pKey, i64 nKey, const void *pData, int nData, int nZero, int bias, int seekResult); int sqlite3BtreeFirst(BtCursor*, int *pRes); int sqlite3BtreeLast(BtCursor*, int *pRes); Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -233,11 +233,11 @@ AuthContext sContext; /* Authorization context */ NameContext sNC; /* Name context to resolve expressions in */ int iDb; /* Database number */ int memCnt = -1; /* Memory cell used for change counting */ int rcauth; /* Value returned by authorization callback */ - int okOnePass; /* True for one-pass algorithm without the FIFO */ + int eOnePass; /* Non-zero for one-pass algorithm without the FIFO */ int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ u8 *aToOpen = 0; /* Open cursor iTabCur+j if aToOpen[j] is true */ Index *pPk; /* The PRIMARY KEY index on the table */ int iPk = 0; /* First of nPk registers holding PRIMARY KEY value */ i16 nPk = 1; /* Number of columns in the PRIMARY KEY */ @@ -245,16 +245,16 @@ i16 nKey; /* Number of memory cells in the row key */ int iEphCur = 0; /* Ephemeral table holding all primary key values */ int iRowSet = 0; /* Register for rowset of rows to delete */ int addrBypass = 0; /* Address of jump over the delete logic */ int addrLoop = 0; /* Top of the delete loop */ - int addrDelete = 0; /* Jump directly to the delete logic */ int addrEphOpen = 0; /* Instruction to open the Ephemeral table */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to delete from a view */ Trigger *pTrigger; /* List of table triggers, if required */ + int bComplex; /* True if there are either triggers or FKs */ #endif memset(&sContext, 0, sizeof(sContext)); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ @@ -274,13 +274,15 @@ ** deleted from is a view */ #ifndef SQLITE_OMIT_TRIGGER pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); isView = pTab->pSelect!=0; + bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0); #else # define pTrigger 0 # define isView 0 +# define bComplex 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif @@ -357,13 +359,11 @@ #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ - if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) - && 0==sqlite3FkRequired(pParse, pTab, 0, 0) - ){ + if( rcauth==SQLITE_OK && pWhere==0 && !bComplex && !IsVirtual(pTab) ){ assert( !isView ); sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName); if( HasRowid(pTab) ){ sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt, pTab->zName, P4_STATIC); @@ -373,10 +373,12 @@ sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb); } }else #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */ { + u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK; + wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW); if( HasRowid(pTab) ){ /* For a rowid table, initialize the RowSet to an empty set */ pPk = 0; nPk = 1; iRowSet = ++pParse->nMem; @@ -393,17 +395,21 @@ addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk); sqlite3VdbeSetP4KeyInfo(pParse, pPk); } /* Construct a query to find the rowid or primary key for every row - ** to be deleted, based on the WHERE clause. + ** to be deleted, based on the WHERE clause. Set variable eOnePass + ** to indicate the strategy used to implement this delete: + ** + ** 0: Two-pass approach - use a FIFO for rowids/PK values. + ** 1: One-pass approach - at most one row deleted. + ** 2: One-pass approach - any number of rows may be deleted. */ - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, - WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK, - iTabCur+1); + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1); if( pWInfo==0 ) goto delete_from_cleanup; - okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); + eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); + assert( IsVirtual(pTab)==0 || eOnePass==0 ); /* Keep track of the number of rows to be deleted */ if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } @@ -420,15 +426,14 @@ iKey = pParse->nMem + 1; iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0); if( iKey>pParse->nMem ) pParse->nMem = iKey; } - if( okOnePass ){ - /* For ONEPASS, no need to store the rowid/primary-key. There is only + if( eOnePass ){ + /* For ONEPASS, no need to store the rowid/primary-key. There is only ** one, so just keep it in its register(s) and fall through to the - ** delete code. - */ + ** delete code. */ nKey = nPk; /* OP_Found will use an unpacked key */ aToOpen = sqlite3DbMallocRaw(db, nIdx+2); if( aToOpen==0 ){ sqlite3WhereEnd(pWInfo); goto delete_from_cleanup; @@ -436,53 +441,54 @@ memset(aToOpen, 1, nIdx+1); aToOpen[nIdx+1] = 0; if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0; if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0; if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen); - addrDelete = sqlite3VdbeAddOp0(v, OP_Goto); /* Jump to DELETE logic */ - }else if( pPk ){ - /* Construct a composite key for the row to be deleted and remember it */ - iKey = ++pParse->nMem; - nKey = 0; /* Zero tells OP_Found to use a composite key */ - sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey, - sqlite3IndexAffinityStr(pParse->db, pPk), nPk); - sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey); - }else{ - /* Get the rowid of the row to be deleted and remember it in the RowSet */ - nKey = 1; /* OP_Seek always uses a single rowid */ - sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey); - } - - /* End of the WHERE loop */ - sqlite3WhereEnd(pWInfo); - if( okOnePass ){ - /* Bypass the delete logic below if the WHERE loop found zero rows */ - addrBypass = sqlite3VdbeMakeLabel(v); - sqlite3VdbeGoto(v, addrBypass); - sqlite3VdbeJumpHere(v, addrDelete); + }else{ + if( pPk ){ + /* Add the PK key for this row to the temporary table */ + iKey = ++pParse->nMem; + nKey = 0; /* Zero tells OP_Found to use a composite key */ + sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey, + sqlite3IndexAffinityStr(pParse->db, pPk), nPk); + sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey); + }else{ + /* Add the rowid of the row to be deleted to the RowSet */ + nKey = 1; /* OP_Seek always uses a single rowid */ + sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey); + } + } + + /* If this DELETE cannot use the ONEPASS strategy, this is the + ** end of the WHERE loop */ + if( eOnePass ){ + addrBypass = sqlite3VdbeMakeLabel(v); + }else{ + sqlite3WhereEnd(pWInfo); } /* Unless this is a view, open cursors for the table we are ** deleting from and all its indices. If this is a view, then the ** only effect this statement has is to fire the INSTEAD OF ** triggers. */ if( !isView ){ + int iAddrOnce = 0; + if( eOnePass==2 ) iAddrOnce = sqlite3CodeOnce(pParse); testcase( IsVirtual(pTab) ); sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen, &iDataCur, &iIdxCur); assert( pPk || IsVirtual(pTab) || iDataCur==iTabCur ); assert( pPk || IsVirtual(pTab) || iIdxCur==iDataCur+1 ); + if( eOnePass==2 ) sqlite3VdbeJumpHere(v, iAddrOnce); } /* Set up a loop over the rowids/primary-keys that were found in the ** where-clause loop above. */ - if( okOnePass ){ - /* Just one row. Hence the top-of-loop is a no-op */ + if( eOnePass ){ assert( nKey==nPk ); /* OP_Found will use an unpacked key */ - assert( !IsVirtual(pTab) ); if( aToOpen[iDataCur-iTabCur] ){ assert( pPk!=0 || pTab->pSelect!=0 ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey); VdbeCoverage(v); } @@ -506,17 +512,22 @@ sqlite3MayAbort(pParse); }else #endif { int count = (pParse->nested==0); /* True to count changes */ + int iIdxNoSeek = -1; + if( bComplex==0 && aiCurOnePass[1]!=iDataCur ){ + iIdxNoSeek = aiCurOnePass[1]; + } sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, - iKey, nKey, count, OE_Default, okOnePass); + iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek); } /* End of the loop over all rowids/primary-keys. */ - if( okOnePass ){ + if( eOnePass ){ sqlite3VdbeResolveLabel(v, addrBypass); + sqlite3WhereEnd(pWInfo); }else if( pPk ){ sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v); sqlite3VdbeJumpHere(v, addrLoop); }else{ sqlite3VdbeGoto(v, addrLoop); @@ -584,10 +595,29 @@ ** ** 3. The primary key for the row to be deleted must be stored in a ** sequence of nPk memory cells starting at iPk. If nPk==0 that means ** that a search record formed from OP_MakeRecord is contained in the ** single memory location iPk. +** +** eMode: +** Parameter eMode may be passed either 0, 1 or 2. If it is passed a +** non-zero value, then it is guaranteed that cursor iDataCur already +** points to the row to delete. If it is passed 0, then this function +** must seek iDataCur to the entry identified by iPk and nPk before +** reading from it. +** +** If eMode is passed the value 2, then this call is being made as part +** of a ONEPASS delete that affects multiple rows. In this case, if +** iIdxNoSeek is a valid cursor number (>=0), then its position should +** be preserved following the delete operation. Or, if iIdxNoSeek is not +** a valid cursor number, the position of iDataCur should be preserved +** instead. +** +** iIdxNoSeek: +** If iIdxNoSeek is a valid cursor number (>=0), then it identifies an +** index cursor (from within array of cursors starting at iIdxCur) that +** already points to the index entry to be deleted. */ void sqlite3GenerateRowDelete( Parse *pParse, /* Parsing context */ Table *pTab, /* Table containing the row to be deleted */ Trigger *pTrigger, /* List of triggers to (potentially) fire */ @@ -595,11 +625,12 @@ int iIdxCur, /* First index cursor */ int iPk, /* First memory cell containing the PRIMARY KEY */ i16 nPk, /* Number of PRIMARY KEY memory cells */ u8 count, /* If non-zero, increment the row change counter */ u8 onconf, /* Default ON CONFLICT policy for triggers */ - u8 bNoSeek /* iDataCur is already pointing to the row to delete */ + u8 eMode, /* See explanation above */ + int iIdxNoSeek /* Cursor number of cursor that does not need seeking */ ){ Vdbe *v = pParse->pVdbe; /* Vdbe */ int iOld = 0; /* First register in OLD.* array */ int iLabel; /* Label resolved to end of generated code */ u8 opSeek; /* Seek opcode */ @@ -612,11 +643,11 @@ /* Seek cursor iCur to the row to delete. If this row no longer exists ** (this can happen if a trigger program has already deleted it), do ** not attempt to delete it or fire any DELETE triggers. */ iLabel = sqlite3VdbeMakeLabel(v); opSeek = HasRowid(pTab) ? OP_NotExists : OP_NotFound; - if( !bNoSeek ){ + if( eMode==0 ){ sqlite3VdbeAddOp4Int(v, opSeek, iDataCur, iLabel, iPk, nPk); VdbeCoverageIf(v, opSeek==OP_NotExists); VdbeCoverageIf(v, opSeek==OP_NotFound); } @@ -672,15 +703,19 @@ /* Delete the index and table entries. Skip this step if pTab is really ** a view (in which case the only effect of the DELETE statement is to ** fire the INSTEAD OF triggers). */ if( pTab->pSelect==0 ){ - sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0); + sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,iIdxNoSeek); sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0)); if( count ){ sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT); } + if( iIdxNoSeek>=0 ){ + sqlite3VdbeAddOp1(v, OP_Delete, iIdxNoSeek); + } + sqlite3VdbeChangeP5(v, eMode==2); } /* 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 deleted. */ @@ -719,11 +754,12 @@ void sqlite3GenerateRowIndexDelete( Parse *pParse, /* Parsing and code generating context */ Table *pTab, /* Table containing the row to be deleted */ int iDataCur, /* Cursor of table holding data. */ int iIdxCur, /* First index cursor */ - int *aRegIdx /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */ + int *aRegIdx, /* Only delete if aRegIdx!=0 && aRegIdx[i]>0 */ + int iIdxNoSeek /* Do not delete from this cursor */ ){ int i; /* Index loop counter */ int r1 = -1; /* Register holding an index key */ int iPartIdxLabel; /* Jump destination for skipping partial index entries */ Index *pIdx; /* Current index */ @@ -735,15 +771,16 @@ pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab); for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ assert( iIdxCur+i!=iDataCur || pPk==pIdx ); if( aRegIdx!=0 && aRegIdx[i]==0 ) continue; if( pIdx==pPk ) continue; + if( iIdxCur+i==iIdxNoSeek ) continue; VdbeModuleComment((v, "GenRowIdxDel for %s", pIdx->zName)); r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 1, - &iPartIdxLabel, pPrior, r1); + &iPartIdxLabel, pPrior, r1); sqlite3VdbeAddOp3(v, OP_IdxDelete, iIdxCur+i, r1, - pIdx->uniqNotNull ? pIdx->nKeyCol : pIdx->nColumn); + pIdx->uniqNotNull ? pIdx->nKeyCol : pIdx->nColumn); sqlite3ResolvePartIdxLabel(pParse, iPartIdxLabel); pPrior = pIdx; } } Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1345,14 +1345,14 @@ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){ sqlite3MultiWrite(pParse); sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, - regNewData, 1, 0, OE_Replace, 1); + regNewData, 1, 0, OE_Replace, 1, -1); }else if( pTab->pIndex ){ sqlite3MultiWrite(pParse); - sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0); + sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, 0, -1); } seenReplace = 1; break; } case OE_Ignore: { @@ -1526,11 +1526,11 @@ sqlite3MultiWrite(pParse); if( db->flags&SQLITE_RecTriggers ){ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, - regR, nPkField, 0, OE_Replace, pIdx==pPk); + regR, nPkField, 0, OE_Replace, pIdx==pPk, -1); seenReplace = 1; break; } } sqlite3VdbeResolveLabel(v, addrUniqueOk); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2350,10 +2350,11 @@ #define WHERE_GROUPBY 0x0100 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0200 /* pOrderby is really a DISTINCT clause */ #define WHERE_WANT_DISTINCT 0x0400 /* All output needs to be distinct */ #define WHERE_SORTBYGROUP 0x0800 /* Support sqlite3WhereIsSorted() */ #define WHERE_REOPEN_IDX 0x1000 /* Try to use OP_ReopenIdx */ +#define WHERE_ONEPASS_MULTIROW 0x2000 /* ONEPASS is ok with multiple rows */ /* Allowed return values from sqlite3WhereIsDistinct() */ #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ @@ -3438,12 +3439,13 @@ int sqlite3ExprIsTableConstant(Expr*,int); int sqlite3ExprIsInteger(Expr*, int*); int sqlite3ExprCanBeNull(const Expr*); int sqlite3ExprNeedsNoAffinityChange(const Expr*, char); int sqlite3IsRowid(const char*); -void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8); -void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*); +void sqlite3GenerateRowDelete( + Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int); +void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int); int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int); void sqlite3ResolvePartIdxLabel(Parse*,int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int, u8,u8,int,int*); void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int); Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -585,11 +585,11 @@ }else{ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, 0, regOldRowid); } VdbeCoverageNeverTaken(v); } - sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx); + sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur, aRegIdx, -1); /* If changing the record number, delete the old record. */ if( hasFK || chngKey || pPk!=0 ){ sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, 0); } Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -3966,13 +3966,14 @@ /* Opcode: NotExists P1 P2 P3 * * ** Synopsis: intkey=r[P3] ** ** P1 is the index of a cursor open on an SQL table btree (with integer ** keys). P3 is an integer rowid. If P1 does not contain a record with -** rowid P3 then jump immediately to P2. If P1 does contain a record -** with rowid P3 then leave the cursor pointing at that record and fall -** through to the next instruction. +** rowid P3 then jump immediately to P2. Or, if P2 is 0, raise an +** SQLITE_CORRUPT error. If P1 does contain a record with rowid P3 then +** leave the cursor pointing at that record and fall through to the next +** instruction. ** ** The OP_NotFound opcode performs the same operation on index btrees ** (with arbitrary multi-value keys). ** ** This opcode leaves the cursor in a state where it cannot be advanced @@ -4006,11 +4007,14 @@ pC->nullRow = 0; pC->cacheStatus = CACHE_STALE; pC->deferredMoveto = 0; VdbeBranchTaken(res!=0,2); pC->seekResult = res; - if( res!=0 ) goto jump_to_p2; + if( res!=0 ){ + if( pOp->p2==0 && rc==SQLITE_OK ) rc = SQLITE_CORRUPT_BKPT; + goto jump_to_p2; + } break; } /* Opcode: Sequence P1 P2 * * * ** Synopsis: r[P2]=cursor[P1].ctr++ @@ -4272,18 +4276,19 @@ assert( pC->iDb>=0 ); } break; } -/* Opcode: Delete P1 P2 * P4 * +/* Opcode: Delete P1 P2 * P4 P5 ** ** Delete the record at which the P1 cursor is currently pointing. ** -** The cursor will be left pointing at either the next or the previous -** record in the table. If it is left pointing at the next record, then -** the next Next instruction will be a no-op. Hence it is OK to delete -** a record from within a Next loop. +** If the P5 parameter is non-zero, the cursor will be left pointing at +** either the next or the previous record in the table. If it is left +** pointing at the next record, then the next Next instruction will be a +** no-op. As a result, in this case it is OK to delete a record from within a +** Next loop. If P5 is zero, then the cursor is left in an undefined state. ** ** If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is ** incremented (otherwise not). ** ** P1 must not be pseudo-table. It has to be a real table with @@ -4300,23 +4305,27 @@ assert( pOp->p1>=0 && pOp->p1nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( pC->pCursor!=0 ); /* Only valid for real tables, no pseudotables */ assert( pC->deferredMoveto==0 ); + + if( pOp->p5 && db->xUpdateCallback && pOp->p4.z && pC->isTable ){ + sqlite3BtreeKeySize(pC->pCursor, &pC->movetoTarget); + } #ifdef SQLITE_DEBUG /* The seek operation that positioned the cursor prior to OP_Delete will ** have also set the pC->movetoTarget field to the rowid of the row that ** is being deleted */ - if( pOp->p4.z && pC->isTable ){ + if( pOp->p4.z && pC->isTable && pOp->p5==0 ){ i64 iKey = 0; sqlite3BtreeKeySize(pC->pCursor, &iKey); assert( pC->movetoTarget==iKey ); } #endif - rc = sqlite3BtreeDelete(pC->pCursor); + rc = sqlite3BtreeDelete(pC->pCursor, pOp->p5); pC->cacheStatus = CACHE_STALE; /* Invoke the update-hook if required. */ if( rc==SQLITE_OK && db->xUpdateCallback && pOp->p4.z && pC->isTable ){ db->xUpdateCallback(db->pUpdateArg, SQLITE_DELETE, @@ -4855,11 +4864,11 @@ #ifdef SQLITE_DEBUG { int i; for(i=0; ideferredMoveto==0 ); pC->cacheStatus = CACHE_STALE; break; } Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -3956,10 +3956,14 @@ WhereLoop *pLoop; /* Pointer to a single WhereLoop object */ int ii; /* Loop counter */ sqlite3 *db; /* Database connection */ int rc; /* Return code */ + assert( (wctrlFlags & WHERE_ONEPASS_MULTIROW)==0 || ( + (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 + && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 + )); /* Variable initialization */ db = pParse->db; memset(&sWLB, 0, sizeof(sWLB)); @@ -4197,15 +4201,20 @@ ** to use a one-pass algorithm, determine if this is appropriate. ** The one-pass algorithm only works if the WHERE clause constrains ** the statement to update or delete a single row. */ assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 ); - if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 - && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){ - pWInfo->okOnePass = 1; - if( HasRowid(pTabList->a[0].pTab) ){ - pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY; + if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 ){ + int wsFlags = pWInfo->a[0].pWLoop->wsFlags; + int bOnerow = (wsFlags & WHERE_ONEROW)!=0; + if( bOnerow || ( (wctrlFlags & WHERE_ONEPASS_MULTIROW) + && 0==(wsFlags & WHERE_VIRTUALTABLE) + )){ + pWInfo->okOnePass = bOnerow ? 1 : 2; + if( HasRowid(pTabList->a[0].pTab) ){ + pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY; + } } } /* Open all tables in the pTabList and any indices selected for ** searching those tables. Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -1067,11 +1067,15 @@ /* pIdx is a covering index. No need to access the main table. */ }else if( HasRowid(pIdx->pTable) ){ iRowidReg = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg); sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg); - sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg); /* Deferred seek */ + if( pWInfo->okOnePass ){ + sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg); + }else{ + sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg); /* Deferred seek */ + } }else if( iCur!=iIdxCur ){ Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable); iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol); for(j=0; jnKeyCol; j++){ k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]); Index: test/delete.test ================================================================== --- test/delete.test +++ test/delete.test @@ -65,11 +65,10 @@ } 1 do_test delete-3.1.7 { execsql {SELECT * FROM table1 ORDER BY f1} } {1 2 4 16} integrity_check delete-3.2 - # Semantic errors in the WHERE clause # do_test delete-4.1 { execsql {CREATE TABLE table2(f1 int, f2 int)} ADDED test/delete4.test Index: test/delete4.test ================================================================== --- /dev/null +++ test/delete4.test @@ -0,0 +1,102 @@ +# 2005 August 24 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this script is a test of the DELETE command. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix delete4 + +do_execsql_test 1.1 { + CREATE TABLE t1(x INTEGER PRIMARY KEY, y); + INSERT INTO t1 VALUES(1, 0); + INSERT INTO t1 VALUES(2, 1); + INSERT INTO t1 VALUES(3, 0); + INSERT INTO t1 VALUES(4, 1); + INSERT INTO t1 VALUES(5, 0); + INSERT INTO t1 VALUES(6, 1); + INSERT INTO t1 VALUES(7, 0); + INSERT INTO t1 VALUES(8, 1); +} +do_execsql_test 1.2 { + DELETE FROM t1 WHERE y=1; +} +do_execsql_test 1.3 { + SELECT x FROM t1; +} {1 3 5 7} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 2.1 { + CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z); + INSERT INTO t1 VALUES(1, 0, randomblob(200)); + INSERT INTO t1 VALUES(2, 1, randomblob(200)); + INSERT INTO t1 VALUES(3, 0, randomblob(200)); + INSERT INTO t1 VALUES(4, 1, randomblob(200)); + INSERT INTO t1 VALUES(5, 0, randomblob(200)); + INSERT INTO t1 VALUES(6, 1, randomblob(200)); + INSERT INTO t1 VALUES(7, 0, randomblob(200)); + INSERT INTO t1 VALUES(8, 1, randomblob(200)); +} +do_execsql_test 2.2 { + DELETE FROM t1 WHERE y=1; +} +do_execsql_test 2.3 { + SELECT x FROM t1; +} {1 3 5 7} + + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(2, 4); + INSERT INTO t1 VALUES(1, 5); + DELETE FROM t1 WHERE a=1; + SELECT * FROM t1; +} {2 4} + +#------------------------------------------------------------------------- +# DELETE statement that uses the OR optimization +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b); + CREATE INDEX i1a ON t1(a); + CREATE INDEX i1b ON t1(b); + INSERT INTO t1 VALUES(1, 'one', 'i'); + INSERT INTO t1 VALUES(2, 'two', 'ii'); + INSERT INTO t1 VALUES(3, 'three', 'iii'); + INSERT INTO t1 VALUES(4, 'four', 'iv'); + INSERT INTO t1 VALUES(5, 'one', 'i'); + INSERT INTO t1 VALUES(6, 'two', 'ii'); + INSERT INTO t1 VALUES(7, 'three', 'iii'); + INSERT INTO t1 VALUES(8, 'four', 'iv'); +} {} + +do_execsql_test 3.2 { + DELETE FROM t1 WHERE a='two' OR b='iv'; +} + +do_execsql_test 3.3 { + SELECT i FROM t1 ORDER BY i; +} {1 3 5 7} + +do_execsql_test 3.4 { + PRAGMA integrity_check; +} {ok} + + +finish_test Index: test/indexedby.test ================================================================== --- test/indexedby.test +++ test/indexedby.test @@ -229,17 +229,17 @@ # # Test that "INDEXED BY" can be used in a DELETE statement. # do_execsql_test indexedby-7.1 { EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-7.2 { EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 } {0 0 0 {SCAN TABLE t1}} do_execsql_test indexedby-7.3 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-7.4 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-7.5 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10