Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.425 2008/04/01 05:07:15 drh Exp $ +** $Id: select.c,v 1.426 2008/04/10 13:33:18 drh Exp $ */ #include "sqliteInt.h" /* @@ -2675,11 +2675,11 @@ } #endif /* SQLITE_OMIT_VIEW */ /* ** Analyze the SELECT statement passed as an argument to see if it -** is a min() or max() query. Return ORDERBY_MIN or ORDERBY_MAX if +** is a min() or max() query. Return WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX if ** it is, or 0 otherwise. At present, a query is considered to be ** a min()/max() query if: ** ** 1. There is a single object in the FROM clause. ** @@ -2688,22 +2688,22 @@ */ static int minMaxQuery(Parse *pParse, Select *p){ Expr *pExpr; ExprList *pEList = p->pEList; - if( pEList->nExpr!=1 ) return ORDERBY_NORMAL; + if( pEList->nExpr!=1 ) return WHERE_ORDERBY_NORMAL; pExpr = pEList->a[0].pExpr; pEList = pExpr->pList; if( pExpr->op!=TK_AGG_FUNCTION || pEList==0 || pEList->nExpr!=1 ) return 0; - if( pEList->a[0].pExpr->op!=TK_AGG_COLUMN ) return ORDERBY_NORMAL; - if( pExpr->token.n!=3 ) return ORDERBY_NORMAL; + if( pEList->a[0].pExpr->op!=TK_AGG_COLUMN ) return WHERE_ORDERBY_NORMAL; + if( pExpr->token.n!=3 ) return WHERE_ORDERBY_NORMAL; if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){ - return ORDERBY_MIN; + return WHERE_ORDERBY_MIN; }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){ - return ORDERBY_MAX; + return WHERE_ORDERBY_MAX; } - return ORDERBY_NORMAL; + return WHERE_ORDERBY_NORMAL; } /* ** This routine resolves any names used in the result set of the ** supplied SELECT statement. If the SELECT statement being resolved @@ -3557,11 +3557,11 @@ */ flag = minMaxQuery(pParse, p); if( flag ){ pDel = pMinMax = sqlite3ExprListDup(db, p->pEList->a[0].pExpr->pList); if( pMinMax && !db->mallocFailed ){ - pMinMax->a[0].sortOrder = ((flag==ORDERBY_MIN)?0:1); + pMinMax->a[0].sortOrder = ((flag==WHERE_ORDERBY_MIN)?0:1); pMinMax->a[0].pExpr->op = TK_COLUMN; } } /* This case runs if the aggregate has no GROUP BY clause. The @@ -3575,11 +3575,11 @@ goto select_end; } updateAccumulator(pParse, &sAggInfo); if( !pMinMax && flag ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); - VdbeComment((v, "%s() by index", (flag==ORDERBY_MIN?"min":"max"))); + VdbeComment((v, "%s() by index", (flag==WHERE_ORDERBY_MIN?"min":"max"))); } sqlite3WhereEnd(pWInfo); finalizeAggFunctions(pParse, &sAggInfo); pOrderBy = 0; if( pHaving ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -9,11 +9,11 @@ ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.689 2008/04/05 18:41:43 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.690 2008/04/10 13:33:18 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* @@ -1309,23 +1309,28 @@ ** FROM clause and the WhereLevel structure is a convenient place. */ sqlite3_index_info *pIdxInfo; /* Index info for n-th source table */ }; -#define ORDERBY_NORMAL 0 -#define ORDERBY_MIN 1 -#define ORDERBY_MAX 2 +/* +** Flags appropriate for the wflags parameter of sqlite3WhereBegin(). +*/ +#define WHERE_ORDERBY_NORMAL 0 /* No-op */ +#define WHERE_ORDERBY_MIN 1 /* ORDER BY processing for min() func */ +#define WHERE_ORDERBY_MAX 2 /* ORDER BY processing for max() func */ +#define WHERE_ONEPASS_DESIRED 4 /* Want to do one-pass UPDATE/DELETE */ /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed ** into the second half to give some continuity. */ struct WhereInfo { - Parse *pParse; + Parse *pParse; /* Parsing and code generating context */ + u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE or DELETE */ SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -10,11 +10,11 @@ ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle UPDATE statements. ** -** $Id: update.c,v 1.175 2008/04/01 05:07:15 drh Exp $ +** $Id: update.c,v 1.176 2008/04/10 13:33:18 drh Exp $ */ #include "sqliteInt.h" #ifndef SQLITE_OMIT_VIRTUALTABLE /* Forward declaration */ @@ -101,10 +101,11 @@ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int j1; /* Addresses of jump instructions */ + int okOnePass; /* True for one-pass algorithm without the FIFO */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* Trying to update a view */ int triggers_exist = 0; /* True if any row triggers exist */ #endif @@ -339,17 +340,20 @@ goto update_cleanup; } /* Begin the database scan */ - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0); + sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, + WHERE_ONEPASS_DESIRED); if( pWInfo==0 ) goto update_cleanup; + okOnePass = pWInfo->okOnePass; /* Remember the rowid of every item to be updated. */ - sqlite3VdbeAddOp2(v, IsVirtual(pTab) ? OP_VRowid : OP_Rowid,iCur,regOldRowid); - sqlite3VdbeAddOp2(v, OP_FifoWrite, regOldRowid, 0); + sqlite3VdbeAddOp2(v, IsVirtual(pTab)?OP_VRowid:OP_Rowid, iCur, regOldRowid); + if( !okOnePass ) sqlite3VdbeAddOp2(v, OP_FifoWrite, regOldRowid, 0); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); @@ -365,11 +369,11 @@ ** Open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ - sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); + if( !okOnePass ) sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenWrite); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ @@ -393,11 +397,17 @@ if( triggers_exist ){ sqlite3VdbeResolveLabel(v, addr); } /* Top of the update loop */ - addr = sqlite3VdbeAddOp2(v, OP_FifoRead, regOldRowid, 0); + if( okOnePass ){ + int a1 = sqlite3VdbeAddOp1(v, OP_NotNull, regOldRowid); + addr = sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeJumpHere(v, a1); + }else{ + addr = sqlite3VdbeAddOp2(v, OP_FifoRead, regOldRowid, 0); + } if( triggers_exist ){ int regRowid; int regRow; int regCols; Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -14,11 +14,11 @@ ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.297 2008/04/01 05:07:15 drh Exp $ +** $Id: where.c,v 1.298 2008/04/10 13:33:18 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". @@ -2000,11 +2000,11 @@ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ - u8 obflag /* One of ORDERBY_MIN, ORDERBY_MAX or ORDERBY_NORMAL */ + u8 wflags /* One of the WHERE_* flags defined in sqliteInt.h */ ){ int i; /* Loop counter */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ int brk, cont = 0; /* Addresses used during code generation */ @@ -2207,10 +2207,21 @@ ** clause is irrelevant. */ if( (andFlags & WHERE_UNIQUE)!=0 && ppOrderBy ){ *ppOrderBy = 0; } + + /* If the caller is an UPDATE or DELETE statement that is requesting + ** to use a one-pass algorithm, determine if this is appropriate. + ** The one-pass algorithm only works if the WHERE clause constraints + ** the statement to update a single row. + */ + assert( (wflags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 ); + if( (wflags & WHERE_ONEPASS_DESIRED)!=0 && (andFlags & WHERE_UNIQUE)!=0 ){ + pWInfo->okOnePass = 1; + pWInfo->a[0].flags &= ~WHERE_IDX_ONLY; + } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ @@ -2256,12 +2267,13 @@ sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, (const char*)pTab->pVtab, P4_VTAB); }else #endif if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){ - sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, OP_OpenRead); - if( pTab->nCol<(sizeof(Bitmask)*8) ){ + int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead; + sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op); + if( !pWInfo->okOnePass && pTab->nCol<(sizeof(Bitmask)*8) ){ Bitmask b = pTabItem->colUsed; int n = 0; for(; b; b=b>>1, n++){} sqlite3VdbeChangeP2(v, sqlite3VdbeCurrentAddr(v)-2, n); assert( n<=pTab->nCol ); @@ -2494,11 +2506,11 @@ ** a single iteration. This means that the first row returned ** should not have a NULL value stored in 'x'. If column 'x' is ** the first one after the nEq equality constraints in the index, ** this requires some special handling. */ - if( (obflag==ORDERBY_MIN) + if( (wflags&WHERE_ORDERBY_MIN)!=0 && (pLevel->flags&WHERE_ORDERBY) && (pIdx->nColumn>nEq) && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq]) ){ isMinQuery = 1; @@ -2633,11 +2645,11 @@ ** and leave the values of those terms on the stack. */ regBase = codeAllEqualityTerms(pParse, pLevel, &wc, notReady, 1); nxt = pLevel->nxt; - if( (obflag==ORDERBY_MIN) + if( (wflags&WHERE_ORDERBY_MIN)!=0 && (pLevel->flags&WHERE_ORDERBY) && (pIdx->nColumn>nEq) && (pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq]) ){ isMinQuery = 1; @@ -2851,11 +2863,11 @@ for(i=0, pLevel=pWInfo->a; inSrc; i++, pLevel++){ struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom]; Table *pTab = pTabItem->pTab; assert( pTab!=0 ); if( pTab->isEphem || pTab->pSelect ) continue; - if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){ + if( !pWInfo->okOnePass && (pLevel->flags & WHERE_IDX_ONLY)==0 ){ sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor); } if( pLevel->pIdx!=0 ){ sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur); } Index: test/veryquick.test ================================================================== --- test/veryquick.test +++ test/veryquick.test @@ -4,11 +4,11 @@ # May you share freely, never taking more than you give. # #*********************************************************************** # This file runs all tests. # -# $Id: veryquick.test,v 1.1 2008/03/31 23:51:35 drh Exp $ +# $Id: veryquick.test,v 1.2 2008/04/10 13:33:18 drh Exp $ proc lshift {lvar} { upvar $lvar l set ret [lindex $l 0] set l [lrange $l 1 end] @@ -93,10 +93,11 @@ speed2.test speed3.test speed4.test speed4p.test sqllimits1.test + vacuum3.test tkt2686.test thread001.test thread002.test