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