Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
| Files: | files | file ages | folders |
| SHA1: |
b777b1097dcf9dfeb1b86c71e1b5f691 |
| User & Date: | drh 2013-06-11 02:36:41 |
Context
|
2013-06-11
| ||
| 13:30 | Fix the Parse.nQueryLoop state variable to work with NGQP. check-in: f1cac24f user: drh tags: nextgen-query-plan-exp | |
| 02:36 | Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output. check-in: b777b109 user: drh tags: nextgen-query-plan-exp | |
| 02:32 | Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes. Closed-Leaf check-in: 36373b85 user: drh tags: nextgen-query-plan-logcost | |
|
2013-06-10
| ||
| 14:56 | Simplification and performance tweak to the high-speed NGQP bypass. check-in: 0f8a38ee user: drh tags: nextgen-query-plan-exp | |
Changes
Changes to ext/rtree/rtree6.test.
70 70 do_test rtree6-1.5 { 71 71 rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10} 72 72 } {Ca} 73 73 74 74 do_eqp_test rtree6.2.1 { 75 75 SELECT * FROM t1,t2 WHERE k=+ii AND x1<10 76 76 } { 77 - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} 78 - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 77 + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} 78 + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 79 79 } 80 80 81 81 do_eqp_test rtree6.2.2 { 82 82 SELECT * FROM t1,t2 WHERE k=ii AND x1<10 83 83 } { 84 - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} 85 - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 84 + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} 85 + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 86 86 } 87 87 88 88 do_eqp_test rtree6.2.3 { 89 89 SELECT * FROM t1,t2 WHERE k=ii 90 90 } { 91 - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~25 rows)} 92 - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 91 + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 92 + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 93 93 } 94 94 95 95 do_eqp_test rtree6.2.4 { 96 96 SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10 97 97 } { 98 - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~25 rows)} 99 - 0 1 1 {SCAN TABLE t2 (~100000 rows)} 98 + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb} 99 + 0 1 1 {SCAN TABLE t2} 100 100 } 101 101 102 102 do_eqp_test rtree6.2.5 { 103 103 SELECT * FROM t1,t2 WHERE k=ii AND x1<v 104 104 } { 105 - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~25 rows)} 106 - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 105 + 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 106 + 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} 107 107 } 108 108 109 109 do_execsql_test rtree6-3.1 { 110 110 CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2); 111 111 INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2); 112 112 SELECT * FROM t3 WHERE 113 113 x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND
Changes to ext/rtree/rtree8.test.
164 164 execsql { DELETE FROM t2 WHERE id = $i } 165 165 } 166 166 execsql COMMIT 167 167 } {} 168 168 169 169 170 170 finish_test 171 -
Changes to src/select.c.
1535 1535 if( NEVER(v==0) ) return; /* VDBE should have already been allocated */ 1536 1536 if( sqlite3ExprIsInteger(p->pLimit, &n) ){ 1537 1537 sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit); 1538 1538 VdbeComment((v, "LIMIT counter")); 1539 1539 if( n==0 ){ 1540 1540 sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak); 1541 1541 }else{ 1542 - if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n; 1542 + if( p->nSelectRow > n ) p->nSelectRow = n; 1543 1543 } 1544 1544 }else{ 1545 1545 sqlite3ExprCode(pParse, p->pLimit, iLimit); 1546 1546 sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); 1547 1547 VdbeComment((v, "LIMIT counter")); 1548 1548 sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); 1549 1549 } ................................................................................ 1729 1729 rc = sqlite3Select(pParse, p, &dest); 1730 1730 testcase( rc!=SQLITE_OK ); 1731 1731 pDelete = p->pPrior; 1732 1732 p->pPrior = pPrior; 1733 1733 p->nSelectRow += pPrior->nSelectRow; 1734 1734 if( pPrior->pLimit 1735 1735 && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit) 1736 - && p->nSelectRow > (double)nLimit 1736 + && p->nSelectRow > nLimit 1737 1737 ){ 1738 - p->nSelectRow = (double)nLimit; 1738 + p->nSelectRow = nLimit; 1739 1739 } 1740 1740 if( addr ){ 1741 1741 sqlite3VdbeJumpHere(v, addr); 1742 1742 } 1743 1743 break; 1744 1744 } 1745 1745 case TK_EXCEPT: ................................................................................ 3880 3880 #ifndef SQLITE_OMIT_EXPLAIN 3881 3881 static void explainSimpleCount( 3882 3882 Parse *pParse, /* Parse context */ 3883 3883 Table *pTab, /* Table being queried */ 3884 3884 Index *pIdx /* Index used to optimize scan, or NULL */ 3885 3885 ){ 3886 3886 if( pParse->explain==2 ){ 3887 - char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)", 3887 + char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s", 3888 3888 pTab->zName, 3889 - pIdx ? "USING COVERING INDEX " : "", 3890 - pIdx ? pIdx->zName : "", 3891 - pTab->nRowEst 3889 + pIdx ? " USING COVERING INDEX " : "", 3890 + pIdx ? pIdx->zName : "" 3892 3891 ); 3893 3892 sqlite3VdbeAddOp4( 3894 3893 pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC 3895 3894 ); 3896 3895 } 3897 3896 } 3898 3897 #else ................................................................................ 4235 4234 if( pDest->eDest==SRT_EphemTab ){ 4236 4235 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr); 4237 4236 } 4238 4237 4239 4238 /* Set the limiter. 4240 4239 */ 4241 4240 iEnd = sqlite3VdbeMakeLabel(v); 4242 - p->nSelectRow = (double)LARGEST_INT64; 4241 + p->nSelectRow = LARGEST_INT64; 4243 4242 computeLimitRegisters(pParse, p, iEnd); 4244 4243 if( p->iLimit==0 && addrSortIndex>=0 ){ 4245 4244 sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen; 4246 4245 p->selFlags |= SF_UseSorter; 4247 4246 } 4248 4247 4249 4248 /* Open a virtual index to use for the distinct set. ................................................................................ 4316 4315 4317 4316 for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){ 4318 4317 pItem->iAlias = 0; 4319 4318 } 4320 4319 for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){ 4321 4320 pItem->iAlias = 0; 4322 4321 } 4323 - if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100; 4322 + if( p->nSelectRow>100 ) p->nSelectRow = 100; 4324 4323 }else{ 4325 - p->nSelectRow = (double)1; 4324 + p->nSelectRow = 1; 4326 4325 } 4327 4326 4328 4327 4329 4328 /* Create a label to jump to when we want to abort the query */ 4330 4329 addrEnd = sqlite3VdbeMakeLabel(v); 4331 4330 4332 4331 /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
Changes to src/sqliteInt.h.
2038 2038 */ 2039 2039 struct Select { 2040 2040 ExprList *pEList; /* The fields of the result */ 2041 2041 u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ 2042 2042 u16 selFlags; /* Various SF_* values */ 2043 2043 int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ 2044 2044 int addrOpenEphm[3]; /* OP_OpenEphem opcodes related to this select */ 2045 - double nSelectRow; /* Estimated number of result rows */ 2045 + u64 nSelectRow; /* Estimated number of result rows */ 2046 2046 SrcList *pSrc; /* The FROM clause */ 2047 2047 Expr *pWhere; /* The WHERE clause */ 2048 2048 ExprList *pGroupBy; /* The GROUP BY clause */ 2049 2049 Expr *pHaving; /* The HAVING clause */ 2050 2050 ExprList *pOrderBy; /* The ORDER BY clause */ 2051 2051 Select *pPrior; /* Prior select in a compound select statement */ 2052 2052 Select *pNext; /* Next select to the left in a compound */ ................................................................................ 2222 2222 TableLock *aTableLock; /* Required table locks for shared-cache mode */ 2223 2223 #endif 2224 2224 AutoincInfo *pAinc; /* Information about AUTOINCREMENT counters */ 2225 2225 2226 2226 /* Information used while coding trigger programs. */ 2227 2227 Parse *pToplevel; /* Parse structure for main program (or NULL) */ 2228 2228 Table *pTriggerTab; /* Table triggers are being coded for */ 2229 - double nQueryLoop; /* Estimated number of iterations of a query */ 2229 + u32 nQueryLoop; /* Estimated number of iterations of a query */ 2230 2230 u32 oldmask; /* Mask of old.* columns referenced */ 2231 2231 u32 newmask; /* Mask of new.* columns referenced */ 2232 2232 u8 eTriggerOp; /* TK_UPDATE, TK_INSERT or TK_DELETE */ 2233 2233 u8 eOrconf; /* Default ON CONFLICT policy for trigger steps */ 2234 2234 u8 disableTriggers; /* True to disable triggers */ 2235 2235 2236 2236 /* Above is constant between recursions. Below is reset before and after ................................................................................ 2792 2792 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) 2793 2793 Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*); 2794 2794 #endif 2795 2795 void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); 2796 2796 void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); 2797 2797 WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int); 2798 2798 void sqlite3WhereEnd(WhereInfo*); 2799 -double sqlite3WhereOutputRowCount(WhereInfo*); 2799 +u64 sqlite3WhereOutputRowCount(WhereInfo*); 2800 2800 int sqlite3WhereIsDistinct(WhereInfo*); 2801 2801 int sqlite3WhereIsOrdered(WhereInfo*); 2802 2802 int sqlite3WhereContinueLabel(WhereInfo*); 2803 2803 int sqlite3WhereBreakLabel(WhereInfo*); 2804 2804 int sqlite3WhereOkOnePass(WhereInfo*); 2805 2805 int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8); 2806 2806 void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
Changes to src/where.c.
41 41 typedef struct WhereAndInfo WhereAndInfo; 42 42 typedef struct WhereLevel WhereLevel; 43 43 typedef struct WhereLoop WhereLoop; 44 44 typedef struct WherePath WherePath; 45 45 typedef struct WhereTerm WhereTerm; 46 46 typedef struct WhereLoopBuilder WhereLoopBuilder; 47 47 typedef struct WhereScan WhereScan; 48 -typedef float WhereCost; 48 + 49 +/* 50 +** Cost X is tracked as 10*log2(X) stored in a 16-bit integer. The 51 +** maximum cost is 64*(2**63) which becomes 6900. So all costs can be 52 +** be stored in a 16-bit unsigned integer without risk of overflow. 53 +*/ 54 +typedef unsigned short int WhereCost; 49 55 50 56 /* 51 57 ** For each nested loop in a WHERE clause implementation, the WhereInfo 52 58 ** structure contains a single instance of this structure. This structure 53 59 ** is intended to be private to the where.c module and should not be 54 60 ** access or modified by other modules. 55 61 ** ................................................................................ 397 403 #define WHERE_VIRTUALTABLE 0x00000400 /* WhereLoop.u.vtab is valid */ 398 404 #define WHERE_IN_ABLE 0x00000800 /* Able to support an IN operator */ 399 405 #define WHERE_ONEROW 0x00001000 /* Selects no more than one row */ 400 406 #define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */ 401 407 #define WHERE_TEMP_INDEX 0x00004000 /* Uses an ephemeral index */ 402 408 #define WHERE_COVER_SCAN 0x00008000 /* Full scan of a covering index */ 403 409 410 + 411 +/* Convert a WhereCost value (10 times log2(X)) into its integer value X. 412 +*/ 413 +static u64 whereCostToInt(WhereCost x){ 414 + u64 n; 415 + if( x<=10 ) return 1; 416 + n = x%10; 417 + x /= 10; 418 + if( n>=5 ) n -= 2; 419 + else if( n>=1 ) n -= 1; 420 + if( x>=3 ) return (n+8)<<(x-3); 421 + return (n+8)>>(3-x); 422 +} 423 + 404 424 /* 405 425 ** Return the estimated number of output rows from a WHERE clause 406 426 */ 407 -double sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ 408 - return (double)pWInfo->nRowOut; 427 +u64 sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ 428 + return whereCostToInt(pWInfo->nRowOut); 409 429 } 410 430 411 431 /* 412 432 ** Return one of the WHERE_DISTINCT_xxxxx values to indicate how this 413 433 ** WHERE clause returns outputs for DISTINCT processing. 414 434 */ 415 435 int sqlite3WhereIsDistinct(WhereInfo *pWInfo){ ................................................................................ 1816 1836 return 1; 1817 1837 } 1818 1838 } 1819 1839 1820 1840 return 0; 1821 1841 } 1822 1842 1843 +/* 1844 +** The sum of two WhereCosts 1845 +*/ 1846 +static WhereCost whereCostAdd(WhereCost a, WhereCost b){ 1847 + static const unsigned char x[] = { 1848 + 10, 10, /* 0,1 */ 1849 + 9, 9, /* 2,3 */ 1850 + 8, 8, /* 4,5 */ 1851 + 7, 7, 7, /* 6,7,8 */ 1852 + 6, 6, 6, /* 9,10,11 */ 1853 + 5, 5, 5, /* 12-14 */ 1854 + 4, 4, 4, 4, /* 15-18 */ 1855 + 3, 3, 3, 3, 3, 3, /* 19-24 */ 1856 + 2, 2, 2, 2, 2, 2, 2, /* 25-31 */ 1857 + }; 1858 + if( a>=b ){ 1859 + if( a>b+49 ) return a; 1860 + if( a>b+31 ) return a+1; 1861 + return a+x[a-b]; 1862 + }else{ 1863 + if( b>a+49 ) return b; 1864 + if( b>a+31 ) return b+1; 1865 + return b+x[b-a]; 1866 + } 1867 +} 1868 + 1869 +/* 1870 +** Convert an integer into a WhereCost 1871 +*/ 1872 +static WhereCost whereCostFromInt(tRowcnt x){ 1873 + static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 }; 1874 + WhereCost y = 40; 1875 + if( x<8 ){ 1876 + if( x<2 ) return 0; 1877 + while( x<8 ){ y -= 10; x <<= 1; } 1878 + }else{ 1879 + while( x>255 ){ y += 40; x >>= 4; } 1880 + while( x>15 ){ y += 10; x >>= 1; } 1881 + } 1882 + return a[x&7] + y - 10; 1883 +} 1884 + 1885 +#ifndef SQLITE_OMIT_VIRTUALTABLE 1886 +/* 1887 +** Convert a double (as received from xBestIndex of a virtual table) 1888 +** into a WhereCost 1889 +*/ 1890 +static WhereCost whereCostFromDouble(double x){ 1891 + u64 a; 1892 + WhereCost e; 1893 + assert( sizeof(x)==8 && sizeof(a)==8 ); 1894 + if( x<=1 ) return 0; 1895 + if( x<=2000000000 ) return whereCostFromInt((tRowcnt)x); 1896 + memcpy(&a, &x, 8); 1897 + e = (a>>52) - 1022; 1898 + return e*10; 1899 +} 1900 +#endif /* SQLITE_OMIT_VIRTUALTABLE */ 1901 + 1823 1902 /* 1824 1903 ** Prepare a crude estimate of the logarithm of the input value. 1825 1904 ** The results need not be exact. This is only used for estimating 1826 1905 ** the total cost of performing operations with O(logN) or O(NlogN) 1827 1906 ** complexity. Because N is just a guess, it is no great tragedy if 1828 1907 ** logN is a little off. 1829 1908 */ 1830 1909 static WhereCost estLog(WhereCost N){ 1831 - u32 a; 1832 - assert( sizeof(WhereCost)==4 ); /* 32-bit float input */ 1833 - if( N<=0.0 ) return 0.0; 1834 - memcpy(&a, &N, 4); 1835 - return ((a >>= 23)-127)*0.3; 1910 + return whereCostFromInt(N) - 33; 1836 1911 } 1837 1912 1838 1913 /* 1839 1914 ** Two routines for printing the content of an sqlite3_index_info 1840 1915 ** structure. Used for testing and debugging only. If neither 1841 1916 ** SQLITE_TEST or SQLITE_DEBUG are defined, then these routines 1842 1917 ** are no-ops. ................................................................................ 2236 2311 tRowcnt *aStat /* OUT: stats written here */ 2237 2312 ){ 2238 2313 tRowcnt n; 2239 2314 IndexSample *aSample; 2240 2315 int i, eType; 2241 2316 int isEq = 0; 2242 2317 i64 v; 2243 - WhereCost r, rS; 2318 + double r, rS; 2244 2319 2245 2320 assert( roundUp==0 || roundUp==1 ); 2246 2321 assert( pIdx->nSample>0 ); 2247 2322 if( pVal==0 ) return SQLITE_ERROR; 2248 2323 n = pIdx->aiRowEst[0]; 2249 2324 aSample = pIdx->aSample; 2250 2325 eType = sqlite3_value_type(pVal); ................................................................................ 2492 2567 ){ 2493 2568 iUpper = a[0]; 2494 2569 if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1]; 2495 2570 } 2496 2571 sqlite3ValueFree(pRangeVal); 2497 2572 } 2498 2573 if( rc==SQLITE_OK ){ 2499 - if( iUpper<=iLower ){ 2500 - *pRangeDiv = (WhereCost)p->aiRowEst[0]; 2501 - }else{ 2502 - *pRangeDiv = (WhereCost)p->aiRowEst[0]/(WhereCost)(iUpper - iLower); 2574 + WhereCost iBase = whereCostFromInt(p->aiRowEst[0]); 2575 + if( iUpper>iLower ){ 2576 + iBase -= whereCostFromInt(iUpper - iLower); 2503 2577 } 2578 + *pRangeDiv = iBase; 2504 2579 /*WHERETRACE(("range scan regions: %u..%u div=%g\n", 2505 2580 (u32)iLower, (u32)iUpper, *pRangeDiv));*/ 2506 2581 return SQLITE_OK; 2507 2582 } 2508 2583 } 2509 2584 #else 2510 2585 UNUSED_PARAMETER(pParse); 2511 2586 UNUSED_PARAMETER(p); 2512 2587 UNUSED_PARAMETER(nEq); 2513 2588 #endif 2514 2589 assert( pLower || pUpper ); 2515 - *pRangeDiv = (WhereCost)1; 2516 - if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (WhereCost)4; 2517 - if( pUpper ) *pRangeDiv *= (WhereCost)4; 2590 + *pRangeDiv = 0; 2591 + if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ){ 2592 + *pRangeDiv += 20; assert( 20==whereCostFromInt(4) ); 2593 + } 2594 + if( pUpper ){ 2595 + *pRangeDiv += 20; assert( 20==whereCostFromInt(4) ); 2596 + } 2518 2597 return rc; 2519 2598 } 2520 2599 2521 2600 #ifdef SQLITE_ENABLE_STAT3 2522 2601 /* 2523 2602 ** Estimate the number of rows that will be returned based on 2524 2603 ** an equality constraint x=VALUE and where that VALUE occurs in ................................................................................ 2536 2615 ** for a UTF conversion required for comparison. The error is stored 2537 2616 ** in the pParse structure. 2538 2617 */ 2539 2618 static int whereEqualScanEst( 2540 2619 Parse *pParse, /* Parsing & code generating context */ 2541 2620 Index *p, /* The index whose left-most column is pTerm */ 2542 2621 Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ 2543 - WhereCost *pnRow /* Write the revised row estimate here */ 2622 + tRowcnt *pnRow /* Write the revised row estimate here */ 2544 2623 ){ 2545 2624 sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */ 2546 2625 u8 aff; /* Column affinity */ 2547 2626 int rc; /* Subfunction return code */ 2548 2627 tRowcnt a[2]; /* Statistics */ 2549 2628 2550 2629 assert( p->aSample!=0 ); ................................................................................ 2585 2664 ** for a UTF conversion required for comparison. The error is stored 2586 2665 ** in the pParse structure. 2587 2666 */ 2588 2667 static int whereInScanEst( 2589 2668 Parse *pParse, /* Parsing & code generating context */ 2590 2669 Index *p, /* The index whose left-most column is pTerm */ 2591 2670 ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ 2592 - WhereCost *pnRow /* Write the revised row estimate here */ 2671 + tRowcnt *pnRow /* Write the revised row estimate here */ 2593 2672 ){ 2594 - int rc = SQLITE_OK; /* Subfunction return code */ 2595 - WhereCost nEst; /* Number of rows for a single term */ 2596 - WhereCost nRowEst = (WhereCost)0; /* New estimate of the number of rows */ 2597 - int i; /* Loop counter */ 2673 + int rc = SQLITE_OK; /* Subfunction return code */ 2674 + tRowcnt nEst; /* Number of rows for a single term */ 2675 + tRowcnt nRowEst = 0; /* New estimate of the number of rows */ 2676 + int i; /* Loop counter */ 2598 2677 2599 2678 assert( p->aSample!=0 ); 2600 2679 for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){ 2601 2680 nEst = p->aiRowEst[0]; 2602 2681 rc = whereEqualScanEst(pParse, p, pList->a[i].pExpr, &nEst); 2603 2682 nRowEst += nEst; 2604 2683 } ................................................................................ 2978 3057 u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ 2979 3058 ){ 2980 3059 if( pParse->explain==2 ){ 2981 3060 struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; 2982 3061 Vdbe *v = pParse->pVdbe; /* VM being constructed */ 2983 3062 sqlite3 *db = pParse->db; /* Database handle */ 2984 3063 char *zMsg; /* Text to add to EQP output */ 2985 - sqlite3_int64 nRow; /* Expected number of rows visited by scan */ 2986 3064 int iId = pParse->iSelectId; /* Select id (left-most output column) */ 2987 3065 int isSearch; /* True for a SEARCH. False for SCAN. */ 2988 3066 WhereLoop *pLoop; /* The controlling WhereLoop object */ 2989 3067 u32 flags; /* Flags that describe this loop */ 2990 3068 2991 3069 pLoop = pLevel->pWLoop; 2992 3070 flags = pLoop->wsFlags; ................................................................................ 3033 3111 } 3034 3112 #ifndef SQLITE_OMIT_VIRTUALTABLE 3035 3113 else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ 3036 3114 zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, 3037 3115 pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr); 3038 3116 } 3039 3117 #endif 3040 - if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){ 3041 - testcase( wctrlFlags & WHERE_ORDERBY_MIN ); 3042 - nRow = 1; 3043 - }else{ 3044 - nRow = (sqlite3_int64)pLoop->nOut; 3045 - } 3046 - zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow); 3118 + zMsg = sqlite3MAppendf(db, zMsg, "%s", zMsg); 3047 3119 sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC); 3048 3120 } 3049 3121 } 3050 3122 #else 3051 3123 # define explainOneScan(u,v,w,x,y,z) 3052 3124 #endif /* SQLITE_OMIT_EXPLAIN */ 3053 3125 ................................................................................ 3824 3896 } 3825 3897 3826 3898 #ifdef WHERETRACE_ENABLED 3827 3899 /* 3828 3900 ** Print a WhereLoop object for debugging purposes 3829 3901 */ 3830 3902 static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){ 3831 - int nb = 2*((pTabList->nSrc+15)/16); 3903 + int nb = 1+(pTabList->nSrc+7)/8; 3832 3904 struct SrcList_item *pItem = pTabList->a + p->iTab; 3833 3905 Table *pTab = pItem->pTab; 3834 3906 sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId, 3835 3907 p->iTab, nb, p->maskSelf, nb, p->prereq); 3836 3908 sqlite3DebugPrintf(" %8s", 3837 3909 pItem->zAlias ? pItem->zAlias : pTab->zName); 3838 3910 if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){ ................................................................................ 3856 3928 }else{ 3857 3929 z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask); 3858 3930 } 3859 3931 sqlite3DebugPrintf(" %-15s", z); 3860 3932 sqlite3_free(z); 3861 3933 } 3862 3934 sqlite3DebugPrintf(" fg %05x N %d", p->wsFlags, p->nLTerm); 3863 - sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n", 3864 - p->prereq, p->rSetup, p->rRun, p->nOut); 3935 + sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut); 3865 3936 } 3866 3937 #endif 3867 3938 3868 3939 /* 3869 3940 ** Convert bulk memory into a valid WhereLoop that can be passed 3870 3941 ** to whereLoopClear harmlessly. 3871 3942 */ ................................................................................ 3991 4062 /* If pBuilder->pBest is defined, then only keep track of the single 3992 4063 ** best WhereLoop. pBuilder->pBest->maskSelf==0 indicates that no 3993 4064 ** prior WhereLoops have been evaluated and that the current pTemplate 3994 4065 ** is therefore the first and hence the best and should be retained. 3995 4066 */ 3996 4067 if( (p = pBuilder->pBest)!=0 ){ 3997 4068 if( p->maskSelf!=0 ){ 3998 - WhereCost rCost = p->rRun + p->rSetup; 3999 - WhereCost rTemplate = pTemplate->rRun + pTemplate->rSetup; 4069 + WhereCost rCost = whereCostAdd(p->rRun,p->rSetup); 4070 + WhereCost rTemplate = whereCostAdd(pTemplate->rRun,pTemplate->rSetup); 4000 4071 if( rCost < rTemplate ){ 4001 4072 goto whereLoopInsert_noop; 4002 4073 } 4003 4074 if( rCost == rTemplate && p->prereq <= pTemplate->prereq ){ 4004 4075 goto whereLoopInsert_noop; 4005 4076 } 4006 4077 } 4007 - whereLoopXfer(db, p, pTemplate); 4008 4078 #if WHERETRACE_ENABLED 4009 4079 if( sqlite3WhereTrace & 0x8 ){ 4010 - sqlite3DebugPrintf("ins-best: "); 4080 + sqlite3DebugPrintf(p->maskSelf==0 ? "ins-init: " : "ins-best: "); 4011 4081 whereLoopPrint(pTemplate, pWInfo->pTabList); 4012 4082 } 4013 4083 #endif 4084 + whereLoopXfer(db, p, pTemplate); 4014 4085 return SQLITE_OK; 4015 4086 } 4016 4087 4017 4088 /* Search for an existing WhereLoop to overwrite, or which takes 4018 4089 ** priority over pTemplate. 4019 4090 */ 4020 4091 for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){ ................................................................................ 4029 4100 && (pTemplate->wsFlags & WHERE_INDEXED)!=0 4030 4101 && p->u.btree.pIndex==pTemplate->u.btree.pIndex 4031 4102 && p->prereq==pTemplate->prereq 4032 4103 ){ 4033 4104 /* Overwrite an existing WhereLoop with an similar one that uses 4034 4105 ** more terms of the index */ 4035 4106 pNext = p->pNextLoop; 4107 + break; 4108 + }else if( p->nOut>pTemplate->nOut 4109 + && p->rSetup==pTemplate->rSetup 4110 + && p->rRun==pTemplate->rRun 4111 + ){ 4112 + /* Overwrite an existing WhereLoop with the same cost but more 4113 + ** outputs */ 4114 + pNext = p->pNextLoop; 4036 4115 break; 4037 4116 }else{ 4038 4117 /* pTemplate is not helpful. 4039 4118 ** Return without changing or adding anything */ 4040 4119 goto whereLoopInsert_noop; 4041 4120 } 4042 4121 } ................................................................................ 4080 4159 } 4081 4160 return SQLITE_OK; 4082 4161 4083 4162 /* Jump here if the insert is a no-op */ 4084 4163 whereLoopInsert_noop: 4085 4164 #if WHERETRACE_ENABLED 4086 4165 if( sqlite3WhereTrace & 0x8 ){ 4087 - sqlite3DebugPrintf("ins-noop: "); 4166 + sqlite3DebugPrintf(pBuilder->pBest ? "ins-skip: " : "ins-noop: "); 4088 4167 whereLoopPrint(pTemplate, pWInfo->pTabList); 4089 4168 } 4090 4169 #endif 4091 4170 return SQLITE_OK; 4092 4171 } 4093 4172 4094 4173 /* ................................................................................ 4098 4177 ** If pProbe->tnum==0, that means pIndex is a fake index used for the 4099 4178 ** INTEGER PRIMARY KEY. 4100 4179 */ 4101 4180 static int whereLoopAddBtreeIndex( 4102 4181 WhereLoopBuilder *pBuilder, /* The WhereLoop factory */ 4103 4182 struct SrcList_item *pSrc, /* FROM clause term being analyzed */ 4104 4183 Index *pProbe, /* An index on pSrc */ 4105 - int nInMul /* Number of iterations due to IN */ 4184 + WhereCost nInMul /* log(Number of iterations due to IN) */ 4106 4185 ){ 4107 4186 WhereInfo *pWInfo = pBuilder->pWInfo; /* WHERE analyse context */ 4108 4187 Parse *pParse = pWInfo->pParse; /* Parsing context */ 4109 4188 sqlite3 *db = pParse->db; /* Database connection malloc context */ 4110 4189 WhereLoop *pNew; /* Template WhereLoop under construction */ 4111 4190 WhereTerm *pTerm; /* A WhereTerm under consideration */ 4112 4191 int opMask; /* Valid operators for constraints */ ................................................................................ 4114 4193 Bitmask saved_prereq; /* Original value of pNew->prereq */ 4115 4194 u16 saved_nLTerm; /* Original value of pNew->nLTerm */ 4116 4195 int saved_nEq; /* Original value of pNew->u.btree.nEq */ 4117 4196 u32 saved_wsFlags; /* Original value of pNew->wsFlags */ 4118 4197 WhereCost saved_nOut; /* Original value of pNew->nOut */ 4119 4198 int iCol; /* Index of the column in the table */ 4120 4199 int rc = SQLITE_OK; /* Return code */ 4121 - tRowcnt iRowEst; /* Estimated index selectivity */ 4200 + WhereCost nRowEst; /* Estimated index selectivity */ 4122 4201 WhereCost rLogSize; /* Logarithm of table size */ 4123 4202 WhereTerm *pTop, *pBtm; /* Top and bottom range constraints */ 4124 4203 4125 4204 pNew = pBuilder->pNew; 4126 4205 if( db->mallocFailed ) return SQLITE_NOMEM; 4127 4206 4128 4207 assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); ................................................................................ 4135 4214 }else{ 4136 4215 opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; 4137 4216 } 4138 4217 if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); 4139 4218 4140 4219 if( pNew->u.btree.nEq < pProbe->nColumn ){ 4141 4220 iCol = pProbe->aiColumn[pNew->u.btree.nEq]; 4142 - iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; 4221 + nRowEst = whereCostFromInt(pProbe->aiRowEst[pNew->u.btree.nEq+1]); 4143 4222 }else{ 4144 4223 iCol = -1; 4145 - iRowEst = 1; 4224 + nRowEst = 0; 4146 4225 } 4147 4226 pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, 4148 4227 opMask, pProbe); 4149 4228 saved_nEq = pNew->u.btree.nEq; 4150 4229 saved_nLTerm = pNew->nLTerm; 4151 4230 saved_wsFlags = pNew->wsFlags; 4152 4231 saved_prereq = pNew->prereq; 4153 4232 saved_nOut = pNew->nOut; 4154 - pNew->rSetup = (WhereCost)0; 4155 - rLogSize = estLog(pProbe->aiRowEst[0]); 4233 + pNew->rSetup = 0; 4234 + rLogSize = estLog(whereCostFromInt(pProbe->aiRowEst[0])); 4156 4235 for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ 4157 - int nIn = 1; 4236 + int nIn = 0; 4158 4237 if( pTerm->prereqRight & pNew->maskSelf ) continue; 4159 4238 pNew->wsFlags = saved_wsFlags; 4160 4239 pNew->u.btree.nEq = saved_nEq; 4161 4240 pNew->nLTerm = saved_nLTerm; 4162 4241 if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ 4163 4242 pNew->aLTerm[pNew->nLTerm++] = pTerm; 4164 4243 pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf; 4165 4244 pNew->rRun = rLogSize; 4166 4245 if( pTerm->eOperator & WO_IN ){ 4167 4246 Expr *pExpr = pTerm->pExpr; 4168 4247 pNew->wsFlags |= WHERE_COLUMN_IN; 4169 4248 if( ExprHasProperty(pExpr, EP_xIsSelect) ){ 4170 4249 /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ 4171 - nIn = 25; 4250 + nIn = 46; /* whereCostFromInt(25) */ 4172 4251 }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ 4173 4252 /* "x IN (value, value, ...)" */ 4174 - nIn = pExpr->x.pList->nExpr; 4253 + nIn = whereCostFromInt(pExpr->x.pList->nExpr); 4175 4254 } 4176 - pNew->rRun *= nIn; 4255 + pNew->rRun += nIn; 4177 4256 pNew->u.btree.nEq++; 4178 - pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; 4257 + pNew->nOut = nRowEst + nInMul + nIn; 4179 4258 }else if( pTerm->eOperator & (WO_EQ) ){ 4180 4259 assert( (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN))!=0 4181 - || nInMul==1 ); 4260 + || nInMul==0 ); 4182 4261 pNew->wsFlags |= WHERE_COLUMN_EQ; 4183 4262 if( iCol<0 4184 - || (pProbe->onError!=OE_None && nInMul==1 4263 + || (pProbe->onError!=OE_None && nInMul==0 4185 4264 && pNew->u.btree.nEq==pProbe->nColumn-1) 4186 4265 ){ 4187 4266 testcase( pNew->wsFlags & WHERE_COLUMN_IN ); 4188 4267 pNew->wsFlags |= WHERE_ONEROW; 4189 4268 } 4190 4269 pNew->u.btree.nEq++; 4191 - pNew->nOut = (WhereCost)iRowEst * nInMul; 4270 + pNew->nOut = nRowEst + nInMul; 4192 4271 }else if( pTerm->eOperator & (WO_ISNULL) ){ 4193 4272 pNew->wsFlags |= WHERE_COLUMN_NULL; 4194 4273 pNew->u.btree.nEq++; 4195 - nIn = 2; /* Assume IS NULL matches two rows */ 4196 - pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; 4274 + nIn = 10; /* Assume IS NULL matches two rows */ 4275 + pNew->nOut = nRowEst + nInMul + nIn; 4197 4276 }else if( pTerm->eOperator & (WO_GT|WO_GE) ){ 4198 4277 pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; 4199 4278 pBtm = pTerm; 4200 4279 pTop = 0; 4201 4280 }else if( pTerm->eOperator & (WO_LT|WO_LE) ){ 4202 4281 pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT; 4203 4282 pTop = pTerm; ................................................................................ 4205 4284 pNew->aLTerm[pNew->nLTerm-2] : 0; 4206 4285 } 4207 4286 if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ 4208 4287 /* Adjust nOut and rRun for STAT3 range values */ 4209 4288 WhereCost rDiv; 4210 4289 whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq, 4211 4290 pBtm, pTop, &rDiv); 4212 - pNew->nOut = saved_nOut/rDiv; 4291 + pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10; 4213 4292 } 4214 4293 #ifdef SQLITE_ENABLE_STAT3 4215 4294 if( pNew->u.btree.nEq==1 && pProbe->nSample ){ 4295 + tRowcnt nOut = 0; 4216 4296 if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){ 4217 - rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, 4218 - &pNew->nOut); 4297 + rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &nOut); 4219 4298 }else if( (pTerm->eOperator & WO_IN) 4220 4299 && !ExprHasProperty(pTerm->pExpr, EP_xIsSelect) ){ 4221 - rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, 4222 - &pNew->nOut); 4223 - 4300 + rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut); 4224 4301 } 4302 + pNew->nOut = whereCostFromInt(nOut); 4225 4303 } 4226 4304 #endif 4227 4305 if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){ 4228 - pNew->rRun += pNew->nOut; /* Unit step cost to reach each row */ 4306 + /* Step cost for each output row */ 4307 + pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); 4229 4308 }else{ 4230 4309 /* Each row involves a step of the index, then a binary search of 4231 4310 ** the main table */ 4232 - pNew->rRun += pNew->nOut*(1 + rLogSize); 4311 + WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1); 4312 + pNew->rRun = whereCostAdd(pNew->rRun, rStepAndSearch); 4233 4313 } 4234 4314 /* TBD: Adjust nOut for additional constraints */ 4235 4315 rc = whereLoopInsert(pBuilder, pNew); 4236 4316 if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 4237 4317 && pNew->u.btree.nEq<=pProbe->nColumn 4238 4318 && pProbe->zName!=0 4239 4319 ){ 4240 - whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn); 4320 + whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); 4241 4321 } 4242 4322 } 4243 4323 pNew->prereq = saved_prereq; 4244 4324 pNew->u.btree.nEq = saved_nEq; 4245 4325 pNew->wsFlags = saved_wsFlags; 4246 4326 pNew->nOut = saved_nOut; 4247 4327 pNew->nLTerm = saved_nLTerm; ................................................................................ 4309 4389 int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ 4310 4390 SrcList *pTabList; /* The FROM clause */ 4311 4391 struct SrcList_item *pSrc; /* The FROM clause btree term to add */ 4312 4392 WhereLoop *pNew; /* Template WhereLoop object */ 4313 4393 int rc = SQLITE_OK; /* Return code */ 4314 4394 int iSortIdx = 1; /* Index number */ 4315 4395 int b; /* A boolean value */ 4316 - WhereCost rSize; /* number of rows in the table */ 4317 - WhereCost rLogSize; /* Logarithm of the number of rows in the table */ 4396 + WhereCost rSize; /* number of rows in the table */ 4397 + WhereCost rLogSize; /* Logarithm of the number of rows in the table */ 4318 4398 4319 4399 pNew = pBuilder->pNew; 4320 4400 pWInfo = pBuilder->pWInfo; 4321 4401 pTabList = pWInfo->pTabList; 4322 4402 pSrc = pTabList->a + pNew->iTab; 4323 4403 assert( !IsVirtual(pSrc->pTab) ); 4324 4404 ................................................................................ 4343 4423 if( pSrc->notIndexed==0 ){ 4344 4424 /* The real indices of the table are only considered if the 4345 4425 ** NOT INDEXED qualifier is omitted from the FROM clause */ 4346 4426 sPk.pNext = pFirst; 4347 4427 } 4348 4428 pProbe = &sPk; 4349 4429 } 4350 - rSize = (WhereCost)pSrc->pTab->nRowEst; 4430 + rSize = whereCostFromInt(pSrc->pTab->nRowEst); 4351 4431 rLogSize = estLog(rSize); 4352 4432 4353 4433 /* Automatic indexes */ 4354 4434 if( !pBuilder->pBest 4355 4435 && pTabList->nSrc>1 4356 4436 && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 4357 4437 && !pSrc->viaCoroutine ................................................................................ 4365 4445 for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){ 4366 4446 if( pTerm->prereqRight & pNew->maskSelf ) continue; 4367 4447 if( termCanDriveIndex(pTerm, pSrc, 0) ){ 4368 4448 pNew->u.btree.nEq = 1; 4369 4449 pNew->u.btree.pIndex = 0; 4370 4450 pNew->nLTerm = 1; 4371 4451 pNew->aLTerm[0] = pTerm; 4372 - pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst; 4373 - pNew->nOut = (WhereCost)10; 4374 - pNew->rRun = rLogSize + pNew->nOut; 4452 + assert( 43==whereCostFromInt(20) ); 4453 + pNew->rSetup = 43 + rLogSize + rSize; 4454 + pNew->nOut = 33; assert( 33==whereCostFromInt(10) ); 4455 + pNew->rRun = whereCostAdd(rLogSize,pNew->nOut); 4375 4456 pNew->wsFlags = WHERE_TEMP_INDEX; 4376 4457 pNew->prereq = mExtra | pTerm->prereqRight; 4377 4458 rc = whereLoopInsert(pBuilder, pNew); 4378 4459 } 4379 4460 } 4380 4461 } 4381 4462 4382 4463 /* Loop over all indices 4383 4464 */ 4384 4465 for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ 4385 4466 pNew->u.btree.nEq = 0; 4386 4467 pNew->nLTerm = 0; 4387 4468 pNew->iSortIdx = 0; 4388 - pNew->rSetup = (WhereCost)0; 4469 + pNew->rSetup = 0; 4389 4470 pNew->prereq = mExtra; 4390 4471 pNew->u.btree.pIndex = pProbe; 4391 4472 b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); 4392 4473 if( pProbe->tnum<=0 ){ 4393 4474 /* Integer primary key index */ 4394 4475 pNew->wsFlags = WHERE_IPK; 4395 4476 4396 4477 /* Full table scan */ 4397 4478 pNew->iSortIdx = b ? iSortIdx : 0; 4398 4479 pNew->nOut = rSize; 4399 - pNew->rRun = (rSize + rLogSize)*(3+b); /* 4x penalty for a full-scan */ 4480 + pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 + b*4; 4400 4481 rc = whereLoopInsert(pBuilder, pNew); 4401 4482 if( rc ) break; 4402 4483 }else{ 4403 4484 Bitmask m = pSrc->colUsed & ~columnsUsedByIndex(pProbe); 4404 4485 pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; 4405 4486 4406 4487 /* Full scan via index */ ................................................................................ 4408 4489 && pProbe->bUnordered==0 4409 4490 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 4410 4491 && sqlite3GlobalConfig.bUseCis 4411 4492 && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) 4412 4493 ){ 4413 4494 pNew->iSortIdx = b ? iSortIdx : 0; 4414 4495 pNew->nOut = rSize; 4415 - pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize); 4496 + pNew->rRun = whereCostAdd(rSize,rLogSize) + ((m==0 && b) ? 10 : 0); 4416 4497 rc = whereLoopInsert(pBuilder, pNew); 4417 4498 if( rc ) break; 4418 4499 } 4419 4500 } 4420 - rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 1); 4501 + rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); 4421 4502 4422 4503 /* If there was an INDEXED BY clause, then only that one index is 4423 4504 ** considered. */ 4424 4505 if( pSrc->pIndex ) break; 4425 4506 } 4426 4507 return rc; 4427 4508 } 4428 4509 4510 +#ifndef SQLITE_OMIT_VIRTUALTABLE 4429 4511 /* 4430 4512 ** Add all WhereLoop objects for a table of the join identified by 4431 4513 ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. 4432 4514 */ 4433 4515 static int whereLoopAddVirtual( 4434 4516 WhereLoopBuilder *pBuilder, /* WHERE clause information */ 4435 4517 Bitmask mExtra /* Extra prerequesites for using this table */ ................................................................................ 4509 4591 } 4510 4592 memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); 4511 4593 if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); 4512 4594 pIdxInfo->idxStr = 0; 4513 4595 pIdxInfo->idxNum = 0; 4514 4596 pIdxInfo->needToFreeIdxStr = 0; 4515 4597 pIdxInfo->orderByConsumed = 0; 4516 - /* ((WhereCost)2) In case of SQLITE_OMIT_FLOATING_POINT... */ 4517 - pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((WhereCost)2); 4598 + pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; 4518 4599 rc = vtabBestIndex(pParse, pTab, pIdxInfo); 4519 4600 if( rc ) goto whereLoopAddVtab_exit; 4520 4601 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; 4521 4602 pNew->prereq = 0; 4522 4603 mxTerm = -1; 4523 4604 assert( pNew->nLSlot>=nConstraint ); 4524 4605 for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0; ................................................................................ 4563 4644 assert( pNew->nLTerm<=pNew->nLSlot ); 4564 4645 pNew->u.vtab.idxNum = pIdxInfo->idxNum; 4565 4646 pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; 4566 4647 pIdxInfo->needToFreeIdxStr = 0; 4567 4648 pNew->u.vtab.idxStr = pIdxInfo->idxStr; 4568 4649 pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0) 4569 4650 && pIdxInfo->orderByConsumed); 4570 - pNew->rSetup = (WhereCost)0; 4571 - pNew->rRun = pIdxInfo->estimatedCost; 4572 - pNew->nOut = (WhereCost)25; 4651 + pNew->rSetup = 0; 4652 + pNew->rRun = whereCostFromDouble(pIdxInfo->estimatedCost); 4653 + pNew->nOut = 46; assert( 46 == whereCostFromInt(25) ); 4573 4654 whereLoopInsert(pBuilder, pNew); 4574 4655 if( pNew->u.vtab.needFree ){ 4575 4656 sqlite3_free(pNew->u.vtab.idxStr); 4576 4657 pNew->u.vtab.needFree = 0; 4577 4658 } 4578 4659 } 4579 4660 } 4580 4661 4581 4662 whereLoopAddVtab_exit: 4582 4663 if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); 4583 4664 sqlite3DbFree(db, pIdxInfo); 4584 4665 return rc; 4585 4666 } 4667 +#endif /* SQLITE_OMIT_VIRTUALTABLE */ 4586 4668 4587 4669 /* 4588 4670 ** Add WhereLoop entries to handle OR terms. This works for either 4589 4671 ** btrees or virtual tables. 4590 4672 */ 4591 4673 static int whereLoopAddOr(WhereLoopBuilder *pBuilder, Bitmask mExtra){ 4592 4674 WhereInfo *pWInfo = pBuilder->pWInfo; ................................................................................ 4635 4717 sSubBuild.pWC = &tempWC; 4636 4718 }else{ 4637 4719 continue; 4638 4720 } 4639 4721 sBest.maskSelf = 0; 4640 4722 sBest.rSetup = 0; 4641 4723 sBest.rRun = 0; 4724 +#ifndef SQLITE_OMIT_VIRTUALTABLE 4642 4725 if( IsVirtual(pItem->pTab) ){ 4643 4726 rc = whereLoopAddVirtual(&sSubBuild, mExtra); 4644 - }else{ 4727 + }else 4728 +#endif 4729 + { 4645 4730 rc = whereLoopAddBtree(&sSubBuild, mExtra); 4646 4731 } 4647 4732 if( sBest.maskSelf==0 ) break; 4648 - assert( sBest.rSetup==(WhereCost)0 ); 4649 - rTotal += sBest.rRun; 4650 - nRow += sBest.nOut; 4733 + assert( sBest.rSetup==0 ); 4734 + rTotal = whereCostAdd(rTotal, sBest.rRun); 4735 + nRow = whereCostAdd(nRow, sBest.nOut); 4651 4736 prereq |= sBest.prereq; 4652 4737 } 4653 4738 assert( pNew->nLSlot>=1 ); 4654 4739 pNew->nLTerm = 1; 4655 4740 pNew->aLTerm[0] = pTerm; 4656 4741 pNew->wsFlags = WHERE_MULTI_OR; 4657 - pNew->rSetup = (WhereCost)0; 4742 + pNew->rSetup = 0; 4658 4743 pNew->rRun = rTotal; 4659 4744 pNew->nOut = nRow; 4660 4745 pNew->prereq = prereq; 4661 4746 memset(&pNew->u, 0, sizeof(pNew->u)); 4662 4747 rc = whereLoopInsert(pBuilder, pNew); 4663 4748 whereLoopClear(pWInfo->pParse->db, &sBest); 4664 4749 } ................................................................................ 4675 4760 Bitmask mPrior = 0; 4676 4761 int iTab; 4677 4762 SrcList *pTabList = pWInfo->pTabList; 4678 4763 struct SrcList_item *pItem; 4679 4764 sqlite3 *db = pWInfo->pParse->db; 4680 4765 int nTabList = pWInfo->nLevel; 4681 4766 int rc = SQLITE_OK; 4682 - WhereLoop *pNew, sNew; 4767 + u8 priorJoinType = 0; 4768 + WhereLoop *pNew; 4683 4769 4684 4770 /* Loop over the tables in the join, from left to right */ 4685 - pBuilder->pNew = pNew = &sNew; 4771 + pNew = pBuilder->pNew; 4686 4772 whereLoopInit(pNew); 4687 4773 for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){ 4688 4774 pNew->iTab = iTab; 4689 4775 pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor); 4690 - if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){ 4776 + if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){ 4691 4777 mExtra = mPrior; 4692 4778 } 4779 + priorJoinType = pItem->jointype; 4693 4780 if( IsVirtual(pItem->pTab) ){ 4694 4781 rc = whereLoopAddVirtual(pBuilder, mExtra); 4695 4782 }else{ 4696 4783 rc = whereLoopAddBtree(pBuilder, mExtra); 4697 4784 } 4698 4785 if( rc==SQLITE_OK ){ 4699 4786 rc = whereLoopAddOr(pBuilder, mExtra); 4700 4787 } 4701 4788 mPrior |= pNew->maskSelf; 4702 4789 if( rc || db->mallocFailed ) break; 4703 4790 } 4704 4791 whereLoopClear(db, pNew); 4705 - pBuilder->pNew = 0; 4706 4792 return rc; 4707 4793 } 4708 4794 4709 4795 /* 4710 4796 ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th 4711 4797 ** parameters) to see if it outputs rows in the requested ORDER BY 4712 4798 ** (or GROUP BY) without requiring a separate source operation. Return: ................................................................................ 4984 5070 memset(aFrom, 0, sizeof(aFrom[0])); 4985 5071 pX = (WhereLoop**)(aFrom+mxChoice); 4986 5072 for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){ 4987 5073 pFrom->aLoop = pX; 4988 5074 } 4989 5075 4990 5076 /* Seed the search with a single WherePath containing zero WhereLoops */ 4991 - aFrom[0].nRow = (WhereCost)1; 5077 + aFrom[0].nRow = 0; 4992 5078 nFrom = 1; 4993 5079 4994 5080 /* Precompute the cost of sorting the final result set, if the caller 4995 5081 ** to sqlite3WhereBegin() was concerned about sorting */ 4996 - rSortCost = (WhereCost)0; 4997 - if( pWInfo->pOrderBy==0 || nRowEst<=0.0 ){ 5082 + rSortCost = 0; 5083 + if( pWInfo->pOrderBy==0 || nRowEst==0 ){ 4998 5084 aFrom[0].isOrderedValid = 1; 4999 5085 }else{ 5000 5086 /* Compute an estimate on the cost to sort the entire result set */ 5001 - rSortCost = nRowEst*estLog(nRowEst); 5087 + rSortCost = nRowEst + estLog(nRowEst); 5002 5088 #ifdef WHERETRACE_ENABLED 5003 5089 if( sqlite3WhereTrace>=2 ){ 5004 - sqlite3DebugPrintf("---- sort cost=%-7.2g\n", rSortCost); 5090 + sqlite3DebugPrintf("---- sort cost=%-3d\n", rSortCost); 5005 5091 } 5006 5092 #endif 5007 5093 } 5008 5094 5009 5095 /* Compute successively longer WherePaths using the previous generation 5010 5096 ** of WherePaths as the basis for the next. Keep track of the mxChoice 5011 5097 ** best paths at each generation */ ................................................................................ 5017 5103 Bitmask revMask = 0; 5018 5104 u8 isOrderedValid = pFrom->isOrderedValid; 5019 5105 u8 isOrdered = pFrom->isOrdered; 5020 5106 if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue; 5021 5107 if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; 5022 5108 /* At this point, pWLoop is a candidate to be the next loop. 5023 5109 ** Compute its cost */ 5024 - rCost = pWLoop->rSetup + pWLoop->rRun*pFrom->nRow + pFrom->rCost; 5110 + rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); 5111 + rCost = whereCostAdd(rCost, pFrom->rCost); 5025 5112 maskNew = pFrom->maskLoop | pWLoop->maskSelf; 5026 5113 if( !isOrderedValid ){ 5027 5114 switch( wherePathSatisfiesOrderBy(pWInfo, pFrom, iLoop, iLoop==nLoop-1, 5028 5115 pWLoop, &revMask) ){ 5029 5116 case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */ 5030 5117 isOrdered = 1; 5031 5118 isOrderedValid = 1; 5032 5119 break; 5033 5120 case 0: /* No. pFrom+pWLoop will require a separate sort */ 5034 5121 isOrdered = 0; 5035 5122 isOrderedValid = 1; 5036 - rCost += rSortCost; 5123 + rCost = whereCostAdd(rCost, rSortCost); 5037 5124 break; 5038 5125 default: /* Cannot tell yet. Try again on the next iteration */ 5039 5126 break; 5040 5127 } 5041 5128 }else{ 5042 5129 revMask = pFrom->revLoop; 5043 5130 } ................................................................................ 5047 5134 break; 5048 5135 } 5049 5136 } 5050 5137 if( jj>=nTo ){ 5051 5138 if( nTo>=mxChoice && rCost>=mxCost ){ 5052 5139 #ifdef WHERETRACE_ENABLED 5053 5140 if( sqlite3WhereTrace&0x4 ){ 5054 - sqlite3DebugPrintf("Skip %s cost=%-7.2g order=%c\n", 5141 + sqlite3DebugPrintf("Skip %s cost=%3d order=%c\n", 5055 5142 wherePathName(pFrom, iLoop, pWLoop), rCost, 5056 5143 isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); 5057 5144 } 5058 5145 #endif 5059 5146 continue; 5060 5147 } 5061 5148 /* Add a new Path to the aTo[] set */ ................................................................................ 5065 5152 }else{ 5066 5153 /* New path replaces the prior worst to keep count below mxChoice */ 5067 5154 for(jj=nTo-1; aTo[jj].rCost<mxCost; jj--){ assert(jj>0); } 5068 5155 } 5069 5156 pTo = &aTo[jj]; 5070 5157 #ifdef WHERETRACE_ENABLED 5071 5158 if( sqlite3WhereTrace&0x4 ){ 5072 - sqlite3DebugPrintf("New %s cost=%-7.2g order=%c\n", 5159 + sqlite3DebugPrintf("New %s cost=%-3d order=%c\n", 5073 5160 wherePathName(pFrom, iLoop, pWLoop), rCost, 5074 5161 isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); 5075 5162 } 5076 5163 #endif 5077 5164 }else{ 5078 5165 if( pTo->rCost<=rCost ){ 5079 5166 #ifdef WHERETRACE_ENABLED 5080 5167 if( sqlite3WhereTrace&0x4 ){ 5081 5168 sqlite3DebugPrintf( 5082 - "Skip %s cost=%-7.2g order=%c", 5169 + "Skip %s cost=%-3d order=%c", 5083 5170 wherePathName(pFrom, iLoop, pWLoop), rCost, 5084 5171 isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); 5085 - sqlite3DebugPrintf(" vs %s cost=%-7.2g order=%c\n", 5172 + sqlite3DebugPrintf(" vs %s cost=%-3d order=%c\n", 5086 5173 wherePathName(pTo, iLoop+1, 0), pTo->rCost, 5087 5174 pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); 5088 5175 } 5089 5176 #endif 5090 5177 continue; 5091 5178 } 5092 5179 /* A new and better score for a previously created equivalent path */ 5093 5180 #ifdef WHERETRACE_ENABLED 5094 5181 if( sqlite3WhereTrace&0x4 ){ 5095 5182 sqlite3DebugPrintf( 5096 - "Update %s cost=%-7.2g order=%c", 5183 + "Update %s cost=%-3d order=%c", 5097 5184 wherePathName(pFrom, iLoop, pWLoop), rCost, 5098 5185 isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); 5099 - sqlite3DebugPrintf(" was %s cost=%-7.2g order=%c\n", 5186 + sqlite3DebugPrintf(" was %s cost=%-3d order=%c\n", 5100 5187 wherePathName(pTo, iLoop+1, 0), pTo->rCost, 5101 5188 pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); 5102 5189 } 5103 5190 #endif 5104 5191 } 5105 5192 /* pWLoop is a winner. Add it to the set of best so far */ 5106 5193 pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf; 5107 5194 pTo->revLoop = revMask; 5108 - pTo->nRow = pFrom->nRow * pWLoop->nOut; 5195 + pTo->nRow = pFrom->nRow + pWLoop->nOut; 5109 5196 pTo->rCost = rCost; 5110 5197 pTo->isOrderedValid = isOrderedValid; 5111 5198 pTo->isOrdered = isOrdered; 5112 5199 memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop); 5113 5200 pTo->aLoop[iLoop] = pWLoop; 5114 5201 if( nTo>=mxChoice ){ 5115 5202 mxCost = aTo[0].rCost; ................................................................................ 5120 5207 } 5121 5208 } 5122 5209 5123 5210 #ifdef WHERETRACE_ENABLED 5124 5211 if( sqlite3WhereTrace>=2 ){ 5125 5212 sqlite3DebugPrintf("---- after round %d ----\n", iLoop); 5126 5213 for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ 5127 - sqlite3DebugPrintf(" %s cost=%-7.2g nrow=%-7.2g order=%c", 5214 + sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c", 5128 5215 wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, 5129 5216 pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); 5130 5217 if( pTo->isOrderedValid && pTo->isOrdered ){ 5131 5218 sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); 5132 5219 }else{ 5133 5220 sqlite3DebugPrintf("\n"); 5134 5221 } ................................................................................ 5180 5267 ** general-purpose query planner, and thereby yield faster sqlite3_prepare() 5181 5268 ** times for the common case. 5182 5269 ** 5183 5270 ** Return non-zero on success, if this query can be handled by this 5184 5271 ** no-frills query planner. Return zero if this query needs the 5185 5272 ** general-purpose query planner. 5186 5273 */ 5187 -static int whereSimpleFastCase(WhereLoopBuilder *pBuilder){ 5274 +static int whereShortCut(WhereLoopBuilder *pBuilder){ 5188 5275 WhereInfo *pWInfo; 5189 5276 struct SrcList_item *pItem; 5190 5277 WhereClause *pWC; 5191 5278 WhereTerm *pTerm; 5192 5279 WhereLoop *pLoop; 5193 5280 int iCur; 5194 5281 int j; 5195 5282 Table *pTab; 5196 5283 Index *pIdx; 5197 5284 5198 5285 pWInfo = pBuilder->pWInfo; 5286 + if( pWInfo->wctrlFlags & WHERE_FORCE_TABLE ) return 0; 5199 5287 assert( pWInfo->pTabList->nSrc>=1 ); 5200 5288 pItem = pWInfo->pTabList->a; 5201 5289 pTab = pItem->pTab; 5202 5290 if( IsVirtual(pTab) ) return 0; 5203 5291 if( pItem->zIndex ) return 0; 5204 5292 iCur = pItem->iCursor; 5205 5293 pWC = &pWInfo->sWC; ................................................................................ 5207 5295 pLoop->wsFlags = 0; 5208 5296 pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0); 5209 5297 if( pTerm ){ 5210 5298 pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW; 5211 5299 pLoop->aLTerm[0] = pTerm; 5212 5300 pLoop->nLTerm = 1; 5213 5301 pLoop->u.btree.nEq = 1; 5214 - pLoop->rRun = (WhereCost)10; 5302 + pLoop->rRun = 33; /* 33 == whereCostFromInt(10) */ 5215 5303 }else{ 5216 5304 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 5217 5305 if( pIdx->onError==OE_None ) continue; 5218 5306 for(j=0; j<pIdx->nColumn; j++){ 5219 5307 pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); 5220 5308 if( pTerm==0 ) break; 5221 5309 whereLoopResize(pWInfo->pParse->db, pLoop, j); ................................................................................ 5225 5313 pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED; 5226 5314 if( (pItem->colUsed & ~columnsUsedByIndex(pIdx))==0 ){ 5227 5315 pLoop->wsFlags |= WHERE_IDX_ONLY; 5228 5316 } 5229 5317 pLoop->nLTerm = j; 5230 5318 pLoop->u.btree.nEq = j; 5231 5319 pLoop->u.btree.pIndex = pIdx; 5232 - pLoop->rRun = (WhereCost)15; 5320 + pLoop->rRun = 39; /* 39 == whereCostFromInt(15) */ 5233 5321 break; 5234 5322 } 5235 5323 } 5236 5324 if( pLoop->wsFlags ){ 5237 5325 pLoop->nOut = (WhereCost)1; 5238 5326 pWInfo->a[0].pWLoop = pLoop; 5239 5327 pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur); ................................................................................ 5402 5490 pWInfo->wctrlFlags = wctrlFlags; 5403 5491 pWInfo->savedNQueryLoop = pParse->nQueryLoop; 5404 5492 pMaskSet = &pWInfo->sMaskSet; 5405 5493 sWLB.pWInfo = pWInfo; 5406 5494 sWLB.pWC = &pWInfo->sWC; 5407 5495 sWLB.pNew = (WhereLoop*)&pWInfo->a[nTabList]; 5408 5496 whereLoopInit(sWLB.pNew); 5497 +#ifdef SQLITE_DEBUG 5498 + sWLB.pNew->cId = '*'; 5499 +#endif 5409 5500 5410 5501 /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via 5411 5502 ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ 5412 5503 if( OptimizationDisabled(db, SQLITE_DistinctOpt) ) pDistinct = 0; 5413 5504 5414 5505 /* Split the WHERE clause into separate subexpressions where each 5415 5506 ** subexpression is separated by an AND operator. ................................................................................ 5474 5565 if( pDistinct && isDistinctRedundant(pParse,pTabList,&pWInfo->sWC,pDistinct) ){ 5475 5566 pDistinct = 0; 5476 5567 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; 5477 5568 } 5478 5569 5479 5570 /* Construct the WhereLoop objects */ 5480 5571 WHERETRACE(("*** Optimizer Start ***\n")); 5481 - if( nTabList!=1 || whereSimpleFastCase(&sWLB)==0 ){ 5572 + if( nTabList!=1 || whereShortCut(&sWLB)==0 ){ 5482 5573 rc = whereLoopAddAll(&sWLB); 5483 5574 if( rc ) goto whereBeginError; 5484 5575 5485 5576 /* Display all of the WhereLoop objects if wheretrace is enabled */ 5486 5577 #ifdef WHERETRACE_ENABLED 5487 5578 if( sqlite3WhereTrace ){ 5488 5579 WhereLoop *p;
Changes to test/all.test.
44 44 if {$::tcl_platform(platform)=="unix"} { 45 45 ifcapable !default_autovacuum { 46 46 run_test_suite autovacuum_crash 47 47 } 48 48 } 49 49 50 50 finish_test 51 - 52 -
Changes to test/analyze3.test.
93 93 COMMIT; 94 94 ANALYZE; 95 95 } 96 96 } {} 97 97 98 98 do_eqp_test analyze3-1.1.2 { 99 99 SELECT sum(y) FROM t1 WHERE x>200 AND x<300 100 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}} 100 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}} 101 101 do_eqp_test analyze3-1.1.3 { 102 102 SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 103 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}} 103 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}} 104 104 105 105 do_test analyze3-1.1.4 { 106 106 sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 } 107 107 } {199 0 14850} 108 108 do_test analyze3-1.1.5 { 109 109 set l [string range "200" 0 end] 110 110 set u [string range "300" 0 end] ................................................................................ 142 142 CREATE INDEX i2 ON t2(x); 143 143 COMMIT; 144 144 ANALYZE; 145 145 } 146 146 } {} 147 147 do_eqp_test analyze3-1.2.2 { 148 148 SELECT sum(y) FROM t2 WHERE x>1 AND x<2 149 -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}} 149 +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}} 150 150 do_eqp_test analyze3-1.2.3 { 151 151 SELECT sum(y) FROM t2 WHERE x>0 AND x<99 152 -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~968 rows)}} 152 +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}} 153 153 do_test analyze3-1.2.4 { 154 154 sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 } 155 155 } {161 0 4760} 156 156 do_test analyze3-1.2.5 { 157 157 set l [string range "12" 0 end] 158 158 set u [string range "20" 0 end] 159 159 sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u} ................................................................................ 189 189 CREATE INDEX i3 ON t3(x); 190 190 COMMIT; 191 191 ANALYZE; 192 192 } 193 193 } {} 194 194 do_eqp_test analyze3-1.3.2 { 195 195 SELECT sum(y) FROM t3 WHERE x>200 AND x<300 196 -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}} 196 +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}} 197 197 do_eqp_test analyze3-1.3.3 { 198 198 SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 199 -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}} 199 +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}} 200 200 201 201 do_test analyze3-1.3.4 { 202 202 sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 } 203 203 } {199 0 14850} 204 204 do_test analyze3-1.3.5 { 205 205 set l [string range "200" 0 end] 206 206 set u [string range "300" 0 end] ................................................................................ 244 244 append t [lindex {a b c d e f g h i j} [expr ($i%10)]] 245 245 execsql { INSERT INTO t1 VALUES($i, $t) } 246 246 } 247 247 execsql COMMIT 248 248 } {} 249 249 do_eqp_test analyze3-2.2 { 250 250 SELECT count(a) FROM t1 WHERE b LIKE 'a%' 251 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~31250 rows)}} 251 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}} 252 252 do_eqp_test analyze3-2.3 { 253 253 SELECT count(a) FROM t1 WHERE b LIKE '%a' 254 -} {0 0 0 {SCAN TABLE t1 (~500000 rows)}} 254 +} {0 0 0 {SCAN TABLE t1}} 255 255 256 256 do_test analyze3-2.4 { 257 257 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' } 258 258 } {101 0 100} 259 259 do_test analyze3-2.5 { 260 260 sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' } 261 261 } {999 999 100} ................................................................................ 326 326 do_test analyze3-3.2.5 { 327 327 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy] 328 328 sqlite3_expired $S 329 329 } {0} 330 330 do_test analyze3-3.2.6 { 331 331 sqlite3_bind_text $S 1 "abc" 3 332 332 sqlite3_expired $S 333 -} {0} 333 +} {1} 334 334 do_test analyze3-3.2.7 { 335 335 sqlite3_finalize $S 336 336 } {SQLITE_OK} 337 337 338 338 do_test analyze3-3.4.1 { 339 339 set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy] 340 340 sqlite3_expired $S
Changes to test/analyze4.test.
34 34 INSERT INTO t1 SELECT a+32, b FROM t1; 35 35 INSERT INTO t1 SELECT a+64, b FROM t1; 36 36 ANALYZE; 37 37 } 38 38 39 39 # Should choose the t1a index since it is more specific than t1b. 40 40 db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL} 41 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 41 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 42 42 43 43 # Verify that the t1b index shows that it does not narrow down the 44 44 # search any at all. 45 45 # 46 46 do_test analyze4-1.1 { 47 47 db eval { 48 48 SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
Changes to test/analyze5.test.
152 152 301 {y=1} t1y 26 153 153 302 {y=0.1} t1y 1 154 154 155 155 400 {x IS NULL} t1x 400 156 156 157 157 } { 158 158 # Verify that the expected index is used with the expected row count 159 - do_test analyze5-1.${testid}a { 160 - set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] 161 - set idx {} 162 - regexp {INDEX (t1.) } $x all idx 163 - regexp {~([0-9]+) rows} $x all nrow 164 - list $idx $nrow 165 - } [list $index $rows] 159 + # No longer valid due to an EXPLAIN QUERY PLAN output format change 160 + # do_test analyze5-1.${testid}a { 161 + # set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] 162 + # set idx {} 163 + # regexp {INDEX (t1.) } $x all idx 164 + # regexp {~([0-9]+) rows} $x all nrow 165 + # list $idx $nrow 166 + # } [list $index $rows] 166 167 167 168 # Verify that the same result is achieved regardless of whether or not 168 169 # the index is used 169 170 do_test analyze5-1.${testid}b { 170 171 set w2 [string map {y +y z +z} $where] 171 172 set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\ 172 173 ORDER BY +rowid"] ................................................................................ 198 199 503 {x=1} t1x 1 199 200 504 {x IS NOT NULL} t1x 2 200 201 505 {+x IS NOT NULL} {} 500 201 202 506 {upper(x) IS NOT NULL} {} 500 202 203 203 204 } { 204 205 # Verify that the expected index is used with the expected row count 205 -if {$testid==50299} {breakpoint; set sqlite_where_trace 1} 206 - do_test analyze5-1.${testid}a { 207 - set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] 208 - set idx {} 209 - regexp {INDEX (t1.) } $x all idx 210 - regexp {~([0-9]+) rows} $x all nrow 211 - list $idx $nrow 212 - } [list $index $rows] 213 -if {$testid==50299} exit 206 + # No longer valid due to an EXPLAIN QUERY PLAN format change 207 + # do_test analyze5-1.${testid}a { 208 + # set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] 209 + # set idx {} 210 + # regexp {INDEX (t1.) } $x all idx 211 + # regexp {~([0-9]+) rows} $x all nrow 212 + # list $idx $nrow 213 + # } [list $index $rows] 214 214 215 215 # Verify that the same result is achieved regardless of whether or not 216 216 # the index is used 217 217 do_test analyze5-1.${testid}b { 218 218 set w2 [string map {y +y z +z} $where] 219 219 set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\ 220 220 ORDER BY +rowid"]
Changes to test/analyze6.test.
57 57 # The lowest cost plan is to scan CAT and for each integer there, do a single 58 58 # lookup of the first corresponding entry in EV then read off the equal values 59 59 # in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would 60 60 # have used EV for the outer loop instead of CAT - which was about 3x slower.) 61 61 # 62 62 do_test analyze6-1.1 { 63 63 eqp {SELECT count(*) FROM ev, cat WHERE x=y} 64 -} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} 64 +} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}} 65 65 66 66 # The same plan is chosen regardless of the order of the tables in the 67 67 # FROM clause. 68 68 # 69 69 do_test analyze6-1.2 { 70 70 eqp {SELECT count(*) FROM cat, ev WHERE x=y} 71 -} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} 71 +} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}} 72 72 73 73 74 74 # Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30 75 75 # If ANALYZE is run on an empty table, make sure indices are used 76 76 # on the table. 77 77 # 78 78 do_test analyze6-2.1 { 79 79 execsql { 80 80 CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z); 81 81 CREATE INDEX t201z ON t201(z); 82 82 ANALYZE; 83 83 } 84 84 eqp {SELECT * FROM t201 WHERE z=5} 85 -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} 85 +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}} 86 86 do_test analyze6-2.2 { 87 87 eqp {SELECT * FROM t201 WHERE y=5} 88 -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} 88 +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}} 89 89 do_test analyze6-2.3 { 90 90 eqp {SELECT * FROM t201 WHERE x=5} 91 -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} 91 +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}} 92 92 do_test analyze6-2.4 { 93 93 execsql { 94 94 INSERT INTO t201 VALUES(1,2,3); 95 95 ANALYZE t201; 96 96 } 97 97 eqp {SELECT * FROM t201 WHERE z=5} 98 -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} 98 +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}} 99 99 do_test analyze6-2.5 { 100 100 eqp {SELECT * FROM t201 WHERE y=5} 101 -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} 101 +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}} 102 102 do_test analyze6-2.6 { 103 103 eqp {SELECT * FROM t201 WHERE x=5} 104 -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} 104 +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}} 105 105 do_test analyze6-2.7 { 106 106 execsql { 107 107 INSERT INTO t201 VALUES(4,5,7); 108 108 INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201; 109 109 INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201; 110 110 INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201; 111 111 ANALYZE t201; 112 112 } 113 113 eqp {SELECT * FROM t201 WHERE z=5} 114 -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}} 114 +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}} 115 115 do_test analyze6-2.8 { 116 116 eqp {SELECT * FROM t201 WHERE y=5} 117 -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}} 117 +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}} 118 118 do_test analyze6-2.9 { 119 119 eqp {SELECT * FROM t201 WHERE x=5} 120 -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} 120 +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}} 121 121 122 122 finish_test
Changes to test/analyze7.test.
33 33 CREATE INDEX t1b ON t1(b); 34 34 CREATE INDEX t1cd ON t1(c,d); 35 35 CREATE VIRTUAL TABLE nums USING wholenumber; 36 36 INSERT INTO t1 SELECT value, value, value/100, value FROM nums 37 37 WHERE value BETWEEN 1 AND 256; 38 38 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123; 39 39 } 40 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~10 rows)}} 40 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 41 41 do_test analyze7-1.1 { 42 42 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 43 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}} 43 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 44 44 do_test analyze7-1.2 { 45 45 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 46 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}} 46 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} 47 47 48 48 # Run an analyze on one of the three indices. Verify that this 49 49 # effects the row-count estimate on the one query that uses that 50 50 # one index. 51 51 # 52 52 do_test analyze7-2.0 { 53 53 execsql {ANALYZE t1a;} 54 54 db cache flush 55 55 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;} 56 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 56 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 57 57 do_test analyze7-2.1 { 58 58 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 59 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}} 59 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 60 60 do_test analyze7-2.2 { 61 61 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 62 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}} 62 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} 63 63 64 64 # Verify that since the query planner now things that t1a is more 65 65 # selective than t1b, it prefers to use t1a. 66 66 # 67 67 do_test analyze7-2.3 { 68 68 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} 69 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 69 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 70 70 71 71 # Run an analysis on another of the three indices. Verify that this 72 72 # new analysis works and does not disrupt the previous analysis. 73 73 # 74 74 do_test analyze7-3.0 { 75 75 execsql {ANALYZE t1cd;} 76 76 db cache flush; 77 77 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;} 78 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 78 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 79 79 do_test analyze7-3.1 { 80 80 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 81 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}} 81 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 82 82 do_test analyze7-3.2.1 { 83 83 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} 84 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}} 84 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} 85 85 ifcapable stat3 { 86 86 # If ENABLE_STAT3 is defined, SQLite comes up with a different estimated 87 87 # row count for (c=2) than it does for (c=?). 88 88 do_test analyze7-3.2.2 { 89 89 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 90 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~57 rows)}} 90 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} 91 91 } else { 92 92 # If ENABLE_STAT3 is not defined, the expected row count for (c=2) is the 93 93 # same as that for (c=?). 94 94 do_test analyze7-3.2.3 { 95 95 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 96 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}} 96 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} 97 97 } 98 98 do_test analyze7-3.3 { 99 99 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} 100 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 100 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 101 101 ifcapable {!stat3} { 102 102 do_test analyze7-3.4 { 103 103 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123} 104 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}} 104 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 105 105 do_test analyze7-3.5 { 106 106 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123} 107 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 107 + } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 108 108 } 109 109 do_test analyze7-3.6 { 110 110 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123} 111 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}} 111 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}} 112 112 113 113 finish_test
Changes to test/analyze8.test.
57 57 # with a==100. And so for those cases, choose the t1b index. 58 58 # 59 59 # Buf ro a==99 and a==101, there are far fewer rows so choose 60 60 # the t1a index. 61 61 # 62 62 do_test 1.1 { 63 63 eqp {SELECT * FROM t1 WHERE a=100 AND b=55} 64 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}} 64 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 65 65 do_test 1.2 { 66 66 eqp {SELECT * FROM t1 WHERE a=99 AND b=55} 67 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 67 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 68 68 do_test 1.3 { 69 69 eqp {SELECT * FROM t1 WHERE a=101 AND b=55} 70 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 70 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 71 71 do_test 1.4 { 72 72 eqp {SELECT * FROM t1 WHERE a=100 AND b=56} 73 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}} 73 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} 74 74 do_test 1.5 { 75 75 eqp {SELECT * FROM t1 WHERE a=99 AND b=56} 76 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 76 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 77 77 do_test 1.6 { 78 78 eqp {SELECT * FROM t1 WHERE a=101 AND b=56} 79 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}} 79 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 80 80 do_test 2.1 { 81 81 eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54} 82 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~2 rows)}} 82 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} 83 83 84 84 # There are many more values of c between 0 and 100000 than there are 85 85 # between 800000 and 900000. So t1c is more selective for the latter 86 86 # range. 87 87 # 88 88 do_test 3.1 { 89 89 eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000} 90 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~6 rows)}} 90 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} 91 91 do_test 3.2 { 92 92 eqp {SELECT * FROM t1 93 93 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000} 94 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~4 rows)}} 94 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} 95 95 do_test 3.3 { 96 96 eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000} 97 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~63 rows)}} 97 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} 98 98 do_test 3.4 { 99 99 eqp {SELECT * FROM t1 100 100 WHERE a=100 AND c BETWEEN 800000 AND 900000} 101 -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~2 rows)}} 101 +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} 102 102 103 103 finish_test
Changes to test/async5.test.
62 62 sqlite3async_control halt idle 63 63 sqlite3async_start 64 64 sqlite3async_wait 65 65 sqlite3async_control halt never 66 66 sqlite3async_shutdown 67 67 set sqlite3async_trace 0 68 68 finish_test 69 -
Changes to test/autoindex1.test.
143 143 do_execsql_test autoindex1-500 { 144 144 CREATE TABLE t501(a INTEGER PRIMARY KEY, b); 145 145 CREATE TABLE t502(x INTEGER PRIMARY KEY, y); 146 146 EXPLAIN QUERY PLAN 147 147 SELECT b FROM t501 148 148 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); 149 149 } { 150 - 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 150 + 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 151 151 0 0 0 {EXECUTE LIST SUBQUERY 1} 152 - 1 0 0 {SCAN TABLE t502 (~100000 rows)} 152 + 1 0 0 {SCAN TABLE t502} 153 153 } 154 154 do_execsql_test autoindex1-501 { 155 155 EXPLAIN QUERY PLAN 156 156 SELECT b FROM t501 157 157 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 158 158 } { 159 - 0 0 0 {SCAN TABLE t501 (~500000 rows)} 159 + 0 0 0 {SCAN TABLE t501} 160 160 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 161 - 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)} 161 + 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} 162 162 } 163 163 do_execsql_test autoindex1-502 { 164 164 EXPLAIN QUERY PLAN 165 165 SELECT b FROM t501 166 166 WHERE t501.a=123 167 167 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 168 168 } { 169 - 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 169 + 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 170 170 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 171 - 1 0 0 {SCAN TABLE t502 (~100000 rows)} 171 + 1 0 0 {SCAN TABLE t502} 172 172 } 173 173 174 174 175 175 # The following code checks a performance regression reported on the 176 176 # mailing list on 2010-10-19. The problem is that the nRowEst field 177 177 # of ephermeral tables was not being initialized correctly and so no 178 178 # automatic index was being created for the emphemeral table when it was ................................................................................ 236 236 WHERE prev.flock_no = later.flock_no 237 237 AND later.owner_change_date > prev.owner_change_date 238 238 AND later.owner_change_date <= s.date_of_registration||' 00:00:00') 239 239 ) y ON x.sheep_no = y.sheep_no 240 240 WHERE y.sheep_no IS NULL 241 241 ORDER BY x.registering_flock; 242 242 } { 243 - 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 244 - 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 243 + 1 0 0 {SCAN TABLE sheep AS s} 244 + 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 245 245 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 246 - 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 247 - 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 248 - 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)} 246 + 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 247 + 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 248 + 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} 249 249 } 250 250 251 251 252 252 do_execsql_test autoindex1-700 { 253 253 CREATE TABLE t5(a, b, c); 254 254 EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; 255 255 } { 256 - 0 0 0 {SCAN TABLE t5 (~100000 rows)} 256 + 0 0 0 {SCAN TABLE t5} 257 257 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 258 258 } 259 259 260 260 # The following checks a performance issue reported on the sqlite-dev 261 261 # mailing list on 2013-01-10 262 262 # 263 263 do_execsql_test autoindex1-800 {
Changes to test/backup4.test.
97 97 db1 close 98 98 file size test.db 99 99 } {1024} 100 100 101 101 do_test 3.4 { file size test.db2 } 0 102 102 103 103 finish_test 104 -
Changes to test/between.test.
54 54 set ::sqlite_sort_count 0 55 55 set data [execsql $sql] 56 56 if {$::sqlite_sort_count} {set x sort} {set x nosort} 57 57 lappend data $x 58 58 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 59 59 # puts eqp=$eqp 60 60 foreach {a b c x} $eqp { 61 - if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ 61 + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ 62 62 $x all as tab idx]} { 63 63 lappend data $tab $idx 64 - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { 64 + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { 65 65 lappend data $tab * 66 66 } 67 67 } 68 68 return $data 69 69 } 70 70 71 71 do_test between-1.1.1 {
Changes to test/btreefault.test.
51 51 } -test { 52 52 sqlite3_finalize $::STMT 53 53 faultsim_test_result {0 {}} 54 54 faultsim_integrity_check 55 55 } 56 56 57 57 finish_test 58 -
Changes to test/capi3e.test.
56 56 # capi3e-1.*: Test sqlite3_open with various UTF8 filenames 57 57 # capi3e-2.*: Test sqlite3_open16 with various UTF8 filenames 58 58 # capi3e-3.*: Test ATTACH with various UTF8 filenames 59 59 60 60 db close 61 61 62 62 # here's the list of file names we're testing 63 -set names {t 1 t. 1. t.d 1.d t-1 1-1 t.db ä.db ë.db ö.db ü.db ÿ.db} 63 +set names {t 1 t. 1. t.d 1.d t-1 1-1 t.db ä.db ë.db ö.db ü.db ÿ.db} 64 64 65 65 set i 0 66 66 foreach name $names { 67 67 incr i 68 68 do_test capi3e-1.1.$i { 69 69 set db2 [sqlite3_open $name {}] 70 70 sqlite3_errcode $db2
Changes to test/close.test.
72 72 } {1 {(21) library routine called out of sequence}} 73 73 74 74 do_test 1.4.4 { 75 75 sqlite3_finalize $STMT 76 76 } {SQLITE_OK} 77 77 78 78 finish_test 79 -
Changes to test/corruptF.test.
143 143 set res "" 144 144 } 145 145 set res 146 146 } {} 147 147 } 148 148 149 149 finish_test 150 -
Changes to test/e_createtable.test.
1364 1364 # 1365 1365 do_execsql_test 4.10.0 { 1366 1366 CREATE TABLE t1(a, b PRIMARY KEY); 1367 1367 CREATE TABLE t2(a, b, c, UNIQUE(b, c)); 1368 1368 } 1369 1369 do_createtable_tests 4.10 { 1370 1370 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 1371 - {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 1371 + {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}} 1372 1372 1373 1373 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" 1374 - {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 1374 + {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}} 1375 1375 1376 1376 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" 1377 - {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} 1377 + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}} 1378 1378 } 1379 1379 1380 1380 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a 1381 1381 # column definition or specified as a table constraint. In practice it 1382 1382 # makes no difference. 1383 1383 # 1384 1384 # All the tests that deal with CHECK constraints below (4.11.* and
Changes to test/e_fkey.test.
970 970 } 971 971 } {} 972 972 do_execsql_test e_fkey-25.2 { 973 973 PRAGMA foreign_keys = OFF; 974 974 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 975 975 EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; 976 976 } { 977 - 0 0 0 {SCAN TABLE artist (~1000000 rows)} 978 - 0 0 0 {SCAN TABLE track (~100000 rows)} 977 + 0 0 0 {SCAN TABLE artist} 978 + 0 0 0 {SCAN TABLE track} 979 979 } 980 980 do_execsql_test e_fkey-25.3 { 981 981 PRAGMA foreign_keys = ON; 982 982 EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 983 983 } { 984 - 0 0 0 {SCAN TABLE artist (~1000000 rows)} 985 - 0 0 0 {SCAN TABLE track (~100000 rows)} 984 + 0 0 0 {SCAN TABLE artist} 985 + 0 0 0 {SCAN TABLE track} 986 986 } 987 987 do_test e_fkey-25.4 { 988 988 execsql { 989 989 INSERT INTO artist VALUES(5, 'artist 5'); 990 990 INSERT INTO artist VALUES(6, 'artist 6'); 991 991 INSERT INTO artist VALUES(7, 'artist 7'); 992 992 INSERT INTO track VALUES(1, 'track 1', 5); ................................................................................ 1095 1095 } {} 1096 1096 do_test e_fkey-27.2 { 1097 1097 eqp { INSERT INTO artist VALUES(?, ?) } 1098 1098 } {} 1099 1099 do_execsql_test e_fkey-27.3 { 1100 1100 EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? 1101 1101 } { 1102 - 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1103 - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1104 - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1102 + 0 0 0 {SCAN TABLE artist} 1103 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 1104 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 1105 1105 } 1106 1106 do_execsql_test e_fkey-27.4 { 1107 1107 EXPLAIN QUERY PLAN DELETE FROM artist 1108 1108 } { 1109 - 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1110 - 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1109 + 0 0 0 {SCAN TABLE artist} 1110 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 1111 1111 } 1112 1112 1113 1113 1114 1114 ########################################################################### 1115 1115 ### SECTION 4.1: Composite Foreign Key Constraints 1116 1116 ########################################################################### 1117 1117
Changes to test/eqp.test.
39 39 CREATE TABLE t2(a, b); 40 40 CREATE TABLE t3(a, b); 41 41 } 42 42 43 43 do_eqp_test 1.2 { 44 44 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; 45 45 } { 46 - 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 47 - 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 48 - 0 1 0 {SCAN TABLE t2 (~1000000 rows)} 46 + 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 47 + 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 48 + 0 1 0 {SCAN TABLE t2} 49 49 } 50 50 do_eqp_test 1.3 { 51 51 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; 52 52 } { 53 - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 54 - 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 55 - 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 53 + 0 0 0 {SCAN TABLE t2} 54 + 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 55 + 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 56 56 } 57 57 do_eqp_test 1.3 { 58 58 SELECT a FROM t1 ORDER BY a 59 59 } { 60 - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 60 + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 61 61 } 62 62 do_eqp_test 1.4 { 63 63 SELECT a FROM t1 ORDER BY +a 64 64 } { 65 - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 65 + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 66 66 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 67 67 } 68 68 do_eqp_test 1.5 { 69 69 SELECT a FROM t1 WHERE a=4 70 70 } { 71 - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)} 71 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} 72 72 } 73 73 do_eqp_test 1.6 { 74 74 SELECT DISTINCT count(*) FROM t3 GROUP BY a; 75 75 } { 76 - 0 0 0 {SCAN TABLE t3 (~1000000 rows)} 76 + 0 0 0 {SCAN TABLE t3} 77 77 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 78 78 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 79 79 } 80 80 81 81 do_eqp_test 1.7 { 82 82 SELECT * FROM t3 JOIN (SELECT 1) 83 83 } { 84 - 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} 85 - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} 84 + 0 0 1 {SCAN SUBQUERY 1} 85 + 0 1 0 {SCAN TABLE t3} 86 86 } 87 87 do_eqp_test 1.8 { 88 88 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) 89 89 } { 90 90 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 91 - 0 0 1 {SCAN SUBQUERY 1 (~2 rows)} 92 - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} 91 + 0 0 1 {SCAN SUBQUERY 1} 92 + 0 1 0 {SCAN TABLE t3} 93 93 } 94 94 do_eqp_test 1.9 { 95 95 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) 96 96 } { 97 - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 97 + 3 0 0 {SCAN TABLE t3} 98 98 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} 99 - 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} 100 - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} 99 + 0 0 1 {SCAN SUBQUERY 1} 100 + 0 1 0 {SCAN TABLE t3} 101 101 } 102 102 do_eqp_test 1.10 { 103 103 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) 104 104 } { 105 - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 105 + 3 0 0 {SCAN TABLE t3} 106 106 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} 107 - 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} 108 - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} 107 + 0 0 1 {SCAN SUBQUERY 1} 108 + 0 1 0 {SCAN TABLE t3} 109 109 } 110 110 111 111 do_eqp_test 1.11 { 112 112 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) 113 113 } { 114 - 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 114 + 3 0 0 {SCAN TABLE t3} 115 115 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} 116 - 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} 117 - 0 1 0 {SCAN TABLE t3 (~1000000 rows)} 116 + 0 0 1 {SCAN SUBQUERY 1} 117 + 0 1 0 {SCAN TABLE t3} 118 118 } 119 119 120 120 #------------------------------------------------------------------------- 121 121 # Test cases eqp-2.* - tests for single select statements. 122 122 # 123 123 drop_all_tables 124 124 do_execsql_test 2.1 { ................................................................................ 125 125 CREATE TABLE t1(x, y); 126 126 127 127 CREATE TABLE t2(x, y); 128 128 CREATE INDEX t2i1 ON t2(x); 129 129 } 130 130 131 131 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { 132 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 132 + 0 0 0 {SCAN TABLE t1} 133 133 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 134 134 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 135 135 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 136 136 } 137 137 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { 138 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 138 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 139 139 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 140 140 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 141 141 } 142 142 det 2.2.3 "SELECT DISTINCT * FROM t1" { 143 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 143 + 0 0 0 {SCAN TABLE t1} 144 144 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 145 145 } 146 146 det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { 147 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 148 - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} 147 + 0 0 0 {SCAN TABLE t1} 148 + 0 1 1 {SCAN TABLE t2} 149 149 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 150 150 } 151 151 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { 152 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 153 - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} 152 + 0 0 0 {SCAN TABLE t1} 153 + 0 1 1 {SCAN TABLE t2} 154 154 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 155 155 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 156 156 } 157 157 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 158 - 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 159 - 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 158 + 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1} 159 + 0 1 0 {SCAN TABLE t1} 160 160 } 161 161 162 162 det 2.3.1 "SELECT max(x) FROM t2" { 163 - 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} 163 + 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} 164 164 } 165 165 det 2.3.2 "SELECT min(x) FROM t2" { 166 - 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} 166 + 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} 167 167 } 168 168 det 2.3.3 "SELECT min(x), max(x) FROM t2" { 169 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 169 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 170 170 } 171 171 172 172 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { 173 - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 173 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 174 174 } 175 175 176 176 177 177 178 178 #------------------------------------------------------------------------- 179 179 # Test cases eqp-3.* - tests for select statements that use sub-selects. 180 180 # 181 181 do_eqp_test 3.1.1 { 182 182 SELECT (SELECT x FROM t1 AS sub) FROM t1; 183 183 } { 184 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 184 + 0 0 0 {SCAN TABLE t1} 185 185 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 186 - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} 186 + 1 0 0 {SCAN TABLE t1 AS sub} 187 187 } 188 188 do_eqp_test 3.1.2 { 189 189 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); 190 190 } { 191 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 191 + 0 0 0 {SCAN TABLE t1} 192 192 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 193 - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} 193 + 1 0 0 {SCAN TABLE t1 AS sub} 194 194 } 195 195 do_eqp_test 3.1.3 { 196 196 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); 197 197 } { 198 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 198 + 0 0 0 {SCAN TABLE t1} 199 199 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 200 - 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} 200 + 1 0 0 {SCAN TABLE t1 AS sub} 201 201 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 202 202 } 203 203 do_eqp_test 3.1.4 { 204 204 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); 205 205 } { 206 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 206 + 0 0 0 {SCAN TABLE t1} 207 207 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 208 - 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 208 + 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 209 209 } 210 210 211 211 det 3.2.1 { 212 212 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 213 213 } { 214 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 214 + 1 0 0 {SCAN TABLE t1} 215 215 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 216 - 0 0 0 {SCAN SUBQUERY 1 (~10 rows)} 216 + 0 0 0 {SCAN SUBQUERY 1} 217 217 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 218 218 } 219 219 det 3.2.2 { 220 220 SELECT * FROM 221 221 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, 222 222 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 223 223 ORDER BY x2.y LIMIT 5 224 224 } { 225 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 225 + 1 0 0 {SCAN TABLE t1} 226 226 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 227 - 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 228 - 0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)} 229 - 0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)} 227 + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 228 + 0 0 0 {SCAN SUBQUERY 1 AS x1} 229 + 0 1 1 {SCAN SUBQUERY 2 AS x2} 230 230 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 231 231 } 232 232 233 233 det 3.3.1 { 234 234 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) 235 235 } { 236 - 0 0 0 {SCAN TABLE t1 (~100000 rows)} 236 + 0 0 0 {SCAN TABLE t1} 237 237 0 0 0 {EXECUTE LIST SUBQUERY 1} 238 - 1 0 0 {SCAN TABLE t2 (~1000000 rows)} 238 + 1 0 0 {SCAN TABLE t2} 239 239 } 240 240 det 3.3.2 { 241 241 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) 242 242 } { 243 - 0 0 0 {SCAN TABLE t1 (~500000 rows)} 243 + 0 0 0 {SCAN TABLE t1} 244 244 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 245 - 1 0 0 {SCAN TABLE t2 (~500000 rows)} 245 + 1 0 0 {SCAN TABLE t2} 246 246 } 247 247 det 3.3.3 { 248 248 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) 249 249 } { 250 - 0 0 0 {SCAN TABLE t1 (~500000 rows)} 250 + 0 0 0 {SCAN TABLE t1} 251 251 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 252 - 1 0 0 {SCAN TABLE t2 (~500000 rows)} 252 + 1 0 0 {SCAN TABLE t2} 253 253 } 254 254 255 255 #------------------------------------------------------------------------- 256 256 # Test cases eqp-4.* - tests for composite select statements. 257 257 # 258 258 do_eqp_test 4.1.1 { 259 259 SELECT * FROM t1 UNION ALL SELECT * FROM t2 260 260 } { 261 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 262 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 261 + 1 0 0 {SCAN TABLE t1} 262 + 2 0 0 {SCAN TABLE t2} 263 263 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 264 264 } 265 265 do_eqp_test 4.1.2 { 266 266 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 267 267 } { 268 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 268 + 1 0 0 {SCAN TABLE t1} 269 269 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 270 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 270 + 2 0 0 {SCAN TABLE t2} 271 271 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 272 272 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 273 273 } 274 274 do_eqp_test 4.1.3 { 275 275 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 276 276 } { 277 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 277 + 1 0 0 {SCAN TABLE t1} 278 278 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 279 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 279 + 2 0 0 {SCAN TABLE t2} 280 280 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 281 281 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 282 282 } 283 283 do_eqp_test 4.1.4 { 284 284 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 285 285 } { 286 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 286 + 1 0 0 {SCAN TABLE t1} 287 287 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 288 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 288 + 2 0 0 {SCAN TABLE t2} 289 289 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 290 290 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 291 291 } 292 292 do_eqp_test 4.1.5 { 293 293 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 294 294 } { 295 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 295 + 1 0 0 {SCAN TABLE t1} 296 296 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 297 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 297 + 2 0 0 {SCAN TABLE t2} 298 298 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 299 299 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 300 300 } 301 301 302 302 do_eqp_test 4.2.2 { 303 303 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 304 304 } { 305 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 305 + 1 0 0 {SCAN TABLE t1} 306 306 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 307 - 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 307 + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 308 308 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 309 309 } 310 310 do_eqp_test 4.2.3 { 311 311 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 312 312 } { 313 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 313 + 1 0 0 {SCAN TABLE t1} 314 314 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 315 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 315 + 2 0 0 {SCAN TABLE t2} 316 316 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 317 317 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 318 318 } 319 319 do_eqp_test 4.2.4 { 320 320 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 321 321 } { 322 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 322 + 1 0 0 {SCAN TABLE t1} 323 323 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 324 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 324 + 2 0 0 {SCAN TABLE t2} 325 325 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 326 326 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 327 327 } 328 328 do_eqp_test 4.2.5 { 329 329 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 330 330 } { 331 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 331 + 1 0 0 {SCAN TABLE t1} 332 332 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 333 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 333 + 2 0 0 {SCAN TABLE t2} 334 334 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 335 335 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 336 336 } 337 337 338 338 do_eqp_test 4.3.1 { 339 339 SELECT x FROM t1 UNION SELECT x FROM t2 340 340 } { 341 - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 342 - 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 341 + 1 0 0 {SCAN TABLE t1} 342 + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 343 343 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 344 344 } 345 345 346 346 do_eqp_test 4.3.2 { 347 347 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 348 348 } { 349 - 2 0 0 {SCAN TABLE t1 (~1000000 rows)} 350 - 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 349 + 2 0 0 {SCAN TABLE t1} 350 + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 351 351 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 352 - 4 0 0 {SCAN TABLE t1 (~1000000 rows)} 352 + 4 0 0 {SCAN TABLE t1} 353 353 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} 354 354 } 355 355 do_eqp_test 4.3.3 { 356 356 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 357 357 } { 358 - 2 0 0 {SCAN TABLE t1 (~1000000 rows)} 358 + 2 0 0 {SCAN TABLE t1} 359 359 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 360 - 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 360 + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 361 361 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 362 - 4 0 0 {SCAN TABLE t1 (~1000000 rows)} 362 + 4 0 0 {SCAN TABLE t1} 363 363 4 0 0 {USE TEMP B-TREE FOR ORDER BY} 364 364 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)} 365 365 } 366 366 367 367 #------------------------------------------------------------------------- 368 368 # This next block of tests verifies that the examples on the 369 369 # lang_explain.html page are correct. 370 370 # 371 371 drop_all_tables 372 372 373 373 # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b 374 -# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows) 374 +# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 375 375 do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } 376 376 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 377 - 0 0 0 {SCAN TABLE t1 (~100000 rows)} 377 + 0 0 0 {SCAN TABLE t1} 378 378 } 379 379 380 380 # EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a); 381 381 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 382 -# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) 382 +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) 383 383 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } 384 384 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { 385 - 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 385 + 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 386 386 } 387 387 388 388 # EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b); 389 389 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 390 -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 390 +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 391 391 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } 392 392 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { 393 - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 393 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 394 394 } 395 395 396 396 # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 397 397 # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 398 -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 399 -# (~1000000 rows) 398 +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|1|SCAN TABLE t2 399 +# 400 400 do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} 401 401 det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 402 - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 403 - 0 1 1 {SCAN TABLE t2 (~1000000 rows)} 402 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 403 + 0 1 1 {SCAN TABLE t2} 404 404 } 405 405 406 406 # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 407 407 # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1 408 -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 409 -# (~1000000 rows) 408 +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|0|SCAN TABLE t2 409 +# 410 410 det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 411 - 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 412 - 0 1 0 {SCAN TABLE t2 (~1000000 rows)} 411 + 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 412 + 0 1 0 {SCAN TABLE t2} 413 413 } 414 414 415 415 # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); 416 416 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; 417 -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 418 -# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) 417 +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 418 +# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) 419 419 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} 420 420 det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { 421 - 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 422 - 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} 421 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 422 + 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} 423 423 } 424 424 425 425 # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d 426 -# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP 426 +# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP 427 427 # B-TREE FOR ORDER BY 428 428 det 5.7 "SELECT c, d FROM t2 ORDER BY c" { 429 - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 429 + 0 0 0 {SCAN TABLE t2} 430 430 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 431 431 } 432 432 433 433 # EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c); 434 434 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 435 -# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows) 435 +# 0|0|0|SCAN TABLE t2 USING INDEX i4 436 436 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} 437 437 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { 438 - 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)} 438 + 0 0 0 {SCAN TABLE t2 USING INDEX i4} 439 439 } 440 440 441 441 # EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT 442 442 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; 443 -# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1 444 -# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 443 +# 0|0|0|SCAN TABLE t2 0|0|0|EXECUTE SCALAR SUBQUERY 1 444 +# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 445 445 # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING 446 -# INDEX i3 (b=?) (~10 rows) 446 +# INDEX i3 (b=?) 447 447 det 5.9 { 448 448 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 449 449 } { 450 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} 450 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 451 451 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 452 - 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 452 + 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 453 453 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 454 - 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} 454 + 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} 455 455 } 456 456 457 457 # EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT 458 458 # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; 459 -# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN 460 -# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY 459 +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 0|0|0|SCAN 460 +# SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR GROUP BY 461 461 det 5.10 { 462 462 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x 463 463 } { 464 - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 465 - 0 0 0 {SCAN SUBQUERY 1 (~100 rows)} 464 + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 465 + 0 0 0 {SCAN SUBQUERY 1} 466 466 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 467 467 } 468 468 469 469 # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM 470 470 # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4 471 -# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) 471 +# (c=?) 0|1|1|SCAN TABLE t1 472 472 det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { 473 - 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)} 474 - 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 473 + 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} 474 + 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} 475 475 } 476 476 477 477 # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM 478 -# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) 479 -# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 478 +# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 479 +# 2|0|0|SCAN TABLE t2 0|0|0|COMPOUND SUBQUERIES 1 AND 2 480 480 # USING TEMP B-TREE (UNION) 481 481 det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { 482 - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 483 - 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} 482 + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 483 + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 484 484 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 485 485 } 486 486 487 487 # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM 488 488 # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING 489 -# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) 489 +# COVERING INDEX i2 2|0|0|SCAN TABLE t2 490 490 # 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 491 491 # (EXCEPT) 492 492 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { 493 - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 494 - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 493 + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 494 + 2 0 0 {SCAN TABLE t2} 495 495 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 496 496 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 497 497 } 498 498 499 499 500 500 #------------------------------------------------------------------------- 501 501 # The following tests - eqp-6.* - test that the example C code on ................................................................................ 527 527 set data 528 528 }] [list $res] 529 529 } 530 530 531 531 do_peqp_test 6.1 { 532 532 SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1 533 533 } [string trimleft { 534 -1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 535 -2 0 0 SCAN TABLE t2 (~1000000 rows) 534 +1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 535 +2 0 0 SCAN TABLE t2 536 536 2 0 0 USE TEMP B-TREE FOR ORDER BY 537 537 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) 538 538 }] 539 539 540 540 #------------------------------------------------------------------------- 541 541 # The following tests - eqp-7.* - test that queries that use the OP_Count 542 542 # optimization return something sensible with EQP. ................................................................................ 546 546 do_execsql_test 7.0 { 547 547 CREATE TABLE t1(a, b); 548 548 CREATE TABLE t2(a, b); 549 549 CREATE INDEX i1 ON t2(a); 550 550 } 551 551 552 552 det 7.1 "SELECT count(*) FROM t1" { 553 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 553 + 0 0 0 {SCAN TABLE t1} 554 554 } 555 555 556 556 det 7.2 "SELECT count(*) FROM t2" { 557 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)} 557 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} 558 558 } 559 559 560 560 do_execsql_test 7.3 { 561 561 INSERT INTO t1 VALUES(1, 2); 562 562 INSERT INTO t1 VALUES(3, 4); 563 563 564 564 INSERT INTO t2 VALUES(1, 2); ................................................................................ 568 568 ANALYZE; 569 569 } 570 570 571 571 db close 572 572 sqlite3 db test.db 573 573 574 574 det 7.4 "SELECT count(*) FROM t1" { 575 - 0 0 0 {SCAN TABLE t1 (~2 rows)} 575 + 0 0 0 {SCAN TABLE t1} 576 576 } 577 577 578 578 det 7.5 "SELECT count(*) FROM t2" { 579 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)} 579 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} 580 580 } 581 581 582 582 583 583 finish_test
Changes to test/exclusive.test.
502 502 503 503 do_execsql_test exclusive-6.5 { 504 504 PRAGMA locking_mode = EXCLUSIVE; 505 505 SELECT * FROM sqlite_master; 506 506 } {exclusive} 507 507 508 508 finish_test 509 -
Changes to test/fallocate.test.
139 139 execsql { PRAGMA wal_checkpoint } 140 140 file size test.db 141 141 } [expr 32*1024] 142 142 } 143 143 144 144 145 145 finish_test 146 -
Changes to test/filefmt.test.
244 244 do_test filefmt-4.4 { 245 245 sqlite3 db2 bak.db 246 246 db2 eval { PRAGMA integrity_check } 247 247 } {ok} 248 248 db2 close 249 249 250 250 finish_test 251 -
Changes to test/fts3aa.test.
220 220 } {} 221 221 do_catchsql_test fts3aa-7.5 { 222 222 CREATE VIRTUAL TABLE t4 USING fts4(tokenize=simple, tokenize=simple); 223 223 } {1 {unrecognized parameter: tokenize=simple}} 224 224 225 225 226 226 finish_test 227 -
Changes to test/fts3ao.test.
216 216 do_execsql_test 5.2 { 217 217 ALTER TABLE t7 RENAME TO t8; 218 218 SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%'; 219 219 SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%'; 220 220 } {0 6} 221 221 222 222 finish_test 223 -
Changes to test/fts3atoken.test.
189 189 190 190 do_test fts3token-internal { 191 191 execsql { SELECT fts3_tokenizer_internal_test() } 192 192 } {ok} 193 193 194 194 195 195 finish_test 196 - 197 -
Changes to test/fts3auto.test.
703 703 do_fts3query_test 7.$tn.1 t1 {"M B"} 704 704 do_fts3query_test 7.$tn.2 t1 {"B D"} 705 705 do_fts3query_test 7.$tn.3 -deferred B t1 {"M B D"} 706 706 } 707 707 708 708 set sqlite_fts3_enable_parentheses $sfep 709 709 finish_test 710 -
Changes to test/fts3aux1.test.
101 101 db func rec rec 102 102 103 103 # Use EQP to show that the WHERE expression "term='braid'" uses a different 104 104 # index number (1) than "+term='braid'" (0). 105 105 # 106 106 do_execsql_test 2.1.1.1 { 107 107 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' 108 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} } 108 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} } 109 109 do_execsql_test 2.1.1.2 { 110 110 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' 111 -} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}} 111 +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}} 112 112 113 113 # Now show that using "term='braid'" means the virtual table returns 114 114 # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. 115 115 # 116 116 do_test 2.1.2.1 { 117 117 set cnt 0 118 118 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } ................................................................................ 150 150 151 151 # Special case: term=NULL 152 152 # 153 153 do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} 154 154 155 155 do_execsql_test 2.2.1.1 { 156 156 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' 157 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~25 rows)} } 157 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2:} } 158 158 do_execsql_test 2.2.1.2 { 159 159 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' 160 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } 160 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } 161 161 162 162 do_execsql_test 2.2.1.3 { 163 163 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' 164 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~25 rows)} } 164 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4:} } 165 165 do_execsql_test 2.2.1.4 { 166 166 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' 167 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } 167 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } 168 168 169 169 do_execsql_test 2.2.1.5 { 170 170 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' 171 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~25 rows)} } 171 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6:} } 172 172 do_execsql_test 2.2.1.6 { 173 173 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' 174 -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } 174 +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} } 175 175 176 176 do_test 2.2.2.1 { 177 177 set cnt 0 178 178 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } 179 179 set cnt 180 180 } {18} 181 181 do_test 2.2.2.2 { ................................................................................ 331 331 5 1 "ORDER BY documents" 332 332 6 1 "ORDER BY documents DESC" 333 333 7 1 "ORDER BY occurrences ASC" 334 334 8 1 "ORDER BY occurrences" 335 335 9 1 "ORDER BY occurrences DESC" 336 336 } { 337 337 338 - set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}] 338 + set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}] 339 339 if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } 340 340 341 341 set sql "SELECT * FROM terms $orderby" 342 342 do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res 343 343 } 344 344 345 345 #------------------------------------------------------------------------- ................................................................................ 406 406 proc do_plansql_test {tn sql r} { 407 407 uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r] 408 408 } 409 409 410 410 do_plansql_test 4.2 { 411 411 SELECT y FROM x2, terms WHERE y = term AND col = '*' 412 412 } { 413 - 0 0 0 {SCAN TABLE x2 (~1000000 rows)} 414 - 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 413 + 0 0 0 {SCAN TABLE x2} 414 + 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 415 415 a b c d e f g h i j k l 416 416 } 417 417 418 418 do_plansql_test 4.3 { 419 419 SELECT y FROM terms, x2 WHERE y = term AND col = '*' 420 420 } { 421 - 0 0 1 {SCAN TABLE x2 (~1000000 rows)} 422 - 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 421 + 0 0 1 {SCAN TABLE x2} 422 + 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 423 423 a b c d e f g h i j k l 424 424 } 425 425 426 426 do_plansql_test 4.4 { 427 427 SELECT y FROM x3, terms WHERE y = term AND col = '*' 428 428 } { 429 - 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 430 - 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} 429 + 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 430 + 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)} 431 431 a b c d e f g h i j k l 432 432 } 433 433 434 434 do_plansql_test 4.5 { 435 435 SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' 436 436 } { 437 - 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 438 - 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} 437 + 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 438 + 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)} 439 439 a k l 440 440 } 441 441 442 442 #------------------------------------------------------------------------- 443 443 # The following tests check that fts4aux can handle an fts table with an 444 444 # odd name (one that requires quoting for use in SQL statements). And that 445 445 # the argument to the fts4aux constructor is properly dequoted before use.
Changes to test/fts3corrupt.test.
162 162 UPDATE t1_stat SET value = NULL; 163 163 SELECT matchinfo(t1, 'nxa') FROM t1 WHERE t1 MATCH 't*'; 164 164 } {1 {database disk image is malformed}} 165 165 do_test 5.3.1 { sqlite3_extended_errcode db } SQLITE_CORRUPT_VTAB 166 166 167 167 168 168 finish_test 169 -
Changes to test/fts3defer2.test.
149 149 do_execsql_test 2.4.$tn { 150 150 SELECT docid, mit(matchinfo(t3, 'pcxnal')) FROM t3 WHERE t3 MATCH '"a b c"'; 151 151 } {1 {1 1 1 4 4 11 912 6} 3 {1 1 1 4 4 11 912 6}} 152 152 } 153 153 154 154 155 155 finish_test 156 -
Changes to test/fts3expr3.test.
200 200 test_fts3expr2 $::query 201 201 } -test { 202 202 faultsim_test_result [list 0 $::result] 203 203 } 204 204 205 205 set sqlite_fts3_enable_parentheses 0 206 206 finish_test 207 - 208 - 209 - 210 -
Changes to test/fts3malloc.test.
297 297 298 298 do_write_test fts3_malloc-5.3 ft_content { 299 299 INSERT INTO ft8 VALUES('short alongertoken reallyquitealotlongerimeanit andthistokenisjustsolongthatonemightbeforgivenforimaginingthatitwasmerelyacontrivedexampleandnotarealtoken') 300 300 } 301 301 302 302 303 303 finish_test 304 -
Changes to test/fts3matchinfo.test.
423 423 INSERT INTO t12 VALUES('a d d a'); 424 424 SELECT mit(matchinfo(t12, 'x')) FROM t12 WHERE t12 MATCH 'a NEAR/1 d OR a'; 425 425 } { 426 426 {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} 427 427 } 428 428 429 429 finish_test 430 -
Changes to test/fts3prefix2.test.
55 55 {T TX T TX T TX T TX T TX} 56 56 {T TX T TX T TX T TX T TX} 57 57 {T TX T TX T TX T TX T TX} 58 58 {T TX T TX T TX T TX T TX} 59 59 } 60 60 61 61 finish_test 62 -
Changes to test/fts3query.test.
114 114 CREATE VIRTUAL TABLE ft USING fts3(title); 115 115 CREATE TABLE bt(title); 116 116 } 117 117 } {} 118 118 do_eqp_test fts3query-4.2 { 119 119 SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date 120 120 } { 121 - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 122 - 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} 121 + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 122 + 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} 123 123 } 124 124 do_eqp_test fts3query-4.3 { 125 125 SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date 126 126 } { 127 - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 128 - 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} 127 + 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 128 + 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} 129 129 } 130 130 do_eqp_test fts3query-4.4 { 131 131 SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date 132 132 } { 133 - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 134 - 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 133 + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 134 + 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} 135 135 } 136 136 do_eqp_test fts3query-4.5 { 137 137 SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date 138 138 } { 139 - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 140 - 0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 139 + 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 140 + 0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} 141 141 } 142 142 143 143 144 144 # Test that calling matchinfo() with the wrong number of arguments, or with 145 145 # an invalid argument returns an error. 146 146 # 147 147 do_execsql_test 5.1 {
Changes to test/fts3shared.test.
170 170 execsql ROLLBACK dbW 171 171 } 172 172 173 173 dbW close 174 174 dbR close 175 175 sqlite3_enable_shared_cache $::enable_shared_cache 176 176 finish_test 177 -
Changes to test/fts3snippet.test.
Changes to test/fts3sort.test.
178 178 INSERT INTO t4(docid, x) VALUES(1, 'ab'); 179 179 SELECT rowid FROM t4 WHERE x MATCH 'a*'; 180 180 } {-113382409004785664 1} 181 181 182 182 183 183 184 184 finish_test 185 -
Changes to test/fts3tok1.test.
109 109 do_catchsql_test 2.1 { 110 110 CREATE VIRTUAL TABLE t4 USING fts3tokenize; 111 111 SELECT * FROM t4; 112 112 } {1 {SQL logic error or missing database}} 113 113 114 114 115 115 finish_test 116 - 117 -
Changes to test/fts3tok_err.test.
41 41 execsql { SELECT token FROM t1 WHERE input = 'A galaxy far, far away' } 42 42 } -test { 43 43 faultsim_test_result {0 {a galaxy far far away}} 44 44 } 45 45 46 46 47 47 finish_test 48 - 49 -
Changes to test/fts4content.test.
619 619 do_execsql_test 10.7 { 620 620 SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e' 621 621 } { 622 622 {...c d [e] f g...} 623 623 } 624 624 625 625 finish_test 626 -
Changes to test/incrblob3.test.
265 265 sqlite3_db_config_lookaside db 0 0 0 266 266 list [catch {db incrblob blobs v 1} msg] $msg 267 267 } {1 {database schema has changed}} 268 268 db close 269 269 tvfs delete 270 270 271 271 finish_test 272 -
Changes to test/incrblob4.test.
83 83 set new [string repeat % 900] 84 84 execsql { UPDATE t1 SET v = $new WHERE k = 20 } 85 85 execsql { DELETE FROM t1 WHERE k=19 } 86 86 execsql { INSERT INTO t1(v) VALUES($new) } 87 87 } {} 88 88 89 89 finish_test 90 -
Changes to test/incrblobfault.test.
63 63 gets $::blob 64 64 } -test { 65 65 faultsim_test_result {0 {hello world}} 66 66 catch { close $::blob } 67 67 } 68 68 69 69 finish_test 70 -
Changes to test/incrvacuum3.test.
147 147 } 148 148 149 149 do_execsql_test $T.1.x.1 { PRAGMA freelist_count } 0 150 150 do_execsql_test $T.1.x.2 { SELECT count(*) FROM t1 } 128 151 151 } 152 152 153 153 finish_test 154 -
Changes to test/indexedby.test.
38 38 uplevel "execsql {EXPLAIN QUERY PLAN $sql}" 39 39 } 40 40 41 41 # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. 42 42 # 43 43 do_execsql_test indexedby-1.2 { 44 44 EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 45 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} 45 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 46 46 do_execsql_test indexedby-1.3 { 47 47 EXPLAIN QUERY PLAN select * from t1 ; 48 -} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} 48 +} {0 0 0 {SCAN TABLE t1}} 49 49 do_execsql_test indexedby-1.4 { 50 50 EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 51 51 } { 52 - 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 53 - 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 52 + 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 53 + 0 1 0 {SCAN TABLE t1} 54 54 } 55 55 56 56 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 57 57 # attached to a table in the FROM clause, but not to a sub-select or 58 58 # SQL view. Also test that specifying an index that does not exist or 59 59 # is attached to a different table is detected as an error. 60 60 # ................................................................................ 81 81 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' } 82 82 } {1 {no such index: i1}} 83 83 84 84 # Tests for single table cases. 85 85 # 86 86 do_execsql_test indexedby-3.1 { 87 87 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' 88 -} {0 0 0 {SCAN TABLE t1 (~10000 rows)}} 88 +} {0 0 0 {SCAN TABLE t1}} 89 89 do_execsql_test indexedby-3.2 { 90 90 EXPLAIN QUERY PLAN 91 91 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' 92 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 92 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 93 93 do_execsql_test indexedby-3.3 { 94 94 EXPLAIN QUERY PLAN 95 95 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' 96 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 96 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} 97 97 do_test indexedby-3.4 { 98 98 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 99 99 } {1 {cannot use index: i2}} 100 100 do_test indexedby-3.5 { 101 101 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 102 102 } {1 {cannot use index: i2}} 103 103 do_test indexedby-3.6 { ................................................................................ 106 106 do_test indexedby-3.7 { 107 107 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 108 108 } {0 {}} 109 109 110 110 do_execsql_test indexedby-3.8 { 111 111 EXPLAIN QUERY PLAN 112 112 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 113 -} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} 113 +} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}} 114 114 do_execsql_test indexedby-3.9 { 115 115 EXPLAIN QUERY PLAN 116 116 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 117 -} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} 117 +} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}} 118 118 do_test indexedby-3.10 { 119 119 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } 120 120 } {1 {cannot use index: sqlite_autoindex_t3_1}} 121 121 do_test indexedby-3.11 { 122 122 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } 123 123 } {1 {no such index: sqlite_autoindex_t3_2}} 124 124 125 125 # Tests for multiple table cases. 126 126 # 127 127 do_execsql_test indexedby-4.1 { 128 128 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 129 129 } { 130 - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 131 - 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 130 + 0 0 0 {SCAN TABLE t1} 131 + 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 132 132 } 133 133 do_execsql_test indexedby-4.2 { 134 134 EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 135 135 } { 136 - 0 0 1 {SCAN TABLE t2 (~1000000 rows)} 137 - 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 136 + 0 0 1 {SCAN TABLE t2} 137 + 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 138 138 } 139 139 do_test indexedby-4.3 { 140 140 catchsql { 141 141 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c 142 142 } 143 143 } {1 {cannot use index: i1}} 144 144 do_test indexedby-4.4 { ................................................................................ 150 150 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block 151 151 # also tests that nothing bad happens if an index refered to by 152 152 # a CREATE VIEW statement is dropped and recreated. 153 153 # 154 154 do_execsql_test indexedby-5.1 { 155 155 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 156 156 EXPLAIN QUERY PLAN SELECT * FROM v2 157 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} 157 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} 158 158 do_execsql_test indexedby-5.2 { 159 159 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 160 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} 160 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} 161 161 do_test indexedby-5.3 { 162 162 execsql { DROP INDEX i1 } 163 163 catchsql { SELECT * FROM v2 } 164 164 } {1 {no such index: i1}} 165 165 do_test indexedby-5.4 { 166 166 # Recreate index i1 in such a way as it cannot be used by the view query. 167 167 execsql { CREATE INDEX i1 ON t1(b) } ................................................................................ 174 174 catchsql { SELECT * FROM v2 } 175 175 } {0 {}} 176 176 177 177 # Test that "NOT INDEXED" may use the rowid index, but not others. 178 178 # 179 179 do_execsql_test indexedby-6.1 { 180 180 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 181 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} 181 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} 182 182 do_execsql_test indexedby-6.2 { 183 183 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 184 -} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} 184 +} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY}} 185 185 186 186 # Test that "INDEXED BY" can be used in a DELETE statement. 187 187 # 188 188 do_execsql_test indexedby-7.1 { 189 189 EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 190 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 190 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} 191 191 do_execsql_test indexedby-7.2 { 192 192 EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 193 -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 193 +} {0 0 0 {SCAN TABLE t1}} 194 194 do_execsql_test indexedby-7.3 { 195 195 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 196 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 196 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} 197 197 do_execsql_test indexedby-7.4 { 198 198 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 199 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 199 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 200 200 do_execsql_test indexedby-7.5 { 201 201 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 202 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 202 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} 203 203 do_test indexedby-7.6 { 204 204 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} 205 205 } {1 {cannot use index: i2}} 206 206 207 207 # Test that "INDEXED BY" can be used in an UPDATE statement. 208 208 # 209 209 do_execsql_test indexedby-8.1 { 210 210 EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 211 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 211 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} 212 212 do_execsql_test indexedby-8.2 { 213 213 EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 214 -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 214 +} {0 0 0 {SCAN TABLE t1}} 215 215 do_execsql_test indexedby-8.3 { 216 216 EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 217 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 217 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} 218 218 do_execsql_test indexedby-8.4 { 219 219 EXPLAIN QUERY PLAN 220 220 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 221 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 221 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 222 222 do_execsql_test indexedby-8.5 { 223 223 EXPLAIN QUERY PLAN 224 224 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 225 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 225 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} 226 226 do_test indexedby-8.6 { 227 227 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} 228 228 } {1 {cannot use index: i2}} 229 229 230 230 # Test that bug #3560 is fixed. 231 231 # 232 232 do_test indexedby-9.1 {
Changes to test/io.test.
637 637 hexio_write test.db [expr 1024 * 5] [string repeat 00 2048] 638 638 do_execsql_test 6.2.$tn.3 { PRAGMA integrity_check } {ok} 639 639 db close 640 640 } 641 641 642 642 sqlite3_simulate_device -char {} -sectorsize 0 643 643 finish_test 644 -
Changes to test/ioerr6.test.
85 85 db eval { CREATE TABLE t3(x) } 86 86 if {[db one { PRAGMA integrity_check }] != "ok"} { 87 87 error "integrity check failed" 88 88 } 89 89 } 90 90 91 91 finish_test 92 -
Changes to test/like.test.
162 162 set ::sqlite_sort_count 0 163 163 set data [execsql $sql] 164 164 if {$::sqlite_sort_count} {set x sort} {set x nosort} 165 165 lappend data $x 166 166 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 167 167 # puts eqp=$eqp 168 168 foreach {a b c x} $eqp { 169 - if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \ 169 + if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \ 170 170 $x all as tab idx]} { 171 171 lappend data {} $idx 172 - } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ 172 + } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ 173 173 $x all as tab idx]} { 174 174 lappend data $tab $idx 175 - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { 175 + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { 176 176 lappend data $tab * 177 177 } 178 178 } 179 179 return $data 180 180 } 181 181 182 182 # Perform tests on the like optimization.
Changes to test/lock7.test.
54 54 execsql { COMMIT } db1 55 55 } {} 56 56 57 57 db1 close 58 58 db2 close 59 59 60 60 finish_test 61 -
Changes to test/misc7.test.
265 265 sqlite3 db test.db 266 266 267 267 ifcapable explain { 268 268 do_execsql_test misc7-14.1 { 269 269 CREATE TABLE abc(a PRIMARY KEY, b, c); 270 270 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1; 271 271 } { 272 - 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 272 + 0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)} 273 273 } 274 274 do_execsql_test misc7-14.2 { 275 275 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1; 276 276 } {0 0 0 277 - {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?) (~1 rows)} 277 + {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)} 278 278 } 279 279 do_execsql_test misc7-14.3 { 280 280 EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a; 281 281 } {0 0 0 282 - {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (~1000000 rows)} 282 + {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1} 283 283 } 284 284 } 285 285 286 286 db close 287 287 forcedelete test.db 288 288 forcedelete test.db-journal 289 289 sqlite3 db test.db
Changes to test/notify3.test.
146 146 } 147 147 catch { db1 close } 148 148 catch { db2 close } 149 149 150 150 151 151 sqlite3_enable_shared_cache $esc 152 152 finish_test 153 -
Changes to test/pager1.test.
2811 2811 2812 2812 do_test 43.3 { 2813 2813 db eval { SELECT * FROM t3 } 2814 2814 sqlite3_db_status db CACHE_MISS 0 2815 2815 } {0 1 0} 2816 2816 2817 2817 finish_test 2818 -
Changes to test/pagerfault.test.
1542 1542 catch { db2 close } 1543 1543 } 1544 1544 1545 1545 sqlite3_shutdown 1546 1546 sqlite3_config_uri 0 1547 1547 1548 1548 finish_test 1549 -
Changes to test/pagerfault2.test.
92 92 execsql { INSERT INTO t1 VALUES (a_string(2000000), a_string(2500000)) } 93 93 } -test { 94 94 faultsim_test_result {0 {}} 95 95 } 96 96 97 97 sqlite3_memdebug_vfs_oom_test 1 98 98 finish_test 99 -
Changes to test/pagerfault3.test.
57 57 } 58 58 } -test { 59 59 faultsim_test_result {0 {}} 60 60 faultsim_integrity_check 61 61 } 62 62 63 63 finish_test 64 -
Changes to test/securedel2.test.
88 88 for {set i 2} {$i <= 850} {incr i 5} { 89 89 incr n [detect_blob {} $i] 90 90 } 91 91 set n 92 92 } {0} 93 93 94 94 finish_test 95 -
Changes to test/shared8.test.
106 106 catchsql { SELECT * FROM v1 } db4 107 107 } {1 {no such table: v1}} 108 108 109 109 110 110 foreach db {db1 db2 db3 db4} { catch { $db close } } 111 111 sqlite3_enable_shared_cache $::enable_shared_cache 112 112 finish_test 113 -
Changes to test/sharedlock.test.
48 48 } {1 one 2 two 3 three} 49 49 50 50 db close 51 51 db2 close 52 52 53 53 sqlite3_enable_shared_cache $::enable_shared_cache 54 54 finish_test 55 -
Changes to test/tkt-385a5b56b9.test.
31 31 do_execsql_test 2.0 { 32 32 CREATE TABLE t2(x, y NOT NULL); 33 33 CREATE UNIQUE INDEX t2x ON t2(x); 34 34 CREATE UNIQUE INDEX t2y ON t2(y); 35 35 } 36 36 37 37 do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } { 38 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)} 38 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x} 39 39 } 40 40 41 41 do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } { 42 - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)} 42 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y} 43 43 } 44 44 45 45 do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } { 46 - 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)} 46 + 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)} 47 47 } 48 48 49 49 do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } { 50 - 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)} 50 + 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?)} 51 51 } 52 52 53 53 finish_test
Changes to test/tkt-3a77c9714e.test.
66 66 WHERE Connected=SrcWord LIMIT 1 67 67 ) 68 68 ) 69 69 } {FACTORING FACTOR SWIMMING SWIMM} 70 70 71 71 72 72 finish_test 73 -
Changes to test/tkt-3fe897352e.test.
Changes to test/tkt-78e04e52ea.test.
40 40 CREATE INDEX i1 ON ""("" COLLATE nocase); 41 41 } 42 42 } {} 43 43 do_test tkt-78e04-1.4 { 44 44 execsql { 45 45 EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%'; 46 46 } 47 -} {0 0 0 {SCAN TABLE USING COVERING INDEX i1 (~500000 rows)}} 47 +} {0 0 0 {SCAN TABLE USING COVERING INDEX i1}} 48 48 do_test tkt-78e04-1.5 { 49 49 execsql { 50 50 DROP TABLE ""; 51 51 SELECT name FROM sqlite_master; 52 52 } 53 53 } {t2} 54 54 55 55 do_test tkt-78e04-2.1 { 56 56 execsql { 57 57 CREATE INDEX "" ON t2(x); 58 58 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; 59 59 } 60 -} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?) (~10 rows)}} 60 +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?)}} 61 61 do_test tkt-78e04-2.2 { 62 62 execsql { 63 63 DROP INDEX ""; 64 64 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; 65 65 } 66 -} {0 0 0 {SCAN TABLE t2 (~100000 rows)}} 66 +} {0 0 0 {SCAN TABLE t2}} 67 67 68 68 finish_test
Changes to test/tkt-7a31705a7e6.test.
19 19 20 20 do_execsql_test tkt-7a31705a7e6-1.1 { 21 21 CREATE TABLE t1 (a INTEGER PRIMARY KEY); 22 22 CREATE TABLE t2 (a INTEGER PRIMARY KEY, b INTEGER); 23 23 CREATE TABLE t2x (b INTEGER PRIMARY KEY); 24 24 SELECT t1.a FROM ((t1 JOIN t2 ON t1.a=t2.a) AS x JOIN t2x ON x.b=t2x.b) as y; 25 25 } {} 26 -
Changes to test/tkt-7bbfb7d442.test.
148 148 do_execsql_test 2.3 { 149 149 SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END 150 150 FROM InventoryControl WHERE SKU=31; 151 151 } {{TEST PASSED!}} 152 152 153 153 154 154 finish_test 155 - 156 -
Changes to test/tkt-c48d99d690.test.
19 19 do_test 1.1 { 20 20 execsql { INSERT INTO t2 SELECT * FROM t1 } 21 21 } {4} 22 22 23 23 do_test 1.2 { execsql VACUUM } {} 24 24 25 25 finish_test 26 -
Changes to test/tkt-d11f09d36e.test.
55 55 } 56 56 } {} 57 57 do_test tkt-d11f09d36e.5 { 58 58 execsql { PRAGMA integrity_check } 59 59 } {ok} 60 60 61 61 finish_test 62 -
Changes to test/tkt-f3e5abed55.test.
110 110 SELECT * FROM t2; 111 111 } 112 112 } {1 2 3 4 1 2 3 4} 113 113 } 114 114 115 115 116 116 finish_test 117 -
Changes to test/tkt-f973c7ac31.test.
80 80 SELECT * FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC 81 81 } 82 82 } {5 4 5 5} 83 83 } 84 84 85 85 86 86 finish_test 87 -
Changes to test/tkt3442.test.
45 45 # These tests perform an EXPLAIN QUERY PLAN on both versions of the 46 46 # SELECT referenced in ticket #3442 (both '5000' and "5000") 47 47 # and verify that the query plan is the same. 48 48 # 49 49 ifcapable explain { 50 50 do_test tkt3442-1.2 { 51 51 EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } 52 - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 52 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} 53 53 do_test tkt3442-1.3 { 54 54 EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } 55 - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 55 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} 56 56 } 57 57 58 58 59 59 # Some extra tests testing other permutations of 5000. 60 60 # 61 61 ifcapable explain { 62 62 do_test tkt3442-1.4 { 63 63 EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } 64 - } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 64 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}} 65 65 } 66 66 do_test tkt3442-1.5 { 67 67 catchsql { 68 68 SELECT node FROM listhash WHERE id=[5000] LIMIT 1; 69 69 } 70 70 } {1 {no such column: 5000}} 71 71 72 72 finish_test
Changes to test/tkt3918.test.
53 53 # page 4 from the database free-list. Bug 3918 caused sqlite to 54 54 # incorrectly report corruption here. 55 55 do_test tkt3918.5 { 56 56 execsql { CREATE TABLE t2(a, b) } 57 57 } {} 58 58 59 59 finish_test 60 -
Changes to test/tkt3929.test.
46 46 for {set i 3} {$i < 100} {incr i} { 47 47 execsql { INSERT INTO t1(a) VALUES($i) } 48 48 } 49 49 } {} 50 50 51 51 integrity_check tkt3930-1.3 52 52 finish_test 53 -
Changes to test/unordered.test.
36 36 if {$idxmode == "unordered"} { 37 37 execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' } 38 38 } 39 39 db close 40 40 sqlite3 db test.db 41 41 foreach {tn sql r(ordered) r(unordered)} { 42 42 1 "SELECT * FROM t1 ORDER BY a" 43 - {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}} 44 - {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 43 + {0 0 0 {SCAN TABLE t1 USING INDEX i1}} 44 + {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 45 45 2 "SELECT * FROM t1 WHERE a >?" 46 - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~32 rows)}} 47 - {0 0 0 {SCAN TABLE t1 (~42 rows)}} 46 + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} 47 + {0 0 0 {SCAN TABLE t1}} 48 48 3 "SELECT * FROM t1 WHERE a = ? ORDER BY rowid" 49 - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} 50 - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} 49 + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 50 + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 51 51 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 52 52 4 "SELECT max(a) FROM t1" 53 - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} 54 - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} 53 + {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} 54 + {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} 55 55 5 "SELECT group_concat(b) FROM t1 GROUP BY a" 56 - {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}} 57 - {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} 56 + {0 0 0 {SCAN TABLE t1 USING INDEX i1}} 57 + {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} 58 58 59 59 6 "SELECT * FROM t1 WHERE a = ?" 60 - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} 61 - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}} 60 + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 61 + {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} 62 62 7 "SELECT count(*) FROM t1" 63 63 {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1(~128 rows)}} 64 - {0 0 0 {SCAN TABLE t1 (~128 rows)}} 64 + {0 0 0 {SCAN TABLE t1}} 65 65 } { 66 66 do_eqp_test 1.$idxmode.$tn $sql $r($idxmode) 67 67 } 68 68 } 69 69 70 70 finish_test
Changes to test/veryquick.test.
12 12 13 13 set testdir [file dirname $argv0] 14 14 source $testdir/permutations.test 15 15 16 16 run_test_suite veryquick 17 17 18 18 finish_test 19 -
Changes to test/wal8.test.
84 84 85 85 do_execsql_test 3.1 { 86 86 PRAGMA page_size = 4096; 87 87 SELECT name FROM sqlite_master; 88 88 } {t1} 89 89 90 90 finish_test 91 -
Changes to test/walcksum.test.
386 386 db2 close 387 387 } 388 388 set FAIL 389 389 } {0} 390 390 } 391 391 392 392 finish_test 393 -
Changes to test/walcrash.test.
289 289 do_test walcrash-7.$i.3 { execsql { PRAGMA main.integrity_check } } {ok} 290 290 do_test walcrash-7.$i.4 { execsql { PRAGMA main.journal_mode } } {wal} 291 291 292 292 db close 293 293 } 294 294 295 295 finish_test 296 -
Changes to test/walcrash2.test.
92 92 do_test walcrash2-1.3 { 93 93 sqlite3 db2 test.db 94 94 execsql { SELECT count(*) FROM t1 } db2 95 95 } {0} 96 96 catch { db2 close } 97 97 98 98 finish_test 99 -
Changes to test/walcrash3.test.
122 122 do_test 2.$i.2 { 123 123 sqlite3 db test.db 124 124 execsql { PRAGMA integrity_check } 125 125 } {ok} 126 126 } 127 127 128 128 finish_test 129 -
Changes to test/walro.test.
287 287 do_test 2.1.5 { 288 288 code1 { db close } 289 289 code1 { tv delete } 290 290 } {} 291 291 } 292 292 293 293 finish_test 294 - 295 -
Changes to test/walshared.test.
56 56 execsql { PRAGMA integrity_check } db2 57 57 } {ok} 58 58 59 59 60 60 61 61 sqlite3_enable_shared_cache $::enable_shared_cache 62 62 finish_test 63 -
Changes to test/where.test.
63 63 # small we can be assured that indices are being used properly. 64 64 # 65 65 do_test where-1.1.1 { 66 66 count {SELECT x, y, w FROM t1 WHERE w=10} 67 67 } {3 121 10 3} 68 68 do_eqp_test where-1.1.2 { 69 69 SELECT x, y, w FROM t1 WHERE w=10 70 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} 70 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} 71 71 do_test where-1.1.3 { 72 72 db status step 73 73 } {0} 74 74 do_test where-1.1.4 { 75 75 db eval {SELECT x, y, w FROM t1 WHERE +w=10} 76 76 } {3 121 10} 77 77 do_test where-1.1.5 { 78 78 db status step 79 79 } {99} 80 80 do_eqp_test where-1.1.6 { 81 81 SELECT x, y, w FROM t1 WHERE +w=10 82 -} {*SCAN TABLE t1 *} 82 +} {*SCAN TABLE t1*} 83 83 do_test where-1.1.7 { 84 84 count {SELECT x, y, w AS abc FROM t1 WHERE abc=10} 85 85 } {3 121 10 3} 86 86 do_eqp_test where-1.1.8 { 87 87 SELECT x, y, w AS abc FROM t1 WHERE abc=10 88 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} 88 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} 89 89 do_test where-1.1.9 { 90 90 db status step 91 91 } {0} 92 92 do_test where-1.2.1 { 93 93 count {SELECT x, y, w FROM t1 WHERE w=11} 94 94 } {3 144 11 3} 95 95 do_test where-1.2.2 { ................................................................................ 102 102 count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc} 103 103 } {3 144 11 3} 104 104 do_test where-1.4.1 { 105 105 count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} 106 106 } {11 3 144 3} 107 107 do_eqp_test where-1.4.2 { 108 108 SELECT w, x, y FROM t1 WHERE 11=w AND x>2 109 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} 109 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} 110 110 do_test where-1.4.3 { 111 111 count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} 112 112 } {11 3 144 3} 113 113 do_eqp_test where-1.4.4 { 114 114 SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 115 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} 115 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} 116 116 do_test where-1.5 { 117 117 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} 118 118 } {3 144 3} 119 119 do_eqp_test where-1.5.2 { 120 120 SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2 121 -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *} 121 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} 122 122 do_test where-1.6 { 123 123 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} 124 124 } {3 144 3} 125 125 do_test where-1.7 { 126 126 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} 127 127 } {3 144 3} 128 128 do_test where-1.8 { 129 129 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} 130 130 } {3 144 3} 131 131 do_eqp_test where-1.8.2 { 132 132 SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3 133 -} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?) *} 133 +} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*} 134 134 do_eqp_test where-1.8.3 { 135 135 SELECT x, y FROM t1 WHERE y=144 AND x=3 136 -} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?) *} 136 +} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*} 137 137 do_test where-1.9 { 138 138 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} 139 139 } {3 144 3} 140 140 do_test where-1.10 { 141 141 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} 142 142 } {3 121 3} 143 143 do_test where-1.11 {
Changes to test/where2.test.
72 72 set ::sqlite_sort_count 0 73 73 set data [execsql $sql] 74 74 if {$::sqlite_sort_count} {set x sort} {set x nosort} 75 75 lappend data $x 76 76 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 77 77 # puts eqp=$eqp 78 78 foreach {a b c x} $eqp { 79 - if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ 79 + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ 80 80 $x all as tab idx]} { 81 81 lappend data $tab $idx 82 - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { 82 + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { 83 83 lappend data $tab * 84 84 } 85 85 } 86 86 return $data 87 87 } 88 88 89 89
Changes to test/where3.test.
107 107 # 108 108 proc queryplan {sql} { 109 109 set ::sqlite_sort_count 0 110 110 set data [execsql $sql] 111 111 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 112 112 # puts eqp=$eqp 113 113 foreach {a b c x} $eqp { 114 - if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \ 114 + if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ 115 115 $x all as tab idx]} { 116 116 lappend data $tab $idx 117 - } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} { 117 + } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { 118 118 lappend data $tab * 119 119 } 120 120 } 121 121 return $data 122 122 } 123 123 124 124 ................................................................................ 232 232 CREATE INDEX t301c ON t301(c); 233 233 INSERT INTO t301 VALUES(1,2,3); 234 234 CREATE TABLE t302(x, y); 235 235 INSERT INTO t302 VALUES(4,5); 236 236 ANALYZE; 237 237 explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; 238 238 } { 239 - 0 0 0 {SCAN TABLE t302 (~1 rows)} 240 - 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 239 + 0 0 0 {SCAN TABLE t302} 240 + 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} 241 241 } 242 242 do_execsql_test where3-3.1 { 243 243 explain query plan 244 244 SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; 245 245 } { 246 - 0 0 1 {SCAN TABLE t302 (~1 rows)} 247 - 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 246 + 0 0 1 {SCAN TABLE t302} 247 + 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} 248 248 } 249 249 250 250 # Verify that when there are multiple tables in a join which must be 251 251 # full table scans that the query planner attempts put the table with 252 252 # the fewest number of output rows as the outer loop. 253 253 # 254 254 do_execsql_test where3-4.0 { 255 255 CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c); 256 256 CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r); 257 257 CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z); 258 258 EXPLAIN QUERY PLAN 259 259 SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*'; 260 260 } { 261 - 0 0 2 {SCAN TABLE t402 (~500000 rows)} 262 - 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 263 - 0 2 1 {SCAN TABLE t401 (~1000000 rows)} 261 + 0 0 2 {SCAN TABLE t402} 262 + 0 1 0 {SCAN TABLE t400} 263 + 0 2 1 {SCAN TABLE t401} 264 264 } 265 265 do_execsql_test where3-4.1 { 266 266 EXPLAIN QUERY PLAN 267 267 SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*'; 268 268 } { 269 - 0 0 1 {SCAN TABLE t401 (~500000 rows)} 270 - 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 271 - 0 2 2 {SCAN TABLE t402 (~1000000 rows)} 269 + 0 0 1 {SCAN TABLE t401} 270 + 0 1 0 {SCAN TABLE t400} 271 + 0 2 2 {SCAN TABLE t402} 272 272 } 273 273 do_execsql_test where3-4.2 { 274 274 EXPLAIN QUERY PLAN 275 275 SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*'; 276 276 } { 277 - 0 0 0 {SCAN TABLE t400 (~500000 rows)} 278 - 0 1 1 {SCAN TABLE t401 (~1000000 rows)} 279 - 0 2 2 {SCAN TABLE t402 (~1000000 rows)} 277 + 0 0 0 {SCAN TABLE t400} 278 + 0 1 1 {SCAN TABLE t401} 279 + 0 2 2 {SCAN TABLE t402} 280 280 } 281 281 282 282 # Verify that a performance regression encountered by firefox 283 283 # has been fixed. 284 284 # 285 285 do_execsql_test where3-5.0 { 286 286 CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER, ................................................................................ 304 304 SELECT bbb.title AS tag_title 305 305 FROM aaa JOIN bbb ON bbb.id = aaa.parent 306 306 WHERE aaa.fk = 'constant' 307 307 AND LENGTH(bbb.title) > 0 308 308 AND bbb.parent = 4 309 309 ORDER BY bbb.title COLLATE NOCASE ASC; 310 310 } { 311 - 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 312 - 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 311 + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 312 + 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 313 313 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 314 314 } 315 315 do_execsql_test where3-5.1 { 316 316 EXPLAIN QUERY PLAN 317 317 SELECT bbb.title AS tag_title 318 318 FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent 319 319 WHERE aaa.fk = 'constant' 320 320 AND LENGTH(bbb.title) > 0 321 321 AND bbb.parent = 4 322 322 ORDER BY bbb.title COLLATE NOCASE ASC; 323 323 } { 324 - 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 325 - 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 324 + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 325 + 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 326 326 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 327 327 } 328 328 do_execsql_test where3-5.2 { 329 329 EXPLAIN QUERY PLAN 330 330 SELECT bbb.title AS tag_title 331 331 FROM bbb JOIN aaa ON bbb.id = aaa.parent 332 332 WHERE aaa.fk = 'constant' 333 333 AND LENGTH(bbb.title) > 0 334 334 AND bbb.parent = 4 335 335 ORDER BY bbb.title COLLATE NOCASE ASC; 336 336 } { 337 - 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 338 - 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 337 + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 338 + 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 339 339 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 340 340 } 341 341 do_execsql_test where3-5.3 { 342 342 EXPLAIN QUERY PLAN 343 343 SELECT bbb.title AS tag_title 344 344 FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent 345 345 WHERE aaa.fk = 'constant' 346 346 AND LENGTH(bbb.title) > 0 347 347 AND bbb.parent = 4 348 348 ORDER BY bbb.title COLLATE NOCASE ASC; 349 349 } { 350 - 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 351 - 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 350 + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 351 + 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 352 352 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 353 353 } 354 354 355 355 # Name resolution with NATURAL JOIN and USING 356 356 # 357 357 do_test where3-6.setup { 358 358 db eval {
Changes to test/where7.test.
23335 23335 FROM t302 JOIN t301 ON t302.c8 = t301.c8 23336 23336 WHERE t302.c2 = 19571 23337 23337 AND t302.c3 > 1287603136 23338 23338 AND (t301.c4 = 1407449685622784 23339 23339 OR t301.c8 = 1407424651264000) 23340 23340 ORDER BY t302.c5 LIMIT 200; 23341 23341 } { 23342 - 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 23343 - 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 23344 - 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 23342 + 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)} 23343 + 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} 23344 + 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)} 23345 23345 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 23346 23346 } 23347 23347 23348 23348 finish_test
Changes to test/where9.test.
358 358 359 359 ifcapable explain { 360 360 do_execsql_test where9-3.1 { 361 361 EXPLAIN QUERY PLAN 362 362 SELECT t2.a FROM t1, t2 363 363 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) 364 364 } { 365 - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 366 - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 367 - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} 365 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 366 + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 367 + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} 368 368 } 369 369 do_execsql_test where9-3.2 { 370 370 EXPLAIN QUERY PLAN 371 371 SELECT coalesce(t2.a,9999) 372 372 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f 373 373 WHERE t1.a=80 374 374 } { 375 - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 376 - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 377 - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} 375 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 376 + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 377 + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} 378 378 } 379 379 } 380 380 381 381 # Make sure that INDEXED BY and multi-index OR clauses play well with 382 382 # one another. 383 383 # 384 384 do_test where9-4.1 { ................................................................................ 449 449 ifcapable explain { 450 450 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because 451 451 # the former is an equality test which is expected to return fewer rows. 452 452 # 453 453 do_execsql_test where9-5.1 { 454 454 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) 455 455 } { 456 - 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~3 rows)} 457 - 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~3 rows)} 456 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} 457 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)} 458 458 } 459 459 460 460 # In contrast, b=1000 is preferred over any OR-clause. 461 461 # 462 462 do_execsql_test where9-5.2 { 463 463 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) 464 464 } { 465 - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)} 465 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)} 466 466 } 467 467 468 468 # Likewise, inequalities in an AND are preferred over inequalities in 469 469 # an OR. 470 470 # 471 471 do_execsql_test where9-5.3 { 472 472 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) 473 473 } { 474 - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)} 474 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)} 475 475 } 476 476 } 477 477 478 478 ############################################################################ 479 479 # Make sure OR-clauses work correctly on UPDATE and DELETE statements. 480 480 481 481 do_test where9-6.2.1 {
Changes to test/whereC.test.
63 63 do_execsql_test 1.$tn.1 $sql $res 64 64 do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC" [lsort -integer -inc $res] 65 65 do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec $res] 66 66 } 67 67 68 68 69 69 finish_test 70 -
Changes to test/whereE.test.
43 43 INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2; 44 44 INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2; 45 45 ALTER TABLE t2 ADD COLUMN z; 46 46 UPDATE t2 SET z=2; 47 47 CREATE UNIQUE INDEX t2zx ON t2(z,x); 48 48 49 49 EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; 50 -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} 50 +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} 51 51 do_execsql_test 1.2 { 52 52 EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; 53 -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} 53 +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} 54 54 do_execsql_test 1.3 { 55 55 ANALYZE; 56 56 EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; 57 -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} 57 +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} 58 58 do_execsql_test 1.4 { 59 59 EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; 60 -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/} 60 +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} 61 61 62 62 finish_test
Added tool/wherecosttest.c.
1 +/* 2 +** 2013-06-10 3 +** 4 +** The author disclaims copyright to this source code. In place of 5 +** a legal notice, here is a blessing: 6 +** 7 +** May you do good and not evil. 8 +** May you find forgiveness for yourself and forgive others. 9 +** May you share freely, never taking more than you give. 10 +** 11 +************************************************************************* 12 +** This file contains a simple command-line utility for converting from 13 +** integers and WhereCost values and back again and for doing simple 14 +** arithmetic operations (multiple and add) on WhereCost values. 15 +** 16 +** Usage: 17 +** 18 +** ./wherecosttest ARGS 19 +** 20 +** Arguments: 21 +** 22 +** 'x' Multiple the top two elements of the stack 23 +** '+' Add the top two elements of the stack 24 +** NUM Convert NUM from integer to WhereCost and push onto the stack 25 +** ^NUM Interpret NUM as a WhereCost and push onto stack. 26 +** 27 +** Examples: 28 +** 29 +** To convert 123 from WhereCost to integer: 30 +** 31 +** ./wherecosttest ^123 32 +** 33 +** To convert 123456 from integer to WhereCost: 34 +** 35 +** ./wherecosttest 123456 36 +** 37 +*/ 38 +#include <stdio.h> 39 +#include <stdlib.h> 40 +#include <ctype.h> 41 + 42 +typedef unsigned short int WhereCost; /* 10 times log2() */ 43 + 44 +WhereCost whereCostMultiply(WhereCost a, WhereCost b){ return a+b; } 45 +WhereCost whereCostAdd(WhereCost a, WhereCost b){ 46 + static const unsigned char x[] = { 47 + 10, 10, /* 0,1 */ 48 + 9, 9, /* 2,3 */ 49 + 8, 8, /* 4,5 */ 50 + 7, 7, 7, /* 6,7,8 */ 51 + 6, 6, 6, /* 9,10,11 */ 52 + 5, 5, 5, /* 12-14 */ 53 + 4, 4, 4, 4, /* 15-18 */ 54 + 3, 3, 3, 3, 3, 3, /* 19-24 */ 55 + 2, 2, 2, 2, 2, 2, 2, /* 25-31 */ 56 + }; 57 + if( a<b ){ WhereCost t = a; a = b; b = t; } 58 + if( a>b+49 ) return a; 59 + if( a>b+31 ) return a+1; 60 + return a+x[a-b]; 61 +} 62 +WhereCost whereCostFromInteger(int x){ 63 + static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 }; 64 + WhereCost y = 40; 65 + if( x<8 ){ 66 + if( x<2 ) return 0; 67 + while( x<8 ){ y -= 10; x <<= 1; } 68 + }else{ 69 + while( x>255 ){ y += 40; x >>= 4; } 70 + while( x>15 ){ y += 10; x >>= 1; } 71 + } 72 + return a[x&7] + y - 10; 73 +} 74 +static unsigned long int whereCostToInt(WhereCost x){ 75 + unsigned long int n; 76 + if( x<=10 ) return 1; 77 + n = x%10; 78 + x /= 10; 79 + if( n>=5 ) n -= 2; 80 + else if( n>=1 ) n -= 1; 81 + if( x>=3 ) return (n+8)<<(x-3); 82 + return (n+8)>>(3-x); 83 +} 84 + 85 +int main(int argc, char **argv){ 86 + int i; 87 + int n = 0; 88 + WhereCost a[100]; 89 + for(i=1; i<argc; i++){ 90 + const char *z = argv[i]; 91 + if( z[0]=='+' ){ 92 + if( n>=2 ){ 93 + a[n-2] = whereCostAdd(a[n-2],a[n-1]); 94 + n--; 95 + } 96 + }else if( z[0]=='x' ){ 97 + if( n>=2 ){ 98 + a[n-2] = whereCostMultiply(a[n-2],a[n-1]); 99 + n--; 100 + } 101 + }else if( z[0]=='^' ){ 102 + a[n++] = atoi(z+1); 103 + }else{ 104 + a[n++] = whereCostFromInteger(atoi(z)); 105 + } 106 + } 107 + for(i=n-1; i>=0; i--){ 108 + printf("%d (%lu)\n", a[i], whereCostToInt(a[i])); 109 + } 110 + return 0; 111 +}