From 4445c0fa2196a8a6f58892c63be3306ff121dc6c Mon Sep 17 00:00:00 2001 From: dan Date: Tue, 8 Dec 2015 19:50:52 +0000 Subject: [PATCH] Experimental optimization for DELETE statements with WHERE clauses that qualify for the OR-optimization. FossilOrigin-Name: d52376df53f8d17c6f6e7ca8cc8da2a24f257a7f --- manifest | 23 +- manifest.uuid | 2 +- src/delete.c | 500 +++++++++++++++++++++++++++----------------- src/sqliteInt.h | 9 +- src/where.c | 58 ++++- test/fordelete.test | 2 +- 6 files changed, 386 insertions(+), 208 deletions(-) diff --git a/manifest b/manifest index 35182fc887..b7d4ed887d 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Changes\sto\savoid\sobscure,\stheoretical\sundefined\sbehavior.\sThis\sis\spreventative\nmeasures\sonly\s-\sno\sactual\sproblems\sobserved\son\stested\scompilers. -D 2015-12-07T16:43:44.102 +C Experimental\soptimization\sfor\sDELETE\sstatements\swith\sWHERE\sclauses\sthat\squalify\sfor\sthe\sOR-optimization. +D 2015-12-08T19:50:52.435 F Makefile.in 28bcd6149e050dff35d4dcfd97e890cd387a499d F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc e8fdca1cb89a1b58b5f4d3a130ea9a3d28cb314d @@ -291,7 +291,7 @@ F src/complete.c addcd8160b081131005d5bc2d34adf20c1c5c92f F src/ctime.c 60e135af364d777a9ab41c97e5e89cd224da6198 F src/date.c fb1c99172017dcc8e237339132c91a21a0788584 F src/dbstat.c ffd63fc8ba7541476ced189b95e95d7f2bc63f78 -F src/delete.c 00af9f08a15ddc5cba5962d3d3e5bf2d67b2e7da +F src/delete.c 97d9d98151a9668e4e3e2b49b09b4af78d756031 F src/expr.c cb1a419508e5b27769a91e00e36e94724e7b1d51 F src/fault.c 160a0c015b6c2629d3899ed2daf63d75754a32bb F src/fkey.c 31900763094a3736a5fc887469202eb579fef2d0 @@ -344,7 +344,7 @@ F src/shell.c 2796237990d42e6a5a7beafee65ef70cc8767d21 F src/sqlite.h.in 1248a78548024bdc8ef5893faa0ff9552b4cceb4 F src/sqlite3.rc 992c9f5fb8285ae285d6be28240a7e8d3a7f2bad F src/sqlite3ext.h dfbe62ffd95b99afe2140d8c35b180d11924072d -F src/sqliteInt.h 64256d193a16a147d9f6317cc4e095fdd3e0a2e9 +F src/sqliteInt.h 8014a4a02831a10a77717be0f63cda56b2ca3883 F src/sqliteLimit.h 216557999cb45f2e3578ed53ebefe228d779cb46 F src/status.c 70912d7be68e9e2dbc4010c93d344af61d4c59ba F src/table.c 51b46b2a62d1b3a959633d593b89bab5e2c9155e @@ -418,7 +418,7 @@ F src/vxworks.h c18586c8edc1bddbc15c004fa16aeb1e1342b4fb F src/wal.c 1569802364cd192bbd5c4a8ea3fd6de593edecbd F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c 2e14d17f592d176b6dc879c33fbdec4fbccaa2ba -F src/where.c b18edbb9e5afabb77f4f27550c471c5c824e0fe7 +F src/where.c 93f5c7ec7fbcf055fdf618cf43d9d1f0969842bc F src/whereInt.h e20801d89e34de1912bb6a3babb30c390da27add F src/wherecode.c dfbfe198e418b01f208b489e088edd230c91a4e7 F src/whereexpr.c eebba8340c90de73b3d3bbe8c43b84559b8e6e2c @@ -634,7 +634,7 @@ F test/fkey6.test abb59f866c1b44926fd02d1fdd217d831fe04f48 F test/fkey7.test 72e915890ee4a005daaf3002cb208e8fe973ac13 F test/fkey8.test 8f08203458321e6c19a263829de4cfc936274ab0 F test/fkey_malloc.test 594a7ea1fbab553c036c70813cd8bd9407d63749 -F test/fordelete.test ba12ec1d27cc34a4c23db4446029126d773f3849 +F test/fordelete.test 235fab593b486cc8de2fb64348544fe79e1855ad F test/format4.test 1f0cac8ff3895e9359ed87e41aaabee982a812eb F test/fts-9fd058691.test 78b887e30ae6816df0e1fed6259de4b5a64ad33c F test/fts1a.test 46090311f85da51bb33bd5ce84f7948359c6d8d7 @@ -1408,7 +1408,10 @@ F tool/vdbe_profile.tcl 246d0da094856d72d2c12efec03250d71639d19f F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 48bd54594752d5be3337f12c72f28d2080cb630b F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 4ecbc75b465533cf80e166a9d0879b9afd3fe2be -R 52003d35f295f961555bc017c6dea8be -U drh -Z df68b9901f4a3c159da303c7427e83e3 +P a9e819082ba19e72db03bba37edfb7702ff489a5 +R 6cf318895b737d01fcea67a968880787 +T *branch * onepass-delete-or +T *sym-onepass-delete-or * +T -sym-trunk * +U dan +Z 99461ed23b9ae573287349e17aa3866d diff --git a/manifest.uuid b/manifest.uuid index 72ed16cea5..343abb225e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -a9e819082ba19e72db03bba37edfb7702ff489a5 \ No newline at end of file +d52376df53f8d17c6f6e7ca8cc8da2a24f257a7f \ No newline at end of file diff --git a/src/delete.c b/src/delete.c index ed273bde81..cd0be700c0 100644 --- a/src/delete.c +++ b/src/delete.c @@ -208,6 +208,311 @@ limit_where_cleanup_2: #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */ /* && !defined(SQLITE_OMIT_SUBQUERY) */ + + +/* +** sqlite3WalkExpr() callback used by deleteSetColUsed(). +*/ +static int deleteSetColUsedExpr(Walker *pWalker, Expr *pExpr){ + if( pExpr->op==TK_COLUMN ){ + int i = pExpr->iColumn; + if( i>=0 ){ + pWalker->u.pSrcList->a[0].colUsed |= ((Bitmask)1)<<(i>=BMS ? BMS-1 : i); + } + } + return WRC_Continue; +} + +/* +** No-op sqlite3WalkExpr() callback used by deleteSetColUsed(). +*/ +static void deleteSetColUsedSelect(Walker *pWalker, Select *pSelect){ + UNUSED_PARAMETER2(pWalker, pSelect); +} + +/* +** Argument pSrc is guaranteed to contain a single table. All column +** references within expression pExpr have already been successfully +** resolved to this table. This function sets the pSrc->a[0].colUsed +** field to reflect the set of table columns used by pExpr (and no +** others). +*/ +static void deleteSetColUsed(Parse *pParse, SrcList *pSrc, Expr *pExpr){ + Walker w; + memset(&w, 0, sizeof(w)); + w.pParse = pParse; + w.u.pSrcList = pSrc; + w.xExprCallback = deleteSetColUsedExpr; + w.xSelectCallback2 = deleteSetColUsedSelect; + pSrc->a[0].colUsed = 0; + sqlite3WalkExpr(&w, pExpr); +} + +/* +** Generate code for a DELETE statement. This function is called by +** sqlite3DeleteFrom() after all table names and column references have +** been resolved. SQLITE_OK is returned if successful, or an SQLite +** error code otherwise. +** +** DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL; +** \________/ \________________/ +** pTabList pWhere +*/ +int deleteFrom( + Parse *pParse, /* The parser context */ + SrcList *pTabList, /* Table from which we should delete things */ + Expr *pWhere, /* The WHERE clause. May be null */ + 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 */ + int memCnt, /* Cell used for count-changes */ + int nIdx /* Number of indexes on table */ +){ + Vdbe *v = pParse->pVdbe; /* VM being coded */ + sqlite3 *db = pParse->db; /* Database handle */ + int iEphCur = 0; /* Ephemeral table holding all primary key values */ + int iRowSet = 0; /* Register for rowset of rows to delete */ + int iKey; /* Memory cell holding key of row to be deleted */ + i16 nKey; /* Number of memory cells in the row key */ + int addrBypass = 0; /* Address of jump over the delete logic */ + int addrLoop = 0; /* Top of the delete loop */ + int addrEphOpen = 0; /* Instruction to open the Ephemeral table */ + WhereInfo *pWInfo; /* Information about the WHERE clause */ + int eOnePass; /* ONEPASS_OFF or _SINGLE or _MULTI */ + int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ + u8 *aToOpen = 0; /* Open cursor iTabCur+j if aToOpen[j] is true */ + Index *pPk = 0; /* The PRIMARY KEY index on the table */ + int iPk = 0; /* First of nPk reg holding PRIMARY KEY value */ + i16 nPk = 1; /* Number of columns in the PRIMARY KEY */ + int iTabCur; /* Cursor number for the table */ + int iDataCur = 0; /* VDBE cursor for the canonical data source */ + int iIdxCur = 0; /* Cursor number of the first index */ + Table *pTab = pTabList->a[0].pTab; + u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK; + + iTabCur = pTabList->a[0].iCursor; + if( !HasRowid(pTab) ){ + pPk = sqlite3PrimaryKeyIndex(pTab); + assert( pPk!=0 ); + nPk = pPk->nKeyCol; + iPk = pParse->nMem+1; + pParse->nMem += nPk; + } + + if( isView ){ + iDataCur = iIdxCur = iTabCur; + } + + /* If there are triggers to fire or foreign keys to enforce, or the + ** table is a virtual table, the two pass strategy may be required. In + ** this case allocate either the rowset register (IPK tables) or an + ** ephemeral table (WITHOUT ROWID tables) in which the list of rows + ** to delete will be accumulated. + ** + ** Otherwise, if there are no triggers or foreign keys and this is not + ** a virtual table, set the WHERE_ONEPASS_MULTIROW flag on the mask of + ** flags that will be passed to sqlite3WhereBegin(). */ + if( bComplex || IsVirtual(pTab) ){ + if( HasRowid(pTab) ){ + /* For a rowid table, initialize the RowSet to an empty set */ + pPk = 0; + nPk = 1; + iRowSet = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); + }else{ + /* For a WITHOUT ROWID table, create an ephemeral table used to + ** hold all primary keys for rows to be deleted. */ + iEphCur = pParse->nTab++; + addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk); + sqlite3VdbeSetP4KeyInfo(pParse, pPk); + } + }else{ + wcf |= WHERE_ONEPASS_MULTIROW; + } + + /* Construct a query to find the rowid or primary key for every row + ** to be deleted, based on the WHERE clause. Set variable eOnePass + ** to indicate the strategy used to implement this delete: + ** + ** ONEPASS_OFF: Two-pass approach - use a FIFO for rowids/PK values. + ** ONEPASS_SINGLE: One-pass approach - at most one row deleted. + ** ONEPASS_MULTI: One-pass approach - any number of rows may be deleted. + ** ONEPASS_SPLIT_DELETE: See special case below. + */ + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1); + if( pWInfo==0 ) return SQLITE_NOMEM; + eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); + assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI ); + assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF ); + + /* If sqlite3WhereOkOnePass() returns SPLIT_DELETE, then the WHERE clause + ** consists of one or more indexable terms connected by OR operators. In + ** this case it is more efficient to run a separate delete program for + ** each OR'd term. */ + if( eOnePass==ONEPASS_SPLIT_DELETE ){ + int rc = SQLITE_OK; /* Return code */ + Expr *pExpr = 0; /* OR'd expression */ + int i; /* Counter variable */ + assert( !IsVirtual(pTab) && bComplex==0 ); + + /* This loop iterates once for each OR-connected term in the WHERE clause */ + for(i=0; rc==SQLITE_OK && (pExpr=sqlite3WhereSplitExpr(pWInfo, i)); i++){ + if( db->mallocFailed ) break; + deleteSetColUsed(pParse, pTabList, pExpr); + rc = deleteFrom(pParse, pTabList, pExpr, 0, 0, 0, memCnt, nIdx); + sqlite3ExprDelete(db, pExpr); + } + sqlite3WhereInfoFree(db, pWInfo); + return rc; + } + + /* Keep track of the number of rows to be deleted */ + if( db->flags & SQLITE_CountRows ){ + sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); + } + + /* Extract the rowid or primary key for the current row */ + if( pPk ){ + int i; + for(i=0; iaiColumn[i]>=0 ); + sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, + pPk->aiColumn[i], iPk+i); + } + iKey = iPk; + }else{ + iKey = pParse->nMem + 1; + iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0); + if( iKey>pParse->nMem ) pParse->nMem = iKey; + } + + if( eOnePass!=ONEPASS_OFF ){ + /* 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. */ + nKey = nPk; /* OP_Found will use an unpacked key */ + aToOpen = sqlite3DbMallocRaw(db, nIdx+2); + if( aToOpen==0 ){ + sqlite3WhereEnd(pWInfo); + return SQLITE_NOMEM; + } + 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); + }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!=ONEPASS_OFF ){ + 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; + u8 p5 = (eOnePass==ONEPASS_OFF ? 0 : OPFLAG_FORDELETE); + if( eOnePass==ONEPASS_MULTI ){ + iAddrOnce = sqlite3CodeOnce(pParse); VdbeCoverage(v); + } + testcase( IsVirtual(pTab) ); + sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, p5, iTabCur, + aToOpen, &iDataCur, &iIdxCur); + assert( pPk || IsVirtual(pTab) || iDataCur==iTabCur ); + assert( pPk || IsVirtual(pTab) || iIdxCur==iDataCur+1 ); + if( eOnePass==ONEPASS_MULTI ) sqlite3VdbeJumpHere(v, iAddrOnce); + } + + /* Set up a loop over the rowids/primary-keys that were found in the + ** where-clause loop above. + */ + if( eOnePass!=ONEPASS_OFF ){ + assert( nKey==nPk ); /* OP_Found will use an unpacked key */ + if( !IsVirtual(pTab) && aToOpen[iDataCur-iTabCur] ){ + assert( pPk!=0 || pTab->pSelect!=0 ); + sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey); + VdbeCoverage(v); + } + }else if( pPk ){ + addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_RowKey, iEphCur, iKey); + assert( nKey==0 ); /* OP_Found will use a composite key */ + }else{ + addrLoop = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, 0, iKey); + VdbeCoverage(v); + assert( nKey==1 ); + } + + /* Delete the row */ +#ifndef SQLITE_OMIT_VIRTUALTABLE + if( IsVirtual(pTab) ){ + const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); + sqlite3VtabMakeWritable(pParse, pTab); + sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iKey, pVTab, P4_VTAB); + sqlite3VdbeChangeP5(v, OE_Abort); + assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE ); + sqlite3MayAbort(pParse); + if( eOnePass==ONEPASS_SINGLE && sqlite3IsToplevel(pParse) ){ + pParse->isMultiWrite = 0; + } + }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, eOnePass, iIdxNoSeek); + } + + /* End of the loop over all rowids/primary-keys. */ + if( eOnePass!=ONEPASS_OFF ){ + sqlite3VdbeResolveLabel(v, addrBypass); + sqlite3WhereEnd(pWInfo); + }else if( pPk ){ + sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v); + sqlite3VdbeJumpHere(v, addrLoop); + }else{ + sqlite3VdbeGoto(v, addrLoop); + sqlite3VdbeJumpHere(v, addrLoop); + } + + /* Close the cursors open on the table and its indexes. */ + if( !isView && !IsVirtual(pTab) ){ + Index *pIdx; + int i; + if( !pPk ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur); + for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ + sqlite3VdbeAddOp1(v, OP_Close, iIdxCur + i); + } + } + + sqlite3DbFree(db, aToOpen); + return SQLITE_OK; +} + /* ** Generate code for a DELETE FROM statement. ** @@ -220,35 +525,21 @@ void sqlite3DeleteFrom( SrcList *pTabList, /* The table from which we should delete things */ Expr *pWhere /* The WHERE clause. May be null */ ){ + sqlite3 *db; Vdbe *v; /* The virtual database engine */ Table *pTab; /* The table from which records will be deleted */ const char *zDb; /* Name of database holding pTab */ int i; /* Loop counter */ - WhereInfo *pWInfo; /* Information about the WHERE clause */ Index *pIdx; /* For looping over indices of the table */ int iTabCur; /* Cursor number for the table */ int iDataCur = 0; /* VDBE cursor for the canonical data source */ int iIdxCur = 0; /* Cursor number of the first index */ int nIdx; /* Number of indices */ - sqlite3 *db; /* Main database structure */ 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 eOnePass; /* ONEPASS_OFF or _SINGLE or _MULTI */ - 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 */ - int iKey; /* Memory cell holding key of row to be deleted */ - 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 addrEphOpen = 0; /* Instruction to open the Ephemeral table */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to delete from a view */ @@ -379,180 +670,12 @@ void sqlite3DeleteFrom( } }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; - sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); - }else{ - /* For a WITHOUT ROWID table, create an ephemeral table used to - ** hold all primary keys for rows to be deleted. */ - pPk = sqlite3PrimaryKeyIndex(pTab); - assert( pPk!=0 ); - nPk = pPk->nKeyCol; - iPk = pParse->nMem+1; - pParse->nMem += nPk; - iEphCur = pParse->nTab++; - 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. Set variable eOnePass - ** to indicate the strategy used to implement this delete: - ** - ** ONEPASS_OFF: Two-pass approach - use a FIFO for rowids/PK values. - ** ONEPASS_SINGLE: One-pass approach - at most one row deleted. - ** ONEPASS_MULTI: One-pass approach - any number of rows may be deleted. - */ - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1); - if( pWInfo==0 ) goto delete_from_cleanup; - eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); - assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI ); - assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF ); - - /* Keep track of the number of rows to be deleted */ - if( db->flags & SQLITE_CountRows ){ - sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); - } - - /* Extract the rowid or primary key for the current row */ - if( pPk ){ - for(i=0; iaiColumn[i]>=0 ); - sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, - pPk->aiColumn[i], iPk+i); - } - iKey = iPk; - }else{ - iKey = pParse->nMem + 1; - iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0); - if( iKey>pParse->nMem ) pParse->nMem = iKey; - } - - if( eOnePass!=ONEPASS_OFF ){ - /* 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. */ - nKey = nPk; /* OP_Found will use an unpacked key */ - aToOpen = sqlite3DbMallocRaw(db, nIdx+2); - if( aToOpen==0 ){ - sqlite3WhereEnd(pWInfo); - goto delete_from_cleanup; - } - 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); - }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!=ONEPASS_OFF ){ - 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; - u8 p5 = (eOnePass==ONEPASS_OFF ? 0 : OPFLAG_FORDELETE); - if( eOnePass==ONEPASS_MULTI ){ - iAddrOnce = sqlite3CodeOnce(pParse); VdbeCoverage(v); - } - testcase( IsVirtual(pTab) ); - sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, p5, iTabCur, - aToOpen, &iDataCur, &iIdxCur); - assert( pPk || IsVirtual(pTab) || iDataCur==iTabCur ); - assert( pPk || IsVirtual(pTab) || iIdxCur==iDataCur+1 ); - if( eOnePass==ONEPASS_MULTI ) sqlite3VdbeJumpHere(v, iAddrOnce); - } - - /* Set up a loop over the rowids/primary-keys that were found in the - ** where-clause loop above. - */ - if( eOnePass!=ONEPASS_OFF ){ - assert( nKey==nPk ); /* OP_Found will use an unpacked key */ - if( !IsVirtual(pTab) && aToOpen[iDataCur-iTabCur] ){ - assert( pPk!=0 || pTab->pSelect!=0 ); - sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey); - VdbeCoverage(v); - } - }else if( pPk ){ - addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_RowKey, iEphCur, iKey); - assert( nKey==0 ); /* OP_Found will use a composite key */ - }else{ - addrLoop = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, 0, iKey); - VdbeCoverage(v); - assert( nKey==1 ); - } - - /* Delete the row */ -#ifndef SQLITE_OMIT_VIRTUALTABLE - if( IsVirtual(pTab) ){ - const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); - sqlite3VtabMakeWritable(pParse, pTab); - sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iKey, pVTab, P4_VTAB); - sqlite3VdbeChangeP5(v, OE_Abort); - assert( eOnePass==ONEPASS_OFF || eOnePass==ONEPASS_SINGLE ); - sqlite3MayAbort(pParse); - if( eOnePass==ONEPASS_SINGLE && sqlite3IsToplevel(pParse) ){ - pParse->isMultiWrite = 0; - } - }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, eOnePass, iIdxNoSeek); - } - - /* End of the loop over all rowids/primary-keys. */ - if( eOnePass!=ONEPASS_OFF ){ - sqlite3VdbeResolveLabel(v, addrBypass); - sqlite3WhereEnd(pWInfo); - }else if( pPk ){ - sqlite3VdbeAddOp2(v, OP_Next, iEphCur, addrLoop+1); VdbeCoverage(v); - sqlite3VdbeJumpHere(v, addrLoop); - }else{ - sqlite3VdbeGoto(v, addrLoop); - sqlite3VdbeJumpHere(v, addrLoop); - } - - /* Close the cursors open on the table and its indexes. */ - if( !isView && !IsVirtual(pTab) ){ - if( !pPk ) sqlite3VdbeAddOp1(v, OP_Close, iDataCur); - for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ - sqlite3VdbeAddOp1(v, OP_Close, iIdxCur + i); - } + if( deleteFrom(pParse, pTabList, pWhere, isView, pTrigger, bComplex, + memCnt, nIdx) + ){ + goto delete_from_cleanup; } } /* End non-truncate path */ @@ -578,7 +701,6 @@ delete_from_cleanup: sqlite3AuthContextPop(&sContext); sqlite3SrcListDelete(db, pTabList); sqlite3ExprDelete(db, pWhere); - sqlite3DbFree(db, aToOpen); return; } /* Make sure "isView" and other macros defined above are undefined. Otherwise diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 338a573253..ff48d1d05b 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3423,6 +3423,8 @@ void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int); void sqlite3WhereEnd(WhereInfo*); +void sqlite3WhereInfoFree(sqlite3*, WhereInfo*); +Expr *sqlite3WhereSplitExpr(WhereInfo*, int); u64 sqlite3WhereOutputRowCount(WhereInfo*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); @@ -3430,9 +3432,10 @@ int sqlite3WhereIsSorted(WhereInfo*); int sqlite3WhereContinueLabel(WhereInfo*); int sqlite3WhereBreakLabel(WhereInfo*); int sqlite3WhereOkOnePass(WhereInfo*, int*); -#define ONEPASS_OFF 0 /* Use of ONEPASS not allowed */ -#define ONEPASS_SINGLE 1 /* ONEPASS valid for a single row update */ -#define ONEPASS_MULTI 2 /* ONEPASS is valid for multiple rows */ +#define ONEPASS_OFF 0 /* Use of ONEPASS not allowed */ +#define ONEPASS_SINGLE 1 /* ONEPASS valid for a single row update */ +#define ONEPASS_MULTI 2 /* ONEPASS is valid for multiple rows */ +#define ONEPASS_SPLIT_DELETE 3 void sqlite3ExprCodeLoadIndexColumn(Parse*, Index*, int, int, int); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8); void sqlite3ExprCodeGetColumnToReg(Parse*, Table*, int, int, int); diff --git a/src/where.c b/src/where.c index 7d68664598..f739fe0c98 100644 --- a/src/where.c +++ b/src/where.c @@ -1774,7 +1774,7 @@ static void whereLoopDelete(sqlite3 *db, WhereLoop *p){ /* ** Free a WhereInfo structure */ -static void whereInfoFree(sqlite3 *db, WhereInfo *pWInfo){ +void sqlite3WhereInfoFree(sqlite3 *db, WhereInfo *pWInfo){ if( ALWAYS(pWInfo) ){ int i; for(i=0; inLevel; i++){ @@ -1793,6 +1793,52 @@ static void whereInfoFree(sqlite3 *db, WhereInfo *pWInfo){ } } +/* +** This function may only be called if sqlite3WhereOkOnePass() on the same +** WhereInfo object has returned ONEPASS_SPLIT_DELETE, indicating that the +** WHERE clause consists of a series of sub-expressions connected by OR +** operators. This function is used to access elements of this set of +** sub-expressions. +** +** OR-connected sub-expressions are numbered contiguously starting from +** zero. The sub-expression to return is identified by the iExpr parameter +** passed to this function. If iExpr is equal to or greater than the number +** of sub-expressions, NULL is returned. Otherwise, a pointer to a copy of +** sub-expression iExpr is returned. The caller is responsible for eventually +** deleting this object using sqlite3ExprDelete(). +** +** If an OOM error occurs, NULL is returned. In this case the mallocFailed +** field of the database handle (pWInfo->pParse->db->mallocFailed) is set +** to record the error. +*/ +Expr *sqlite3WhereSplitExpr(WhereInfo *pWInfo, int iExpr){ + sqlite3 *db = pWInfo->pParse->db; + WhereLoop *pLoop = pWInfo->pLoops; + WhereTerm *pTerm = pLoop->aLTerm[0]; + WhereClause *pOrWC = &pTerm->u.pOrInfo->wc; + Expr *pExpr = 0; + + assert( pWInfo->eOnePass==ONEPASS_SPLIT_DELETE ); + assert( pLoop->pNextLoop==0 ); + assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); + + if( iExprnTerm ){ + pExpr = sqlite3ExprDup(db, pOrWC->a[iExpr].pExpr, 0); + if( pExpr ){ + WhereClause *pWC = &pWInfo->sWC; + int ii; + for(ii=0; iinTerm; ii++){ + if( &pWC->a[ii]!=pTerm ){ + Expr *pLeft = sqlite3ExprDup(db, pWC->a[ii].pExpr, 0); + pExpr = sqlite3ExprAnd(db, pLeft, pExpr); + } + } + } + } + + return pExpr; +} + /* ** Return TRUE if all of the following are true: ** @@ -4255,7 +4301,6 @@ WhereInfo *sqlite3WhereBegin( } } WHERETRACE(0xffff,("*** Optimizer Finished ***\n")); - pWInfo->pParse->nQueryLoop += pWInfo->nRowOut; /* If the caller is an UPDATE or DELETE statement that is requesting ** to use a one-pass algorithm, determine if this is appropriate. @@ -4269,6 +4314,10 @@ WhereInfo *sqlite3WhereBegin( if( bOnerow || ( (wctrlFlags & WHERE_ONEPASS_MULTIROW) && 0==(wsFlags & WHERE_VIRTUALTABLE) )){ + if( (wsFlags & WHERE_MULTI_OR) && (wctrlFlags & WHERE_ONEPASS_MULTIROW) ){ + pWInfo->eOnePass = ONEPASS_SPLIT_DELETE; + return pWInfo; + } pWInfo->eOnePass = bOnerow ? ONEPASS_SINGLE : ONEPASS_MULTI; if( HasRowid(pTabList->a[0].pTab) && (wsFlags & WHERE_IDX_ONLY) ){ if( wctrlFlags & WHERE_ONEPASS_MULTIROW ){ @@ -4278,6 +4327,7 @@ WhereInfo *sqlite3WhereBegin( } } } + pWInfo->pParse->nQueryLoop += pWInfo->nRowOut; /* Open all tables in the pTabList and any indices selected for ** searching those tables. @@ -4437,7 +4487,7 @@ WhereInfo *sqlite3WhereBegin( whereBeginError: if( pWInfo ){ pParse->nQueryLoop = pWInfo->savedNQueryLoop; - whereInfoFree(db, pWInfo); + sqlite3WhereInfoFree(db, pWInfo); } return 0; } @@ -4621,6 +4671,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ /* Final cleanup */ pParse->nQueryLoop = pWInfo->savedNQueryLoop; - whereInfoFree(db, pWInfo); + sqlite3WhereInfoFree(db, pWInfo); return; } diff --git a/test/fordelete.test b/test/fordelete.test index 1e860e8867..2965b90ab8 100644 --- a/test/fordelete.test +++ b/test/fordelete.test @@ -70,7 +70,7 @@ do_execsql_test 2.0 { foreach {tn sql res} { 1 { DELETE FROM t2 WHERE a=?} { t2* t2a t2b* t2c* } 2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2 t2a t2b* t2c* } - 3 { DELETE FROM t2 WHERE a=? OR b=?} { t2 t2a* t2b* t2c* } + 3 { DELETE FROM t2 WHERE a=? OR b=?} { t2* t2* t2a t2a* t2b t2b* t2c* t2c* } 4 { DELETE FROM t2 WHERE +a=? } { t2 t2a* t2b* t2c* } 5 { DELETE FROM t2 WHERE rowid=? } { t2 t2a* t2b* t2c* } } { -- 2.47.2