Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1617,11 +1617,11 @@ sumEq += aSample[i].anEq[iCol]; nSum100 += 100; } } - if( nDist100>nSum100 ){ + if( nDist100>nSum100 && sumEqaAvgEq[iCol] = avgEq; } Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -947,11 +947,15 @@ } pTable->zName = zName; pTable->iPKey = -1; pTable->pSchema = db->aDb[iDb].pSchema; pTable->nRef = 1; +#ifdef SQLITE_DEFAULT_ROWEST + pTable->nRowLogEst = sqlite3LogEst(SQLITE_DEFAULT_ROWEST); +#else pTable->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); +#endif assert( pParse->pNewTable==0 ); pParse->pNewTable = pTable; /* If this is the magic sqlite_sequence table used by autoincrement, ** then record a pointer to this table in the main database structure Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -88,10 +88,13 @@ */ void sqlite3MaterializeView( Parse *pParse, /* Parsing context */ Table *pView, /* View definition */ Expr *pWhere, /* Optional WHERE clause to be added */ + ExprList *pOrderBy, /* Optional ORDER BY clause */ + Expr *pLimit, /* Optional LIMIT clause */ + Expr *pOffset, /* Optional OFFSET clause */ int iCur /* Cursor number for ephemeral table */ ){ SelectDest dest; Select *pSel; SrcList *pFrom; @@ -104,11 +107,12 @@ pFrom->a[0].zName = sqlite3DbStrDup(db, pView->zName); pFrom->a[0].zDatabase = sqlite3DbStrDup(db, db->aDb[iDb].zName); assert( pFrom->a[0].pOn==0 ); assert( pFrom->a[0].pUsing==0 ); } - pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0); + pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, pOrderBy, 0, + pLimit, pOffset); sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur); sqlite3Select(pParse, pSel, &dest); sqlite3SelectDelete(db, pSel); } #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */ @@ -129,22 +133,23 @@ ExprList *pOrderBy, /* The ORDER BY clause. May be null */ Expr *pLimit, /* The LIMIT clause. May be null */ Expr *pOffset, /* The OFFSET clause. May be null */ char *zStmtType /* Either DELETE or UPDATE. For err msgs. */ ){ - Expr *pWhereRowid = NULL; /* WHERE rowid .. */ + sqlite3 *db = pParse->db; + Expr *pLhs = NULL; /* LHS of IN(SELECT...) operator */ Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */ - Expr *pSelectRowid = NULL; /* SELECT rowid ... */ ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */ SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */ Select *pSelect = NULL; /* Complete SELECT tree */ + Table *pTab; /* Check that there isn't an ORDER BY without a LIMIT clause. */ - if( pOrderBy && (pLimit == 0) ) { + if( pOrderBy && pLimit==0 ) { sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); - goto limit_where_cleanup_2; + goto limit_where_cleanup; } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ @@ -160,46 +165,54 @@ ** becomes: ** DELETE FROM table_a WHERE rowid IN ( ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** ); */ - - pSelectRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); - if( pSelectRowid == 0 ) goto limit_where_cleanup_2; - pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid); - if( pEList == 0 ) goto limit_where_cleanup_2; + pTab = pSrc->a[0].pTab; + if( HasRowid(pTab) ){ + pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); + pEList = sqlite3ExprListAppend( + pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0, 0) + ); + }else{ + Index *pPk = sqlite3PrimaryKeyIndex(pTab); + if( pPk->nKeyCol==1 ){ + const char *zName = pTab->aCol[pPk->aiColumn[0]].zName; + pLhs = sqlite3Expr(db, TK_ID, zName); + pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName)); + }else{ + sqlite3ErrorMsg(pParse, + "ORDER BY and LIMIT are not supported for table %s", pTab->zName + ); + goto limit_where_cleanup; + } + } /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ + pSrc->a[0].pTab = 0; pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0); - if( pSelectSrc == 0 ) { - sqlite3ExprListDelete(pParse->db, pEList); - goto limit_where_cleanup_2; - } + pSrc->a[0].pTab = pTab; + pSrc->a[0].pIndex = 0; /* generate the SELECT expression tree. */ - pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0, - pOrderBy,0,pLimit,pOffset); - if( pSelect == 0 ) return 0; + pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, + pOrderBy,0,pLimit,pOffset + ); /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ - pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); - if( pWhereRowid == 0 ) goto limit_where_cleanup_1; - pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); - if( pInClause == 0 ) goto limit_where_cleanup_1; - - pInClause->x.pSelect = pSelect; - pInClause->flags |= EP_xIsSelect; - sqlite3ExprSetHeightAndFlags(pParse, pInClause); + pInClause = sqlite3PExpr(pParse, TK_IN, pLhs, 0, 0); + if( pInClause ){ + pInClause->x.pSelect = pSelect; + ExprSetProperty(pInClause, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, pInClause); + }else{ + sqlite3SelectDelete(pParse->db, pSelect); + } return pInClause; - /* something went wrong. clean up anything allocated. */ -limit_where_cleanup_1: - sqlite3SelectDelete(pParse->db, pSelect); - return 0; - -limit_where_cleanup_2: +limit_where_cleanup: sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); sqlite3ExprDelete(pParse->db, pLimit); sqlite3ExprDelete(pParse->db, pOffset); return 0; @@ -215,11 +228,14 @@ ** pTabList pWhere */ void sqlite3DeleteFrom( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table from which we should delete things */ - Expr *pWhere /* The WHERE clause. May be null */ + Expr *pWhere, /* The WHERE clause. May be null */ + ExprList *pOrderBy, /* ORDER BY clause. May be null */ + Expr *pLimit, /* LIMIT clause. May be null */ + Expr *pOffset /* OFFSET clause. May be null */ ){ 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 */ @@ -259,10 +275,11 @@ db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto delete_from_cleanup; } assert( pTabList->nSrc==1 ); + /* Locate the table which we want to delete. This table has to be ** put in an SrcList structure because some of the subroutines we ** will be calling are designed to work with multiple tables and expect ** an SrcList* parameter instead of just a Table* parameter. @@ -282,10 +299,20 @@ #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif + +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( !isView ){ + pWhere = sqlite3LimitWhere( + pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "DELETE" + ); + pOrderBy = 0; + pLimit = pOffset = 0; + } +#endif /* If pTab is really a view, make sure it has been initialized. */ if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto delete_from_cleanup; @@ -330,12 +357,16 @@ /* If we are trying to delete from a view, realize that view into ** an ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ - sqlite3MaterializeView(pParse, pTab, pWhere, iTabCur); + sqlite3MaterializeView(pParse, pTab, + pWhere, pOrderBy, pLimit, pOffset, iTabCur + ); iDataCur = iIdxCur = iTabCur; + pOrderBy = 0; + pLimit = pOffset = 0; } #endif /* Resolve the column names in the WHERE clause. */ @@ -551,10 +582,15 @@ delete_from_cleanup: sqlite3AuthContextPop(&sContext); sqlite3SrcListDelete(db, pTabList); sqlite3ExprDelete(db, pWhere); +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) + sqlite3ExprListDelete(db, pOrderBy); + sqlite3ExprDelete(db, pLimit); + sqlite3ExprDelete(db, pOffset); +#endif sqlite3DbFree(db, aToOpen); return; } /* Make sure "isView" and other macros defined above are undefined. Otherwise ** they may interfere with compilation of other functions in this file Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1749,10 +1749,15 @@ VdbeComment((v, "%s", pIdx->zName)); assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 ); eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0]; if( prRhsHasNull && !pTab->aCol[iCol].notNull ){ +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK + const i64 sOne = 1; + sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, + iTab, 0, 0, (u8*)&sOne, P4_INT64); +#endif *prRhsHasNull = ++pParse->nMem; sqlite3SetHasNullFlag(v, iTab, *prRhsHasNull); } sqlite3VdbeJumpHere(v, iAddr); } @@ -2204,21 +2209,10 @@ sqlite3ExprCachePop(pParse); VdbeComment((v, "end IN expr")); } #endif /* SQLITE_OMIT_SUBQUERY */ -/* -** Duplicate an 8-byte value -*/ -static char *dup8bytes(Vdbe *v, const char *in){ - char *out = sqlite3DbMallocRaw(sqlite3VdbeDb(v), 8); - if( out ){ - memcpy(out, in, 8); - } - return out; -} - #ifndef SQLITE_OMIT_FLOATING_POINT /* ** Generate an instruction that will put the floating point ** value described by z[0..n-1] into register iMem. ** @@ -2227,16 +2221,14 @@ ** like the continuation of the number. */ static void codeReal(Vdbe *v, const char *z, int negateFlag, int iMem){ if( ALWAYS(z!=0) ){ double value; - char *zV; sqlite3AtoF(z, &value, sqlite3Strlen30(z), SQLITE_UTF8); assert( !sqlite3IsNaN(value) ); /* The new AtoF never returns NaN */ if( negateFlag ) value = -value; - zV = dup8bytes(v, (char*)&value); - sqlite3VdbeAddOp4(v, OP_Real, 0, iMem, 0, zV, P4_REAL); + sqlite3VdbeAddOp4Dup8(v, OP_Real, 0, iMem, 0, (u8*)&value, P4_REAL); } } #endif @@ -2258,14 +2250,12 @@ i64 value; const char *z = pExpr->u.zToken; assert( z!=0 ); c = sqlite3DecOrHexToI64(z, &value); if( c==0 || (c==2 && negFlag) ){ - char *zV; if( negFlag ){ value = c==2 ? SMALLEST_INT64 : -value; } - zV = dup8bytes(v, (char*)&value); - sqlite3VdbeAddOp4(v, OP_Int64, 0, iMem, 0, zV, P4_INT64); + sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iMem, 0, (u8*)&value, P4_INT64); }else{ #ifdef SQLITE_OMIT_FLOATING_POINT sqlite3ErrorMsg(pParse, "oversized integer: %s%s", negFlag ? "-" : "", z); #else #ifndef SQLITE_OMIT_HEX_INTEGER Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -719,11 +719,11 @@ iSkip = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); VdbeCoverage(v); } pParse->disableTriggers = 1; - sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0); + sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0, 0, 0, 0); pParse->disableTriggers = 0; /* If the DELETE has generated immediate foreign key constraint ** violations, halt the VDBE and return an error at this point, before ** any modifications to the schema are made. This is because statement Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1647,34 +1647,48 @@ } /* ** pExpr points to an expression which implements a function. If ** it is appropriate to apply the LIKE optimization to that function -** then set aWc[0] through aWc[2] to the wildcard characters and -** return TRUE. If the function is not a LIKE-style function then -** return FALSE. +** then set aWc[0] through aWc[2] to the wildcard characters and the +** escape character and then return TRUE. If the function is not a +** LIKE-style function then return FALSE. +** +** The expression "a LIKE b ESCAPE c" is only considered a valid LIKE +** operator if c is a string literal that is exactly one byte in length. +** That one byte is stored in aWc[3]. aWc[3] is set to zero if there is +** no ESCAPE clause. ** ** *pIsNocase is set to true if uppercase and lowercase are equivalent for ** the function (default for LIKE). If the function makes the distinction ** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to ** false. */ int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){ FuncDef *pDef; - if( pExpr->op!=TK_FUNCTION - || !pExpr->x.pList - || pExpr->x.pList->nExpr!=2 - ){ + int nExpr; + if( pExpr->op!=TK_FUNCTION || !pExpr->x.pList ){ return 0; } assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); + nExpr = pExpr->x.pList->nExpr; pDef = sqlite3FindFunction(db, pExpr->u.zToken, sqlite3Strlen30(pExpr->u.zToken), - 2, SQLITE_UTF8, 0); + nExpr, SQLITE_UTF8, 0); if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){ return 0; } + if( nExpr<3 ){ + aWc[3] = 0; + }else{ + Expr *pEscape = pExpr->x.pList->a[2].pExpr; + char *zEscape; + if( pEscape->op!=TK_STRING ) return 0; + zEscape = pEscape->u.zToken; + if( zEscape[0]==0 || zEscape[1]!=0 ) return 0; + aWc[3] = zEscape[0]; + } /* The memcpy() statement assumes that the wildcard characters are ** the first three statements in the compareInfo structure. The ** asserts() that follow verify that assumption */ Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -709,19 +709,18 @@ %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); - W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE"); - sqlite3DeleteFrom(pParse,X,W); + sqlite3DeleteFrom(pParse,X,W,O,L.pLimit,L.pOffset); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); - sqlite3DeleteFrom(pParse,X,W); + sqlite3DeleteFrom(pParse,X,W,0,0,0); } %endif %type where_opt {Expr*} %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);} @@ -735,21 +734,20 @@ cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); - sqlite3Update(pParse,X,Y,W,R); + sqlite3Update(pParse,X,Y,W,R,O,L.pLimit,L.pOffset); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - sqlite3Update(pParse,X,Y,W,R); + sqlite3Update(pParse,X,Y,W,R,0,0,0); } %endif %type setlist {ExprList*} %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);} Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -633,11 +633,12 @@ */ case TK_ROW: { SrcList *pSrcList = pNC->pSrcList; struct SrcList_item *pItem; assert( pSrcList && pSrcList->nSrc==1 ); - pItem = pSrcList->a; + pItem = pSrcList->a; + assert( HasRowid(pItem->pTab) && pItem->pTab->pSelect==0 ); pExpr->op = TK_COLUMN; pExpr->pTab = pItem->pTab; pExpr->iTable = pItem->iCursor; pExpr->iColumn = -1; pExpr->affinity = SQLITE_AFF_INTEGER; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -51,11 +51,13 @@ int nOBSat; /* Number of ORDER BY terms satisfied by indices */ int iECursor; /* Cursor number for the sorter */ int regReturn; /* Register holding block-output return address */ int labelBkOut; /* Start label for the block-output subroutine */ int addrSortIndex; /* Address of the OP_SorterOpen or OP_OpenEphemeral */ + int labelDone; /* Jump here when done, ex: LIMIT reached */ u8 sortFlags; /* Zero or more SORTFLAG_* bits */ + u8 bOrderedInnerLoop; /* ORDER BY correctly sorts the inner loop */ }; #define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */ /* ** Delete all the content of a Select structure. Deallocate the structure @@ -499,27 +501,30 @@ int nBase = nExpr + bSeq + nData; /* Fields in sorter record */ int regBase; /* Regs for sorter record */ int regRecord = ++pParse->nMem; /* Assembled sorter record */ int nOBSat = pSort->nOBSat; /* ORDER BY terms to skip */ int op; /* Opcode to add sorter record to sorter */ + int iLimit; /* LIMIT counter */ assert( bSeq==0 || bSeq==1 ); if( nPrefixReg ){ assert( nPrefixReg==nExpr+bSeq ); regBase = regData - nExpr - bSeq; }else{ regBase = pParse->nMem + 1; pParse->nMem += nBase; } + assert( pSelect->iOffset==0 || pSelect->iLimit!=0 ); + iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit; + pSort->labelDone = sqlite3VdbeMakeLabel(v); sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, SQLITE_ECEL_DUP); if( bSeq ){ sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr); } if( nPrefixReg==0 ){ sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData); } - sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord); if( nOBSat>0 ){ int regPrevKey; /* The first nOBSat columns of the previous row */ int addrFirst; /* Address of the OP_IfNot opcode */ int addrJmp; /* Address of the OP_Jump opcode */ @@ -550,10 +555,14 @@ sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v); pSort->labelBkOut = sqlite3VdbeMakeLabel(v); pSort->regReturn = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor); + if( iLimit ){ + sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, pSort->labelDone); + VdbeCoverage(v); + } sqlite3VdbeJumpHere(v, addrFirst); sqlite3ExprCodeMove(pParse, regBase, regPrevKey, pSort->nOBSat); sqlite3VdbeJumpHere(v, addrJmp); } if( pSort->sortFlags & SORTFLAG_UseSorter ){ @@ -560,21 +569,36 @@ op = OP_SorterInsert; }else{ op = OP_IdxInsert; } sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord); - if( pSelect->iLimit ){ + if( iLimit ){ int addr; - int iLimit; - if( pSelect->iOffset ){ - iLimit = pSelect->iOffset+1; - }else{ - iLimit = pSelect->iLimit; - } + int r1 = 0; + /* Fill the sorter until it contains LIMIT+OFFSET entries. (The iLimit + ** register is initialized with value of LIMIT+OFFSET.) After the sorter + ** fills up, delete the least entry in the sorter after each insert. + ** Thus we never hold more than the LIMIT+OFFSET rows in memory at once */ addr = sqlite3VdbeAddOp3(v, OP_IfNotZero, iLimit, 0, -1); VdbeCoverage(v); sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor); + if( pSort->bOrderedInnerLoop ){ + r1 = ++pParse->nMem; + sqlite3VdbeAddOp3(v, OP_Column, pSort->iECursor, nExpr, r1); + VdbeComment((v, "seq")); + } sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor); + if( pSort->bOrderedInnerLoop ){ + /* If the inner loop is driven by an index such that values from + ** the same iteration of the inner loop are in sorted order, then + ** immediately jump to the next iteration of an inner loop if the + ** entry from the current iteration does not fit into the top + ** LIMIT+OFFSET entries of the sorter. */ + int iBrk = sqlite3VdbeCurrentAddr(v) + 2; + sqlite3VdbeAddOp3(v, OP_Eq, regBase+nExpr, iBrk, r1); + sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); + VdbeCoverage(v); + } sqlite3VdbeJumpHere(v, addr); } } /* @@ -1166,11 +1190,11 @@ SortCtx *pSort, /* Information on the ORDER BY clause */ int nColumn, /* Number of columns of data */ SelectDest *pDest /* Write the sorted results here */ ){ Vdbe *v = pParse->pVdbe; /* The prepared statement */ - int addrBreak = sqlite3VdbeMakeLabel(v); /* Jump here to exit loop */ + int addrBreak = pSort->labelDone; /* Jump here to exit loop */ int addrContinue = sqlite3VdbeMakeLabel(v); /* Jump here for next cycle */ int addr; int addrOnce = 0; int iTab; ExprList *pOrderBy = pSort->pOrderBy; @@ -1185,10 +1209,11 @@ int bSeq; /* True if sorter record includes seq. no. */ #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS struct ExprList_item *aOutEx = p->pEList->a; #endif + assert( addrBreak<0 ); if( pSort->labelBkOut ){ sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrBreak); sqlite3VdbeResolveLabel(v, pSort->labelBkOut); } @@ -2854,14 +2879,15 @@ ** row of results comes from selectA or selectB. Also add explicit ** collations to the ORDER BY clause terms so that when the subqueries ** to the right and the left are evaluated, they use the correct ** collation. */ - aPermute = sqlite3DbMallocRaw(db, sizeof(int)*nOrderBy); + aPermute = sqlite3DbMallocRaw(db, sizeof(int)*(nOrderBy + 1)); if( aPermute ){ struct ExprList_item *pItem; - for(i=0, pItem=pOrderBy->a; ia; i<=nOrderBy; i++, pItem++){ assert( pItem->u.x.iOrderByCol>0 && pItem->u.x.iOrderByCol<=p->pEList->nExpr ); aPermute[i] = pItem->u.x.iOrderByCol - 1; } pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1); @@ -3718,10 +3744,87 @@ sqlite3TreeViewSelect(0, p, 0); } #endif return 1; +} +#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */ + + + +#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) +/* +** Make copies of relevant WHERE clause terms of the outer query into +** the WHERE clause of subquery. Example: +** +** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1) WHERE x=5 AND y=10; +** +** Transformed into: +** +** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10) +** WHERE x=5 AND y=10; +** +** The hope is that the terms added to the inner query will make it more +** efficient. +** +** Do not attempt this optimization if: +** +** (1) The inner query is an aggregate. (In that case, we'd really want +** to copy the outer WHERE-clause terms onto the HAVING clause of the +** inner query. But they probably won't help there so do not bother.) +** +** (2) The inner query is the recursive part of a common table expression. +** +** (3) The inner query has a LIMIT clause (since the changes to the WHERE +** close would change the meaning of the LIMIT). +** +** (4) The inner query is the right operand of a LEFT JOIN. (The caller +** enforces this restriction since this routine does not have enough +** information to know.) +** +** (5) The WHERE clause expression originates in the ON or USING clause +** of a LEFT JOIN. +** +** Return 0 if no changes are made and non-zero if one or more WHERE clause +** terms are duplicated into the subquery. +*/ +static int pushDownWhereTerms( + sqlite3 *db, /* The database connection (for malloc()) */ + Select *pSubq, /* The subquery whose WHERE clause is to be augmented */ + Expr *pWhere, /* The WHERE clause of the outer query */ + int iCursor /* Cursor number of the subquery */ +){ + Expr *pNew; + int nChng = 0; + Select *pX; /* For looping over compound SELECTs in pSubq */ + if( pWhere==0 ) return 0; + for(pX=pSubq; pX; pX=pX->pPrior){ + if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){ + testcase( pX->selFlags & SF_Aggregate ); + testcase( pX->selFlags & SF_Recursive ); + testcase( pX!=pSubq ); + return 0; /* restrictions (1) and (2) */ + } + } + if( pSubq->pLimit!=0 ){ + return 0; /* restriction (3) */ + } + while( pWhere->op==TK_AND ){ + nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor); + pWhere = pWhere->pLeft; + } + if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */ + if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){ + nChng++; + while( pSubq ){ + pNew = sqlite3ExprDup(db, pWhere, 0); + pNew = substExpr(db, pNew, iCursor, pSubq->pEList); + pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew); + pSubq = pSubq->pPrior; + } + } + return nChng; } #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */ /* ** Based on the contents of the AggInfo structure indicated by the first @@ -4683,10 +4786,98 @@ } #else # define explainSimpleCount(a,b,c) #endif +#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION +/* +** Attempt to transform a query of the form +** +** SELECT count(*) FROM (SELECT x FROM t1 UNION ALL SELECT y FROM t2) +** +** Into this: +** +** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2) +** +** The transformation only works if all of the following are true: +** +** * The subquery is a UNION ALL of two or more terms +** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries +** * The outer query is a simple count(*) +** +** Return TRUE if the optimization is undertaken. +*/ +static int countOfViewOptimization(Parse *pParse, Select *p){ + Select *pSub, *pPrior; + Expr *pExpr; + Expr *pCount; + sqlite3 *db; + if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate query */ + if( p->pEList->nExpr!=1 ) return 0; /* Single result column */ + pExpr = p->pEList->a[0].pExpr; + if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */ + if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Must be count() */ + if( pExpr->x.pList!=0 ) return 0; /* Must be count(*) */ + if( p->pSrc->nSrc!=1 ) return 0; /* One table in the FROM clause */ + pSub = p->pSrc->a[0].pSelect; + if( pSub==0 ) return 0; /* The FROM is a subquery */ + if( pSub->pPrior==0 ) return 0; /* Must be a compound subquery */ + do{ + if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */ + if( pSub->pWhere ) return 0; /* No WHERE clause */ + if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */ + pSub = pSub->pPrior; /* Repeat over compound terms */ + }while( pSub ); + + /* If we reach this point, that means it is OK to perform the transformation */ + + db = pParse->db; + pCount = pExpr; + pExpr = 0; + pSub = p->pSrc->a[0].pSelect; + p->pSrc->a[0].pSelect = 0; + sqlite3SrcListDelete(db, p->pSrc); + p->pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*p->pSrc)); + while( pSub ){ + Expr *pTerm; + pPrior = pSub->pPrior; + pSub->pPrior = 0; + pSub->pNext = 0; + pSub->selFlags |= SF_Aggregate; + pSub->selFlags &= ~SF_Compound; + pSub->nSelectRow = 0; + sqlite3ExprListDelete(db, pSub->pEList); + pTerm = pPrior ? sqlite3ExprDup(db, pCount, 0) : pCount; + pSub->pEList = sqlite3ExprListAppend(pParse, 0, pTerm); + pTerm = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0); + if( pTerm ){ + pTerm->x.pSelect = pSub; + ExprSetProperty(pTerm, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, pTerm); + if( pExpr==0 ){ + pExpr = pTerm; + }else{ + pExpr = sqlite3PExpr(pParse, TK_PLUS, pTerm, pExpr, 0); + } + }else{ + sqlite3SelectDelete(db, pSub); + } + pSub = pPrior; + } + p->pEList->a[0].pExpr = pExpr; + p->selFlags &= ~SF_Aggregate; + +#if SELECTTRACE_ENABLED + if( sqlite3SelectTrace & 0x400 ){ + SELECTTRACE(0x400,pParse,p,("After count-of-view optimization:\n")); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + return 1; +} +#endif /* SQLITE_COUNTOFVIEW_OPTIMIZATION */ + /* ** Generate code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. ** See comments in sqliteInt.h for further information. @@ -4824,63 +5015,75 @@ if( isAggSub ){ isAgg = 1; p->selFlags |= SF_Aggregate; } i = -1; - }else if( pTabList->nSrc==1 - && OptimizationEnabled(db, SQLITE_SubqCoroutine) - ){ - /* Implement a co-routine that will return a single row of the result - ** set on each invocation. - */ - int addrTop = sqlite3VdbeCurrentAddr(v)+1; - pItem->regReturn = ++pParse->nMem; - sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); - VdbeComment((v, "%s", pItem->pTab->zName)); - pItem->addrFillSub = addrTop; - sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); - explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); - sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); - pItem->viaCoroutine = 1; - pItem->regResult = dest.iSdst; - sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn); - sqlite3VdbeJumpHere(v, addrTop-1); - sqlite3ClearTempRegCache(pParse); - }else{ - /* Generate a subroutine that will fill an ephemeral table with - ** the content of this subquery. pItem->addrFillSub will point - ** to the address of the generated subroutine. pItem->regReturn - ** is a register allocated to hold the subroutine return address - */ - int topAddr; - int onceAddr = 0; - int retAddr; - assert( pItem->addrFillSub==0 ); - pItem->regReturn = ++pParse->nMem; - topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); - pItem->addrFillSub = topAddr+1; - if( pItem->isCorrelated==0 ){ - /* If the subquery is not correlated and if we are not inside of - ** a trigger, then we only need to compute the value of the subquery - ** once. */ - onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); - VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); - }else{ - VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); - } - sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); - explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); - sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); - if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); - retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); - VdbeComment((v, "end %s", pItem->pTab->zName)); - sqlite3VdbeChangeP1(v, topAddr, retAddr); - sqlite3ClearTempRegCache(pParse); - } - if( /*pParse->nErr ||*/ db->mallocFailed ){ + }else{ + if( (pItem->jointype & JT_OUTER)==0 + && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor) + ){ +#if SELECTTRACE_ENABLED + if( sqlite3SelectTrace & 0x100 ){ + sqlite3DebugPrintf("After WHERE-clause push-down:\n"); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + } + if( pTabList->nSrc==1 + && OptimizationEnabled(db, SQLITE_SubqCoroutine) + ){ + /* Implement a co-routine that will return a single row of the result + ** set on each invocation. + */ + int addrTop = sqlite3VdbeCurrentAddr(v)+1; + pItem->regReturn = ++pParse->nMem; + sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); + VdbeComment((v, "%s", pItem->pTab->zName)); + pItem->addrFillSub = addrTop; + sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); + explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); + sqlite3Select(pParse, pSub, &dest); + pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); + pItem->viaCoroutine = 1; + pItem->regResult = dest.iSdst; + sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn); + sqlite3VdbeJumpHere(v, addrTop-1); + sqlite3ClearTempRegCache(pParse); + }else{ + /* Generate a subroutine that will fill an ephemeral table with + ** the content of this subquery. pItem->addrFillSub will point + ** to the address of the generated subroutine. pItem->regReturn + ** is a register allocated to hold the subroutine return address + */ + int topAddr; + int onceAddr = 0; + int retAddr; + assert( pItem->addrFillSub==0 ); + pItem->regReturn = ++pParse->nMem; + topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); + pItem->addrFillSub = topAddr+1; + if( pItem->isCorrelated==0 ){ + /* If the subquery is not correlated and if we are not inside of + ** a trigger, then we only need to compute the value of the subquery + ** once. */ + onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); + VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); + }else{ + VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); + } + sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); + explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); + sqlite3Select(pParse, pSub, &dest); + pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); + if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); + retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); + VdbeComment((v, "end %s", pItem->pTab->zName)); + sqlite3VdbeChangeP1(v, topAddr, retAddr); + sqlite3ClearTempRegCache(pParse); + } + } + if( db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ @@ -4905,10 +5108,20 @@ pParse->nSelectIndent--; #endif return rc; } #endif + +#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION + if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView) + && countOfViewOptimization(pParse, p) + ){ + if( db->mallocFailed ) goto select_end; + pEList = p->pEList; + pTabList = p->pSrc; + } +#endif /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and ** if the select-list is the same as the ORDER BY list, then this query ** can be rewritten as a GROUP BY. In other words, this: ** @@ -4999,10 +5212,11 @@ if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){ sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo); } if( sSort.pOrderBy ){ sSort.nOBSat = sqlite3WhereIsOrdered(pWInfo); + sSort.bOrderedInnerLoop = sqlite3WhereOrderedInnerLoop(pWInfo); if( sSort.nOBSat==sSort.pOrderBy->nExpr ){ sSort.pOrderBy = 0; } } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1243,10 +1243,11 @@ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ +#define SQLITE_CountOfView 0x1000 /* The count-of-view optimization */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ @@ -2274,10 +2275,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_ORDERBY_LIMIT 0x2000 /* ORDERBY+LIMIT on the inner loop */ /* Allowed return values from sqlite3WhereIsDistinct() */ #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ @@ -3280,17 +3282,18 @@ int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*); #endif -void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); -void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); +void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*, Expr*); +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*,Expr*); WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int); void sqlite3WhereEnd(WhereInfo*); u64 sqlite3WhereOutputRowCount(WhereInfo*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); +int sqlite3WhereOrderedInnerLoop(WhereInfo*); int sqlite3WhereIsSorted(WhereInfo*); int sqlite3WhereContinueLabel(WhereInfo*); int sqlite3WhereBreakLabel(WhereInfo*); int sqlite3WhereOkOnePass(WhereInfo*, int*); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8); @@ -3380,11 +3383,11 @@ int sqlite3SafetyCheckOk(sqlite3*); int sqlite3SafetyCheckSickOrOk(sqlite3*); void sqlite3ChangeCookie(Parse*, int); #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) -void sqlite3MaterializeView(Parse*, Table*, Expr*, int); +void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,Expr*,int); #endif #ifndef SQLITE_OMIT_TRIGGER void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*, Expr*,int, int); Index: src/trigger.c ================================================================== --- src/trigger.c +++ src/trigger.c @@ -728,11 +728,11 @@ case TK_UPDATE: { sqlite3Update(pParse, targetSrcList(pParse, pStep), sqlite3ExprListDup(db, pStep->pExprList, 0), sqlite3ExprDup(db, pStep->pWhere, 0), - pParse->eOrconf + pParse->eOrconf, 0, 0, 0 ); break; } case TK_INSERT: { sqlite3Insert(pParse, @@ -744,11 +744,11 @@ break; } case TK_DELETE: { sqlite3DeleteFrom(pParse, targetSrcList(pParse, pStep), - sqlite3ExprDup(db, pStep->pWhere, 0) + sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0, 0 ); break; } default: assert( pStep->op==TK_SELECT ); { SelectDest sDest; Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -89,11 +89,14 @@ void sqlite3Update( Parse *pParse, /* The parser context */ SrcList *pTabList, /* The table in which we should change things */ ExprList *pChanges, /* Things to be changed */ Expr *pWhere, /* The WHERE clause. May be null */ - int onError /* How to handle constraint errors */ + int onError, /* How to handle constraint errors */ + ExprList *pOrderBy, /* ORDER BY clause. May be null */ + Expr *pLimit, /* LIMIT clause. May be null */ + Expr *pOffset /* OFFSET clause. May be null */ ){ int i, j; /* Loop counters */ Table *pTab; /* The table to be updated */ int addrTop = 0; /* VDBE instruction address of the start of the loop */ WhereInfo *pWInfo; /* Information about the WHERE clause */ @@ -168,10 +171,20 @@ #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif + +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT + if( !isView ){ + pWhere = sqlite3LimitWhere( + pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "UPDATE" + ); + pOrderBy = 0; + pLimit = pOffset = 0; + } +#endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ @@ -329,11 +342,15 @@ /* If we are trying to update a view, realize that view into ** an ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ - sqlite3MaterializeView(pParse, pTab, pWhere, iDataCur); + sqlite3MaterializeView(pParse, pTab, + pWhere, pOrderBy, pLimit, pOffset, iDataCur + ); + pOrderBy = 0; + pLimit = pOffset = 0; } #endif /* Resolve the column names in all the expressions in the ** WHERE clause. @@ -663,10 +680,15 @@ sqlite3AuthContextPop(&sContext); sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */ sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pChanges); sqlite3ExprDelete(db, pWhere); +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) + sqlite3ExprListDelete(db, pOrderBy); + sqlite3ExprDelete(db, pLimit); + sqlite3ExprDelete(db, pOffset); +#endif return; } /* Make sure "isView" and other macros defined above are undefined. Otherwise ** they may interfere with compilation of other functions in this file ** (or in another file, if this file becomes part of the amalgamation). */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1978,15 +1978,18 @@ ** of integers in P4. ** ** The permutation is only valid until the next OP_Compare that has ** the OPFLAG_PERMUTE bit set in P5. Typically the OP_Permutation should ** occur immediately prior to the OP_Compare. +** +** The first integer in the P4 integer array is the length of the array +** and does not become part of the permutation. */ case OP_Permutation: { assert( pOp->p4type==P4_INTARRAY ); assert( pOp->p4.ai ); - aPermute = pOp->p4.ai; + aPermute = pOp->p4.ai + 1; break; } /* Opcode: Compare P1 P2 P3 P4 P5 ** Synopsis: r[P1@P3] <-> r[P2@P3] @@ -2288,16 +2291,20 @@ u32 avail; /* Number of bytes of available data */ u32 t; /* A type code from the record header */ u16 fx; /* pDest->flags value */ Mem *pReg; /* PseudoTable input register */ + pC = p->apCsr[pOp->p1]; p2 = pOp->p2; + + /* If the cursor cache is stale, bring it up-to-date */ + rc = sqlite3VdbeCursorMoveto(&pC, &p2); + assert( pOp->p3>0 && pOp->p3<=(p->nMem-p->nCursor) ); pDest = &aMem[pOp->p3]; memAboutToChange(p, pDest); assert( pOp->p1>=0 && pOp->p1nCursor ); - pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( p2nField ); aOffset = pC->aOffset; #ifndef SQLITE_OMIT_VIRTUALTABLE assert( pC->pVtabCursor==0 ); /* OP_Column never called on virtual table */ @@ -2304,12 +2311,10 @@ #endif pCrsr = pC->pCursor; assert( pCrsr!=0 || pC->pseudoTableReg>0 ); /* pCrsr NULL on PseudoTables */ assert( pCrsr!=0 || pC->nullRow ); /* pC->nullRow on PseudoTables */ - /* If the cursor cache is stale, bring it up-to-date */ - rc = sqlite3VdbeCursorMoveto(pC); if( rc ) goto abort_due_to_error; if( pC->cacheStatus!=p->cacheCtr ){ if( pC->nullRow ){ if( pCrsr==0 ){ assert( pC->pseudoTableReg>0 ); @@ -3496,10 +3501,30 @@ assert( pOp->p1>=0 && pOp->p1nCursor ); sqlite3VdbeFreeCursor(p, p->apCsr[pOp->p1]); p->apCsr[pOp->p1] = 0; break; } + +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK +/* Opcode: ColumnsUsed P1 * * P4 * +** +** This opcode (which only exists if SQLite was compiled with +** SQLITE_ENABLE_COLUMN_USED_MASK) identifies which columns of the +** table or index for cursor P1 are used. P4 is a 64-bit integer +** (P4_INT64) in which the first 63 bits are one for each of the +** first 63 columns of the table or index that are actually used +** by the cursor. The high-order bit is set if any column after +** the 64th is used. +*/ +case OP_ColumnsUsed: { + VdbeCursor *pC; + pC = p->apCsr[pOp->p1]; + assert( pC->pCursor ); + pC->maskUsed = *(u64*)pOp->p4.pI64; + break; +} +#endif /* Opcode: SeekGE P1 P2 P3 P4 * ** Synopsis: key=r[P3@P4] ** ** If cursor P1 refers to an SQL table (B-Tree that uses integer keys), @@ -3723,19 +3748,26 @@ pc = pOp->p2 - 1; } break; } -/* Opcode: Seek P1 P2 * * * +/* Opcode: Seek P1 P2 P3 P4 * ** Synopsis: intkey=r[P2] ** ** P1 is an open table cursor and P2 is a rowid integer. Arrange ** for P1 to move so that it points to the rowid given by P2. ** ** This is actually a deferred seek. Nothing actually happens until ** the cursor is used to read a record. That way, if no reads ** occur, no unnecessary I/O happens. +** +** P4 may contain an array of integers (type P4_INTARRAY) containing +** one entry for each column in the table P1 is open on. If so, then +** parameter P3 is a cursor open on a database index. If array entry +** a[i] is non-zero, then reading column (a[i]-1) from cursor P3 is +** equivalent to performing the deferred seek and then reading column i +** from P1. */ case OP_Seek: { /* in2 */ VdbeCursor *pC; assert( pOp->p1>=0 && pOp->p1nCursor ); @@ -3745,10 +3777,13 @@ assert( pC->isTable ); pC->nullRow = 0; pIn2 = &aMem[pOp->p2]; pC->movetoTarget = sqlite3VdbeIntValue(pIn2); pC->deferredMoveto = 1; + assert( pOp->p4type==P4_INTARRAY || pOp->p4.ai==0 ); + pC->aAltMap = pOp->p4.ai; + pC->pAltCursor = p->apCsr[pOp->p3]; break; } /* Opcode: Found P1 P2 P3 P4 * Index: src/vdbe.h ================================================================== --- src/vdbe.h +++ src/vdbe.h @@ -167,10 +167,11 @@ int sqlite3VdbeAddOp0(Vdbe*,int); int sqlite3VdbeAddOp1(Vdbe*,int,int); int sqlite3VdbeAddOp2(Vdbe*,int,int,int); int sqlite3VdbeAddOp3(Vdbe*,int,int,int,int); int sqlite3VdbeAddOp4(Vdbe*,int,int,int,int,const char *zP4,int); +int sqlite3VdbeAddOp4Dup8(Vdbe*,int,int,int,int,const u8*,int); int sqlite3VdbeAddOp4Int(Vdbe*,int,int,int,int,int); int sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp, int iLineno); void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*); void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -58,10 +58,11 @@ ** A pseudo-table is a single-row table implemented by registers. ** ** Every cursor that the virtual machine has open is represented by an ** instance of the following structure. */ +typedef struct VdbeCursor VdbeCursor; struct VdbeCursor { BtCursor *pCursor; /* The cursor structure of the backend */ Btree *pBt; /* Separate file holding temporary table */ KeyInfo *pKeyInfo; /* Info about index keys needed by index cursors */ int seekResult; /* Result of previous sqlite3BtreeMoveto() */ @@ -81,11 +82,15 @@ Pgno pgnoRoot; /* Root page of the open btree cursor */ sqlite3_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ i64 seqCount; /* Sequence counter */ i64 movetoTarget; /* Argument to the deferred sqlite3BtreeMoveto() */ VdbeSorter *pSorter; /* Sorter object for OP_SorterOpen cursors */ - + VdbeCursor *pAltCursor; /* Associated index cursor from which to read */ + int *aAltMap; /* Mapping from table to index column numbers */ +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK + u64 maskUsed; /* Mask of columns used by this cursor */ +#endif /* Cached information about the header for the data record that the ** cursor is currently pointing to. Only valid if cacheStatus matches ** Vdbe.cacheCtr. Vdbe.cacheCtr will never take on the value of ** CACHE_STALE and so setting cacheStatus=CACHE_STALE guarantees that ** the cache is out of date. @@ -102,11 +107,10 @@ u32 aType[1]; /* Type values for all entries in the record */ /* 2*nField extra array elements allocated for aType[], beyond the one ** static element declared in the structure. nField total array slots for ** aType[] and nField+1 array slots for aOffset[] */ }; -typedef struct VdbeCursor VdbeCursor; /* ** When a sub-program is executed (OP_Program), a structure of this type ** is allocated to store the current value of the program counter, as ** well as the current memory cell array and various other frame specific @@ -391,11 +395,11 @@ /* ** Function prototypes */ void sqlite3VdbeFreeCursor(Vdbe *, VdbeCursor*); void sqliteVdbePopStack(Vdbe*,int); -int sqlite3VdbeCursorMoveto(VdbeCursor*); +int sqlite3VdbeCursorMoveto(VdbeCursor**, int*); int sqlite3VdbeCursorRestore(VdbeCursor*); #if defined(SQLITE_DEBUG) || defined(VDBE_PROFILE) void sqlite3VdbePrintOp(FILE*, int, Op*); #endif u32 sqlite3VdbeSerialTypeLen(u32); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -219,10 +219,27 @@ ){ int addr = sqlite3VdbeAddOp3(p, op, p1, p2, p3); sqlite3VdbeChangeP4(p, addr, zP4, p4type); return addr; } + +/* +** Add an opcode that includes the p4 value with a P4_INT64 type. +*/ +int sqlite3VdbeAddOp4Dup8( + Vdbe *p, /* Add the opcode to this VM */ + int op, /* The new opcode */ + int p1, /* The P1 operand */ + int p2, /* The P2 operand */ + int p3, /* The P3 operand */ + const u8 *zP4, /* The P4 operand */ + int p4type /* P4 operand type */ +){ + char *p4copy = sqlite3DbMallocRaw(sqlite3VdbeDb(p), 8); + if( p4copy ) memcpy(p4copy, zP4, 8); + return sqlite3VdbeAddOp4(p, op, p1, p2, p3, p4copy, p4type); +} /* ** Add an OP_ParseSchema opcode. This routine is broken out from ** sqlite3VdbeAddOp4() since it needs to also needs to mark all btrees ** as having been used. @@ -1121,11 +1138,25 @@ sqlite3_snprintf(nTemp, zTemp, "vtab:%p", pVtab); break; } #endif case P4_INTARRAY: { - sqlite3_snprintf(nTemp, zTemp, "intarray"); + int i, j; + int *ai = pOp->p4.ai; + int n = ai[0]; /* The first element of an INTARRAY is always the + ** count of the number of elements to follow */ + zTemp[0] = '['; + for(i=j=1; i1 ) zTemp[j++] = ','; + sqlite3_snprintf(nTemp-j, zTemp+j, "%d", ai[i]); + j += sqlite3Strlen30(zTemp+j); + } + if( ideferredMoveto ){ + int iMap; + if( p->aAltMap && (iMap = p->aAltMap[1+*piCol])>0 ){ + *pp = p->pAltCursor; + *piCol = iMap - 1; + return SQLITE_OK; + } return handleDeferredMoveto(p); } if( p->pCursor && sqlite3BtreeCursorHasMoved(p->pCursor) ){ return handleMovedCursor(p); } Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -39,10 +39,22 @@ ** Return FALSE if the output needs to be sorted. */ int sqlite3WhereIsOrdered(WhereInfo *pWInfo){ return pWInfo->nOBSat; } + +/* +** Return TRUE if the innermost loop of the WHERE clause implementation +** returns rows in ORDER BY order for complete run of the inner loop. +** +** Across multiple iterations of outer loops, the output rows need not be +** sorted. As long as rows are sorted for just the innermost loop, this +** routine can return TRUE. +*/ +int sqlite3WhereOrderedInnerLoop(WhereInfo *pWInfo){ + return pWInfo->bOrderedInnerLoop; +} /* ** Return the VDBE address or label to jump to in order to continue ** immediately with the next row of a WHERE clause. */ @@ -643,11 +655,11 @@ const char *z = 0; /* String on RHS of LIKE operator */ Expr *pRight, *pLeft; /* Right and left size of LIKE operator */ ExprList *pList; /* List of operands to the LIKE operator */ int c; /* One character in z[] */ int cnt; /* Number of non-wildcard prefix characters */ - char wc[3]; /* Wildcard characters */ + char wc[4]; /* Wildcard characters */ sqlite3 *db = pParse->db; /* Database connection */ sqlite3_value *pVal = 0; int op; /* Opcode of pRight */ if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){ @@ -681,20 +693,47 @@ assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER ); }else if( op==TK_STRING ){ z = pRight->u.zToken; } if( z ){ + /* Count the number of prefix characters prior to the first wildcard */ cnt = 0; while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; + if( c==wc[3] && z[cnt]!=0 ){ + if( z[cnt++]>0xc0 ) while( (z[cnt]&0xc0)==0x80 ){ cnt++; } + } } + + /* The optimization is possible only if (1) the pattern does not begin + ** with a wildcard and if (2) the non-wildcard prefix does not end with + ** an (illegal 0xff) character. The second condition is necessary so + ** that we can increment the prefix key to find an upper bound for the + ** range search. + */ if( cnt!=0 && 255!=(u8)z[cnt-1] ){ Expr *pPrefix; + + /* A "complete" match if the pattern ends with "*" or "%" */ *pisComplete = c==wc[0] && z[cnt+1]==0; + + /* Get the pattern prefix. Remove all escapes from the prefix. */ pPrefix = sqlite3Expr(db, TK_STRING, z); - if( pPrefix ) pPrefix->u.zToken[cnt] = 0; + if( pPrefix ){ + int iFrom, iTo; + char *zNew = pPrefix->u.zToken; + zNew[cnt] = 0; + for(iFrom=iTo=0; iFrompVdbe; sqlite3VdbeSetVarmask(v, pRight->iColumn); if( *pisComplete && pRight->u.zToken[1] ){ /* If the rhs of the LIKE expression is a variable, and the current @@ -2207,11 +2246,11 @@ iGap = (iGap*2)/3; }else{ iGap = iGap/3; } aStat[0] = iLower + iGap; - aStat[1] = pIdx->aAvgEq[iCol]; + aStat[1] = pIdx->aAvgEq[nField-1]; } /* Restore the pRec->nField value before returning. */ pRec->nField = nField; return i; @@ -3233,10 +3272,59 @@ || pTerm->pWC->pWInfo->pParse->db->mallocFailed ); pOp->p3 = pLevel->iLikeRepCntr; pOp->p5 = 1; } } + +/* +** Cursor iCur is open on an intkey b-tree (a table). Register iRowid contains +** a rowid value just read from cursor iIdxCur, open on index pIdx. This +** function generates code to do a deferred seek of cursor iCur to the +** rowid stored in register iRowid. +** +** Normally, this is just: +** +** OP_Seek $iCur $iRowid +** +** However, if the scan currently being coded is a branch of an OR-loop and +** the statement currently being coded is a SELECT, then P3 of the OP_Seek +** is set to iIdxCur and P4 is set to point to an array of integers +** containing one entry for each column of the table cursor iCur is open +** on. For each table column, if the column is the i'th column of the +** index, then the corresponding array entry is set to (i+1). If the column +** does not appear in the index at all, the array entry is set to 0. +*/ +static void codeDeferredSeek( + WhereInfo *pWInfo, /* Where clause context */ + Index *pIdx, /* Index scan is using */ + int iCur, /* Cursor for IPK b-tree */ + int iRowid, /* Register containing rowid to seek to */ + int iIdxCur /* Index cursor */ +){ + Parse *pParse = pWInfo->pParse; /* Parse context */ + Vdbe *v = pParse->pVdbe; /* Vdbe to generate code within */ + + assert( iIdxCur>0 ); + assert( pIdx->aiColumn[pIdx->nColumn-1]==-1 ); + + sqlite3VdbeAddOp3(v, OP_Seek, iCur, iRowid, iIdxCur); + if( (pWInfo->wctrlFlags & WHERE_FORCE_TABLE) + && DbMaskAllZero(sqlite3ParseToplevel(pParse)->writeMask) + ){ + int i; + Table *pTab = pIdx->pTable; + int *ai = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*(pTab->nCol+1)); + if( ai ){ + ai[0] = pTab->nCol; + for(i=0; inColumn-1; i++){ + assert( pIdx->aiColumn[i]nCol ); + if( pIdx->aiColumn[i]>=0 ) ai[pIdx->aiColumn[i]+1] = i+1; + } + sqlite3VdbeChangeP4(v, -1, (char*)ai, P4_INTARRAY); + } + } +} /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ @@ -3712,11 +3800,11 @@ /* 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 */ + codeDeferredSeek(pWInfo, pIdx, iCur, iRowidReg, iIdxCur); }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]); @@ -5600,11 +5688,11 @@ */ static i8 wherePathSatisfiesOrderBy( WhereInfo *pWInfo, /* The WHERE clause */ ExprList *pOrderBy, /* ORDER BY or GROUP BY or DISTINCT clause to check */ WherePath *pPath, /* The WherePath to check */ - u16 wctrlFlags, /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */ + u16 wctrlFlags, /* WHERE_GROUPBY, _DISTINCTBY or _ORDERBY_LIMIT */ u16 nLoop, /* Number of entries in pPath->aLoop[] */ WhereLoop *pLast, /* Add this WhereLoop to the end of pPath->aLoop[] */ Bitmask *pRevMask /* OUT: Mask of WhereLoops to run in reverse order */ ){ u8 revSet; /* True if rev is known */ @@ -5611,10 +5699,11 @@ u8 rev; /* Composite sort order */ u8 revIdx; /* Index sort order */ u8 isOrderDistinct; /* All prior WhereLoops are order-distinct */ u8 distinctColumns; /* True if the loop has UNIQUE NOT NULL columns */ u8 isMatch; /* iColumn matches a term of the ORDER BY clause */ + u16 eqOpMask; /* Allowed equality operators */ u16 nKeyCol; /* Number of key columns in pIndex */ u16 nColumn; /* Total number of ordered columns in the index */ u16 nOrderBy; /* Number terms in the ORDER BY clause */ int iLoop; /* Index of WhereLoop in pPath being processed */ int i, j; /* Loop counters */ @@ -5661,12 +5750,20 @@ if( nOrderBy>BMS-1 ) return 0; /* Cannot optimize overly large ORDER BYs */ isOrderDistinct = 1; obDone = MASKBIT(nOrderBy)-1; orderDistinctMask = 0; ready = 0; + eqOpMask = WO_EQ | WO_ISNULL; + if( wctrlFlags & WHERE_ORDERBY_LIMIT ) eqOpMask |= WO_IN; for(iLoop=0; isOrderDistinct && obSat0 ) ready |= pLoop->maskSelf; + if( iLoopaLoop[iLoop]; + if( wctrlFlags & WHERE_ORDERBY_LIMIT ) continue; + }else{ + pLoop = pLast; + } pLoop = iLoopaLoop[iLoop] : pLast; if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){ if( pLoop->u.vtab.isOrdered ) obSat = obDone; break; } @@ -5681,12 +5778,20 @@ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn, - ~ready, WO_EQ|WO_ISNULL, 0); + ~ready, eqOpMask, 0); if( pTerm==0 ) continue; + if( pTerm->eOperator==WO_IN ){ + /* IN terms are only valid for sorting in the ORDER BY LIMIT + ** optimization, and then only if they are actually used + ** by the query plan */ + assert( wctrlFlags & WHERE_ORDERBY_LIMIT ); + for(j=0; jnLTerm && pTerm!=pLoop->aLTerm[j]; j++){} + if( j>=pLoop->nLTerm ) continue; + } if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){ const char *z1, *z2; pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); if( !pColl ) pColl = db->pDfltColl; z1 = pColl->zName; @@ -5719,14 +5824,16 @@ rev = revSet = 0; distinctColumns = 0; for(j=0; ju.btree.nEq && pLoop->nSkip==0 - && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0 + && ((i = pLoop->aLTerm[j]->eOperator) & eqOpMask)!=0 ){ if( i & WO_ISNULL ){ testcase( isOrderDistinct ); isOrderDistinct = 0; } @@ -6088,12 +6195,12 @@ /* The current candidate is no better than any of the mxChoice ** paths currently in the best-so-far buffer. So discard ** this candidate as not viable. */ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ - sqlite3DebugPrintf("Skip %s cost=%-3d,%3d order=%c\n", - wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, + sqlite3DebugPrintf("Skip %s cost=%-3d,%3d,%3d order=%c\n", + wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted, isOrdered>=0 ? isOrdered+'0' : '?'); } #endif continue; } @@ -6107,30 +6214,40 @@ jj = mxI; } pTo = &aTo[jj]; #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ - sqlite3DebugPrintf("New %s cost=%-3d,%3d order=%c\n", - wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, + sqlite3DebugPrintf("New %s cost=%-3d,%3d,%3d order=%c\n", + wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted, isOrdered>=0 ? isOrdered+'0' : '?'); } #endif }else{ /* Control reaches here if best-so-far path pTo=aTo[jj] covers the - ** same set of loops and has the sam isOrdered setting as the + ** same set of loops and has the same isOrdered setting as the ** candidate path. Check to see if the candidate should replace - ** pTo or if the candidate should be skipped */ - if( pTo->rCostrCost==rCost && pTo->nRow<=nOut) ){ + ** pTo or if the candidate should be skipped. + ** + ** The conditional is an expanded vector comparison equivalent to: + ** (pTo->rCost,pTo->nRow,pTo->rUnsorted) <= (rCost,nOut,rUnsorted) + */ + if( pTo->rCostrCost==rCost + && (pTo->nRownRow==nOut && pTo->rUnsorted<=rUnsorted) + ) + ) + ){ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( - "Skip %s cost=%-3d,%3d order=%c", - wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, + "Skip %s cost=%-3d,%3d,%3d order=%c", + wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted, isOrdered>=0 ? isOrdered+'0' : '?'); - sqlite3DebugPrintf(" vs %s cost=%-3d,%d order=%c\n", + sqlite3DebugPrintf(" vs %s cost=%-3d,%3d,%3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, - pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); + pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); } #endif /* Discard the candidate path from further consideration */ testcase( pTo->rCost==rCost ); continue; @@ -6139,16 +6256,16 @@ /* Control reaches here if the candidate path is better than the ** pTo path. Replace pTo with the candidate. */ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( - "Update %s cost=%-3d,%3d order=%c", - wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, + "Update %s cost=%-3d,%3d,%3d order=%c", + wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted, isOrdered>=0 ? isOrdered+'0' : '?'); - sqlite3DebugPrintf(" was %s cost=%-3d,%3d order=%c\n", + sqlite3DebugPrintf(" was %s cost=%-3d,%3d,%3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, - pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); + pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); } #endif } /* pWLoop is a winner. Add it to the set of best so far */ pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf; @@ -6235,12 +6352,23 @@ if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){ pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } }else{ pWInfo->nOBSat = pFrom->isOrdered; - if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0; pWInfo->revMask = pFrom->revLoop; + if( pWInfo->nOBSat<=0 ){ + pWInfo->nOBSat = 0; + if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){ + Bitmask m = 0; + int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, + WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m); + if( rc==pWInfo->pOrderBy->nExpr ){ + pWInfo->bOrderedInnerLoop = 1; + pWInfo->revMask = m; + } + } + } } if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP) && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr ){ Bitmask revMask = 0; @@ -6761,10 +6889,14 @@ for(; b; b=b>>1, n++){} sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1, SQLITE_INT_TO_PTR(n), P4_INT32); assert( n<=pTab->nCol ); } +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK + sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, pTabItem->iCursor, 0, 0, + (const u8*)&pTabItem->colUsed, P4_INT64); +#endif }else{ sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); } if( pLoop->wsFlags & WHERE_INDEXED ){ Index *pIx = pLoop->u.btree.pIndex; @@ -6806,10 +6938,25 @@ && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */ } VdbeComment((v, "%s", pIx->zName)); +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK + { + u64 colUsed = 0; + int ii, jj; + for(ii=0; iinColumn; ii++){ + jj = pIx->aiColumn[ii]; + if( jj<0 ) continue; + if( jj>63 ) jj = 63; + if( (pTabItem->colUsed & MASKBIT(jj))==0 ) continue; + colUsed |= ((u64)1)<<(ii<63 ? ii : 63); + } + sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, iIndexCur, 0, 0, + (u8*)&colUsed, P4_INT64); + } +#endif /* SQLITE_ENABLE_COLUMN_USED_MASK */ } } if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb); notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor); } Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -410,10 +410,11 @@ u8 sorted; /* True if really sorted (not just grouped) */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ + u8 bOrderedInnerLoop; /* True if only the inner-most loop is ordered */ 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 savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ int aiCurOnePass[2]; /* OP_OpenWrite cursors for the ONEPASS opt */ Index: test/join5.test ================================================================== --- test/join5.test +++ test/join5.test @@ -158,7 +158,30 @@ INSERT INTO x3 VALUES('a', NULL); INSERT INTO x3 VALUES('b', NULL); INSERT INTO x3 VALUES('c', NULL); SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b; } {} + +# Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on +# 2015-08-20. LEFT JOIN and the push-down optimization. +# +do_execsql_test join6-4.1 { + SELECT * + FROM ( + SELECT 'apple' fruit + UNION ALL SELECT 'banana' + ) a + JOIN ( + SELECT 'apple' fruit + UNION ALL SELECT 'banana' + ) b ON a.fruit=b.fruit + LEFT JOIN ( + SELECT 1 isyellow + ) c ON b.fruit='banana'; +} {apple apple {} banana banana 1} +do_execsql_test join6-4.2 { + SELECT * + FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana') + LEFT JOIN (SELECT 1) ON fruit='banana'; +} {apple {} banana 1} finish_test Index: test/like.test ================================================================== --- test/like.test +++ test/like.test @@ -946,7 +946,52 @@ do_execsql_test like-12.16 { EXPLAIN QUERY PLAN SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id; } {/SCAN/} + +# As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as +# long as the ESCAPE is a single-byte literal. +# +db close +sqlite3 db :memory: +do_execsql_test like-15.100 { + CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x)); + INSERT INTO t15(x,y) VALUES + ('abcde',1), ('ab%de',2), ('a_cde',3), + ('uvwxy',11),('uvwx%',12),('uvwx_',13), + ('_bcde',21),('%bcde',22), + ('abcd_',31),('abcd%',32), + ('ab%xy',41); + SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/'; +} {2} +do_execsql_test like-15.101 { + EXPLAIN QUERY PLAN + SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/'; +} {/SEARCH/} +do_execsql_test like-15.102 { + EXPLAIN QUERY PLAN + SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//'; +} {/SCAN/} +do_execsql_test like-15.103 { + EXPLAIN QUERY PLAN + SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE ''; +} {/SCAN/} +do_execsql_test like-15.110 { + SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x'; +} {32} +do_execsql_test like-15.111 { + SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y +} {2 41} +do_execsql_test like-15.112 { + EXPLAIN QUERY PLAN + SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y +} {/SEARCH/} +do_execsql_test like-15.120 { + SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/'; +} {22} +do_execsql_test like-15.121 { + EXPLAIN QUERY PLAN + SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/'; +} {/SEARCH/} finish_test ADDED test/limit2.test Index: test/limit2.test ================================================================== --- /dev/null +++ test/limit2.test @@ -0,0 +1,123 @@ +# 2016-05-20 +# +# 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 file is testing the LIMIT in combination with ORDER BY +# and in particular, the optimizations in the inner loop that cause an +# early exit of the inner loop when the LIMIT is reached and the inner +# loop is emitting rows in ORDER BY order. + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test limit2-100 { + CREATE TABLE t1(a,b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) + INSERT INTO t1(a,b) SELECT 1, (x*17)%1000 + 1000 FROM c; + INSERT INTO t1(a,b) VALUES(2,2),(3,1006),(4,4),(5,9999); + CREATE INDEX t1ab ON t1(a,b); +} +set sqlite_search_count 0 +do_execsql_test limit2-100.1 { + SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b LIMIT 5; +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |} +set fast_count $sqlite_search_count +set sqlite_search_count 0 +do_execsql_test limit2-100.2 { + SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b LIMIT 5; +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |} +do_test limit2-100.3 { + set slow_count $sqlite_search_count + expr {$fast_count < 0.02*$slow_count} +} {1} + +do_execsql_test limit2-110 { + CREATE TABLE t2(x,y); + INSERT INTO t2(x,y) VALUES('a',1),('a',2),('a',3),('a',4); + INSERT INTO t2(x,y) VALUES('b',1),('c',2),('d',3),('e',4); + CREATE INDEX t2xy ON t2(x,y); +} +set sqlite_search_count 0 +do_execsql_test limit2-110.1 { + SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY t1.b LIMIT 5; +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |} +set fast_count $sqlite_search_count +set sqlite_search_count 0 +do_execsql_test limit2-110.2 { + SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY +t1.b LIMIT 5; +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |} +set slow_count $sqlite_search_count +do_test limit2-110.3 { + expr {$fast_count < 0.02*$slow_count} +} {1} + +do_execsql_test limit2-120 { + DROP INDEX t1ab; + CREATE INDEX t1ab ON t1(a,b DESC); +} +set sqlite_search_count 0 +do_execsql_test limit2-120.1 { + SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b DESC LIMIT 5; +} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |} +set fast_count $sqlite_search_count +set sqlite_search_count 0 +do_execsql_test limit2-120.2 { + SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b DESC LIMIT 5; +} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |} +do_test limit2-120.3 { + set slow_count $sqlite_search_count + expr {$fast_count < 0.02*$slow_count} +} {1} + +# Bug report against the new ORDER BY LIMIT optimization just prior to +# release. (Unreleased so there is no ticket). +# +# Make sure the optimization is not applied if the inner loop can only +# provide a single row of output. +# +do_execsql_test limit2-200 { + CREATE TABLE t200(a, b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) + INSERT INTO t200(a,b) SELECT x, x FROM c; + CREATE TABLE t201(x INTEGER PRIMARY KEY, y); + INSERT INTO t201(x,y) VALUES(2,12345); + + SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3; +} {2 2 2 12345 |} +do_execsql_test limit2-210 { + SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3; +} {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |} + +# Bug in the ORDER BY LIMIT optimization reported on 2016-09-06. +# Ticket https://www.sqlite.org/src/info/559733b09e96 +# +do_execsql_test limit2-300 { + CREATE TABLE t300(a,b,c); + CREATE INDEX t300x ON t300(a,b,c); + INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0); + SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC; +} {0 1 99 . 0 0 0 . 0 1 0 .} +do_execsql_test limit2-310 { + SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1; +} {0 1 99 .} + +# Make sure the SELECT loop is ordered correctly for the direction of +# the ORDER BY +# +do_execsql_test limit2-400 { + CREATE TABLE t400(a,b); + CREATE INDEX t400_ab ON t400(a,b); + INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20); + SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3; + SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3; +} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y} + +finish_test Index: test/orderby1.test ================================================================== --- test/orderby1.test +++ test/orderby1.test @@ -493,7 +493,24 @@ CREATE INDEX t7ab ON t7(a,b); EXPLAIN QUERY PLAN SELECT * FROM t7 WHERE a=?1 ORDER BY rowid; } {~/ORDER BY/} + +#--------------------------------------------------------------------------- +# https://www.sqlite.org/src/tktview/cb3aa0641d9a413841c004293a4fc06cdc122029 +# +# Adverse interaction between scalar subqueries and the partial-sorting +# logic. +# +do_execsql_test 9.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x INTEGER PRIMARY KEY); + INSERT INTO t1 VALUES(1),(2); + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(y); + INSERT INTO t2 VALUES(9),(8),(3),(4); + SELECT (SELECT x||y FROM t2, t1 ORDER BY x, y); +} {13} + finish_test Index: test/select4.test ================================================================== --- test/select4.test +++ test/select4.test @@ -861,7 +861,44 @@ SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6) } {1 2 3} do_execsql_test select4-14.9 { SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3); } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3} + +# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45 on 2016-04-25 +# +# The where push-down optimization from 2015-06-02 is suppose to disable +# on aggregate subqueries. But if the subquery is a compound where the +# last SELECT is non-aggregate but some other SELECT is an aggregate, the +# test is incomplete and the optimization is not properly disabled. +# +# The following test cases verify that the fix works. +# +do_execsql_test select4-17.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a int, b int); + INSERT INTO t1 VALUES(1,2),(1,18),(2,19); + SELECT x, y FROM ( + SELECT 98 AS x, 99 AS y + UNION + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 20 98 99} +do_execsql_test select4-17.2 { + SELECT x, y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a + UNION + SELECT 98 AS x, 99 AS y + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 20 98 99} +do_catchsql_test select4-17.3 { + SELECT x, y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3 + UNION + SELECT 98 AS x, 99 AS y + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 {LIMIT clause should come after UNION not before}} finish_test Index: test/whereD.test ================================================================== --- test/whereD.test +++ test/whereD.test @@ -154,11 +154,11 @@ OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) } {2 two 1 one search 6} do_searchcount_test 3.5.1 { SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4 -} {1 one 2 two search 2} +} {1 one 2 two search 1} do_searchcount_test 3.5.2 { SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4 } {1 i 2 ii search 2} # Ticket [d02e1406a58ea02d] (2012-10-04) @@ -269,7 +269,78 @@ c8=1 or c9=1 or c10=1 or c11=1 or c12=1 or c13=1 or c14=1 or c15=1 or c16=1 or c17=1; } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {} {}} +#------------------------------------------------------------------------- +#------------------------------------------------------------------------- +do_execsql_test 6.1 { + CREATE TABLE x1(a, b, c, d, e); + CREATE INDEX x1a ON x1(a); + CREATE INDEX x1bc ON x1(b, c); + CREATE INDEX x1cd ON x1(c, d); + + INSERT INTO x1 VALUES(1, 2, 3, 4, 'A'); + INSERT INTO x1 VALUES(5, 6, 7, 8, 'B'); + INSERT INTO x1 VALUES(9, 10, 11, 12, 'C'); + INSERT INTO x1 VALUES(13, 14, 15, 16, 'D'); +} + +do_searchcount_test 6.2.1 { + SELECT e FROM x1 WHERE b=2 OR c=7; +} {A B search 6} +do_searchcount_test 6.2.2 { + SELECT c FROM x1 WHERE b=2 OR c=7; +} {3 7 search 4} + +do_searchcount_test 6.3.1 { + SELECT e FROM x1 WHERE a=1 OR b=10; +} {A C search 6} +do_searchcount_test 6.3.2 { + SELECT c FROM x1 WHERE a=1 OR b=10; +} {3 11 search 5} +do_searchcount_test 6.3.3 { + SELECT rowid FROM x1 WHERE a=1 OR b=10; +} {1 3 search 4} + +do_searchcount_test 6.4.1 { + SELECT a FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {1 9 search 6} +do_searchcount_test 6.4.2 { + SELECT b, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {2 3 10 11 search 5} +do_searchcount_test 6.4.3 { + SELECT rowid, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 +} {1 3 3 11 search 4} + +db eval { + WITH RECURSIVE c(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM c WHERE x<2000) + INSERT INTO x1(rowid,a,b,c,d,e) SELECT x,x,x,x,x,x FROM c; +} + +do_searchcount_test 6.5.1 { + SELECT a FROM x1 WHERE rowid = 2 OR c=11 +} {5 9 search 3} +do_searchcount_test 6.5.2 { + SELECT d FROM x1 WHERE rowid = 2 OR c=11 +} {8 12 search 2} +do_searchcount_test 6.5.3 { + SELECT d FROM x1 WHERE c=11 OR rowid = 2 +} {12 8 search 2} +do_searchcount_test 6.5.4 { + SELECT a FROM x1 WHERE c=11 OR rowid = 2 +} {9 5 search 3} + +do_searchcount_test 6.6.1 { + SELECT rowid FROM x1 WHERE a=1 OR b=6 OR c=11 +} {1 2 3 search 6} +do_searchcount_test 6.6.2 { + SELECT c FROM x1 WHERE a=1 OR b=6 OR c=11 +} {3 7 11 search 7} +do_searchcount_test 6.6.3 { + SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 +} {11 3 7 search 7} +do_searchcount_test 6.6.4 { + SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1 +} {7 11 3 search 7} finish_test ADDED test/wherelfault.test Index: test/wherelfault.test ================================================================== --- /dev/null +++ test/wherelfault.test @@ -0,0 +1,82 @@ +# 2008 October 6 +# +# 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 file is testing fault-injection with the +# LIMIT ... OFFSET ... clause of UPDATE and DELETE statements. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/malloc_common.tcl +set testprefix wherelfault + +ifcapable !update_delete_limit { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 'f'); + INSERT INTO t1 VALUES(2, 'e'); + INSERT INTO t1 VALUES(3, 'd'); + INSERT INTO t1 VALUES(4, 'c'); + INSERT INTO t1 VALUES(5, 'b'); + INSERT INTO t1 VALUES(6, 'a'); + + CREATE VIEW v1 AS SELECT a,b FROM t1; + CREATE TABLE log(op, a); + + CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN + INSERT INTO log VALUES('delete', old.a); + END; + + CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES('update', old.a); + END; +} + +faultsim_save_and_close +do_faultsim_test 1.1 -prep { + faultsim_restore_and_reopen + db eval {SELECT * FROM sqlite_master} +} -body { + execsql { DELETE FROM v1 ORDER BY a LIMIT 3; } +} -test { + faultsim_test_result {0 {}} +} + +do_faultsim_test 1.2 -prep { + faultsim_restore_and_reopen + db eval {SELECT * FROM sqlite_master} +} -body { + execsql { UPDATE v1 SET b = 555 ORDER BY a LIMIT 3 } +} -test { + faultsim_test_result {0 {}} +} + +#------------------------------------------------------------------------- +sqlite3 db test.db +do_execsql_test 2.1.0 { + CREATE TABLE t2(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID; +} +faultsim_save_and_close + +do_faultsim_test 2.1 -prep { + faultsim_restore_and_reopen + db eval {SELECT * FROM sqlite_master} +} -body { + execsql { DELETE FROM t2 WHERE c=? ORDER BY a DESC LIMIT 10 } +} -test { + faultsim_test_result {0 {}} +} + +finish_test Index: test/wherelimit.test ================================================================== --- test/wherelimit.test +++ test/wherelimit.test @@ -36,10 +36,12 @@ return {} } ifcapable {update_delete_limit} { + execsql { CREATE TABLE t1(x, y) } + # check syntax error support do_test wherelimit-0.1 { catchsql {DELETE FROM t1 ORDER BY x} } {1 {ORDER BY without LIMIT on DELETE}} do_test wherelimit-0.2 { @@ -46,10 +48,12 @@ catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x} } {1 {ORDER BY without LIMIT on DELETE}} do_test wherelimit-0.3 { catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x} } {1 {ORDER BY without LIMIT on UPDATE}} + + execsql { DROP TABLE t1 } # no AS on table sources do_test wherelimit-0.4 { catchsql {DELETE FROM t1 AS a WHERE x=1} } {1 {near "AS": syntax error}} @@ -276,9 +280,46 @@ } {6} do_test wherelimit-3.13 { execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} execsql {SELECT count(*) FROM t1 WHERE y=1} } {6} + + # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table + # or a VIEW. (We should fix this someday). + # + db close + sqlite3 db :memory: + do_execsql_test wherelimit-4.1 { + CREATE TABLE t1(a int); + INSERT INTO t1 VALUES(1); + INSERT INTO t1 VALUES(2); + INSERT INTO t1 VALUES(3); + CREATE TABLE t2(a int); + INSERT INTO t2 SELECT a+100 FROM t1; + CREATE VIEW tv AS + SELECT rowid AS r, a FROM t2 UNION ALL SELECT rowid, a FROM t1; + CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv + BEGIN + DELETE FROM t1 WHERE rowid=old.r; + DELETE FROM t2 WHERE rowid=old.r; + END; + } {} + do_catchsql_test wherelimit-4.2 { + DELETE FROM tv WHERE 1 LIMIT 2; + } {0 {}} + do_catchsql_test wherelimit-4.3 { + DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2; + } {0 {}} + do_execsql_test wherelimit-4.10 { + CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a)) WITHOUT ROWID; + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12); + } {} + do_catchsql_test wherelimit-4.11 { + DELETE FROM t3 WHERE a=5 LIMIT 2; + } {0 {}} + do_execsql_test wherelimit-4.12 { + SELECT a,b,c,d FROM t3 ORDER BY 1; + } {1 2 3 4 9 10 11 12} } finish_test ADDED test/wherelimit2.test Index: test/wherelimit2.test ================================================================== --- /dev/null +++ test/wherelimit2.test @@ -0,0 +1,264 @@ +# 2008 October 6 +# +# 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 file is testing the LIMIT ... OFFSET ... clause +# of UPDATE and DELETE statements. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix wherelimit2 + +ifcapable !update_delete_limit { + finish_test + return +} + +#------------------------------------------------------------------------- +# Test with views and INSTEAD OF triggers. +# +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 'f'); + INSERT INTO t1 VALUES(2, 'e'); + INSERT INTO t1 VALUES(3, 'd'); + INSERT INTO t1 VALUES(4, 'c'); + INSERT INTO t1 VALUES(5, 'b'); + INSERT INTO t1 VALUES(6, 'a'); + + CREATE VIEW v1 AS SELECT a,b FROM t1; + CREATE TABLE log(op, a); + + CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN + INSERT INTO log VALUES('delete', old.a); + END; + + CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES('update', old.a); + END; +} + +do_execsql_test 1.1 { + DELETE FROM v1 ORDER BY a LIMIT 3; + SELECT * FROM log; DELETE FROM log; +} { + delete 1 delete 2 delete 3 +} +do_execsql_test 1.2 { + DELETE FROM v1 ORDER BY b LIMIT 3; + SELECT * FROM log; DELETE FROM log; +} { + delete 6 delete 5 delete 4 +} +do_execsql_test 1.3 { + UPDATE v1 SET b = 555 ORDER BY a LIMIT 3; + SELECT * FROM log; DELETE FROM log; +} { + update 1 update 2 update 3 +} +do_execsql_test 1.4 { + UPDATE v1 SET b = 555 ORDER BY b LIMIT 3; + SELECT * FROM log; DELETE FROM log; +} { + update 6 update 5 update 4 +} + +#------------------------------------------------------------------------- +# Simple test using WITHOUT ROWID table. +# +do_execsql_test 2.2.0 { + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID; + INSERT INTO t2 VALUES(1, 1, 'h'); + INSERT INTO t2 VALUES(2, 2, 'g'); + INSERT INTO t2 VALUES(3, 1, 'f'); + INSERT INTO t2 VALUES(4, 2, 'e'); + INSERT INTO t2 VALUES(5, 1, 'd'); + INSERT INTO t2 VALUES(6, 2, 'c'); + INSERT INTO t2 VALUES(7, 1, 'b'); + INSERT INTO t2 VALUES(8, 2, 'a'); +} + +do_execsql_test 2.2.1 { + BEGIN; + DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2; + SELECT c FROM t2 ORDER BY 1; + ROLLBACK; +} {a c e f g h} + +do_execsql_test 2.2.2 { + BEGIN; + UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1; + SELECT a, b, c FROM t2; + ROLLBACK; +} { + 1 1 h + 2 2 g + 3 1 f + 4 2 e + 5 1 {} + 6 2 {} + 7 1 {} + 8 2 a +} + +#------------------------------------------------------------------------- +# Test using a virtual table +# +ifcapable fts3 { + do_execsql_test 3.0 { + CREATE VIRTUAL TABLE ft USING fts3(x); + INSERT INTO ft(rowid, x) VALUES(-45, 'a a'); + INSERT INTO ft(rowid, x) VALUES(12, 'a b'); + INSERT INTO ft(rowid, x) VALUES(444, 'a c'); + INSERT INTO ft(rowid, x) VALUES(12300, 'a d'); + INSERT INTO ft(rowid, x) VALUES(25400, 'a c'); + INSERT INTO ft(rowid, x) VALUES(25401, 'a b'); + INSERT INTO ft(rowid, x) VALUES(50000, 'a a'); + } + + do_execsql_test 3.1.1 { + BEGIN; + DELETE FROM ft ORDER BY rowid LIMIT 3; + SELECT x FROM ft; + ROLLBACK; + } {{a d} {a c} {a b} {a a}} + + do_execsql_test 3.1.2 { + BEGIN; + DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3; + SELECT x FROM ft; + ROLLBACK; + } {{a d} {a c} {a b} {a a}} + + do_execsql_test 3.1.3 { + BEGIN; + DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1; + SELECT rowid FROM ft; + ROLLBACK; + } {-45 12 444 12300 25400 50000} + + do_execsql_test 3.2.1 { + BEGIN; + UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2; + SELECT x FROM ft; + ROLLBACK; + } {{a a} {a b} hello hello {a c} {a b} {a a}} + + do_execsql_test 3.2.2 { + BEGIN; + UPDATE ft SET x='hello' WHERE ft MATCH 'a' + ORDER BY rowid DESC LIMIT 2 OFFSET 2; + SELECT x FROM ft; + ROLLBACK; + } {{a a} {a b} {a c} hello hello {a b} {a a}} +} ;# fts5 + +#------------------------------------------------------------------------- +# Test using INDEXED BY clauses. +# +do_execsql_test 4.0 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d); + CREATE INDEX x1bc ON x1(b, c); + INSERT INTO x1 VALUES(1,1,1,1); + INSERT INTO x1 VALUES(2,1,2,2); + INSERT INTO x1 VALUES(3,2,1,3); + INSERT INTO x1 VALUES(4,2,2,3); + INSERT INTO x1 VALUES(5,3,1,2); + INSERT INTO x1 VALUES(6,3,2,1); +} + +do_execsql_test 4.1 { + BEGIN; + DELETE FROM x1 ORDER BY a LIMIT 2; + SELECT a FROM x1; + ROLLBACK; +} {3 4 5 6} + +do_catchsql_test 4.2 { + DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1; +} {1 {no query solution}} + +do_execsql_test 4.3 { + DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1; + SELECT a FROM x1; +} {1 2 3 4 6} + +do_catchsql_test 4.4 { + UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1; +} {1 {no query solution}} + +do_execsql_test 4.5 { + UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1; + SELECT a, d FROM x1; +} {1 1 2 2 3 5 4 3 6 1} + +#------------------------------------------------------------------------- +# Test using object names that require quoting. +# +do_execsql_test 5.0 { + CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID; + CREATE INDEX xycd ON "x y"("c d"); + + INSERT INTO "x y" VALUES('a', 'a'); + INSERT INTO "x y" VALUES('b', 'b'); + INSERT INTO "x y" VALUES('c', 'c'); + INSERT INTO "x y" VALUES('d', 'd'); + INSERT INTO "x y" VALUES('e', 'a'); + INSERT INTO "x y" VALUES('f', 'b'); + INSERT INTO "x y" VALUES('g', 'c'); + INSERT INTO "x y" VALUES('h', 'd'); +} + +do_execsql_test 5.1 { + BEGIN; + DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2; + SELECT * FROM "x y" ORDER BY 1; + ROLLBACK; +} { + a a c c d d e a g c h d +} + +do_execsql_test 5.2 { + BEGIN; + UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2; + SELECT * FROM "x y" ORDER BY 1; + ROLLBACK; +} { + a a b e c c d d e a f e g c h d +} + +proc log {args} { lappend ::log {*}$args } +db func log log +do_execsql_test 5.3 { + CREATE VIEW "v w" AS SELECT * FROM "x y"; + CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN + SELECT log(old."a b", old."c d"); + END; + CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN + SELECT log(new."a b", new."c d"); + END; +} + +do_test 5.4 { + set ::log {} + execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 } + set ::log +} {a a b b c c} + +do_test 5.5 { + set ::log {} + execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; } + set ::log +} {ax a bx b cx c dx d ex a} + + +finish_test +