Index: ext/rtree/rtree6.test ================================================================== --- ext/rtree/rtree6.test +++ ext/rtree/rtree6.test @@ -72,40 +72,40 @@ } {Ca} do_eqp_test rtree6.2.1 { SELECT * FROM t1,t2 WHERE k=+ii AND x1<10 } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} } do_eqp_test rtree6.2.2 { SELECT * FROM t1,t2 WHERE k=ii AND x1<10 } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} } do_eqp_test rtree6.2.3 { SELECT * FROM t1,t2 WHERE k=ii } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~25 rows)} - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} } do_eqp_test rtree6.2.4 { SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10 } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~25 rows)} - 0 1 1 {SCAN TABLE t2 (~100000 rows)} + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb} + 0 1 1 {SCAN TABLE t2} } do_eqp_test rtree6.2.5 { SELECT * FROM t1,t2 WHERE k=ii AND x1nSelectRow > (double)n ) p->nSelectRow = (double)n; + if( p->nSelectRow > n ) p->nSelectRow = n; } }else{ sqlite3ExprCode(pParse, p->pLimit, iLimit); sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeComment((v, "LIMIT counter")); @@ -1731,13 +1731,13 @@ pDelete = p->pPrior; p->pPrior = pPrior; p->nSelectRow += pPrior->nSelectRow; if( pPrior->pLimit && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit) - && p->nSelectRow > (double)nLimit + && p->nSelectRow > nLimit ){ - p->nSelectRow = (double)nLimit; + p->nSelectRow = nLimit; } if( addr ){ sqlite3VdbeJumpHere(v, addr); } break; @@ -3882,15 +3882,14 @@ Parse *pParse, /* Parse context */ Table *pTab, /* Table being queried */ Index *pIdx /* Index used to optimize scan, or NULL */ ){ if( pParse->explain==2 ){ - char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)", + char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s", pTab->zName, - pIdx ? "USING COVERING INDEX " : "", - pIdx ? pIdx->zName : "", - pTab->nRowEst + pIdx ? " USING COVERING INDEX " : "", + pIdx ? pIdx->zName : "" ); sqlite3VdbeAddOp4( pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC ); } @@ -4237,11 +4236,11 @@ } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); - p->nSelectRow = (double)LARGEST_INT64; + p->nSelectRow = LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); if( p->iLimit==0 && addrSortIndex>=0 ){ sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen; p->selFlags |= SF_UseSorter; } @@ -4318,13 +4317,13 @@ pItem->iAlias = 0; } for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){ pItem->iAlias = 0; } - if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100; + if( p->nSelectRow>100 ) p->nSelectRow = 100; }else{ - p->nSelectRow = (double)1; + p->nSelectRow = 1; } /* Create a label to jump to when we want to abort the query */ addrEnd = sqlite3VdbeMakeLabel(v); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2040,11 +2040,11 @@ ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u16 selFlags; /* Various SF_* values */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ int addrOpenEphm[3]; /* OP_OpenEphem opcodes related to this select */ - double nSelectRow; /* Estimated number of result rows */ + u64 nSelectRow; /* Estimated number of result rows */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ @@ -2224,11 +2224,11 @@ AutoincInfo *pAinc; /* Information about AUTOINCREMENT counters */ /* Information used while coding trigger programs. */ Parse *pToplevel; /* Parse structure for main program (or NULL) */ Table *pTriggerTab; /* Table triggers are being coded for */ - double nQueryLoop; /* Estimated number of iterations of a query */ + u32 nQueryLoop; /* Estimated number of iterations of a query */ u32 oldmask; /* Mask of old.* columns referenced */ u32 newmask; /* Mask of new.* columns referenced */ u8 eTriggerOp; /* TK_UPDATE, TK_INSERT or TK_DELETE */ u8 eOrconf; /* Default ON CONFLICT policy for trigger steps */ u8 disableTriggers; /* True to disable triggers */ @@ -2794,11 +2794,11 @@ #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int); void sqlite3WhereEnd(WhereInfo*); -double sqlite3WhereOutputRowCount(WhereInfo*); +u64 sqlite3WhereOutputRowCount(WhereInfo*); int sqlite3WhereIsDistinct(WhereInfo*); int sqlite3WhereIsOrdered(WhereInfo*); int sqlite3WhereContinueLabel(WhereInfo*); int sqlite3WhereBreakLabel(WhereInfo*); int sqlite3WhereOkOnePass(WhereInfo*); Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -43,11 +43,17 @@ typedef struct WhereLoop WhereLoop; typedef struct WherePath WherePath; typedef struct WhereTerm WhereTerm; typedef struct WhereLoopBuilder WhereLoopBuilder; typedef struct WhereScan WhereScan; -typedef float WhereCost; + +/* +** Cost X is tracked as 10*log2(X) stored in a 16-bit integer. The +** maximum cost is 64*(2**63) which becomes 6900. So all costs can be +** be stored in a 16-bit unsigned integer without risk of overflow. +*/ +typedef unsigned short int WhereCost; /* ** For each nested loop in a WHERE clause implementation, the WhereInfo ** structure contains a single instance of this structure. This structure ** is intended to be private to the where.c module and should not be @@ -399,15 +405,29 @@ #define WHERE_ONEROW 0x00001000 /* Selects no more than one row */ #define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x00004000 /* Uses an ephemeral index */ #define WHERE_COVER_SCAN 0x00008000 /* Full scan of a covering index */ + +/* Convert a WhereCost value (10 times log2(X)) into its integer value X. +*/ +static u64 whereCostToInt(WhereCost x){ + u64 n; + if( x<=10 ) return 1; + n = x%10; + x /= 10; + if( n>=5 ) n -= 2; + else if( n>=1 ) n -= 1; + if( x>=3 ) return (n+8)<<(x-3); + return (n+8)>>(3-x); +} + /* ** Return the estimated number of output rows from a WHERE clause */ -double sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ - return (double)pWInfo->nRowOut; +u64 sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ + return whereCostToInt(pWInfo->nRowOut); } /* ** Return one of the WHERE_DISTINCT_xxxxx values to indicate how this ** WHERE clause returns outputs for DISTINCT processing. @@ -1818,23 +1838,78 @@ } return 0; } +/* +** The sum of two WhereCosts +*/ +static WhereCost whereCostAdd(WhereCost a, WhereCost b){ + static const unsigned char x[] = { + 10, 10, /* 0,1 */ + 9, 9, /* 2,3 */ + 8, 8, /* 4,5 */ + 7, 7, 7, /* 6,7,8 */ + 6, 6, 6, /* 9,10,11 */ + 5, 5, 5, /* 12-14 */ + 4, 4, 4, 4, /* 15-18 */ + 3, 3, 3, 3, 3, 3, /* 19-24 */ + 2, 2, 2, 2, 2, 2, 2, /* 25-31 */ + }; + if( a>=b ){ + if( a>b+49 ) return a; + if( a>b+31 ) return a+1; + return a+x[a-b]; + }else{ + if( b>a+49 ) return b; + if( b>a+31 ) return b+1; + return b+x[b-a]; + } +} + +/* +** Convert an integer into a WhereCost +*/ +static WhereCost whereCostFromInt(tRowcnt x){ + static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 }; + WhereCost y = 40; + if( x<8 ){ + if( x<2 ) return 0; + while( x<8 ){ y -= 10; x <<= 1; } + }else{ + while( x>255 ){ y += 40; x >>= 4; } + while( x>15 ){ y += 10; x >>= 1; } + } + return a[x&7] + y - 10; +} + +#ifndef SQLITE_OMIT_VIRTUALTABLE +/* +** Convert a double (as received from xBestIndex of a virtual table) +** into a WhereCost +*/ +static WhereCost whereCostFromDouble(double x){ + u64 a; + WhereCost e; + assert( sizeof(x)==8 && sizeof(a)==8 ); + if( x<=1 ) return 0; + if( x<=2000000000 ) return whereCostFromInt((tRowcnt)x); + memcpy(&a, &x, 8); + e = (a>>52) - 1022; + return e*10; +} +#endif /* SQLITE_OMIT_VIRTUALTABLE */ + /* ** Prepare a crude estimate of the logarithm of the input value. ** The results need not be exact. This is only used for estimating ** the total cost of performing operations with O(logN) or O(NlogN) ** complexity. Because N is just a guess, it is no great tragedy if ** logN is a little off. */ static WhereCost estLog(WhereCost N){ - u32 a; - assert( sizeof(WhereCost)==4 ); /* 32-bit float input */ - if( N<=0.0 ) return 0.0; - memcpy(&a, &N, 4); - return ((a >>= 23)-127)*0.3; + return whereCostFromInt(N) - 33; } /* ** Two routines for printing the content of an sqlite3_index_info ** structure. Used for testing and debugging only. If neither @@ -2238,11 +2313,11 @@ tRowcnt n; IndexSample *aSample; int i, eType; int isEq = 0; i64 v; - WhereCost r, rS; + double r, rS; assert( roundUp==0 || roundUp==1 ); assert( pIdx->nSample>0 ); if( pVal==0 ) return SQLITE_ERROR; n = pIdx->aiRowEst[0]; @@ -2494,15 +2569,15 @@ if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1]; } sqlite3ValueFree(pRangeVal); } if( rc==SQLITE_OK ){ - if( iUpper<=iLower ){ - *pRangeDiv = (WhereCost)p->aiRowEst[0]; - }else{ - *pRangeDiv = (WhereCost)p->aiRowEst[0]/(WhereCost)(iUpper - iLower); + WhereCost iBase = whereCostFromInt(p->aiRowEst[0]); + if( iUpper>iLower ){ + iBase -= whereCostFromInt(iUpper - iLower); } + *pRangeDiv = iBase; /*WHERETRACE(("range scan regions: %u..%u div=%g\n", (u32)iLower, (u32)iUpper, *pRangeDiv));*/ return SQLITE_OK; } } @@ -2510,13 +2585,17 @@ UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(p); UNUSED_PARAMETER(nEq); #endif assert( pLower || pUpper ); - *pRangeDiv = (WhereCost)1; - if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (WhereCost)4; - if( pUpper ) *pRangeDiv *= (WhereCost)4; + *pRangeDiv = 0; + if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ){ + *pRangeDiv += 20; assert( 20==whereCostFromInt(4) ); + } + if( pUpper ){ + *pRangeDiv += 20; assert( 20==whereCostFromInt(4) ); + } return rc; } #ifdef SQLITE_ENABLE_STAT3 /* @@ -2538,11 +2617,11 @@ */ static int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ - WhereCost *pnRow /* Write the revised row estimate here */ + tRowcnt *pnRow /* Write the revised row estimate here */ ){ sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */ u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ @@ -2587,16 +2666,16 @@ */ static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ - WhereCost *pnRow /* Write the revised row estimate here */ + tRowcnt *pnRow /* Write the revised row estimate here */ ){ - int rc = SQLITE_OK; /* Subfunction return code */ - WhereCost nEst; /* Number of rows for a single term */ - WhereCost nRowEst = (WhereCost)0; /* New estimate of the number of rows */ - int i; /* Loop counter */ + int rc = SQLITE_OK; /* Subfunction return code */ + tRowcnt nEst; /* Number of rows for a single term */ + tRowcnt nRowEst = 0; /* New estimate of the number of rows */ + int i; /* Loop counter */ assert( p->aSample!=0 ); for(i=0; rc==SQLITE_OK && inExpr; i++){ nEst = p->aiRowEst[0]; rc = whereEqualScanEst(pParse, p, pList->a[i].pExpr, &nEst); @@ -2980,11 +3059,10 @@ if( pParse->explain==2 ){ 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. */ WhereLoop *pLoop; /* The controlling WhereLoop object */ u32 flags; /* Flags that describe this loop */ @@ -3035,17 +3113,11 @@ else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr); } #endif - if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ - testcase( wctrlFlags & WHERE_ORDERBY_MIN ); - nRow = 1; - }else{ - nRow = (sqlite3_int64)pLoop->nOut; - } - zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); + zMsg = sqlite3MAppendf(db, zMsg, "%s", zMsg); sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); } } #else # define explainOneScan(u,v,w,x,y,z) @@ -3826,11 +3898,11 @@ #ifdef WHERETRACE_ENABLED /* ** Print a WhereLoop object for debugging purposes */ static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){ - int nb = 2*((pTabList->nSrc+15)/16); + int nb = 1+(pTabList->nSrc+7)/8; struct SrcList_item *pItem = pTabList->a + p->iTab; Table *pTab = pItem->pTab; sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId, p->iTab, nb, p->maskSelf, nb, p->prereq); sqlite3DebugPrintf(" %8s", @@ -3858,12 +3930,11 @@ } sqlite3DebugPrintf(" %-15s", z); sqlite3_free(z); } sqlite3DebugPrintf(" fg %05x N %d", p->wsFlags, p->nLTerm); - sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n", - p->prereq, p->rSetup, p->rRun, p->nOut); + sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut); } #endif /* ** Convert bulk memory into a valid WhereLoop that can be passed @@ -3993,26 +4064,26 @@ ** prior WhereLoops have been evaluated and that the current pTemplate ** is therefore the first and hence the best and should be retained. */ if( (p = pBuilder->pBest)!=0 ){ if( p->maskSelf!=0 ){ - WhereCost rCost = p->rRun + p->rSetup; - WhereCost rTemplate = pTemplate->rRun + pTemplate->rSetup; + WhereCost rCost = whereCostAdd(p->rRun,p->rSetup); + WhereCost rTemplate = whereCostAdd(pTemplate->rRun,pTemplate->rSetup); if( rCost < rTemplate ){ goto whereLoopInsert_noop; } if( rCost == rTemplate && p->prereq <= pTemplate->prereq ){ goto whereLoopInsert_noop; } } - whereLoopXfer(db, p, pTemplate); #if WHERETRACE_ENABLED if( sqlite3WhereTrace & 0x8 ){ - sqlite3DebugPrintf("ins-best: "); + sqlite3DebugPrintf(p->maskSelf==0 ? "ins-init: " : "ins-best: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif + whereLoopXfer(db, p, pTemplate); return SQLITE_OK; } /* Search for an existing WhereLoop to overwrite, or which takes ** priority over pTemplate. @@ -4031,10 +4102,18 @@ && p->prereq==pTemplate->prereq ){ /* Overwrite an existing WhereLoop with an similar one that uses ** more terms of the index */ pNext = p->pNextLoop; + break; + }else if( p->nOut>pTemplate->nOut + && p->rSetup==pTemplate->rSetup + && p->rRun==pTemplate->rRun + ){ + /* Overwrite an existing WhereLoop with the same cost but more + ** outputs */ + pNext = p->pNextLoop; break; }else{ /* pTemplate is not helpful. ** Return without changing or adding anything */ goto whereLoopInsert_noop; @@ -4082,11 +4161,11 @@ /* Jump here if the insert is a no-op */ whereLoopInsert_noop: #if WHERETRACE_ENABLED if( sqlite3WhereTrace & 0x8 ){ - sqlite3DebugPrintf("ins-noop: "); + sqlite3DebugPrintf(pBuilder->pBest ? "ins-skip: " : "ins-noop: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif return SQLITE_OK; } @@ -4100,11 +4179,11 @@ */ static int whereLoopAddBtreeIndex( WhereLoopBuilder *pBuilder, /* The WhereLoop factory */ struct SrcList_item *pSrc, /* FROM clause term being analyzed */ Index *pProbe, /* An index on pSrc */ - int nInMul /* Number of iterations due to IN */ + WhereCost nInMul /* log(Number of iterations due to IN) */ ){ WhereInfo *pWInfo = pBuilder->pWInfo; /* WHERE analyse context */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection malloc context */ WhereLoop *pNew; /* Template WhereLoop under construction */ @@ -4116,11 +4195,11 @@ int saved_nEq; /* Original value of pNew->u.btree.nEq */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ WhereCost saved_nOut; /* Original value of pNew->nOut */ int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ - tRowcnt iRowEst; /* Estimated index selectivity */ + WhereCost nRowEst; /* Estimated index selectivity */ WhereCost rLogSize; /* Logarithm of table size */ WhereTerm *pTop, *pBtm; /* Top and bottom range constraints */ pNew = pBuilder->pNew; if( db->mallocFailed ) return SQLITE_NOMEM; @@ -4137,26 +4216,26 @@ } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); if( pNew->u.btree.nEq < pProbe->nColumn ){ iCol = pProbe->aiColumn[pNew->u.btree.nEq]; - iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; + nRowEst = whereCostFromInt(pProbe->aiRowEst[pNew->u.btree.nEq+1]); }else{ iCol = -1; - iRowEst = 1; + nRowEst = 0; } pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; - pNew->rSetup = (WhereCost)0; - rLogSize = estLog(pProbe->aiRowEst[0]); + pNew->rSetup = 0; + rLogSize = estLog(whereCostFromInt(pProbe->aiRowEst[0])); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ - int nIn = 1; + int nIn = 0; if( pTerm->prereqRight & pNew->maskSelf ) continue; pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ @@ -4166,36 +4245,36 @@ if( pTerm->eOperator & WO_IN ){ Expr *pExpr = pTerm->pExpr; pNew->wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ - nIn = 25; + nIn = 46; /* whereCostFromInt(25) */ }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ - nIn = pExpr->x.pList->nExpr; + nIn = whereCostFromInt(pExpr->x.pList->nExpr); } - pNew->rRun *= nIn; + pNew->rRun += nIn; pNew->u.btree.nEq++; - pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; + pNew->nOut = nRowEst + nInMul + nIn; }else if( pTerm->eOperator & (WO_EQ) ){ assert( (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN))!=0 - || nInMul==1 ); + || nInMul==0 ); pNew->wsFlags |= WHERE_COLUMN_EQ; if( iCol<0 - || (pProbe->onError!=OE_None && nInMul==1 + || (pProbe->onError!=OE_None && nInMul==0 && pNew->u.btree.nEq==pProbe->nColumn-1) ){ testcase( pNew->wsFlags & WHERE_COLUMN_IN ); pNew->wsFlags |= WHERE_ONEROW; } pNew->u.btree.nEq++; - pNew->nOut = (WhereCost)iRowEst * nInMul; + pNew->nOut = nRowEst + nInMul; }else if( pTerm->eOperator & (WO_ISNULL) ){ pNew->wsFlags |= WHERE_COLUMN_NULL; pNew->u.btree.nEq++; - nIn = 2; /* Assume IS NULL matches two rows */ - pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; + nIn = 10; /* Assume IS NULL matches two rows */ + pNew->nOut = nRowEst + nInMul + nIn; }else if( pTerm->eOperator & (WO_GT|WO_GE) ){ pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; pBtm = pTerm; pTop = 0; }else if( pTerm->eOperator & (WO_LT|WO_LE) ){ @@ -4207,39 +4286,40 @@ if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ /* Adjust nOut and rRun for STAT3 range values */ WhereCost rDiv; whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq, pBtm, pTop, &rDiv); - pNew->nOut = saved_nOut/rDiv; + pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10; } #ifdef SQLITE_ENABLE_STAT3 if( pNew->u.btree.nEq==1 && pProbe->nSample ){ + tRowcnt nOut = 0; if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){ - rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, - &pNew->nOut); + rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &nOut); }else if( (pTerm->eOperator & WO_IN) && !ExprHasProperty(pTerm->pExpr, EP_xIsSelect) ){ - rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, - &pNew->nOut); - + rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut); } + pNew->nOut = whereCostFromInt(nOut); } #endif if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){ - pNew->rRun += pNew->nOut; /* Unit step cost to reach each row */ + /* Step cost for each output row */ + pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); }else{ /* Each row involves a step of the index, then a binary search of ** the main table */ - pNew->rRun += pNew->nOut*(1 + rLogSize); + WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1); + pNew->rRun = whereCostAdd(pNew->rRun, rStepAndSearch); } /* TBD: Adjust nOut for additional constraints */ rc = whereLoopInsert(pBuilder, pNew); if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<=pProbe->nColumn && pProbe->zName!=0 ){ - whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn); + whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } } pNew->prereq = saved_prereq; pNew->u.btree.nEq = saved_nEq; pNew->wsFlags = saved_wsFlags; @@ -4311,12 +4391,12 @@ struct SrcList_item *pSrc; /* The FROM clause btree term to add */ WhereLoop *pNew; /* Template WhereLoop object */ int rc = SQLITE_OK; /* Return code */ int iSortIdx = 1; /* Index number */ int b; /* A boolean value */ - WhereCost rSize; /* number of rows in the table */ - WhereCost rLogSize; /* Logarithm of the number of rows in the table */ + WhereCost rSize; /* number of rows in the table */ + WhereCost rLogSize; /* Logarithm of the number of rows in the table */ pNew = pBuilder->pNew; pWInfo = pBuilder->pWInfo; pTabList = pWInfo->pTabList; pSrc = pTabList->a + pNew->iTab; @@ -4345,11 +4425,11 @@ ** NOT INDEXED qualifier is omitted from the FROM clause */ sPk.pNext = pFirst; } pProbe = &sPk; } - rSize = (WhereCost)pSrc->pTab->nRowEst; + rSize = whereCostFromInt(pSrc->pTab->nRowEst); rLogSize = estLog(rSize); /* Automatic indexes */ if( !pBuilder->pBest && pTabList->nSrc>1 @@ -4367,13 +4447,14 @@ if( termCanDriveIndex(pTerm, pSrc, 0) ){ pNew->u.btree.nEq = 1; pNew->u.btree.pIndex = 0; pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; - pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst; - pNew->nOut = (WhereCost)10; - pNew->rRun = rLogSize + pNew->nOut; + assert( 43==whereCostFromInt(20) ); + pNew->rSetup = 43 + rLogSize + rSize; + pNew->nOut = 33; assert( 33==whereCostFromInt(10) ); + pNew->rRun = whereCostAdd(rLogSize,pNew->nOut); pNew->wsFlags = WHERE_TEMP_INDEX; pNew->prereq = mExtra | pTerm->prereqRight; rc = whereLoopInsert(pBuilder, pNew); } } @@ -4383,11 +4464,11 @@ */ for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ pNew->u.btree.nEq = 0; pNew->nLTerm = 0; pNew->iSortIdx = 0; - pNew->rSetup = (WhereCost)0; + pNew->rSetup = 0; pNew->prereq = mExtra; pNew->u.btree.pIndex = pProbe; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); if( pProbe->tnum<=0 ){ /* Integer primary key index */ @@ -4394,11 +4475,11 @@ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; - pNew->rRun = (rSize + rLogSize)*(3+b); /* 4x penalty for a full-scan */ + pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 + b*4; rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; }else{ Bitmask m = pSrc->colUsed & ~columnsUsedByIndex(pProbe); pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; @@ -4410,24 +4491,25 @@ && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ){ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; - pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize); + pNew->rRun = whereCostAdd(rSize,rLogSize) + ((m==0 && b) ? 10 : 0); rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; } } - rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 1); + rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); /* If there was an INDEXED BY clause, then only that one index is ** considered. */ if( pSrc->pIndex ) break; } return rc; } +#ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. */ static int whereLoopAddVirtual( @@ -4511,12 +4593,11 @@ if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; pIdxInfo->idxNum = 0; pIdxInfo->needToFreeIdxStr = 0; pIdxInfo->orderByConsumed = 0; - /* ((WhereCost)2) In case of SQLITE_OMIT_FLOATING_POINT... */ - pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((WhereCost)2); + pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; rc = vtabBestIndex(pParse, pTab, pIdxInfo); if( rc ) goto whereLoopAddVtab_exit; pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; pNew->prereq = 0; mxTerm = -1; @@ -4565,13 +4646,13 @@ pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; pIdxInfo->needToFreeIdxStr = 0; pNew->u.vtab.idxStr = pIdxInfo->idxStr; pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0) && pIdxInfo->orderByConsumed); - pNew->rSetup = (WhereCost)0; - pNew->rRun = pIdxInfo->estimatedCost; - pNew->nOut = (WhereCost)25; + pNew->rSetup = 0; + pNew->rRun = whereCostFromDouble(pIdxInfo->estimatedCost); + pNew->nOut = 46; assert( 46 == whereCostFromInt(25) ); whereLoopInsert(pBuilder, pNew); if( pNew->u.vtab.needFree ){ sqlite3_free(pNew->u.vtab.idxStr); pNew->u.vtab.needFree = 0; } @@ -4581,10 +4662,11 @@ whereLoopAddVtab_exit: if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); sqlite3DbFree(db, pIdxInfo); return rc; } +#endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Add WhereLoop entries to handle OR terms. This works for either ** btrees or virtual tables. */ @@ -4637,26 +4719,29 @@ continue; } sBest.maskSelf = 0; sBest.rSetup = 0; sBest.rRun = 0; +#ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pItem->pTab) ){ rc = whereLoopAddVirtual(&sSubBuild, mExtra); - }else{ + }else +#endif + { rc = whereLoopAddBtree(&sSubBuild, mExtra); } if( sBest.maskSelf==0 ) break; - assert( sBest.rSetup==(WhereCost)0 ); - rTotal += sBest.rRun; - nRow += sBest.nOut; + assert( sBest.rSetup==0 ); + rTotal = whereCostAdd(rTotal, sBest.rRun); + nRow = whereCostAdd(nRow, sBest.nOut); prereq |= sBest.prereq; } assert( pNew->nLSlot>=1 ); pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->wsFlags = WHERE_MULTI_OR; - pNew->rSetup = (WhereCost)0; + pNew->rSetup = 0; pNew->rRun = rTotal; pNew->nOut = nRow; pNew->prereq = prereq; memset(&pNew->u, 0, sizeof(pNew->u)); rc = whereLoopInsert(pBuilder, pNew); @@ -4677,21 +4762,23 @@ SrcList *pTabList = pWInfo->pTabList; struct SrcList_item *pItem; sqlite3 *db = pWInfo->pParse->db; int nTabList = pWInfo->nLevel; int rc = SQLITE_OK; - WhereLoop *pNew, sNew; + u8 priorJoinType = 0; + WhereLoop *pNew; /* Loop over the tables in the join, from left to right */ - pBuilder->pNew = pNew = &sNew; + pNew = pBuilder->pNew; whereLoopInit(pNew); for(iTab=0, pItem=pTabList->a; iTabiTab = iTab; pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor); - if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){ + if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){ mExtra = mPrior; } + priorJoinType = pItem->jointype; if( IsVirtual(pItem->pTab) ){ rc = whereLoopAddVirtual(pBuilder, mExtra); }else{ rc = whereLoopAddBtree(pBuilder, mExtra); } @@ -4700,11 +4787,10 @@ } mPrior |= pNew->maskSelf; if( rc || db->mallocFailed ) break; } whereLoopClear(db, pNew); - pBuilder->pNew = 0; return rc; } /* ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th @@ -4986,24 +5072,24 @@ for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){ pFrom->aLoop = pX; } /* Seed the search with a single WherePath containing zero WhereLoops */ - aFrom[0].nRow = (WhereCost)1; + aFrom[0].nRow = 0; nFrom = 1; /* Precompute the cost of sorting the final result set, if the caller ** to sqlite3WhereBegin() was concerned about sorting */ - rSortCost = (WhereCost)0; - if( pWInfo->pOrderBy==0 || nRowEst<=0.0 ){ + rSortCost = 0; + if( pWInfo->pOrderBy==0 || nRowEst==0 ){ aFrom[0].isOrderedValid = 1; }else{ /* Compute an estimate on the cost to sort the entire result set */ - rSortCost = nRowEst*estLog(nRowEst); + rSortCost = nRowEst + estLog(nRowEst); #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace>=2 ){ - sqlite3DebugPrintf("---- sort cost=%-7.2g\n", rSortCost); + sqlite3DebugPrintf("---- sort cost=%-3d\n", rSortCost); } #endif } /* Compute successively longer WherePaths using the previous generation @@ -5019,11 +5105,12 @@ u8 isOrdered = pFrom->isOrdered; if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue; if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ - rCost = pWLoop->rSetup + pWLoop->rRun*pFrom->nRow + pFrom->rCost; + rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); + rCost = whereCostAdd(rCost, pFrom->rCost); maskNew = pFrom->maskLoop | pWLoop->maskSelf; if( !isOrderedValid ){ switch( wherePathSatisfiesOrderBy(pWInfo, pFrom, iLoop, iLoop==nLoop-1, pWLoop, &revMask) ){ case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */ @@ -5031,11 +5118,11 @@ isOrderedValid = 1; break; case 0: /* No. pFrom+pWLoop will require a separate sort */ isOrdered = 0; isOrderedValid = 1; - rCost += rSortCost; + rCost = whereCostAdd(rCost, rSortCost); break; default: /* Cannot tell yet. Try again on the next iteration */ break; } }else{ @@ -5049,11 +5136,11 @@ } if( jj>=nTo ){ if( nTo>=mxChoice && rCost>=mxCost ){ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ - sqlite3DebugPrintf("Skip %s cost=%-7.2g order=%c\n", + sqlite3DebugPrintf("Skip %s cost=%3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); } #endif continue; @@ -5067,24 +5154,24 @@ for(jj=nTo-1; aTo[jj].rCost0); } } pTo = &aTo[jj]; #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ - sqlite3DebugPrintf("New %s cost=%-7.2g order=%c\n", + sqlite3DebugPrintf("New %s cost=%-3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); } #endif }else{ if( pTo->rCost<=rCost ){ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( - "Skip %s cost=%-7.2g order=%c", + "Skip %s cost=%-3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); - sqlite3DebugPrintf(" vs %s cost=%-7.2g order=%c\n", + sqlite3DebugPrintf(" vs %s cost=%-3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); } #endif continue; @@ -5091,23 +5178,23 @@ } /* A new and better score for a previously created equivalent path */ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( - "Update %s cost=%-7.2g order=%c", + "Update %s cost=%-3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); - sqlite3DebugPrintf(" was %s cost=%-7.2g order=%c\n", + sqlite3DebugPrintf(" was %s cost=%-3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); } #endif } /* pWLoop is a winner. Add it to the set of best so far */ pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf; pTo->revLoop = revMask; - pTo->nRow = pFrom->nRow * pWLoop->nOut; + pTo->nRow = pFrom->nRow + pWLoop->nOut; pTo->rCost = rCost; pTo->isOrderedValid = isOrderedValid; pTo->isOrdered = isOrdered; memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop); pTo->aLoop[iLoop] = pWLoop; @@ -5122,11 +5209,11 @@ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace>=2 ){ sqlite3DebugPrintf("---- after round %d ----\n", iLoop); for(ii=0, pTo=aTo; iirCost, pTo->nRow, pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); if( pTo->isOrderedValid && pTo->isOrdered ){ sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); }else{ @@ -5182,11 +5269,11 @@ ** ** Return non-zero on success, if this query can be handled by this ** no-frills query planner. Return zero if this query needs the ** general-purpose query planner. */ -static int whereSimpleFastCase(WhereLoopBuilder *pBuilder){ +static int whereShortCut(WhereLoopBuilder *pBuilder){ WhereInfo *pWInfo; struct SrcList_item *pItem; WhereClause *pWC; WhereTerm *pTerm; WhereLoop *pLoop; @@ -5194,10 +5281,11 @@ int j; Table *pTab; Index *pIdx; pWInfo = pBuilder->pWInfo; + if( pWInfo->wctrlFlags & WHERE_FORCE_TABLE ) return 0; assert( pWInfo->pTabList->nSrc>=1 ); pItem = pWInfo->pTabList->a; pTab = pItem->pTab; if( IsVirtual(pTab) ) return 0; if( pItem->zIndex ) return 0; @@ -5209,11 +5297,11 @@ if( pTerm ){ pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW; pLoop->aLTerm[0] = pTerm; pLoop->nLTerm = 1; pLoop->u.btree.nEq = 1; - pLoop->rRun = (WhereCost)10; + pLoop->rRun = 33; /* 33 == whereCostFromInt(10) */ }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_None ) continue; for(j=0; jnColumn; j++){ pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); @@ -5227,11 +5315,11 @@ pLoop->wsFlags |= WHERE_IDX_ONLY; } pLoop->nLTerm = j; pLoop->u.btree.nEq = j; pLoop->u.btree.pIndex = pIdx; - pLoop->rRun = (WhereCost)15; + pLoop->rRun = 39; /* 39 == whereCostFromInt(15) */ break; } } if( pLoop->wsFlags ){ pLoop->nOut = (WhereCost)1; @@ -5404,10 +5492,13 @@ pMaskSet = &pWInfo->sMaskSet; sWLB.pWInfo = pWInfo; sWLB.pWC = &pWInfo->sWC; sWLB.pNew = (WhereLoop*)&pWInfo->a[nTabList]; whereLoopInit(sWLB.pNew); +#ifdef SQLITE_DEBUG + sWLB.pNew->cId = '*'; +#endif /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ if( OptimizationDisabled(db, SQLITE_DistinctOpt) ) pDistinct = 0; @@ -5476,11 +5567,11 @@ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } /* Construct the WhereLoop objects */ WHERETRACE(("*** Optimizer Start ***\n")); - if( nTabList!=1 || whereSimpleFastCase(&sWLB)==0 ){ + if( nTabList!=1 || whereShortCut(&sWLB)==0 ){ rc = whereLoopAddAll(&sWLB); if( rc ) goto whereBeginError; /* Display all of the WhereLoop objects if wheretrace is enabled */ #ifdef WHERETRACE_ENABLED Index: test/all.test ================================================================== --- test/all.test +++ test/all.test @@ -46,7 +46,5 @@ run_test_suite autovacuum_crash } } finish_test - - Index: test/analyze3.test ================================================================== --- test/analyze3.test +++ test/analyze3.test @@ -95,14 +95,14 @@ } } {} do_eqp_test analyze3-1.1.2 { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x? AND x0 AND x<1100 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x? AND x200 AND x<300 } } {199 0 14850} do_test analyze3-1.1.5 { @@ -144,14 +144,14 @@ ANALYZE; } } {} do_eqp_test analyze3-1.2.2 { SELECT sum(y) FROM t2 WHERE x>1 AND x<2 -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x? AND x0 AND x<99 -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x? AND x12 AND x<20 } } {161 0 4760} do_test analyze3-1.2.5 { set l [string range "12" 0 end] @@ -191,14 +191,14 @@ ANALYZE; } } {} do_eqp_test analyze3-1.3.2 { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x? AND x0 AND x<1100 -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x? AND x200 AND x<300 } } {199 0 14850} do_test analyze3-1.3.5 { @@ -246,14 +246,14 @@ } execsql COMMIT } {} do_eqp_test analyze3-2.2 { SELECT count(a) FROM t1 WHERE b LIKE 'a%' -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b? AND b? AND b? AND b? AND b? AND b? AND c? AND c? AND c? AND c? AND owner_change_date? AND owner_change_date10" - {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. Index: test/e_fkey.test ================================================================== --- test/e_fkey.test +++ test/e_fkey.test @@ -972,19 +972,19 @@ do_execsql_test e_fkey-25.2 { PRAGMA foreign_keys = OFF; EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; } { - 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SCAN TABLE track (~100000 rows)} + 0 0 0 {SCAN TABLE artist} + 0 0 0 {SCAN TABLE track} } do_execsql_test e_fkey-25.3 { PRAGMA foreign_keys = ON; EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; } { - 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SCAN TABLE track (~100000 rows)} + 0 0 0 {SCAN TABLE artist} + 0 0 0 {SCAN TABLE track} } do_test e_fkey-25.4 { execsql { INSERT INTO artist VALUES(5, 'artist 5'); INSERT INTO artist VALUES(6, 'artist 6'); @@ -1097,19 +1097,19 @@ eqp { INSERT INTO artist VALUES(?, ?) } } {} do_execsql_test e_fkey-27.3 { EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? } { - 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} + 0 0 0 {SCAN TABLE artist} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} } do_execsql_test e_fkey-27.4 { EXPLAIN QUERY PLAN DELETE FROM artist } { - 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} + 0 0 0 {SCAN TABLE artist} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} } ########################################################################### ### SECTION 4.1: Composite Foreign Key Constraints Index: test/eqp.test ================================================================== --- test/eqp.test +++ test/eqp.test @@ -41,82 +41,82 @@ } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { - 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} - 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} - 0 1 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} + 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} + 0 1 0 {SCAN TABLE t2} } do_eqp_test 1.3 { SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; } { - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} - 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} - 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} + 0 0 0 {SCAN TABLE t2} + 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} + 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} } do_eqp_test 1.3 { SELECT a FROM t1 ORDER BY a } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} } do_eqp_test 1.4 { SELECT a FROM t1 ORDER BY +a } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 1.5 { SELECT a FROM t1 WHERE a=4 } { - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} } do_eqp_test 1.6 { SELECT DISTINCT count(*) FROM t3 GROUP BY a; } { - 0 0 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 0 {SCAN TABLE t3} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } do_eqp_test 1.7 { SELECT * FROM t3 JOIN (SELECT 1) } { - 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 1 {SCAN SUBQUERY 1} + 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.8 { SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) } { 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} - 0 0 1 {SCAN SUBQUERY 1 (~2 rows)} - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 1 {SCAN SUBQUERY 1} + 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.9 { SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) } { - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} + 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} - 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 1 {SCAN SUBQUERY 1} + 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.10 { SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) } { - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} + 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} - 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 1 {SCAN SUBQUERY 1} + 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.11 { SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) } { - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} + 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} - 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} + 0 0 1 {SCAN SUBQUERY 1} + 0 1 0 {SCAN TABLE t3} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # @@ -127,52 +127,52 @@ CREATE TABLE t2(x, y); CREATE INDEX t2i1 ON t2(x); } det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.3 "SELECT DISTINCT * FROM t1" { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} + 0 1 1 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} + 0 1 1 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { - 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} - 0 1 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1} + 0 1 0 {SCAN TABLE t1} } det 2.3.1 "SELECT max(x) FROM t2" { - 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} + 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} } det 2.3.2 "SELECT min(x) FROM t2" { - 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} + 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} } det 2.3.3 "SELECT min(x), max(x) FROM t2" { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} } det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} } #------------------------------------------------------------------------- @@ -179,189 +179,189 @@ # Test cases eqp-3.* - tests for select statements that use sub-selects. # do_eqp_test 3.1.1 { SELECT (SELECT x FROM t1 AS sub) FROM t1; } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 AS sub} } do_eqp_test 3.1.2 { SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 AS sub} } do_eqp_test 3.1.3 { SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 AS sub} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 3.1.4 { SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} - 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} } det 3.2.1 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 0 0 0 {SCAN SUBQUERY 1 (~10 rows)} + 0 0 0 {SCAN SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.2.2 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 ORDER BY x2.y LIMIT 5 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} - 0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)} - 0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)} + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} + 0 0 0 {SCAN SUBQUERY 1 AS x1} + 0 1 1 {SCAN SUBQUERY 2 AS x2} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.3.1 { SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) } { - 0 0 0 {SCAN TABLE t1 (~100000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE LIST SUBQUERY 1} - 1 0 0 {SCAN TABLE t2 (~1000000 rows)} + 1 0 0 {SCAN TABLE t2} } det 3.3.2 { SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) } { - 0 0 0 {SCAN TABLE t1 (~500000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} - 1 0 0 {SCAN TABLE t2 (~500000 rows)} + 1 0 0 {SCAN TABLE t2} } det 3.3.3 { SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) } { - 0 0 0 {SCAN TABLE t1 (~500000 rows)} + 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} - 1 0 0 {SCAN TABLE t2 (~500000 rows)} + 1 0 0 {SCAN TABLE t2} } #------------------------------------------------------------------------- # Test cases eqp-4.* - tests for composite select statements. # do_eqp_test 4.1.1 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} + 2 0 0 {SCAN TABLE t2} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.1.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.1.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} } do_eqp_test 4.1.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} } do_eqp_test 4.1.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } do_eqp_test 4.2.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.2.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} } do_eqp_test 4.2.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} } do_eqp_test 4.2.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } do_eqp_test 4.3.1 { SELECT x FROM t1 UNION SELECT x FROM t2 } { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1} + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } do_eqp_test 4.3.2 { SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 } { - 2 0 0 {SCAN TABLE t1 (~1000000 rows)} - 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 2 0 0 {SCAN TABLE t1} + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} - 4 0 0 {SCAN TABLE t1 (~1000000 rows)} + 4 0 0 {SCAN TABLE t1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} } do_eqp_test 4.3.3 { SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 } { - 2 0 0 {SCAN TABLE t1 (~1000000 rows)} + 2 0 0 {SCAN TABLE t1} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} - 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} - 4 0 0 {SCAN TABLE t1 (~1000000 rows)} + 4 0 0 {SCAN TABLE t1} 4 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)} } #------------------------------------------------------------------------- @@ -369,131 +369,131 @@ # lang_explain.html page are correct. # drop_all_tables # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b -# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows) +# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { - 0 0 0 {SCAN TABLE t1 (~100000 rows)} + 0 0 0 {SCAN TABLE t1} } # EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; -# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { - 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} } # EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} } # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 -# (~1000000 rows) +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|1|SCAN TABLE t2 +# do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} + 0 1 1 {SCAN TABLE t2} } # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1 -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 -# (~1000000 rows) +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|0|SCAN TABLE t2 +# det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { - 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} - 0 1 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} + 0 1 0 {SCAN TABLE t2} } # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) -# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) +# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} - 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} + 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d -# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP +# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP # B-TREE FOR ORDER BY det 5.7 "SELECT c, d FROM t2 ORDER BY c" { - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } # EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c); # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; -# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows) +# 0|0|0|SCAN TABLE t2 USING INDEX i4 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { - 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING INDEX i4} } # EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; -# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1 -# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) +# 0|0|0|SCAN TABLE t2 0|0|0|EXECUTE SCALAR SUBQUERY 1 +# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING -# INDEX i3 (b=?) (~10 rows) +# INDEX i3 (b=?) det 5.9 { SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 } { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} - 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} + 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} - 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} + 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; -# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN -# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 0|0|0|SCAN +# SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR GROUP BY det 5.10 { SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x } { - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} - 0 0 0 {SCAN SUBQUERY 1 (~100 rows)} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} + 0 0 0 {SCAN SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4 -# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) +# (c=?) 0|1|1|SCAN TABLE t1 det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { - 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)} - 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} + 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} + 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} } # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM -# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) -# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 +# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 +# 2|0|0|SCAN TABLE t2 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # USING TEMP B-TREE (UNION) det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING -# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) +# COVERING INDEX i2 2|0|0|SCAN TABLE t2 # 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # (EXCEPT) det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} + 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } @@ -529,12 +529,12 @@ } do_peqp_test 6.1 { SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1 } [string trimleft { -1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) -2 0 0 SCAN TABLE t2 (~1000000 rows) +1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 +2 0 0 SCAN TABLE t2 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) }] #------------------------------------------------------------------------- @@ -548,15 +548,15 @@ CREATE TABLE t2(a, b); CREATE INDEX i1 ON t2(a); } det 7.1 "SELECT count(*) FROM t1" { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1} } det 7.2 "SELECT count(*) FROM t2" { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} } do_execsql_test 7.3 { INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); @@ -570,14 +570,14 @@ db close sqlite3 db test.db det 7.4 "SELECT count(*) FROM t1" { - 0 0 0 {SCAN TABLE t1 (~2 rows)} + 0 0 0 {SCAN TABLE t1} } det 7.5 "SELECT count(*) FROM t2" { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} } finish_test Index: test/exclusive.test ================================================================== --- test/exclusive.test +++ test/exclusive.test @@ -504,6 +504,5 @@ PRAGMA locking_mode = EXCLUSIVE; SELECT * FROM sqlite_master; } {exclusive} finish_test - Index: test/fallocate.test ================================================================== --- test/fallocate.test +++ test/fallocate.test @@ -141,6 +141,5 @@ } [expr 32*1024] } finish_test - Index: test/filefmt.test ================================================================== --- test/filefmt.test +++ test/filefmt.test @@ -246,6 +246,5 @@ db2 eval { PRAGMA integrity_check } } {ok} db2 close finish_test - Index: test/fts3aa.test ================================================================== --- test/fts3aa.test +++ test/fts3aa.test @@ -222,6 +222,5 @@ CREATE VIRTUAL TABLE t4 USING fts4(tokenize=simple, tokenize=simple); } {1 {unrecognized parameter: tokenize=simple}} finish_test - Index: test/fts3ao.test ================================================================== --- test/fts3ao.test +++ test/fts3ao.test @@ -218,6 +218,5 @@ SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%'; SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%'; } {0 6} finish_test - Index: test/fts3atoken.test ================================================================== --- test/fts3atoken.test +++ test/fts3atoken.test @@ -191,7 +191,5 @@ execsql { SELECT fts3_tokenizer_internal_test() } } {ok} finish_test - - Index: test/fts3auto.test ================================================================== --- test/fts3auto.test +++ test/fts3auto.test @@ -705,6 +705,5 @@ do_fts3query_test 7.$tn.3 -deferred B t1 {"M B D"} } set sqlite_fts3_enable_parentheses $sfep finish_test - Index: test/fts3aux1.test ================================================================== --- test/fts3aux1.test +++ test/fts3aux1.test @@ -103,14 +103,14 @@ # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} } do_execsql_test 2.1.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' -} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}} +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}} # Now show that using "term='braid'" means the virtual table returns # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. # do_test 2.1.2.1 { @@ -152,28 +152,28 @@ # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2:} } do_execsql_test 2.2.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } do_execsql_test 2.2.1.3 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4:} } do_execsql_test 2.2.1.4 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } do_execsql_test 2.2.1.5 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6:} } do_execsql_test 2.2.1.6 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } do_test 2.2.2.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } set cnt @@ -333,11 +333,11 @@ 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { - set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}] + set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}] if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set sql "SELECT * FROM terms $orderby" do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res } @@ -408,36 +408,36 @@ } do_plansql_test 4.2 { SELECT y FROM x2, terms WHERE y = term AND col = '*' } { - 0 0 0 {SCAN TABLE x2 (~1000000 rows)} - 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} + 0 0 0 {SCAN TABLE x2} + 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} a b c d e f g h i j k l } do_plansql_test 4.3 { SELECT y FROM terms, x2 WHERE y = term AND col = '*' } { - 0 0 1 {SCAN TABLE x2 (~1000000 rows)} - 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} + 0 0 1 {SCAN TABLE x2} + 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} a b c d e f g h i j k l } do_plansql_test 4.4 { SELECT y FROM x3, terms WHERE y = term AND col = '*' } { - 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} - 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} + 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} + 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)} a b c d e f g h i j k l } do_plansql_test 4.5 { SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' } { - 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} - 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} + 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} + 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)} a k l } #------------------------------------------------------------------------- # The following tests check that fts4aux can handle an fts table with an Index: test/fts3corrupt.test ================================================================== --- test/fts3corrupt.test +++ test/fts3corrupt.test @@ -164,6 +164,5 @@ } {1 {database disk image is malformed}} do_test 5.3.1 { sqlite3_extended_errcode db } SQLITE_CORRUPT_VTAB finish_test - Index: test/fts3defer2.test ================================================================== --- test/fts3defer2.test +++ test/fts3defer2.test @@ -151,6 +151,5 @@ } {1 {1 1 1 4 4 11 912 6} 3 {1 1 1 4 4 11 912 6}} } finish_test - Index: test/fts3expr3.test ================================================================== --- test/fts3expr3.test +++ test/fts3expr3.test @@ -202,9 +202,5 @@ faultsim_test_result [list 0 $::result] } set sqlite_fts3_enable_parentheses 0 finish_test - - - - Index: test/fts3malloc.test ================================================================== --- test/fts3malloc.test +++ test/fts3malloc.test @@ -299,6 +299,5 @@ INSERT INTO ft8 VALUES('short alongertoken reallyquitealotlongerimeanit andthistokenisjustsolongthatonemightbeforgivenforimaginingthatitwasmerelyacontrivedexampleandnotarealtoken') } finish_test - Index: test/fts3matchinfo.test ================================================================== --- test/fts3matchinfo.test +++ test/fts3matchinfo.test @@ -425,6 +425,5 @@ } { {0 3 2 0 3 2 1 4 3} {1 3 2 1 3 2 1 4 3} {2 3 2 2 3 2 2 4 3} } finish_test - Index: test/fts3prefix2.test ================================================================== --- test/fts3prefix2.test +++ test/fts3prefix2.test @@ -57,6 +57,5 @@ {T TX T TX T TX T TX T TX} {T TX T TX T TX T TX T TX} } finish_test - Index: test/fts3query.test ================================================================== --- test/fts3query.test +++ test/fts3query.test @@ -116,30 +116,30 @@ } } {} do_eqp_test fts3query-4.2 { SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} - 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} + 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} } do_eqp_test fts3query-4.3 { SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date } { - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} - 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} + 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} + 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} } do_eqp_test fts3query-4.4 { SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} - 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} + 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} } do_eqp_test fts3query-4.5 { SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date } { - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} - 0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} + 0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} } # Test that calling matchinfo() with the wrong number of arguments, or with # an invalid argument returns an error. Index: test/fts3shared.test ================================================================== --- test/fts3shared.test +++ test/fts3shared.test @@ -172,6 +172,5 @@ dbW close dbR close sqlite3_enable_shared_cache $::enable_shared_cache finish_test - Index: test/fts3snippet.test ================================================================== --- test/fts3snippet.test +++ test/fts3snippet.test @@ -430,14 +430,14 @@ {2 2 1 3 3 2 6 3 0 0 0 1 3 2} {2 2 1 3 3 3 6 3 0 0 0 2 3 2} {2 2 1 3 3 3 6 3 0 0 0 2 3 2} }] - # EVIDENCE-OF: R-40630-02268 If used within a SELECT that uses the - # "query by rowid" or "linear scan" strategies, then the snippet and - # offsets both return an empty string, and the matchinfo function - # returns a blob value zero bytes in size. + # EVIDENCE-OF: R-40630-02268 If used within a SELECT that uses the + # "query by rowid" or "linear scan" strategies, then the snippet and + # offsets both return an empty string, and the matchinfo function + # returns a blob value zero bytes in size. # set r 1000000 ;# A rowid that exists in table ft do_select_test $T.10.0 { SELECT rowid FROM ft WHERE rowid = $r } $r do_select_test $T.10.1 { SELECT length(offsets(ft)), typeof(offsets(ft)) FROM ft; Index: test/fts3sort.test ================================================================== --- test/fts3sort.test +++ test/fts3sort.test @@ -180,6 +180,5 @@ } {-113382409004785664 1} finish_test - Index: test/fts3tok1.test ================================================================== --- test/fts3tok1.test +++ test/fts3tok1.test @@ -111,7 +111,5 @@ SELECT * FROM t4; } {1 {SQL logic error or missing database}} finish_test - - Index: test/fts3tok_err.test ================================================================== --- test/fts3tok_err.test +++ test/fts3tok_err.test @@ -43,7 +43,5 @@ faultsim_test_result {0 {a galaxy far far away}} } finish_test - - Index: test/fts4content.test ================================================================== --- test/fts4content.test +++ test/fts4content.test @@ -621,6 +621,5 @@ } { {...c d [e] f g...} } finish_test - Index: test/incrblob3.test ================================================================== --- test/incrblob3.test +++ test/incrblob3.test @@ -267,6 +267,5 @@ } {1 {database schema has changed}} db close tvfs delete finish_test - Index: test/incrblob4.test ================================================================== --- test/incrblob4.test +++ test/incrblob4.test @@ -85,6 +85,5 @@ execsql { DELETE FROM t1 WHERE k=19 } execsql { INSERT INTO t1(v) VALUES($new) } } {} finish_test - Index: test/incrblobfault.test ================================================================== --- test/incrblobfault.test +++ test/incrblobfault.test @@ -65,6 +65,5 @@ faultsim_test_result {0 {hello world}} catch { close $::blob } } finish_test - Index: test/incrvacuum3.test ================================================================== --- test/incrvacuum3.test +++ test/incrvacuum3.test @@ -149,6 +149,5 @@ do_execsql_test $T.1.x.1 { PRAGMA freelist_count } 0 do_execsql_test $T.1.x.2 { SELECT count(*) FROM t1 } 128 } finish_test - Index: test/indexedby.test ================================================================== --- test/indexedby.test +++ test/indexedby.test @@ -40,19 +40,19 @@ # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. # do_execsql_test indexedby-1.2 { EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-1.3 { EXPLAIN QUERY PLAN select * from t1 ; -} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} +} {0 0 0 {SCAN TABLE t1}} do_execsql_test indexedby-1.4 { EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; } { - 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} - 0 1 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} + 0 1 0 {SCAN TABLE t1} } # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be # attached to a table in the FROM clause, but not to a sub-select or # SQL view. Also test that specifying an index that does not exist or @@ -83,19 +83,19 @@ # Tests for single table cases. # do_execsql_test indexedby-3.1 { EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' -} {0 0 0 {SCAN TABLE t1 (~10000 rows)}} +} {0 0 0 {SCAN TABLE t1}} do_execsql_test indexedby-3.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-3.3 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-3.4 { catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } } {1 {cannot use index: i2}} do_test indexedby-3.5 { catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } @@ -108,15 +108,15 @@ } {0 {}} do_execsql_test indexedby-3.8 { EXPLAIN QUERY PLAN SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e -} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} +} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}} do_execsql_test indexedby-3.9 { EXPLAIN QUERY PLAN SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 -} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}} do_test indexedby-3.10 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } } {1 {cannot use index: sqlite_autoindex_t3_1}} do_test indexedby-3.11 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } @@ -125,18 +125,18 @@ # Tests for multiple table cases. # do_execsql_test indexedby-4.1 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} - 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} + 0 0 0 {SCAN TABLE t1} + 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} } do_execsql_test indexedby-4.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c } { - 0 0 1 {SCAN TABLE t2 (~1000000 rows)} - 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} + 0 0 1 {SCAN TABLE t2} + 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} } do_test indexedby-4.3 { catchsql { SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c } @@ -152,14 +152,14 @@ # a CREATE VIEW statement is dropped and recreated. # do_execsql_test indexedby-5.1 { CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; EXPLAIN QUERY PLAN SELECT * FROM v2 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} do_execsql_test indexedby-5.2 { EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} do_test indexedby-5.3 { execsql { DROP INDEX i1 } catchsql { SELECT * FROM v2 } } {1 {no such index: i1}} do_test indexedby-5.4 { @@ -176,55 +176,55 @@ # Test that "NOT INDEXED" may use the rowid index, but not others. # do_execsql_test indexedby-6.1 { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_execsql_test indexedby-6.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid -} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} +} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY}} # Test that "INDEXED BY" can be used in a DELETE statement. # do_execsql_test indexedby-7.1 { EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} do_execsql_test indexedby-7.2 { EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} +} {0 0 0 {SCAN TABLE t1}} do_execsql_test indexedby-7.3 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} do_execsql_test indexedby-7.4 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-7.5 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-7.6 { catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} } {1 {cannot use index: i2}} # Test that "INDEXED BY" can be used in an UPDATE statement. # do_execsql_test indexedby-8.1 { EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} do_execsql_test indexedby-8.2 { EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} +} {0 0 0 {SCAN TABLE t1}} do_execsql_test indexedby-8.3 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} do_execsql_test indexedby-8.4 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} do_execsql_test indexedby-8.5 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} do_test indexedby-8.6 { catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} } {1 {cannot use index: i2}} # Test that bug #3560 is fixed. Index: test/io.test ================================================================== --- test/io.test +++ test/io.test @@ -639,6 +639,5 @@ db close } sqlite3_simulate_device -char {} -sectorsize 0 finish_test - Index: test/ioerr6.test ================================================================== --- test/ioerr6.test +++ test/ioerr6.test @@ -87,6 +87,5 @@ error "integrity check failed" } } finish_test - Index: test/like.test ================================================================== --- test/like.test +++ test/like.test @@ -164,17 +164,17 @@ if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { - if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \ + if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \ $x all as tab idx]} { lappend data {} $idx - } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ + } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } Index: test/lock7.test ================================================================== --- test/lock7.test +++ test/lock7.test @@ -56,6 +56,5 @@ db1 close db2 close finish_test - Index: test/misc7.test ================================================================== --- test/misc7.test +++ test/misc7.test @@ -267,21 +267,21 @@ ifcapable explain { do_execsql_test misc7-14.1 { CREATE TABLE abc(a PRIMARY KEY, b, c); EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1; } { - 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)} } do_execsql_test misc7-14.2 { EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1; } {0 0 0 - {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?) (~1 rows)} + {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)} } do_execsql_test misc7-14.3 { EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a; } {0 0 0 - {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (~1000000 rows)} + {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1} } } db close forcedelete test.db Index: test/notify3.test ================================================================== --- test/notify3.test +++ test/notify3.test @@ -148,6 +148,5 @@ catch { db2 close } sqlite3_enable_shared_cache $esc finish_test - Index: test/pager1.test ================================================================== --- test/pager1.test +++ test/pager1.test @@ -2813,6 +2813,5 @@ db eval { SELECT * FROM t3 } sqlite3_db_status db CACHE_MISS 0 } {0 1 0} finish_test - Index: test/pagerfault.test ================================================================== --- test/pagerfault.test +++ test/pagerfault.test @@ -1544,6 +1544,5 @@ sqlite3_shutdown sqlite3_config_uri 0 finish_test - Index: test/pagerfault2.test ================================================================== --- test/pagerfault2.test +++ test/pagerfault2.test @@ -94,6 +94,5 @@ faultsim_test_result {0 {}} } sqlite3_memdebug_vfs_oom_test 1 finish_test - Index: test/pagerfault3.test ================================================================== --- test/pagerfault3.test +++ test/pagerfault3.test @@ -59,6 +59,5 @@ faultsim_test_result {0 {}} faultsim_integrity_check } finish_test - Index: test/securedel2.test ================================================================== --- test/securedel2.test +++ test/securedel2.test @@ -90,6 +90,5 @@ } set n } {0} finish_test - Index: test/shared8.test ================================================================== --- test/shared8.test +++ test/shared8.test @@ -108,6 +108,5 @@ foreach db {db1 db2 db3 db4} { catch { $db close } } sqlite3_enable_shared_cache $::enable_shared_cache finish_test - Index: test/sharedlock.test ================================================================== --- test/sharedlock.test +++ test/sharedlock.test @@ -50,6 +50,5 @@ db close db2 close sqlite3_enable_shared_cache $::enable_shared_cache finish_test - Index: test/tkt-385a5b56b9.test ================================================================== --- test/tkt-385a5b56b9.test +++ test/tkt-385a5b56b9.test @@ -33,21 +33,21 @@ CREATE UNIQUE INDEX t2x ON t2(x); CREATE UNIQUE INDEX t2y ON t2(y); } do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x} } do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y} } do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)} + 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)} } do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)} + 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?)} } finish_test Index: test/tkt-3a77c9714e.test ================================================================== --- test/tkt-3a77c9714e.test +++ test/tkt-3a77c9714e.test @@ -68,6 +68,5 @@ ) } {FACTORING FACTOR SWIMMING SWIMM} finish_test - Index: test/tkt-3fe897352e.test ================================================================== --- test/tkt-3fe897352e.test +++ test/tkt-3fe897352e.test @@ -14,13 +14,13 @@ # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl - -# The following tests use hex_to_utf16be() and hex_to_utf16le() which -# which are only available if SQLite is built with UTF16 support. + +# The following tests use hex_to_utf16be() and hex_to_utf16le() which +# which are only available if SQLite is built with UTF16 support. ifcapable {!utf16} { finish_test return } Index: test/tkt-78e04e52ea.test ================================================================== --- test/tkt-78e04e52ea.test +++ test/tkt-78e04e52ea.test @@ -42,11 +42,11 @@ } {} do_test tkt-78e04-1.4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%'; } -} {0 0 0 {SCAN TABLE USING COVERING INDEX i1 (~500000 rows)}} +} {0 0 0 {SCAN TABLE USING COVERING INDEX i1}} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; SELECT name FROM sqlite_master; } @@ -55,14 +55,14 @@ do_test tkt-78e04-2.1 { execsql { CREATE INDEX "" ON t2(x); EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; } -} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?)}} do_test tkt-78e04-2.2 { execsql { DROP INDEX ""; EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; } -} {0 0 0 {SCAN TABLE t2 (~100000 rows)}} +} {0 0 0 {SCAN TABLE t2}} finish_test Index: test/tkt-7a31705a7e6.test ================================================================== --- test/tkt-7a31705a7e6.test +++ test/tkt-7a31705a7e6.test @@ -21,6 +21,5 @@ CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a INTEGER PRIMARY KEY, b INTEGER); CREATE TABLE t2x (b INTEGER PRIMARY KEY); SELECT t1.a FROM ((t1 JOIN t2 ON t1.a=t2.a) AS x JOIN t2x ON x.b=t2x.b) as y; } {} - Index: test/tkt-7bbfb7d442.test ================================================================== --- test/tkt-7bbfb7d442.test +++ test/tkt-7bbfb7d442.test @@ -150,7 +150,5 @@ FROM InventoryControl WHERE SKU=31; } {{TEST PASSED!}} finish_test - - Index: test/tkt-c48d99d690.test ================================================================== --- test/tkt-c48d99d690.test +++ test/tkt-c48d99d690.test @@ -21,6 +21,5 @@ } {4} do_test 1.2 { execsql VACUUM } {} finish_test - Index: test/tkt-d11f09d36e.test ================================================================== --- test/tkt-d11f09d36e.test +++ test/tkt-d11f09d36e.test @@ -57,6 +57,5 @@ do_test tkt-d11f09d36e.5 { execsql { PRAGMA integrity_check } } {ok} finish_test - Index: test/tkt-f3e5abed55.test ================================================================== --- test/tkt-f3e5abed55.test +++ test/tkt-f3e5abed55.test @@ -112,6 +112,5 @@ } {1 2 3 4 1 2 3 4} } finish_test - Index: test/tkt-f973c7ac31.test ================================================================== --- test/tkt-f973c7ac31.test +++ test/tkt-f973c7ac31.test @@ -82,6 +82,5 @@ } {5 4 5 5} } finish_test - Index: test/tkt3442.test ================================================================== --- test/tkt3442.test +++ test/tkt3442.test @@ -47,23 +47,23 @@ # and verify that the query plan is the same. # ifcapable explain { do_test tkt3442-1.2 { EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} do_test tkt3442-1.3 { EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} } # Some extra tests testing other permutations of 5000. # ifcapable explain { do_test tkt3442-1.4 { EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} } do_test tkt3442-1.5 { catchsql { SELECT node FROM listhash WHERE id=[5000] LIMIT 1; } Index: test/tkt3918.test ================================================================== --- test/tkt3918.test +++ test/tkt3918.test @@ -55,6 +55,5 @@ do_test tkt3918.5 { execsql { CREATE TABLE t2(a, b) } } {} finish_test - Index: test/tkt3929.test ================================================================== --- test/tkt3929.test +++ test/tkt3929.test @@ -48,6 +48,5 @@ } } {} integrity_check tkt3930-1.3 finish_test - Index: test/unordered.test ================================================================== --- test/unordered.test +++ test/unordered.test @@ -38,32 +38,32 @@ } db close sqlite3 db test.db foreach {tn sql r(ordered) r(unordered)} { 1 "SELECT * FROM t1 ORDER BY a" - {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}} - {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} + {0 0 0 {SCAN TABLE t1 USING INDEX i1}} + {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 2 "SELECT * FROM t1 WHERE a >?" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~32 rows)}} - {0 0 0 {SCAN TABLE t1 (~42 rows)}} + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} + {0 0 0 {SCAN TABLE t1}} 3 "SELECT * FROM t1 WHERE a = ? ORDER BY rowid" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 4 "SELECT max(a) FROM t1" - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} + {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} + {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} 5 "SELECT group_concat(b) FROM t1 GROUP BY a" - {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}} - {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} + {0 0 0 {SCAN TABLE t1 USING INDEX i1}} + {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} 6 "SELECT * FROM t1 WHERE a = ?" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 7 "SELECT count(*) FROM t1" {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1(~128 rows)}} - {0 0 0 {SCAN TABLE t1 (~128 rows)}} + {0 0 0 {SCAN TABLE t1}} } { do_eqp_test 1.$idxmode.$tn $sql $r($idxmode) } } Index: test/veryquick.test ================================================================== --- test/veryquick.test +++ test/veryquick.test @@ -14,6 +14,5 @@ source $testdir/permutations.test run_test_suite veryquick finish_test - Index: test/wal8.test ================================================================== --- test/wal8.test +++ test/wal8.test @@ -86,6 +86,5 @@ PRAGMA page_size = 4096; SELECT name FROM sqlite_master; } {t1} finish_test - Index: test/walcksum.test ================================================================== --- test/walcksum.test +++ test/walcksum.test @@ -388,6 +388,5 @@ set FAIL } {0} } finish_test - Index: test/walcrash.test ================================================================== --- test/walcrash.test +++ test/walcrash.test @@ -291,6 +291,5 @@ db close } finish_test - Index: test/walcrash2.test ================================================================== --- test/walcrash2.test +++ test/walcrash2.test @@ -94,6 +94,5 @@ execsql { SELECT count(*) FROM t1 } db2 } {0} catch { db2 close } finish_test - Index: test/walcrash3.test ================================================================== --- test/walcrash3.test +++ test/walcrash3.test @@ -124,6 +124,5 @@ execsql { PRAGMA integrity_check } } {ok} } finish_test - Index: test/walro.test ================================================================== --- test/walro.test +++ test/walro.test @@ -289,7 +289,5 @@ code1 { tv delete } } {} } finish_test - - Index: test/walshared.test ================================================================== --- test/walshared.test +++ test/walshared.test @@ -58,6 +58,5 @@ sqlite3_enable_shared_cache $::enable_shared_cache finish_test - Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -65,11 +65,11 @@ do_test where-1.1.1 { count {SELECT x, y, w FROM t1 WHERE w=10} } {3 121 10 3} do_eqp_test where-1.1.2 { SELECT x, y, w FROM t1 WHERE w=10 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.1.3 { db status step } {0} do_test where-1.1.4 { db eval {SELECT x, y, w FROM t1 WHERE +w=10} @@ -77,17 +77,17 @@ do_test where-1.1.5 { db status step } {99} do_eqp_test where-1.1.6 { SELECT x, y, w FROM t1 WHERE +w=10 -} {*SCAN TABLE t1 *} +} {*SCAN TABLE t1*} do_test where-1.1.7 { count {SELECT x, y, w AS abc FROM t1 WHERE abc=10} } {3 121 10 3} do_eqp_test where-1.1.8 { SELECT x, y, w AS abc FROM t1 WHERE abc=10 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.1.9 { db status step } {0} do_test where-1.2.1 { count {SELECT x, y, w FROM t1 WHERE w=11} @@ -104,23 +104,23 @@ do_test where-1.4.1 { count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} } {11 3 144 3} do_eqp_test where-1.4.2 { SELECT w, x, y FROM t1 WHERE 11=w AND x>2 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.4.3 { count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} } {11 3 144 3} do_eqp_test where-1.4.4 { SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.5 { count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} } {3 144 3} do_eqp_test where-1.5.2 { SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.6 { count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} } {3 144 3} do_test where-1.7 { count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} @@ -128,14 +128,14 @@ do_test where-1.8 { count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} } {3 144 3} do_eqp_test where-1.8.2 { SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3 -} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?) *} +} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*} do_eqp_test where-1.8.3 { SELECT x, y FROM t1 WHERE y=144 AND x=3 -} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?) *} +} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*} do_test where-1.9 { count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} } {3 144 3} do_test where-1.10 { count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} Index: test/where2.test ================================================================== --- test/where2.test +++ test/where2.test @@ -74,14 +74,14 @@ if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { - if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } Index: test/where3.test ================================================================== --- test/where3.test +++ test/where3.test @@ -109,14 +109,14 @@ set ::sqlite_sort_count 0 set data [execsql $sql] set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { - if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } @@ -234,19 +234,19 @@ CREATE TABLE t302(x, y); INSERT INTO t302 VALUES(4,5); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { - 0 0 0 {SCAN TABLE t302 (~1 rows)} - 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SCAN TABLE t302} + 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} } do_execsql_test where3-3.1 { explain query plan SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; } { - 0 0 1 {SCAN TABLE t302 (~1 rows)} - 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SCAN TABLE t302} + 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} } # Verify that when there are multiple tables in a join which must be # full table scans that the query planner attempts put the table with # the fewest number of output rows as the outer loop. @@ -256,29 +256,29 @@ CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r); CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z); EXPLAIN QUERY PLAN SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*'; } { - 0 0 2 {SCAN TABLE t402 (~500000 rows)} - 0 1 0 {SCAN TABLE t400 (~1000000 rows)} - 0 2 1 {SCAN TABLE t401 (~1000000 rows)} + 0 0 2 {SCAN TABLE t402} + 0 1 0 {SCAN TABLE t400} + 0 2 1 {SCAN TABLE t401} } do_execsql_test where3-4.1 { EXPLAIN QUERY PLAN SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*'; } { - 0 0 1 {SCAN TABLE t401 (~500000 rows)} - 0 1 0 {SCAN TABLE t400 (~1000000 rows)} - 0 2 2 {SCAN TABLE t402 (~1000000 rows)} + 0 0 1 {SCAN TABLE t401} + 0 1 0 {SCAN TABLE t400} + 0 2 2 {SCAN TABLE t402} } do_execsql_test where3-4.2 { EXPLAIN QUERY PLAN SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*'; } { - 0 0 0 {SCAN TABLE t400 (~500000 rows)} - 0 1 1 {SCAN TABLE t401 (~1000000 rows)} - 0 2 2 {SCAN TABLE t402 (~1000000 rows)} + 0 0 0 {SCAN TABLE t400} + 0 1 1 {SCAN TABLE t401} + 0 2 2 {SCAN TABLE t402} } # Verify that a performance regression encountered by firefox # has been fixed. # @@ -306,12 +306,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} + 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.1 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -319,12 +319,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} + 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.2 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -332,12 +332,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} + 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.3 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -345,12 +345,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} + 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } # Name resolution with NATURAL JOIN and USING # Index: test/where7.test ================================================================== --- test/where7.test +++ test/where7.test @@ -23337,12 +23337,12 @@ AND t302.c3 > 1287603136 AND (t301.c4 = 1407449685622784 OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200; } { - 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} - 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} + 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)} + 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} + 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -360,23 +360,23 @@ do_execsql_test where9-3.1 { EXPLAIN QUERY PLAN SELECT t2.a FROM t1, t2 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) } { - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} } do_execsql_test where9-3.2 { EXPLAIN QUERY PLAN SELECT coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 } { - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} } } # Make sure that INDEXED BY and multi-index OR clauses play well with # one another. @@ -451,29 +451,29 @@ # the former is an equality test which is expected to return fewer rows. # do_execsql_test where9-5.1 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~3 rows)} - 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~3 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)} } # In contrast, b=1000 is preferred over any OR-clause. # do_execsql_test where9-5.2 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)} } # Likewise, inequalities in an AND are preferred over inequalities in # an OR. # do_execsql_test where9-5.3 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)} } } ############################################################################ # Make sure OR-clauses work correctly on UPDATE and DELETE statements. Index: test/whereC.test ================================================================== --- test/whereC.test +++ test/whereC.test @@ -65,6 +65,5 @@ do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec $res] } finish_test - Index: test/whereE.test ================================================================== --- test/whereE.test +++ test/whereE.test @@ -45,18 +45,18 @@ ALTER TABLE t2 ADD COLUMN z; UPDATE t2 SET z=2; CREATE UNIQUE INDEX t2zx ON t2(z,x); EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.2 { EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.3 { ANALYZE; EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.4 { EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} finish_test ADDED tool/wherecosttest.c Index: tool/wherecosttest.c ================================================================== --- /dev/null +++ tool/wherecosttest.c @@ -0,0 +1,111 @@ +/* +** 2013-06-10 +** +** 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 contains a simple command-line utility for converting from +** integers and WhereCost values and back again and for doing simple +** arithmetic operations (multiple and add) on WhereCost values. +** +** Usage: +** +** ./wherecosttest ARGS +** +** Arguments: +** +** 'x' Multiple the top two elements of the stack +** '+' Add the top two elements of the stack +** NUM Convert NUM from integer to WhereCost and push onto the stack +** ^NUM Interpret NUM as a WhereCost and push onto stack. +** +** Examples: +** +** To convert 123 from WhereCost to integer: +** +** ./wherecosttest ^123 +** +** To convert 123456 from integer to WhereCost: +** +** ./wherecosttest 123456 +** +*/ +#include +#include +#include + +typedef unsigned short int WhereCost; /* 10 times log2() */ + +WhereCost whereCostMultiply(WhereCost a, WhereCost b){ return a+b; } +WhereCost whereCostAdd(WhereCost a, WhereCost b){ + static const unsigned char x[] = { + 10, 10, /* 0,1 */ + 9, 9, /* 2,3 */ + 8, 8, /* 4,5 */ + 7, 7, 7, /* 6,7,8 */ + 6, 6, 6, /* 9,10,11 */ + 5, 5, 5, /* 12-14 */ + 4, 4, 4, 4, /* 15-18 */ + 3, 3, 3, 3, 3, 3, /* 19-24 */ + 2, 2, 2, 2, 2, 2, 2, /* 25-31 */ + }; + if( ab+49 ) return a; + if( a>b+31 ) return a+1; + return a+x[a-b]; +} +WhereCost whereCostFromInteger(int x){ + static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 }; + WhereCost y = 40; + if( x<8 ){ + if( x<2 ) return 0; + while( x<8 ){ y -= 10; x <<= 1; } + }else{ + while( x>255 ){ y += 40; x >>= 4; } + while( x>15 ){ y += 10; x >>= 1; } + } + return a[x&7] + y - 10; +} +static unsigned long int whereCostToInt(WhereCost x){ + unsigned long int n; + if( x<=10 ) return 1; + n = x%10; + x /= 10; + if( n>=5 ) n -= 2; + else if( n>=1 ) n -= 1; + if( x>=3 ) return (n+8)<<(x-3); + return (n+8)>>(3-x); +} + +int main(int argc, char **argv){ + int i; + int n = 0; + WhereCost a[100]; + for(i=1; i=2 ){ + a[n-2] = whereCostAdd(a[n-2],a[n-1]); + n--; + } + }else if( z[0]=='x' ){ + if( n>=2 ){ + a[n-2] = whereCostMultiply(a[n-2],a[n-1]); + n--; + } + }else if( z[0]=='^' ){ + a[n++] = atoi(z+1); + }else{ + a[n++] = whereCostFromInteger(atoi(z)); + } + } + for(i=n-1; i>=0; i--){ + printf("%d (%lu)\n", a[i], whereCostToInt(a[i])); + } + return 0; +}