diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/expr.c | 10 | ||||
| -rw-r--r-- | src/prepare.c | 6 | ||||
| -rw-r--r-- | src/select.c | 141 | ||||
| -rw-r--r-- | src/sqliteInt.h | 9 | ||||
| -rw-r--r-- | src/vdbeaux.c | 12 | ||||
| -rw-r--r-- | src/where.c | 211 |
6 files changed, 317 insertions, 72 deletions
diff --git a/src/expr.c b/src/expr.c index ab66e9fc1..a88208bd9 100644 --- a/src/expr.c +++ b/src/expr.c @@ -1600,6 +1600,16 @@ int sqlite3CodeSubselect( assert( testAddr>0 || pParse->db->mallocFailed ); } +#ifndef SQLITE_OMIT_EXPLAIN + if( pParse->explain==2 ){ + char *zMsg = sqlite3MPrintf( + pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr?"":"CORRELATED ", + pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId + ); + sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC); + } +#endif + switch( pExpr->op ){ case TK_IN: { char affinity; /* Affinity of the LHS of the IN */ diff --git a/src/prepare.c b/src/prepare.c index 74accd761..fa64a00df 100644 --- a/src/prepare.c +++ b/src/prepare.c @@ -628,13 +628,13 @@ static int sqlite3Prepare( if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", - "order", "from", "detail" + "selectid", "order", "from", "detail" }; int iFirst, mx; if( pParse->explain==2 ){ - sqlite3VdbeSetNumCols(pParse->pVdbe, 3); + sqlite3VdbeSetNumCols(pParse->pVdbe, 4); iFirst = 8; - mx = 11; + mx = 12; }else{ sqlite3VdbeSetNumCols(pParse->pVdbe, 8); iFirst = 0; diff --git a/src/select.c b/src/select.c index 766f67f8d..0f83bbb36 100644 --- a/src/select.c +++ b/src/select.c @@ -771,6 +771,88 @@ static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList){ return pInfo; } +#ifndef SQLITE_OMIT_COMPOUND_SELECT +/* +** Name of the connection operator, used for error messages. +*/ +static const char *selectOpName(int id){ + char *z; + switch( id ){ + case TK_ALL: z = "UNION ALL"; break; + case TK_INTERSECT: z = "INTERSECT"; break; + case TK_EXCEPT: z = "EXCEPT"; break; + default: z = "UNION"; break; + } + return z; +} +#endif /* SQLITE_OMIT_COMPOUND_SELECT */ + +#ifndef SQLITE_OMIT_EXPLAIN +/* +** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function +** is a no-op. Otherwise, it adds a single row of output to the EQP result, +** where the caption is of the form: +** +** "USE TEMP B-TREE FOR xxx" +** +** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which +** is determined by the zUsage argument. +*/ +static void explainTempTable(Parse *pParse, const char *zUsage){ + if( pParse->explain==2 ){ + Vdbe *v = pParse->pVdbe; + char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage); + sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC); + } +} + +/* +** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function +** is a no-op. Otherwise, it adds a single row of output to the EQP result, +** where the caption is of one of the two forms: +** +** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)" +** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)" +** +** where iSub1 and iSub2 are the integers passed as the corresponding +** function parameters, and op is the text representation of the parameter +** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT, +** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is +** false, or the second form if it is true. +*/ +static void explainComposite( + Parse *pParse, /* Parse context */ + int op, /* One of TK_UNION, TK_EXCEPT etc. */ + int iSub1, /* Subquery id 1 */ + int iSub2, /* Subquery id 2 */ + int bUseTmp /* True if a temp table was used */ +){ + assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL ); + if( pParse->explain==2 ){ + Vdbe *v = pParse->pVdbe; + char *zMsg = sqlite3MPrintf( + pParse->db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2, + bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op) + ); + sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC); + } +} + +/* +** Assign expression b to lvalue a. A second, no-op, version of this macro +** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code +** in sqlite3Select() to assign values to structure member variables that +** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the +** code with #ifndef directives. +*/ +# define explainSetInteger(a, b) a = b + +#else +/* No-op versions of the explainXXX() functions and macros. */ +# define explainTempTable(y,z) +# define explainComposite(v,w,x,y,z) +# define explainSetInteger(y,z) +#endif /* ** If the inner loop was generated using a non-null pOrderBy argument, @@ -1118,22 +1200,6 @@ static void generateColumnNames( generateColumnTypes(pParse, pTabList, pEList); } -#ifndef SQLITE_OMIT_COMPOUND_SELECT -/* -** Name of the connection operator, used for error messages. -*/ -static const char *selectOpName(int id){ - char *z; - switch( id ){ - case TK_ALL: z = "UNION ALL"; break; - case TK_INTERSECT: z = "INTERSECT"; break; - case TK_EXCEPT: z = "EXCEPT"; break; - default: z = "UNION"; break; - } - return z; -} -#endif /* SQLITE_OMIT_COMPOUND_SELECT */ - /* ** Given a an expression list (which is really the list of expressions ** that form the result set of a SELECT statement) compute appropriate @@ -1467,6 +1533,10 @@ static int multiSelect( SelectDest dest; /* Alternative data destination */ Select *pDelete = 0; /* Chain of simple selects to delete */ sqlite3 *db; /* Database connection */ +#ifndef SQLITE_OMIT_EXPLAIN + int iSub1; /* EQP id of left-hand query */ + int iSub2; /* EQP id of right-hand query */ +#endif /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT. @@ -1527,6 +1597,7 @@ static int multiSelect( assert( !pPrior->pLimit ); pPrior->pLimit = p->pLimit; pPrior->pOffset = p->pOffset; + explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &dest); p->pLimit = 0; p->pOffset = 0; @@ -1540,6 +1611,7 @@ static int multiSelect( addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit); VdbeComment((v, "Jump ahead if LIMIT reached")); } + explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &dest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; @@ -1587,6 +1659,7 @@ static int multiSelect( */ assert( !pPrior->pOrderBy ); sqlite3SelectDestInit(&uniondest, priorOp, unionTab); + explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &uniondest); if( rc ){ goto multi_select_end; @@ -1606,6 +1679,7 @@ static int multiSelect( pOffset = p->pOffset; p->pOffset = 0; uniondest.eDest = op; + explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &uniondest); testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. @@ -1671,6 +1745,7 @@ static int multiSelect( /* Code the SELECTs to our left into temporary table "tab1". */ sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1); + explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &intersectdest); if( rc ){ goto multi_select_end; @@ -1687,6 +1762,7 @@ static int multiSelect( pOffset = p->pOffset; p->pOffset = 0; intersectdest.iParm = tab2; + explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &intersectdest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; @@ -1723,6 +1799,8 @@ static int multiSelect( } } + explainComposite(pParse, p->op, iSub1, iSub2, p->op!=TK_ALL); + /* Compute collating sequences used by ** temporary tables needed to implement the compound select. ** Attach the KeyInfo structure to all temporary tables. @@ -2066,6 +2144,10 @@ static int multiSelectOrderBy( ExprList *pOrderBy; /* The ORDER BY clause */ int nOrderBy; /* Number of terms in the ORDER BY clause */ int *aPermute; /* Mapping from ORDER BY terms to result set columns */ +#ifndef SQLITE_OMIT_EXPLAIN + int iSub1; /* EQP id of left-hand query */ + int iSub2; /* EQP id of right-hand query */ +#endif assert( p->pOrderBy!=0 ); assert( pKeyDup==0 ); /* "Managed" code needs this. Ticket #3382. */ @@ -2219,6 +2301,7 @@ static int multiSelectOrderBy( */ VdbeNoopComment((v, "Begin coroutine for left SELECT")); pPrior->iLimit = regLimitA; + explainSetInteger(iSub1, pParse->iNextSelectId); sqlite3Select(pParse, pPrior, &destA); sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofA); sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); @@ -2233,6 +2316,7 @@ static int multiSelectOrderBy( savedOffset = p->iOffset; p->iLimit = regLimitB; p->iOffset = 0; + explainSetInteger(iSub2, pParse->iNextSelectId); sqlite3Select(pParse, p, &destB); p->iLimit = savedLimit; p->iOffset = savedOffset; @@ -2363,6 +2447,7 @@ static int multiSelectOrderBy( /*** TBD: Insert subroutine calls to close cursors on incomplete **** subqueries ****/ + explainComposite(pParse, p->op, iSub1, iSub2, 0); return SQLITE_OK; } #endif @@ -3590,6 +3675,11 @@ int sqlite3Select( int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ +#ifndef SQLITE_OMIT_EXPLAIN + int iRestoreSelectId = pParse->iSelectId; + pParse->iSelectId = pParse->iNextSelectId++; +#endif + db = pParse->db; if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; @@ -3661,6 +3751,7 @@ int sqlite3Select( }else{ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); assert( pItem->isPopulated==0 ); + explainSetInteger(pItem->iSelectId, pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); pItem->isPopulated = 1; } @@ -3696,10 +3787,12 @@ int sqlite3Select( mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT]; if( mxSelect && cnt>mxSelect ){ sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); - return 1; + goto select_end; } } - return multiSelect(pParse, p, pDest); + rc = multiSelect(pParse, p, pDest); + explainSetInteger(pParse->iSelectId, iRestoreSelectId); + return rc; } #endif @@ -3711,7 +3804,6 @@ int sqlite3Select( p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0); pGroupBy = p->pGroupBy; p->selFlags &= ~SF_Distinct; - isDistinct = 0; } /* If there is both a GROUP BY and an ORDER BY clause and they are @@ -3758,7 +3850,7 @@ int sqlite3Select( /* Open a virtual index to use for the distinct set. */ - if( isDistinct ){ + if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; assert( isAgg || pGroupBy ); distinct = pParse->nTab++; @@ -3917,6 +4009,9 @@ int sqlite3Select( int nCol; int nGroupBy; + explainTempTable(pParse, + isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY"); + groupBySort = 1; nGroupBy = pGroupBy->nExpr; nCol = nGroupBy + 1; @@ -4178,10 +4273,15 @@ int sqlite3Select( } /* endif aggregate query */ + if( distinct>=0 ){ + explainTempTable(pParse, "DISTINCT"); + } + /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ if( pOrderBy ){ + explainTempTable(pParse, "ORDER BY"); generateSortTail(pParse, p, v, pEList->nExpr, pDest); } @@ -4198,6 +4298,7 @@ int sqlite3Select( ** successful coding of the SELECT. */ select_end: + explainSetInteger(pParse->iSelectId, iRestoreSelectId); /* Identify column names if results of the SELECT are to be output. */ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 8515efcc4..b9eb650d8 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -1827,6 +1827,9 @@ struct SrcList { Bitmask colUsed; /* Bit N (1<<N) set if column N of pTab is used */ char *zIndex; /* Identifier from "INDEXED BY <zIndex>" clause */ Index *pIndex; /* Index structure corresponding to zIndex, if any */ +#ifndef SQLITE_OMIT_EXPLAIN + int iSelectId; /* If pSelect!=0, the id of the sub-select in EQP */ +#endif } a[1]; /* One entry for each identifier on the list */ }; @@ -1859,6 +1862,7 @@ struct SrcList { struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u32 nEq; /* Number of == constraints */ + double nRow; /* Estimated number of rows (for EQP) */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ @@ -2213,6 +2217,11 @@ struct Parse { int nHeight; /* Expression tree height of current sub-select */ Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ + +#ifndef SQLITE_OMIT_EXPLAIN + int iSelectId; + int iNextSelectId; +#endif }; #ifdef SQLITE_OMIT_VIRTUALTABLE diff --git a/src/vdbeaux.c b/src/vdbeaux.c index c0227d596..e22387433 100644 --- a/src/vdbeaux.c +++ b/src/vdbeaux.c @@ -1182,12 +1182,10 @@ int sqlite3VdbeList( pMem->type = SQLITE_INTEGER; pMem++; - if( p->explain==1 ){ - pMem->flags = MEM_Int; - pMem->u.i = pOp->p3; /* P3 */ - pMem->type = SQLITE_INTEGER; - pMem++; - } + pMem->flags = MEM_Int; + pMem->u.i = pOp->p3; /* P3 */ + pMem->type = SQLITE_INTEGER; + pMem++; if( sqlite3VdbeMemGrow(pMem, 32, 0) ){ /* P4 */ assert( p->db->mallocFailed ); @@ -1232,7 +1230,7 @@ int sqlite3VdbeList( } } - p->nResColumn = 8 - 5*(p->explain-1); + p->nResColumn = 8 - 4*(p->explain-1); p->rc = SQLITE_OK; rc = SQLITE_ROW; } diff --git a/src/where.c b/src/where.c index eb0e8ead7..7c7eae17f 100644 --- a/src/where.c +++ b/src/where.c @@ -192,7 +192,6 @@ struct WhereMaskSet { struct WhereCost { WherePlan plan; /* The lookup strategy */ double rCost; /* Overall cost of pursuing this search strategy */ - double nRow; /* Estimated number of output rows */ Bitmask used; /* Bitmask of cursors used by this plan */ }; @@ -1621,7 +1620,7 @@ static void bestOrClauseIndex( continue; } rTotal += sTermCost.rCost; - nRow += sTermCost.nRow; + nRow += sTermCost.plan.nRow; used |= sTermCost.used; if( rTotal>=pCost->rCost ) break; } @@ -1640,8 +1639,8 @@ static void bestOrClauseIndex( WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); if( rTotal<pCost->rCost ){ pCost->rCost = rTotal; - pCost->nRow = nRow; pCost->used = used; + pCost->plan.nRow = nRow; pCost->plan.wsFlags = flags; pCost->plan.u.pTerm = pTerm; } @@ -1725,7 +1724,7 @@ static void bestAutomaticIndex( WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n", pCost->rCost, costTempIdx)); pCost->rCost = costTempIdx; - pCost->nRow = logN + 1; + pCost->plan.nRow = logN + 1; pCost->plan.wsFlags = WHERE_TEMP_INDEX; pCost->used = pTerm->prereqRight; break; @@ -2798,11 +2797,11 @@ static void bestBtreeIndex( ** index and its cost in the pCost structure. */ if( (!pIdx || wsFlags) - && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->nRow)) + && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow)) ){ pCost->rCost = cost; - pCost->nRow = nRow; pCost->used = used; + pCost->plan.nRow = nRow; pCost->plan.wsFlags = (wsFlags&wsFlagMask); pCost->plan.nEq = nEq; pCost->plan.u.pIdx = pIdx; @@ -3131,6 +3130,157 @@ static int codeAllEqualityTerms( return regBase; } +#ifndef SQLITE_OMIT_EXPLAIN +/* +** This routine is a helper for explainIndexRange() below +** +** pStr holds the text of an expression that we are building up one term +** at a time. This routine adds a new term to the end of the expression. +** Terms are separated by AND so add the "AND" text for second and subsequent +** terms only. +*/ +static void explainAppendTerm( + StrAccum *pStr, /* The text expression being built */ + int iTerm, /* Index of this term. First is zero */ + const char *zColumn, /* Name of the column */ + const char *zOp /* Name of the operator */ +){ + if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5); + sqlite3StrAccumAppend(pStr, zColumn, -1); + sqlite3StrAccumAppend(pStr, zOp, 1); + sqlite3StrAccumAppend(pStr, "?", 1); +} + +/* +** Argument pLevel describes a strategy for scanning table pTab. This +** function returns a pointer to a string buffer containing a description +** of the subset of table rows scanned by the strategy in the form of an +** SQL expression. Or, if all rows are scanned, NULL is returned. +** +** For example, if the query: +** +** SELECT * FROM t1 WHERE a=1 AND b>2; +** +** is run and there is an index on (a, b), then this function returns a +** string similar to: +** +** "a=? AND b>?" +** +** The returned pointer points to memory obtained from sqlite3DbMalloc(). +** It is the responsibility of the caller to free the buffer when it is +** no longer required. +*/ +static char *explainIndexRange(sqlite3 *db, WhereLevel *pLevel, Table *pTab){ + WherePlan *pPlan = &pLevel->plan; + Index *pIndex = pPlan->u.pIdx; + int nEq = pPlan->nEq; + int i, j; + Column *aCol = pTab->aCol; + int *aiColumn = pIndex->aiColumn; + StrAccum txt; + + if( nEq==0 && (pPlan->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ + return 0; + } + sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); + sqlite3StrAccumAppend(&txt, " (", 2); + for(i=0; i<nEq; i++){ + explainAppendTerm(&txt, i, aCol[aiColumn[i]].zName, "="); + } + + j = i; + if( pPlan->wsFlags&WHERE_BTM_LIMIT ){ + explainAppendTerm(&txt, i++, aCol[aiColumn[j]].zName, ">"); + } + if( pPlan->wsFlags&WHERE_TOP_LIMIT ){ + explainAppendTerm(&txt, i, aCol[aiColumn[j]].zName, "<"); + } + sqlite3StrAccumAppend(&txt, ")", 1); + return sqlite3StrAccumFinish(&txt); +} + +/* +** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN +** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single +** record is added to the output to describe the table scan strategy in +** pLevel. +*/ +static void explainOneScan( + Parse *pParse, /* Parse context */ + SrcList *pTabList, /* Table list this loop refers to */ + WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ + int iLevel, /* Value for "level" column of output */ + int iFrom, /* Value for "from" column of output */ + u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ +){ + if( pParse->explain==2 ){ + u32 flags = pLevel->plan.wsFlags; + struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; + Vdbe *v = pParse->pVdbe; /* VM being constructed */ + sqlite3 *db = pParse->db; /* Database handle */ + char *zMsg; /* Text to add to EQP output */ + sqlite3_int64 nRow; /* Expected number of rows visited by scan */ + int iId = pParse->iSelectId; /* Select id (left-most output column) */ + int isSearch; /* True for a SEARCH. False for SCAN. */ + + if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; + + isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT)); + + zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); + if( pItem->pSelect ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); + }else{ + zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); + } + + if( pItem->zAlias ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); + } + if( (flags & WHERE_INDEXED)!=0 ){ + char *zWhere = explainIndexRange(db, pLevel, pItem->pTab); + zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, + ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), + ((flags & WHERE_IDX_ONLY)?"COVERING ":""), + ((flags & WHERE_TEMP_INDEX)?"":" "), + ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), + zWhere + ); + sqlite3DbFree(db, zWhere); + }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); + + if( flags&WHERE_ROWID_EQ ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); + }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); + }else if( flags&WHERE_BTM_LIMIT ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg); + }else if( flags&WHERE_TOP_LIMIT ){ + zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg); + } + } +#ifndef SQLITE_OMIT_VIRTUALTABLE + else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ + sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; + zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, + pVtabIdx->idxNum, pVtabIdx->idxStr); + } +#endif + if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ + nRow = 1; + }else{ + nRow = (sqlite3_int64)pLevel->plan.nRow; + } + zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); + sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); + } +} +#else +# define explainOneScan(u,v,w,x,y,z) +#endif /* SQLITE_OMIT_EXPLAIN */ + + /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. @@ -3672,6 +3822,9 @@ static Bitmask codeOneLoopStart( WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ + explainOneScan( + pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 + ); if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; @@ -4184,11 +4337,12 @@ WhereInfo *sqlite3WhereBegin( && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ - || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow)) + || (sCost.rCost<=bestPlan.rCost + && sCost.plan.nRow<bestPlan.plan.nRow)) ){ WHERETRACE(("=== table %d is best so far" " with cost=%g and nRow=%g\n", - j, sCost.rCost, sCost.nRow)); + j, sCost.rCost, sCost.plan.nRow)); bestPlan = sCost; bestJ = j; } @@ -4199,7 +4353,7 @@ WhereInfo *sqlite3WhereBegin( assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", - bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.nRow)); + bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } @@ -4214,7 +4368,9 @@ WhereInfo *sqlite3WhereBegin( } notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); pLevel->iFrom = (u8)bestJ; - if( bestPlan.nRow>=(double)1 ) pParse->nQueryLoop *= bestPlan.nRow; + if( bestPlan.plan.nRow>=(double)1 ){ + pParse->nQueryLoop *= bestPlan.plan.nRow; + } /* Check that if the table scanned by this loop iteration had an ** INDEXED BY clause attached to it, that the named index is being @@ -4266,37 +4422,6 @@ WhereInfo *sqlite3WhereBegin( Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ -#ifndef SQLITE_OMIT_EXPLAIN - if( pParse->explain==2 ){ - char *zMsg; - struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; - zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName); - if( pItem->zAlias ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); - } - if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s WITH AUTOMATIC INDEX", zMsg); - }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s WITH INDEX %s", - zMsg, pLevel->plan.u.pIdx->zName); - }else if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s VIA MULTI-INDEX UNION", zMsg); - }else if( pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s USING PRIMARY KEY", zMsg); - } -#ifndef SQLITE_OMIT_VIRTUALTABLE - else if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ - sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; - zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, - pVtabIdx->idxNum, pVtabIdx->idxStr); - } -#endif - if( pLevel->plan.wsFlags & WHERE_ORDERBY ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s ORDER BY", zMsg); - } - sqlite3VdbeAddOp4(v, OP_Explain, i, pLevel->iFrom, 0, zMsg, P4_DYNAMIC); - } -#endif /* SQLITE_OMIT_EXPLAIN */ pTabItem = &pTabList->a[pLevel->iFrom]; pTab = pTabItem->pTab; pLevel->iTabCur = pTabItem->iCursor; @@ -4355,8 +4480,10 @@ WhereInfo *sqlite3WhereBegin( */ notReady = ~(Bitmask)0; for(i=0; i<nTabList; i++){ + WhereLevel *pLevel = &pWInfo->a[i]; + explainOneScan(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags); notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); - pWInfo->iContinue = pWInfo->a[i].addrCont; + pWInfo->iContinue = pLevel->addrCont; } #ifdef SQLITE_TEST /* For testing and debugging use only */ |