/ Check-in [b777b109]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: b777b1097dcf9dfeb1b86c71e1b5f6918d68c975
User & Date: drh 2013-06-11 02:36:41
Context
2013-06-11
13:30
Fix the Parse.nQueryLoop state variable to work with NGQP. check-in: f1cac24f user: drh tags: nextgen-query-plan-exp
02:36
Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output. check-in: b777b109 user: drh tags: nextgen-query-plan-exp
02:32
Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes. Closed-Leaf check-in: 36373b85 user: drh tags: nextgen-query-plan-logcost
2013-06-10
14:56
Simplification and performance tweak to the high-speed NGQP bypass. check-in: 0f8a38ee user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree6.test.

    70     70   do_test rtree6-1.5 {
    71     71     rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
    72     72   } {Ca}
    73     73   
    74     74   do_eqp_test rtree6.2.1 {
    75     75     SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
    76     76   } {
    77         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} 
    78         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
           77  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} 
           78  +  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    79     79   }
    80     80   
    81     81   do_eqp_test rtree6.2.2 {
    82     82     SELECT * FROM t1,t2 WHERE k=ii AND x1<10
    83     83   } {
    84         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~25 rows)} 
    85         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
           84  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca} 
           85  +  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    86     86   }
    87     87   
    88     88   do_eqp_test rtree6.2.3 {
    89     89     SELECT * FROM t1,t2 WHERE k=ii
    90     90   } {
    91         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~25 rows)} 
    92         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
           91  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
           92  +  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    93     93   }
    94     94   
    95     95   do_eqp_test rtree6.2.4 {
    96     96     SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
    97     97   } {
    98         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~25 rows)} 
    99         -  0 1 1 {SCAN TABLE t2 (~100000 rows)}
           98  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb} 
           99  +  0 1 1 {SCAN TABLE t2}
   100    100   }
   101    101   
   102    102   do_eqp_test rtree6.2.5 {
   103    103     SELECT * FROM t1,t2 WHERE k=ii AND x1<v
   104    104   } {
   105         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~25 rows)} 
   106         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          105  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
          106  +  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
   107    107   }
   108    108   
   109    109   do_execsql_test rtree6-3.1 {
   110    110     CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
   111    111     INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
   112    112     SELECT * FROM t3 WHERE 
   113    113       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 

Changes to ext/rtree/rtree8.test.

   164    164       execsql { DELETE FROM t2 WHERE id = $i }
   165    165     }
   166    166     execsql COMMIT
   167    167   } {}
   168    168   
   169    169   
   170    170   finish_test
   171         -

Changes to src/select.c.

  1535   1535       if( NEVER(v==0) ) return;  /* VDBE should have already been allocated */
  1536   1536       if( sqlite3ExprIsInteger(p->pLimit, &n) ){
  1537   1537         sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
  1538   1538         VdbeComment((v, "LIMIT counter"));
  1539   1539         if( n==0 ){
  1540   1540           sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
  1541   1541         }else{
  1542         -        if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n;
         1542  +        if( p->nSelectRow > n ) p->nSelectRow = n;
  1543   1543         }
  1544   1544       }else{
  1545   1545         sqlite3ExprCode(pParse, p->pLimit, iLimit);
  1546   1546         sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
  1547   1547         VdbeComment((v, "LIMIT counter"));
  1548   1548         sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak);
  1549   1549       }
................................................................................
  1729   1729         rc = sqlite3Select(pParse, p, &dest);
  1730   1730         testcase( rc!=SQLITE_OK );
  1731   1731         pDelete = p->pPrior;
  1732   1732         p->pPrior = pPrior;
  1733   1733         p->nSelectRow += pPrior->nSelectRow;
  1734   1734         if( pPrior->pLimit
  1735   1735          && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit)
  1736         -       && p->nSelectRow > (double)nLimit 
         1736  +       && p->nSelectRow > nLimit 
  1737   1737         ){
  1738         -        p->nSelectRow = (double)nLimit;
         1738  +        p->nSelectRow = nLimit;
  1739   1739         }
  1740   1740         if( addr ){
  1741   1741           sqlite3VdbeJumpHere(v, addr);
  1742   1742         }
  1743   1743         break;
  1744   1744       }
  1745   1745       case TK_EXCEPT:
................................................................................
  3880   3880   #ifndef SQLITE_OMIT_EXPLAIN
  3881   3881   static void explainSimpleCount(
  3882   3882     Parse *pParse,                  /* Parse context */
  3883   3883     Table *pTab,                    /* Table being queried */
  3884   3884     Index *pIdx                     /* Index used to optimize scan, or NULL */
  3885   3885   ){
  3886   3886     if( pParse->explain==2 ){
  3887         -    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)",
         3887  +    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
  3888   3888           pTab->zName, 
  3889         -        pIdx ? "USING COVERING INDEX " : "",
  3890         -        pIdx ? pIdx->zName : "",
  3891         -        pTab->nRowEst
         3889  +        pIdx ? " USING COVERING INDEX " : "",
         3890  +        pIdx ? pIdx->zName : ""
  3892   3891       );
  3893   3892       sqlite3VdbeAddOp4(
  3894   3893           pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
  3895   3894       );
  3896   3895     }
  3897   3896   }
  3898   3897   #else
................................................................................
  4235   4234     if( pDest->eDest==SRT_EphemTab ){
  4236   4235       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr);
  4237   4236     }
  4238   4237   
  4239   4238     /* Set the limiter.
  4240   4239     */
  4241   4240     iEnd = sqlite3VdbeMakeLabel(v);
  4242         -  p->nSelectRow = (double)LARGEST_INT64;
         4241  +  p->nSelectRow = LARGEST_INT64;
  4243   4242     computeLimitRegisters(pParse, p, iEnd);
  4244   4243     if( p->iLimit==0 && addrSortIndex>=0 ){
  4245   4244       sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen;
  4246   4245       p->selFlags |= SF_UseSorter;
  4247   4246     }
  4248   4247   
  4249   4248     /* Open a virtual index to use for the distinct set.
................................................................................
  4316   4315   
  4317   4316         for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
  4318   4317           pItem->iAlias = 0;
  4319   4318         }
  4320   4319         for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
  4321   4320           pItem->iAlias = 0;
  4322   4321         }
  4323         -      if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100;
         4322  +      if( p->nSelectRow>100 ) p->nSelectRow = 100;
  4324   4323       }else{
  4325         -      p->nSelectRow = (double)1;
         4324  +      p->nSelectRow = 1;
  4326   4325       }
  4327   4326   
  4328   4327    
  4329   4328       /* Create a label to jump to when we want to abort the query */
  4330   4329       addrEnd = sqlite3VdbeMakeLabel(v);
  4331   4330   
  4332   4331       /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in

Changes to src/sqliteInt.h.

  2038   2038   */
  2039   2039   struct Select {
  2040   2040     ExprList *pEList;      /* The fields of the result */
  2041   2041     u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  2042   2042     u16 selFlags;          /* Various SF_* values */
  2043   2043     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  2044   2044     int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
  2045         -  double nSelectRow;     /* Estimated number of result rows */
         2045  +  u64 nSelectRow;        /* Estimated number of result rows */
  2046   2046     SrcList *pSrc;         /* The FROM clause */
  2047   2047     Expr *pWhere;          /* The WHERE clause */
  2048   2048     ExprList *pGroupBy;    /* The GROUP BY clause */
  2049   2049     Expr *pHaving;         /* The HAVING clause */
  2050   2050     ExprList *pOrderBy;    /* The ORDER BY clause */
  2051   2051     Select *pPrior;        /* Prior select in a compound select statement */
  2052   2052     Select *pNext;         /* Next select to the left in a compound */
................................................................................
  2222   2222     TableLock *aTableLock; /* Required table locks for shared-cache mode */
  2223   2223   #endif
  2224   2224     AutoincInfo *pAinc;  /* Information about AUTOINCREMENT counters */
  2225   2225   
  2226   2226     /* Information used while coding trigger programs. */
  2227   2227     Parse *pToplevel;    /* Parse structure for main program (or NULL) */
  2228   2228     Table *pTriggerTab;  /* Table triggers are being coded for */
  2229         -  double nQueryLoop;   /* Estimated number of iterations of a query */
         2229  +  u32 nQueryLoop;      /* Estimated number of iterations of a query */
  2230   2230     u32 oldmask;         /* Mask of old.* columns referenced */
  2231   2231     u32 newmask;         /* Mask of new.* columns referenced */
  2232   2232     u8 eTriggerOp;       /* TK_UPDATE, TK_INSERT or TK_DELETE */
  2233   2233     u8 eOrconf;          /* Default ON CONFLICT policy for trigger steps */
  2234   2234     u8 disableTriggers;  /* True to disable triggers */
  2235   2235   
  2236   2236     /* Above is constant between recursions.  Below is reset before and after
................................................................................
  2792   2792   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  2793   2793   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
  2794   2794   #endif
  2795   2795   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  2796   2796   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  2797   2797   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  2798   2798   void sqlite3WhereEnd(WhereInfo*);
  2799         -double sqlite3WhereOutputRowCount(WhereInfo*);
         2799  +u64 sqlite3WhereOutputRowCount(WhereInfo*);
  2800   2800   int sqlite3WhereIsDistinct(WhereInfo*);
  2801   2801   int sqlite3WhereIsOrdered(WhereInfo*);
  2802   2802   int sqlite3WhereContinueLabel(WhereInfo*);
  2803   2803   int sqlite3WhereBreakLabel(WhereInfo*);
  2804   2804   int sqlite3WhereOkOnePass(WhereInfo*);
  2805   2805   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  2806   2806   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);

Changes to src/where.c.

    41     41   typedef struct WhereAndInfo WhereAndInfo;
    42     42   typedef struct WhereLevel WhereLevel;
    43     43   typedef struct WhereLoop WhereLoop;
    44     44   typedef struct WherePath WherePath;
    45     45   typedef struct WhereTerm WhereTerm;
    46     46   typedef struct WhereLoopBuilder WhereLoopBuilder;
    47     47   typedef struct WhereScan WhereScan;
    48         -typedef float WhereCost;
           48  +
           49  +/*
           50  +** Cost X is tracked as 10*log2(X) stored in a 16-bit integer.  The
           51  +** maximum cost is 64*(2**63) which becomes 6900.  So all costs can be
           52  +** be stored in a 16-bit unsigned integer without risk of overflow.
           53  +*/
           54  +typedef unsigned short int WhereCost;
    49     55   
    50     56   /*
    51     57   ** For each nested loop in a WHERE clause implementation, the WhereInfo
    52     58   ** structure contains a single instance of this structure.  This structure
    53     59   ** is intended to be private to the where.c module and should not be
    54     60   ** access or modified by other modules.
    55     61   **
................................................................................
   397    403   #define WHERE_VIRTUALTABLE 0x00000400  /* WhereLoop.u.vtab is valid */
   398    404   #define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
   399    405   #define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
   400    406   #define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
   401    407   #define WHERE_TEMP_INDEX   0x00004000  /* Uses an ephemeral index */
   402    408   #define WHERE_COVER_SCAN   0x00008000  /* Full scan of a covering index */
   403    409   
          410  +
          411  +/* Convert a WhereCost value (10 times log2(X)) into its integer value X.
          412  +*/
          413  +static u64 whereCostToInt(WhereCost x){
          414  +  u64 n;
          415  +  if( x<=10 ) return 1;
          416  +  n = x%10;
          417  +  x /= 10;
          418  +  if( n>=5 ) n -= 2;
          419  +  else if( n>=1 ) n -= 1;
          420  +  if( x>=3 ) return (n+8)<<(x-3);
          421  +  return (n+8)>>(3-x);
          422  +}
          423  +
   404    424   /*
   405    425   ** Return the estimated number of output rows from a WHERE clause
   406    426   */
   407         -double sqlite3WhereOutputRowCount(WhereInfo *pWInfo){
   408         -  return (double)pWInfo->nRowOut;
          427  +u64 sqlite3WhereOutputRowCount(WhereInfo *pWInfo){
          428  +  return whereCostToInt(pWInfo->nRowOut);
   409    429   }
   410    430   
   411    431   /*
   412    432   ** Return one of the WHERE_DISTINCT_xxxxx values to indicate how this
   413    433   ** WHERE clause returns outputs for DISTINCT processing.
   414    434   */
   415    435   int sqlite3WhereIsDistinct(WhereInfo *pWInfo){
................................................................................
  1816   1836         return 1;
  1817   1837       }
  1818   1838     }
  1819   1839   
  1820   1840     return 0;
  1821   1841   }
  1822   1842   
         1843  +/* 
         1844  +** The sum of two WhereCosts
         1845  +*/
         1846  +static WhereCost whereCostAdd(WhereCost a, WhereCost b){
         1847  +  static const unsigned char x[] = {
         1848  +     10, 10,                         /* 0,1 */
         1849  +      9, 9,                          /* 2,3 */
         1850  +      8, 8,                          /* 4,5 */
         1851  +      7, 7, 7,                       /* 6,7,8 */
         1852  +      6, 6, 6,                       /* 9,10,11 */
         1853  +      5, 5, 5,                       /* 12-14 */
         1854  +      4, 4, 4, 4,                    /* 15-18 */
         1855  +      3, 3, 3, 3, 3, 3,              /* 19-24 */
         1856  +      2, 2, 2, 2, 2, 2, 2,           /* 25-31 */
         1857  +  };
         1858  +  if( a>=b ){
         1859  +    if( a>b+49 ) return a;
         1860  +    if( a>b+31 ) return a+1;
         1861  +    return a+x[a-b];
         1862  +  }else{
         1863  +    if( b>a+49 ) return b;
         1864  +    if( b>a+31 ) return b+1;
         1865  +    return b+x[b-a];
         1866  +  }
         1867  +}
         1868  +
         1869  +/*
         1870  +** Convert an integer into a WhereCost
         1871  +*/
         1872  +static WhereCost whereCostFromInt(tRowcnt x){
         1873  +  static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 };
         1874  +  WhereCost y = 40;
         1875  +  if( x<8 ){
         1876  +    if( x<2 ) return 0;
         1877  +    while( x<8 ){  y -= 10; x <<= 1; }
         1878  +  }else{
         1879  +    while( x>255 ){ y += 40; x >>= 4; }
         1880  +    while( x>15 ){  y += 10; x >>= 1; }
         1881  +  }
         1882  +  return a[x&7] + y - 10;
         1883  +}
         1884  +
         1885  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         1886  +/*
         1887  +** Convert a double (as received from xBestIndex of a virtual table)
         1888  +** into a WhereCost
         1889  +*/
         1890  +static WhereCost whereCostFromDouble(double x){
         1891  +  u64 a;
         1892  +  WhereCost e;
         1893  +  assert( sizeof(x)==8 && sizeof(a)==8 );
         1894  +  if( x<=1 ) return 0;
         1895  +  if( x<=2000000000 ) return whereCostFromInt((tRowcnt)x);
         1896  +  memcpy(&a, &x, 8);
         1897  +  e = (a>>52) - 1022;
         1898  +  return e*10;
         1899  +}
         1900  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
         1901  +
  1823   1902   /*
  1824   1903   ** Prepare a crude estimate of the logarithm of the input value.
  1825   1904   ** The results need not be exact.  This is only used for estimating
  1826   1905   ** the total cost of performing operations with O(logN) or O(NlogN)
  1827   1906   ** complexity.  Because N is just a guess, it is no great tragedy if
  1828   1907   ** logN is a little off.
  1829   1908   */
  1830   1909   static WhereCost estLog(WhereCost N){
  1831         -  u32 a;
  1832         -  assert( sizeof(WhereCost)==4 );  /* 32-bit float input */
  1833         -  if( N<=0.0 ) return 0.0;
  1834         -  memcpy(&a, &N, 4);
  1835         -  return ((a >>= 23)-127)*0.3;
         1910  +  return whereCostFromInt(N) - 33;
  1836   1911   }
  1837   1912   
  1838   1913   /*
  1839   1914   ** Two routines for printing the content of an sqlite3_index_info
  1840   1915   ** structure.  Used for testing and debugging only.  If neither
  1841   1916   ** SQLITE_TEST or SQLITE_DEBUG are defined, then these routines
  1842   1917   ** are no-ops.
................................................................................
  2236   2311     tRowcnt *aStat              /* OUT: stats written here */
  2237   2312   ){
  2238   2313     tRowcnt n;
  2239   2314     IndexSample *aSample;
  2240   2315     int i, eType;
  2241   2316     int isEq = 0;
  2242   2317     i64 v;
  2243         -  WhereCost r, rS;
         2318  +  double r, rS;
  2244   2319   
  2245   2320     assert( roundUp==0 || roundUp==1 );
  2246   2321     assert( pIdx->nSample>0 );
  2247   2322     if( pVal==0 ) return SQLITE_ERROR;
  2248   2323     n = pIdx->aiRowEst[0];
  2249   2324     aSample = pIdx->aSample;
  2250   2325     eType = sqlite3_value_type(pVal);
................................................................................
  2492   2567         ){
  2493   2568           iUpper = a[0];
  2494   2569           if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1];
  2495   2570         }
  2496   2571         sqlite3ValueFree(pRangeVal);
  2497   2572       }
  2498   2573       if( rc==SQLITE_OK ){
  2499         -      if( iUpper<=iLower ){
  2500         -        *pRangeDiv = (WhereCost)p->aiRowEst[0];
  2501         -      }else{
  2502         -        *pRangeDiv = (WhereCost)p->aiRowEst[0]/(WhereCost)(iUpper - iLower);
         2574  +      WhereCost iBase = whereCostFromInt(p->aiRowEst[0]);
         2575  +      if( iUpper>iLower ){
         2576  +        iBase -= whereCostFromInt(iUpper - iLower);
  2503   2577         }
         2578  +      *pRangeDiv = iBase;
  2504   2579         /*WHERETRACE(("range scan regions: %u..%u  div=%g\n",
  2505   2580                     (u32)iLower, (u32)iUpper, *pRangeDiv));*/
  2506   2581         return SQLITE_OK;
  2507   2582       }
  2508   2583     }
  2509   2584   #else
  2510   2585     UNUSED_PARAMETER(pParse);
  2511   2586     UNUSED_PARAMETER(p);
  2512   2587     UNUSED_PARAMETER(nEq);
  2513   2588   #endif
  2514   2589     assert( pLower || pUpper );
  2515         -  *pRangeDiv = (WhereCost)1;
  2516         -  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (WhereCost)4;
  2517         -  if( pUpper ) *pRangeDiv *= (WhereCost)4;
         2590  +  *pRangeDiv = 0;
         2591  +  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ){
         2592  +    *pRangeDiv += 20;  assert( 20==whereCostFromInt(4) );
         2593  +  }
         2594  +  if( pUpper ){
         2595  +    *pRangeDiv += 20;  assert( 20==whereCostFromInt(4) );
         2596  +  }
  2518   2597     return rc;
  2519   2598   }
  2520   2599   
  2521   2600   #ifdef SQLITE_ENABLE_STAT3
  2522   2601   /*
  2523   2602   ** Estimate the number of rows that will be returned based on
  2524   2603   ** an equality constraint x=VALUE and where that VALUE occurs in
................................................................................
  2536   2615   ** for a UTF conversion required for comparison.  The error is stored
  2537   2616   ** in the pParse structure.
  2538   2617   */
  2539   2618   static int whereEqualScanEst(
  2540   2619     Parse *pParse,       /* Parsing & code generating context */
  2541   2620     Index *p,            /* The index whose left-most column is pTerm */
  2542   2621     Expr *pExpr,         /* Expression for VALUE in the x=VALUE constraint */
  2543         -  WhereCost *pnRow     /* Write the revised row estimate here */
         2622  +  tRowcnt *pnRow       /* Write the revised row estimate here */
  2544   2623   ){
  2545   2624     sqlite3_value *pRhs = 0;  /* VALUE on right-hand side of pTerm */
  2546   2625     u8 aff;                   /* Column affinity */
  2547   2626     int rc;                   /* Subfunction return code */
  2548   2627     tRowcnt a[2];             /* Statistics */
  2549   2628   
  2550   2629     assert( p->aSample!=0 );
................................................................................
  2585   2664   ** for a UTF conversion required for comparison.  The error is stored
  2586   2665   ** in the pParse structure.
  2587   2666   */
  2588   2667   static int whereInScanEst(
  2589   2668     Parse *pParse,       /* Parsing & code generating context */
  2590   2669     Index *p,            /* The index whose left-most column is pTerm */
  2591   2670     ExprList *pList,     /* The value list on the RHS of "x IN (v1,v2,v3,...)" */
  2592         -  WhereCost *pnRow     /* Write the revised row estimate here */
         2671  +  tRowcnt *pnRow       /* Write the revised row estimate here */
  2593   2672   ){
  2594         -  int rc = SQLITE_OK;         /* Subfunction return code */
  2595         -  WhereCost nEst;                /* Number of rows for a single term */
  2596         -  WhereCost nRowEst = (WhereCost)0; /* New estimate of the number of rows */
  2597         -  int i;                      /* Loop counter */
         2673  +  int rc = SQLITE_OK;     /* Subfunction return code */
         2674  +  tRowcnt nEst;           /* Number of rows for a single term */
         2675  +  tRowcnt nRowEst = 0;    /* New estimate of the number of rows */
         2676  +  int i;                  /* Loop counter */
  2598   2677   
  2599   2678     assert( p->aSample!=0 );
  2600   2679     for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){
  2601   2680       nEst = p->aiRowEst[0];
  2602   2681       rc = whereEqualScanEst(pParse, p, pList->a[i].pExpr, &nEst);
  2603   2682       nRowEst += nEst;
  2604   2683     }
................................................................................
  2978   3057     u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
  2979   3058   ){
  2980   3059     if( pParse->explain==2 ){
  2981   3060       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  2982   3061       Vdbe *v = pParse->pVdbe;      /* VM being constructed */
  2983   3062       sqlite3 *db = pParse->db;     /* Database handle */
  2984   3063       char *zMsg;                   /* Text to add to EQP output */
  2985         -    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
  2986   3064       int iId = pParse->iSelectId;  /* Select id (left-most output column) */
  2987   3065       int isSearch;                 /* True for a SEARCH. False for SCAN. */
  2988   3066       WhereLoop *pLoop;             /* The controlling WhereLoop object */
  2989   3067       u32 flags;                    /* Flags that describe this loop */
  2990   3068   
  2991   3069       pLoop = pLevel->pWLoop;
  2992   3070       flags = pLoop->wsFlags;
................................................................................
  3033   3111       }
  3034   3112   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3035   3113       else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
  3036   3114         zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
  3037   3115                     pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
  3038   3116       }
  3039   3117   #endif
  3040         -    if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){
  3041         -      testcase( wctrlFlags & WHERE_ORDERBY_MIN );
  3042         -      nRow = 1;
  3043         -    }else{
  3044         -      nRow = (sqlite3_int64)pLoop->nOut;
  3045         -    }
  3046         -    zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow);
         3118  +    zMsg = sqlite3MAppendf(db, zMsg, "%s", zMsg);
  3047   3119       sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  3048   3120     }
  3049   3121   }
  3050   3122   #else
  3051   3123   # define explainOneScan(u,v,w,x,y,z)
  3052   3124   #endif /* SQLITE_OMIT_EXPLAIN */
  3053   3125   
................................................................................
  3824   3896   }
  3825   3897   
  3826   3898   #ifdef WHERETRACE_ENABLED
  3827   3899   /*
  3828   3900   ** Print a WhereLoop object for debugging purposes
  3829   3901   */
  3830   3902   static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){
  3831         -  int nb = 2*((pTabList->nSrc+15)/16);
         3903  +  int nb = 1+(pTabList->nSrc+7)/8;
  3832   3904     struct SrcList_item *pItem = pTabList->a + p->iTab;
  3833   3905     Table *pTab = pItem->pTab;
  3834   3906     sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId,
  3835   3907                        p->iTab, nb, p->maskSelf, nb, p->prereq);
  3836   3908     sqlite3DebugPrintf(" %8s",
  3837   3909                        pItem->zAlias ? pItem->zAlias : pTab->zName);
  3838   3910     if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
................................................................................
  3856   3928       }else{
  3857   3929         z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
  3858   3930       }
  3859   3931       sqlite3DebugPrintf(" %-15s", z);
  3860   3932       sqlite3_free(z);
  3861   3933     }
  3862   3934     sqlite3DebugPrintf(" fg %05x N %d", p->wsFlags, p->nLTerm);
  3863         -  sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n",
  3864         -                     p->prereq, p->rSetup, p->rRun, p->nOut);
         3935  +  sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
  3865   3936   }
  3866   3937   #endif
  3867   3938   
  3868   3939   /*
  3869   3940   ** Convert bulk memory into a valid WhereLoop that can be passed
  3870   3941   ** to whereLoopClear harmlessly.
  3871   3942   */
................................................................................
  3991   4062     /* If pBuilder->pBest is defined, then only keep track of the single
  3992   4063     ** best WhereLoop.  pBuilder->pBest->maskSelf==0 indicates that no
  3993   4064     ** prior WhereLoops have been evaluated and that the current pTemplate
  3994   4065     ** is therefore the first and hence the best and should be retained.
  3995   4066     */
  3996   4067     if( (p = pBuilder->pBest)!=0 ){
  3997   4068       if( p->maskSelf!=0 ){
  3998         -      WhereCost rCost = p->rRun + p->rSetup;
  3999         -      WhereCost rTemplate = pTemplate->rRun + pTemplate->rSetup;
         4069  +      WhereCost rCost = whereCostAdd(p->rRun,p->rSetup);
         4070  +      WhereCost rTemplate = whereCostAdd(pTemplate->rRun,pTemplate->rSetup);
  4000   4071         if( rCost < rTemplate ){
  4001   4072           goto whereLoopInsert_noop;
  4002   4073         }
  4003   4074         if( rCost == rTemplate && p->prereq <= pTemplate->prereq ){
  4004   4075           goto whereLoopInsert_noop;
  4005   4076         }
  4006   4077       }
  4007         -    whereLoopXfer(db, p, pTemplate);
  4008   4078   #if WHERETRACE_ENABLED
  4009   4079       if( sqlite3WhereTrace & 0x8 ){
  4010         -      sqlite3DebugPrintf("ins-best: ");
         4080  +      sqlite3DebugPrintf(p->maskSelf==0 ? "ins-init: " : "ins-best: ");
  4011   4081         whereLoopPrint(pTemplate, pWInfo->pTabList);
  4012   4082       }
  4013   4083   #endif
         4084  +    whereLoopXfer(db, p, pTemplate);
  4014   4085       return SQLITE_OK;
  4015   4086     }
  4016   4087   
  4017   4088     /* Search for an existing WhereLoop to overwrite, or which takes
  4018   4089     ** priority over pTemplate.
  4019   4090     */
  4020   4091     for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){
................................................................................
  4029   4100          && (pTemplate->wsFlags & WHERE_INDEXED)!=0
  4030   4101          && p->u.btree.pIndex==pTemplate->u.btree.pIndex
  4031   4102          && p->prereq==pTemplate->prereq
  4032   4103         ){
  4033   4104           /* Overwrite an existing WhereLoop with an similar one that uses
  4034   4105           ** more terms of the index */
  4035   4106           pNext = p->pNextLoop;
         4107  +        break;
         4108  +      }else if( p->nOut>pTemplate->nOut
         4109  +       && p->rSetup==pTemplate->rSetup
         4110  +       && p->rRun==pTemplate->rRun
         4111  +      ){
         4112  +        /* Overwrite an existing WhereLoop with the same cost but more
         4113  +        ** outputs */
         4114  +        pNext = p->pNextLoop;
  4036   4115           break;
  4037   4116         }else{
  4038   4117           /* pTemplate is not helpful.
  4039   4118           ** Return without changing or adding anything */
  4040   4119           goto whereLoopInsert_noop;
  4041   4120         }
  4042   4121       }
................................................................................
  4080   4159     }
  4081   4160     return SQLITE_OK;
  4082   4161   
  4083   4162     /* Jump here if the insert is a no-op */
  4084   4163   whereLoopInsert_noop:
  4085   4164   #if WHERETRACE_ENABLED
  4086   4165     if( sqlite3WhereTrace & 0x8 ){
  4087         -    sqlite3DebugPrintf("ins-noop: ");
         4166  +    sqlite3DebugPrintf(pBuilder->pBest ? "ins-skip: " : "ins-noop: ");
  4088   4167       whereLoopPrint(pTemplate, pWInfo->pTabList);
  4089   4168     }
  4090   4169   #endif
  4091   4170     return SQLITE_OK;  
  4092   4171   }
  4093   4172   
  4094   4173   /*
................................................................................
  4098   4177   ** If pProbe->tnum==0, that means pIndex is a fake index used for the
  4099   4178   ** INTEGER PRIMARY KEY.
  4100   4179   */
  4101   4180   static int whereLoopAddBtreeIndex(
  4102   4181     WhereLoopBuilder *pBuilder,     /* The WhereLoop factory */
  4103   4182     struct SrcList_item *pSrc,      /* FROM clause term being analyzed */
  4104   4183     Index *pProbe,                  /* An index on pSrc */
  4105         -  int nInMul                      /* Number of iterations due to IN */
         4184  +  WhereCost nInMul                /* log(Number of iterations due to IN) */
  4106   4185   ){
  4107   4186     WhereInfo *pWInfo = pBuilder->pWInfo;  /* WHERE analyse context */
  4108   4187     Parse *pParse = pWInfo->pParse;        /* Parsing context */
  4109   4188     sqlite3 *db = pParse->db;       /* Database connection malloc context */
  4110   4189     WhereLoop *pNew;                /* Template WhereLoop under construction */
  4111   4190     WhereTerm *pTerm;               /* A WhereTerm under consideration */
  4112   4191     int opMask;                     /* Valid operators for constraints */
................................................................................
  4114   4193     Bitmask saved_prereq;           /* Original value of pNew->prereq */
  4115   4194     u16 saved_nLTerm;               /* Original value of pNew->nLTerm */
  4116   4195     int saved_nEq;                  /* Original value of pNew->u.btree.nEq */
  4117   4196     u32 saved_wsFlags;              /* Original value of pNew->wsFlags */
  4118   4197     WhereCost saved_nOut;           /* Original value of pNew->nOut */
  4119   4198     int iCol;                       /* Index of the column in the table */
  4120   4199     int rc = SQLITE_OK;             /* Return code */
  4121         -  tRowcnt iRowEst;                /* Estimated index selectivity */
         4200  +  WhereCost nRowEst;              /* Estimated index selectivity */
  4122   4201     WhereCost rLogSize;             /* Logarithm of table size */
  4123   4202     WhereTerm *pTop, *pBtm;         /* Top and bottom range constraints */
  4124   4203   
  4125   4204     pNew = pBuilder->pNew;
  4126   4205     if( db->mallocFailed ) return SQLITE_NOMEM;
  4127   4206   
  4128   4207     assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
................................................................................
  4135   4214     }else{
  4136   4215       opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
  4137   4216     }
  4138   4217     if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
  4139   4218   
  4140   4219     if( pNew->u.btree.nEq < pProbe->nColumn ){
  4141   4220       iCol = pProbe->aiColumn[pNew->u.btree.nEq];
  4142         -    iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1];
         4221  +    nRowEst = whereCostFromInt(pProbe->aiRowEst[pNew->u.btree.nEq+1]);
  4143   4222     }else{
  4144   4223       iCol = -1;
  4145         -    iRowEst = 1;
         4224  +    nRowEst = 0;
  4146   4225     }
  4147   4226     pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
  4148   4227                           opMask, pProbe);
  4149   4228     saved_nEq = pNew->u.btree.nEq;
  4150   4229     saved_nLTerm = pNew->nLTerm;
  4151   4230     saved_wsFlags = pNew->wsFlags;
  4152   4231     saved_prereq = pNew->prereq;
  4153   4232     saved_nOut = pNew->nOut;
  4154         -  pNew->rSetup = (WhereCost)0;
  4155         -  rLogSize = estLog(pProbe->aiRowEst[0]);
         4233  +  pNew->rSetup = 0;
         4234  +  rLogSize = estLog(whereCostFromInt(pProbe->aiRowEst[0]));
  4156   4235     for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
  4157         -    int nIn = 1;
         4236  +    int nIn = 0;
  4158   4237       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4159   4238       pNew->wsFlags = saved_wsFlags;
  4160   4239       pNew->u.btree.nEq = saved_nEq;
  4161   4240       pNew->nLTerm = saved_nLTerm;
  4162   4241       if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
  4163   4242       pNew->aLTerm[pNew->nLTerm++] = pTerm;
  4164   4243       pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
  4165   4244       pNew->rRun = rLogSize;
  4166   4245       if( pTerm->eOperator & WO_IN ){
  4167   4246         Expr *pExpr = pTerm->pExpr;
  4168   4247         pNew->wsFlags |= WHERE_COLUMN_IN;
  4169   4248         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  4170   4249           /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
  4171         -        nIn = 25;
         4250  +        nIn = 46;  /* whereCostFromInt(25) */
  4172   4251         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4173   4252           /* "x IN (value, value, ...)" */
  4174         -        nIn = pExpr->x.pList->nExpr;
         4253  +        nIn = whereCostFromInt(pExpr->x.pList->nExpr);
  4175   4254         }
  4176         -      pNew->rRun *= nIn;
         4255  +      pNew->rRun += nIn;
  4177   4256         pNew->u.btree.nEq++;
  4178         -      pNew->nOut = (WhereCost)iRowEst * nInMul * nIn;
         4257  +      pNew->nOut = nRowEst + nInMul + nIn;
  4179   4258       }else if( pTerm->eOperator & (WO_EQ) ){
  4180   4259         assert( (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN))!=0
  4181         -                  || nInMul==1 );
         4260  +                  || nInMul==0 );
  4182   4261         pNew->wsFlags |= WHERE_COLUMN_EQ;
  4183   4262         if( iCol<0  
  4184         -       || (pProbe->onError!=OE_None && nInMul==1
         4263  +       || (pProbe->onError!=OE_None && nInMul==0
  4185   4264              && pNew->u.btree.nEq==pProbe->nColumn-1)
  4186   4265         ){
  4187   4266           testcase( pNew->wsFlags & WHERE_COLUMN_IN );
  4188   4267           pNew->wsFlags |= WHERE_ONEROW;
  4189   4268         }
  4190   4269         pNew->u.btree.nEq++;
  4191         -      pNew->nOut = (WhereCost)iRowEst * nInMul;
         4270  +      pNew->nOut = nRowEst + nInMul;
  4192   4271       }else if( pTerm->eOperator & (WO_ISNULL) ){
  4193   4272         pNew->wsFlags |= WHERE_COLUMN_NULL;
  4194   4273         pNew->u.btree.nEq++;
  4195         -      nIn = 2;  /* Assume IS NULL matches two rows */
  4196         -      pNew->nOut = (WhereCost)iRowEst * nInMul * nIn;
         4274  +      nIn = 10;  /* Assume IS NULL matches two rows */
         4275  +      pNew->nOut = nRowEst + nInMul + nIn;
  4197   4276       }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
  4198   4277         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
  4199   4278         pBtm = pTerm;
  4200   4279         pTop = 0;
  4201   4280       }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
  4202   4281         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
  4203   4282         pTop = pTerm;
................................................................................
  4205   4284                        pNew->aLTerm[pNew->nLTerm-2] : 0;
  4206   4285       }
  4207   4286       if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
  4208   4287         /* Adjust nOut and rRun for STAT3 range values */
  4209   4288         WhereCost rDiv;
  4210   4289         whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq,
  4211   4290                           pBtm, pTop, &rDiv);
  4212         -      pNew->nOut = saved_nOut/rDiv;
         4291  +      pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10;
  4213   4292       }
  4214   4293   #ifdef SQLITE_ENABLE_STAT3
  4215   4294       if( pNew->u.btree.nEq==1 && pProbe->nSample ){
         4295  +      tRowcnt nOut = 0;
  4216   4296         if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){
  4217         -        rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight,
  4218         -                               &pNew->nOut);
         4297  +        rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &nOut);
  4219   4298         }else if( (pTerm->eOperator & WO_IN)
  4220   4299                &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
  4221         -        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList,
  4222         -                             &pNew->nOut);
  4223         -
         4300  +        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
  4224   4301         }
         4302  +      pNew->nOut = whereCostFromInt(nOut);
  4225   4303       }
  4226   4304   #endif
  4227   4305       if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
  4228         -      pNew->rRun += pNew->nOut;  /* Unit step cost to reach each row */
         4306  +      /* Step cost for each output row */
         4307  +      pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
  4229   4308       }else{
  4230   4309         /* Each row involves a step of the index, then a binary search of
  4231   4310         ** the main table */
  4232         -      pNew->rRun += pNew->nOut*(1 + rLogSize);
         4311  +      WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1);
         4312  +      pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
  4233   4313       }
  4234   4314       /* TBD: Adjust nOut for additional constraints */
  4235   4315       rc = whereLoopInsert(pBuilder, pNew);
  4236   4316       if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
  4237   4317        && pNew->u.btree.nEq<=pProbe->nColumn
  4238   4318        && pProbe->zName!=0
  4239   4319       ){
  4240         -      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn);
         4320  +      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
  4241   4321       }
  4242   4322     }
  4243   4323     pNew->prereq = saved_prereq;
  4244   4324     pNew->u.btree.nEq = saved_nEq;
  4245   4325     pNew->wsFlags = saved_wsFlags;
  4246   4326     pNew->nOut = saved_nOut;
  4247   4327     pNew->nLTerm = saved_nLTerm;
................................................................................
  4309   4389     int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  4310   4390     SrcList *pTabList;          /* The FROM clause */
  4311   4391     struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  4312   4392     WhereLoop *pNew;            /* Template WhereLoop object */
  4313   4393     int rc = SQLITE_OK;         /* Return code */
  4314   4394     int iSortIdx = 1;           /* Index number */
  4315   4395     int b;                      /* A boolean value */
  4316         -  WhereCost rSize;               /* number of rows in the table */
  4317         -  WhereCost rLogSize;            /* Logarithm of the number of rows in the table */
         4396  +  WhereCost rSize;            /* number of rows in the table */
         4397  +  WhereCost rLogSize;         /* Logarithm of the number of rows in the table */
  4318   4398     
  4319   4399     pNew = pBuilder->pNew;
  4320   4400     pWInfo = pBuilder->pWInfo;
  4321   4401     pTabList = pWInfo->pTabList;
  4322   4402     pSrc = pTabList->a + pNew->iTab;
  4323   4403     assert( !IsVirtual(pSrc->pTab) );
  4324   4404   
................................................................................
  4343   4423       if( pSrc->notIndexed==0 ){
  4344   4424         /* The real indices of the table are only considered if the
  4345   4425         ** NOT INDEXED qualifier is omitted from the FROM clause */
  4346   4426         sPk.pNext = pFirst;
  4347   4427       }
  4348   4428       pProbe = &sPk;
  4349   4429     }
  4350         -  rSize = (WhereCost)pSrc->pTab->nRowEst;
         4430  +  rSize = whereCostFromInt(pSrc->pTab->nRowEst);
  4351   4431     rLogSize = estLog(rSize);
  4352   4432   
  4353   4433     /* Automatic indexes */
  4354   4434     if( !pBuilder->pBest
  4355   4435      && pTabList->nSrc>1
  4356   4436      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 
  4357   4437      && !pSrc->viaCoroutine
................................................................................
  4365   4445       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4366   4446         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4367   4447         if( termCanDriveIndex(pTerm, pSrc, 0) ){
  4368   4448           pNew->u.btree.nEq = 1;
  4369   4449           pNew->u.btree.pIndex = 0;
  4370   4450           pNew->nLTerm = 1;
  4371   4451           pNew->aLTerm[0] = pTerm;
  4372         -        pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst;
  4373         -        pNew->nOut = (WhereCost)10;
  4374         -        pNew->rRun = rLogSize + pNew->nOut;
         4452  +        assert( 43==whereCostFromInt(20) );
         4453  +        pNew->rSetup = 43 + rLogSize + rSize;
         4454  +        pNew->nOut = 33;  assert( 33==whereCostFromInt(10) );
         4455  +        pNew->rRun = whereCostAdd(rLogSize,pNew->nOut);
  4375   4456           pNew->wsFlags = WHERE_TEMP_INDEX;
  4376   4457           pNew->prereq = mExtra | pTerm->prereqRight;
  4377   4458           rc = whereLoopInsert(pBuilder, pNew);
  4378   4459         }
  4379   4460       }
  4380   4461     }
  4381   4462   
  4382   4463     /* Loop over all indices
  4383   4464     */
  4384   4465     for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){
  4385   4466       pNew->u.btree.nEq = 0;
  4386   4467       pNew->nLTerm = 0;
  4387   4468       pNew->iSortIdx = 0;
  4388         -    pNew->rSetup = (WhereCost)0;
         4469  +    pNew->rSetup = 0;
  4389   4470       pNew->prereq = mExtra;
  4390   4471       pNew->u.btree.pIndex = pProbe;
  4391   4472       b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
  4392   4473       if( pProbe->tnum<=0 ){
  4393   4474         /* Integer primary key index */
  4394   4475         pNew->wsFlags = WHERE_IPK;
  4395   4476   
  4396   4477         /* Full table scan */
  4397   4478         pNew->iSortIdx = b ? iSortIdx : 0;
  4398   4479         pNew->nOut = rSize;
  4399         -      pNew->rRun = (rSize + rLogSize)*(3+b); /* 4x penalty for a full-scan */
         4480  +      pNew->rRun = whereCostAdd(rSize,rLogSize) + 16 + b*4;
  4400   4481         rc = whereLoopInsert(pBuilder, pNew);
  4401   4482         if( rc ) break;
  4402   4483       }else{
  4403   4484         Bitmask m = pSrc->colUsed & ~columnsUsedByIndex(pProbe);
  4404   4485         pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
  4405   4486   
  4406   4487         /* Full scan via index */
................................................................................
  4408   4489          && pProbe->bUnordered==0
  4409   4490          && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  4410   4491          && sqlite3GlobalConfig.bUseCis
  4411   4492          && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
  4412   4493         ){
  4413   4494           pNew->iSortIdx = b ? iSortIdx : 0;
  4414   4495           pNew->nOut = rSize;
  4415         -        pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize);
         4496  +        pNew->rRun = whereCostAdd(rSize,rLogSize) + ((m==0 && b) ? 10 : 0);
  4416   4497           rc = whereLoopInsert(pBuilder, pNew);
  4417   4498           if( rc ) break;
  4418   4499         }
  4419   4500       }
  4420         -    rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 1);
         4501  +    rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);
  4421   4502   
  4422   4503       /* If there was an INDEXED BY clause, then only that one index is
  4423   4504       ** considered. */
  4424   4505       if( pSrc->pIndex ) break;
  4425   4506     }
  4426   4507     return rc;
  4427   4508   }
  4428   4509   
         4510  +#ifndef SQLITE_OMIT_VIRTUALTABLE
  4429   4511   /*
  4430   4512   ** Add all WhereLoop objects for a table of the join identified by
  4431   4513   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  4432   4514   */
  4433   4515   static int whereLoopAddVirtual(
  4434   4516     WhereLoopBuilder *pBuilder,  /* WHERE clause information */
  4435   4517     Bitmask mExtra               /* Extra prerequesites for using this table */
................................................................................
  4509   4591       }
  4510   4592       memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
  4511   4593       if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
  4512   4594       pIdxInfo->idxStr = 0;
  4513   4595       pIdxInfo->idxNum = 0;
  4514   4596       pIdxInfo->needToFreeIdxStr = 0;
  4515   4597       pIdxInfo->orderByConsumed = 0;
  4516         -    /* ((WhereCost)2) In case of SQLITE_OMIT_FLOATING_POINT... */
  4517         -    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((WhereCost)2);
         4598  +    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
  4518   4599       rc = vtabBestIndex(pParse, pTab, pIdxInfo);
  4519   4600       if( rc ) goto whereLoopAddVtab_exit;
  4520   4601       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  4521   4602       pNew->prereq = 0;
  4522   4603       mxTerm = -1;
  4523   4604       assert( pNew->nLSlot>=nConstraint );
  4524   4605       for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
................................................................................
  4563   4644         assert( pNew->nLTerm<=pNew->nLSlot );
  4564   4645         pNew->u.vtab.idxNum = pIdxInfo->idxNum;
  4565   4646         pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
  4566   4647         pIdxInfo->needToFreeIdxStr = 0;
  4567   4648         pNew->u.vtab.idxStr = pIdxInfo->idxStr;
  4568   4649         pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0)
  4569   4650                                        && pIdxInfo->orderByConsumed);
  4570         -      pNew->rSetup = (WhereCost)0;
  4571         -      pNew->rRun = pIdxInfo->estimatedCost;
  4572         -      pNew->nOut = (WhereCost)25;
         4651  +      pNew->rSetup = 0;
         4652  +      pNew->rRun = whereCostFromDouble(pIdxInfo->estimatedCost);
         4653  +      pNew->nOut = 46;  assert( 46 == whereCostFromInt(25) );
  4573   4654         whereLoopInsert(pBuilder, pNew);
  4574   4655         if( pNew->u.vtab.needFree ){
  4575   4656           sqlite3_free(pNew->u.vtab.idxStr);
  4576   4657           pNew->u.vtab.needFree = 0;
  4577   4658         }
  4578   4659       }
  4579   4660     }  
  4580   4661   
  4581   4662   whereLoopAddVtab_exit:
  4582   4663     if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
  4583   4664     sqlite3DbFree(db, pIdxInfo);
  4584   4665     return rc;
  4585   4666   }
         4667  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
  4586   4668   
  4587   4669   /*
  4588   4670   ** Add WhereLoop entries to handle OR terms.  This works for either
  4589   4671   ** btrees or virtual tables.
  4590   4672   */
  4591   4673   static int whereLoopAddOr(WhereLoopBuilder *pBuilder, Bitmask mExtra){
  4592   4674     WhereInfo *pWInfo = pBuilder->pWInfo;
................................................................................
  4635   4717             sSubBuild.pWC = &tempWC;
  4636   4718           }else{
  4637   4719             continue;
  4638   4720           }
  4639   4721           sBest.maskSelf = 0;
  4640   4722           sBest.rSetup = 0;
  4641   4723           sBest.rRun = 0;
         4724  +#ifndef SQLITE_OMIT_VIRTUALTABLE
  4642   4725           if( IsVirtual(pItem->pTab) ){
  4643   4726             rc = whereLoopAddVirtual(&sSubBuild, mExtra);
  4644         -        }else{
         4727  +        }else
         4728  +#endif
         4729  +        {
  4645   4730             rc = whereLoopAddBtree(&sSubBuild, mExtra);
  4646   4731           }
  4647   4732           if( sBest.maskSelf==0 ) break;
  4648         -        assert( sBest.rSetup==(WhereCost)0 );
  4649         -        rTotal += sBest.rRun;
  4650         -        nRow += sBest.nOut;
         4733  +        assert( sBest.rSetup==0 );
         4734  +        rTotal = whereCostAdd(rTotal, sBest.rRun);
         4735  +        nRow = whereCostAdd(nRow, sBest.nOut);
  4651   4736           prereq |= sBest.prereq;
  4652   4737         }
  4653   4738         assert( pNew->nLSlot>=1 );
  4654   4739         pNew->nLTerm = 1;
  4655   4740         pNew->aLTerm[0] = pTerm;
  4656   4741         pNew->wsFlags = WHERE_MULTI_OR;
  4657         -      pNew->rSetup = (WhereCost)0;
         4742  +      pNew->rSetup = 0;
  4658   4743         pNew->rRun = rTotal;
  4659   4744         pNew->nOut = nRow;
  4660   4745         pNew->prereq = prereq;
  4661   4746         memset(&pNew->u, 0, sizeof(pNew->u));
  4662   4747         rc = whereLoopInsert(pBuilder, pNew);
  4663   4748         whereLoopClear(pWInfo->pParse->db, &sBest);
  4664   4749       }
................................................................................
  4675   4760     Bitmask mPrior = 0;
  4676   4761     int iTab;
  4677   4762     SrcList *pTabList = pWInfo->pTabList;
  4678   4763     struct SrcList_item *pItem;
  4679   4764     sqlite3 *db = pWInfo->pParse->db;
  4680   4765     int nTabList = pWInfo->nLevel;
  4681   4766     int rc = SQLITE_OK;
  4682         -  WhereLoop *pNew, sNew;
         4767  +  u8 priorJoinType = 0;
         4768  +  WhereLoop *pNew;
  4683   4769   
  4684   4770     /* Loop over the tables in the join, from left to right */
  4685         -  pBuilder->pNew = pNew = &sNew;
         4771  +  pNew = pBuilder->pNew;
  4686   4772     whereLoopInit(pNew);
  4687   4773     for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
  4688   4774       pNew->iTab = iTab;
  4689   4775       pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
  4690         -    if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){
         4776  +    if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
  4691   4777         mExtra = mPrior;
  4692   4778       }
         4779  +    priorJoinType = pItem->jointype;
  4693   4780       if( IsVirtual(pItem->pTab) ){
  4694   4781         rc = whereLoopAddVirtual(pBuilder, mExtra);
  4695   4782       }else{
  4696   4783         rc = whereLoopAddBtree(pBuilder, mExtra);
  4697   4784       }
  4698   4785       if( rc==SQLITE_OK ){
  4699   4786         rc = whereLoopAddOr(pBuilder, mExtra);
  4700   4787       }
  4701   4788       mPrior |= pNew->maskSelf;
  4702   4789       if( rc || db->mallocFailed ) break;
  4703   4790     }
  4704   4791     whereLoopClear(db, pNew);
  4705         -  pBuilder->pNew = 0;
  4706   4792     return rc;
  4707   4793   }
  4708   4794   
  4709   4795   /*
  4710   4796   ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th
  4711   4797   ** parameters) to see if it outputs rows in the requested ORDER BY
  4712   4798   ** (or GROUP BY) without requiring a separate source operation.  Return:
................................................................................
  4984   5070     memset(aFrom, 0, sizeof(aFrom[0]));
  4985   5071     pX = (WhereLoop**)(aFrom+mxChoice);
  4986   5072     for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){
  4987   5073       pFrom->aLoop = pX;
  4988   5074     }
  4989   5075   
  4990   5076     /* Seed the search with a single WherePath containing zero WhereLoops */
  4991         -  aFrom[0].nRow = (WhereCost)1;
         5077  +  aFrom[0].nRow = 0;
  4992   5078     nFrom = 1;
  4993   5079   
  4994   5080     /* Precompute the cost of sorting the final result set, if the caller
  4995   5081     ** to sqlite3WhereBegin() was concerned about sorting */
  4996         -  rSortCost = (WhereCost)0;
  4997         -  if( pWInfo->pOrderBy==0 || nRowEst<=0.0 ){
         5082  +  rSortCost = 0;
         5083  +  if( pWInfo->pOrderBy==0 || nRowEst==0 ){
  4998   5084       aFrom[0].isOrderedValid = 1;
  4999   5085     }else{
  5000   5086       /* Compute an estimate on the cost to sort the entire result set */
  5001         -    rSortCost = nRowEst*estLog(nRowEst);
         5087  +    rSortCost = nRowEst + estLog(nRowEst);
  5002   5088   #ifdef WHERETRACE_ENABLED
  5003   5089       if( sqlite3WhereTrace>=2 ){
  5004         -      sqlite3DebugPrintf("---- sort cost=%-7.2g\n", rSortCost);
         5090  +      sqlite3DebugPrintf("---- sort cost=%-3d\n", rSortCost);
  5005   5091       }
  5006   5092   #endif
  5007   5093     }
  5008   5094   
  5009   5095     /* Compute successively longer WherePaths using the previous generation
  5010   5096     ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  5011   5097     ** best paths at each generation */
................................................................................
  5017   5103           Bitmask revMask = 0;
  5018   5104           u8 isOrderedValid = pFrom->isOrderedValid;
  5019   5105           u8 isOrdered = pFrom->isOrdered;
  5020   5106           if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
  5021   5107           if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
  5022   5108           /* At this point, pWLoop is a candidate to be the next loop. 
  5023   5109           ** Compute its cost */
  5024         -        rCost = pWLoop->rSetup + pWLoop->rRun*pFrom->nRow + pFrom->rCost;
         5110  +        rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
         5111  +        rCost = whereCostAdd(rCost, pFrom->rCost);
  5025   5112           maskNew = pFrom->maskLoop | pWLoop->maskSelf;
  5026   5113           if( !isOrderedValid ){
  5027   5114             switch( wherePathSatisfiesOrderBy(pWInfo, pFrom, iLoop, iLoop==nLoop-1,
  5028   5115                                               pWLoop, &revMask) ){
  5029   5116               case 1:  /* Yes.  pFrom+pWLoop does satisfy the ORDER BY clause */
  5030   5117                 isOrdered = 1;
  5031   5118                 isOrderedValid = 1;
  5032   5119                 break;
  5033   5120               case 0:  /* No.  pFrom+pWLoop will require a separate sort */
  5034   5121                 isOrdered = 0;
  5035   5122                 isOrderedValid = 1;
  5036         -              rCost += rSortCost;
         5123  +              rCost = whereCostAdd(rCost, rSortCost);
  5037   5124                 break;
  5038   5125               default: /* Cannot tell yet.  Try again on the next iteration */
  5039   5126                 break;
  5040   5127             }
  5041   5128           }else{
  5042   5129             revMask = pFrom->revLoop;
  5043   5130           }
................................................................................
  5047   5134               break;
  5048   5135             }
  5049   5136           }
  5050   5137           if( jj>=nTo ){
  5051   5138             if( nTo>=mxChoice && rCost>=mxCost ){
  5052   5139   #ifdef WHERETRACE_ENABLED
  5053   5140               if( sqlite3WhereTrace&0x4 ){
  5054         -              sqlite3DebugPrintf("Skip   %s cost=%-7.2g order=%c\n",
         5141  +              sqlite3DebugPrintf("Skip   %s cost=%3d order=%c\n",
  5055   5142                     wherePathName(pFrom, iLoop, pWLoop), rCost,
  5056   5143                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5057   5144               }
  5058   5145   #endif
  5059   5146               continue;
  5060   5147             }
  5061   5148             /* Add a new Path to the aTo[] set */
................................................................................
  5065   5152             }else{
  5066   5153               /* New path replaces the prior worst to keep count below mxChoice */
  5067   5154               for(jj=nTo-1; aTo[jj].rCost<mxCost; jj--){ assert(jj>0); }
  5068   5155             }
  5069   5156             pTo = &aTo[jj];
  5070   5157   #ifdef WHERETRACE_ENABLED
  5071   5158             if( sqlite3WhereTrace&0x4 ){
  5072         -            sqlite3DebugPrintf("New    %s cost=%-7.2g order=%c\n",
         5159  +            sqlite3DebugPrintf("New    %s cost=%-3d order=%c\n",
  5073   5160                   wherePathName(pFrom, iLoop, pWLoop), rCost,
  5074   5161                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5075   5162             }
  5076   5163   #endif
  5077   5164           }else{
  5078   5165             if( pTo->rCost<=rCost ){
  5079   5166   #ifdef WHERETRACE_ENABLED
  5080   5167               if( sqlite3WhereTrace&0x4 ){
  5081   5168                 sqlite3DebugPrintf(
  5082         -                  "Skip   %s cost=%-7.2g order=%c",
         5169  +                  "Skip   %s cost=%-3d order=%c",
  5083   5170                     wherePathName(pFrom, iLoop, pWLoop), rCost,
  5084   5171                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5085         -              sqlite3DebugPrintf("   vs %s cost=%-7.2g order=%c\n",
         5172  +              sqlite3DebugPrintf("   vs %s cost=%-3d order=%c\n",
  5086   5173                     wherePathName(pTo, iLoop+1, 0), pTo->rCost,
  5087   5174                     pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  5088   5175               }
  5089   5176   #endif
  5090   5177               continue;
  5091   5178             }
  5092   5179             /* A new and better score for a previously created equivalent path */
  5093   5180   #ifdef WHERETRACE_ENABLED
  5094   5181             if( sqlite3WhereTrace&0x4 ){
  5095   5182               sqlite3DebugPrintf(
  5096         -                "Update %s cost=%-7.2g order=%c",
         5183  +                "Update %s cost=%-3d order=%c",
  5097   5184                   wherePathName(pFrom, iLoop, pWLoop), rCost,
  5098   5185                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  5099         -            sqlite3DebugPrintf("  was %s cost=%-7.2g order=%c\n",
         5186  +            sqlite3DebugPrintf("  was %s cost=%-3d order=%c\n",
  5100   5187                   wherePathName(pTo, iLoop+1, 0), pTo->rCost,
  5101   5188                   pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  5102   5189             }
  5103   5190   #endif
  5104   5191           }
  5105   5192           /* pWLoop is a winner.  Add it to the set of best so far */
  5106   5193           pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
  5107   5194           pTo->revLoop = revMask;
  5108         -        pTo->nRow = pFrom->nRow * pWLoop->nOut;
         5195  +        pTo->nRow = pFrom->nRow + pWLoop->nOut;
  5109   5196           pTo->rCost = rCost;
  5110   5197           pTo->isOrderedValid = isOrderedValid;
  5111   5198           pTo->isOrdered = isOrdered;
  5112   5199           memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
  5113   5200           pTo->aLoop[iLoop] = pWLoop;
  5114   5201           if( nTo>=mxChoice ){
  5115   5202             mxCost = aTo[0].rCost;
................................................................................
  5120   5207         }
  5121   5208       }
  5122   5209   
  5123   5210   #ifdef WHERETRACE_ENABLED
  5124   5211       if( sqlite3WhereTrace>=2 ){
  5125   5212         sqlite3DebugPrintf("---- after round %d ----\n", iLoop);
  5126   5213         for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){
  5127         -        sqlite3DebugPrintf(" %s cost=%-7.2g nrow=%-7.2g order=%c",
         5214  +        sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c",
  5128   5215              wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5129   5216              pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  5130   5217           if( pTo->isOrderedValid && pTo->isOrdered ){
  5131   5218             sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop);
  5132   5219           }else{
  5133   5220             sqlite3DebugPrintf("\n");
  5134   5221           }
................................................................................
  5180   5267   ** general-purpose query planner, and thereby yield faster sqlite3_prepare()
  5181   5268   ** times for the common case.
  5182   5269   **
  5183   5270   ** Return non-zero on success, if this query can be handled by this
  5184   5271   ** no-frills query planner.  Return zero if this query needs the 
  5185   5272   ** general-purpose query planner.
  5186   5273   */
  5187         -static int whereSimpleFastCase(WhereLoopBuilder *pBuilder){
         5274  +static int whereShortCut(WhereLoopBuilder *pBuilder){
  5188   5275     WhereInfo *pWInfo;
  5189   5276     struct SrcList_item *pItem;
  5190   5277     WhereClause *pWC;
  5191   5278     WhereTerm *pTerm;
  5192   5279     WhereLoop *pLoop;
  5193   5280     int iCur;
  5194   5281     int j;
  5195   5282     Table *pTab;
  5196   5283     Index *pIdx;
  5197   5284     
  5198   5285     pWInfo = pBuilder->pWInfo;
         5286  +  if( pWInfo->wctrlFlags & WHERE_FORCE_TABLE ) return 0;
  5199   5287     assert( pWInfo->pTabList->nSrc>=1 );
  5200   5288     pItem = pWInfo->pTabList->a;
  5201   5289     pTab = pItem->pTab;
  5202   5290     if( IsVirtual(pTab) ) return 0;
  5203   5291     if( pItem->zIndex ) return 0;
  5204   5292     iCur = pItem->iCursor;
  5205   5293     pWC = &pWInfo->sWC;
................................................................................
  5207   5295     pLoop->wsFlags = 0;
  5208   5296     pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0);
  5209   5297     if( pTerm ){
  5210   5298       pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
  5211   5299       pLoop->aLTerm[0] = pTerm;
  5212   5300       pLoop->nLTerm = 1;
  5213   5301       pLoop->u.btree.nEq = 1;
  5214         -    pLoop->rRun = (WhereCost)10;
         5302  +    pLoop->rRun = 33;  /* 33 == whereCostFromInt(10) */
  5215   5303     }else{
  5216   5304       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  5217   5305         if( pIdx->onError==OE_None ) continue;
  5218   5306         for(j=0; j<pIdx->nColumn; j++){
  5219   5307           pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
  5220   5308           if( pTerm==0 ) break;
  5221   5309           whereLoopResize(pWInfo->pParse->db, pLoop, j);
................................................................................
  5225   5313         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  5226   5314         if( (pItem->colUsed & ~columnsUsedByIndex(pIdx))==0 ){
  5227   5315           pLoop->wsFlags |= WHERE_IDX_ONLY;
  5228   5316         }
  5229   5317         pLoop->nLTerm = j;
  5230   5318         pLoop->u.btree.nEq = j;
  5231   5319         pLoop->u.btree.pIndex = pIdx;
  5232         -      pLoop->rRun = (WhereCost)15;
         5320  +      pLoop->rRun = 39;  /* 39 == whereCostFromInt(15) */
  5233   5321         break;
  5234   5322       }
  5235   5323     }
  5236   5324     if( pLoop->wsFlags ){
  5237   5325       pLoop->nOut = (WhereCost)1;
  5238   5326       pWInfo->a[0].pWLoop = pLoop;
  5239   5327       pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur);
................................................................................
  5402   5490     pWInfo->wctrlFlags = wctrlFlags;
  5403   5491     pWInfo->savedNQueryLoop = pParse->nQueryLoop;
  5404   5492     pMaskSet = &pWInfo->sMaskSet;
  5405   5493     sWLB.pWInfo = pWInfo;
  5406   5494     sWLB.pWC = &pWInfo->sWC;
  5407   5495     sWLB.pNew = (WhereLoop*)&pWInfo->a[nTabList];
  5408   5496     whereLoopInit(sWLB.pNew);
         5497  +#ifdef SQLITE_DEBUG
         5498  +  sWLB.pNew->cId = '*';
         5499  +#endif
  5409   5500   
  5410   5501     /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via
  5411   5502     ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */
  5412   5503     if( OptimizationDisabled(db, SQLITE_DistinctOpt) ) pDistinct = 0;
  5413   5504   
  5414   5505     /* Split the WHERE clause into separate subexpressions where each
  5415   5506     ** subexpression is separated by an AND operator.
................................................................................
  5474   5565     if( pDistinct && isDistinctRedundant(pParse,pTabList,&pWInfo->sWC,pDistinct) ){
  5475   5566       pDistinct = 0;
  5476   5567       pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  5477   5568     }
  5478   5569   
  5479   5570     /* Construct the WhereLoop objects */
  5480   5571     WHERETRACE(("*** Optimizer Start ***\n"));
  5481         -  if( nTabList!=1 || whereSimpleFastCase(&sWLB)==0 ){
         5572  +  if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
  5482   5573       rc = whereLoopAddAll(&sWLB);
  5483   5574       if( rc ) goto whereBeginError;
  5484   5575     
  5485   5576       /* Display all of the WhereLoop objects if wheretrace is enabled */
  5486   5577   #ifdef WHERETRACE_ENABLED
  5487   5578       if( sqlite3WhereTrace ){
  5488   5579         WhereLoop *p;

Changes to test/all.test.

    44     44   if {$::tcl_platform(platform)=="unix"} {
    45     45     ifcapable !default_autovacuum {
    46     46       run_test_suite autovacuum_crash
    47     47     }
    48     48   }
    49     49   
    50     50   finish_test
    51         -
    52         -

Changes to test/analyze3.test.

    93     93       COMMIT;
    94     94       ANALYZE;
    95     95     }
    96     96   } {}
    97     97   
    98     98   do_eqp_test analyze3-1.1.2 {
    99     99     SELECT sum(y) FROM t1 WHERE x>200 AND x<300
   100         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
          100  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
   101    101   do_eqp_test analyze3-1.1.3 {
   102    102     SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
   103         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}
          103  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
   104    104   
   105    105   do_test analyze3-1.1.4 {
   106    106     sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
   107    107   } {199 0 14850}
   108    108   do_test analyze3-1.1.5 {
   109    109     set l [string range "200" 0 end]
   110    110     set u [string range "300" 0 end]
................................................................................
   142    142         CREATE INDEX i2 ON t2(x);
   143    143       COMMIT;
   144    144       ANALYZE;
   145    145     }
   146    146   } {}
   147    147   do_eqp_test analyze3-1.2.2 {
   148    148     SELECT sum(y) FROM t2 WHERE x>1 AND x<2
   149         -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
          149  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
   150    150   do_eqp_test analyze3-1.2.3 {
   151    151     SELECT sum(y) FROM t2 WHERE x>0 AND x<99
   152         -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~968 rows)}}
          152  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
   153    153   do_test analyze3-1.2.4 {
   154    154     sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
   155    155   } {161 0 4760}
   156    156   do_test analyze3-1.2.5 {
   157    157     set l [string range "12" 0 end]
   158    158     set u [string range "20" 0 end]
   159    159     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
................................................................................
   189    189         CREATE INDEX i3 ON t3(x);
   190    190       COMMIT;
   191    191       ANALYZE;
   192    192     }
   193    193   } {}
   194    194   do_eqp_test analyze3-1.3.2 {
   195    195     SELECT sum(y) FROM t3 WHERE x>200 AND x<300
   196         -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
          196  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
   197    197   do_eqp_test analyze3-1.3.3 {
   198    198     SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
   199         -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}
          199  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
   200    200   
   201    201   do_test analyze3-1.3.4 {
   202    202     sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
   203    203   } {199 0 14850}
   204    204   do_test analyze3-1.3.5 {
   205    205     set l [string range "200" 0 end]
   206    206     set u [string range "300" 0 end]
................................................................................
   244    244       append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
   245    245       execsql { INSERT INTO t1 VALUES($i, $t) }
   246    246     }
   247    247     execsql COMMIT
   248    248   } {}
   249    249   do_eqp_test analyze3-2.2 {
   250    250     SELECT count(a) FROM t1 WHERE b LIKE 'a%'
   251         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~31250 rows)}}
          251  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
   252    252   do_eqp_test analyze3-2.3 {
   253    253     SELECT count(a) FROM t1 WHERE b LIKE '%a'
   254         -} {0 0 0 {SCAN TABLE t1 (~500000 rows)}}
          254  +} {0 0 0 {SCAN TABLE t1}}
   255    255   
   256    256   do_test analyze3-2.4 {
   257    257     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
   258    258   } {101 0 100}
   259    259   do_test analyze3-2.5 {
   260    260     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
   261    261   } {999 999 100}
................................................................................
   326    326   do_test analyze3-3.2.5 {
   327    327     set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
   328    328     sqlite3_expired $S
   329    329   } {0}
   330    330   do_test analyze3-3.2.6 {
   331    331     sqlite3_bind_text $S 1 "abc" 3
   332    332     sqlite3_expired $S
   333         -} {0}
          333  +} {1}
   334    334   do_test analyze3-3.2.7 {
   335    335     sqlite3_finalize $S
   336    336   } {SQLITE_OK}
   337    337   
   338    338   do_test analyze3-3.4.1 {
   339    339     set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
   340    340     sqlite3_expired $S

Changes to test/analyze4.test.

    34     34       INSERT INTO t1 SELECT a+32, b FROM t1;
    35     35       INSERT INTO t1 SELECT a+64, b FROM t1;
    36     36       ANALYZE;
    37     37     }
    38     38   
    39     39     # Should choose the t1a index since it is more specific than t1b.
    40     40     db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
    41         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           41  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    42     42   
    43     43   # Verify that the t1b index shows that it does not narrow down the
    44     44   # search any at all.
    45     45   #
    46     46   do_test analyze4-1.1 {
    47     47     db eval {
    48     48       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;

Changes to test/analyze5.test.

   152    152     301  {y=1}                 t1y   26
   153    153     302  {y=0.1}               t1y    1
   154    154   
   155    155     400  {x IS NULL}           t1x  400
   156    156   
   157    157   } {
   158    158     # Verify that the expected index is used with the expected row count
   159         -  do_test analyze5-1.${testid}a {
   160         -    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
   161         -    set idx {}
   162         -    regexp {INDEX (t1.) } $x all idx
   163         -    regexp {~([0-9]+) rows} $x all nrow
   164         -    list $idx $nrow
   165         -  } [list $index $rows]
          159  +  # No longer valid due to an EXPLAIN QUERY PLAN output format change
          160  +  # do_test analyze5-1.${testid}a {
          161  +  #   set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
          162  +  #   set idx {}
          163  +  #   regexp {INDEX (t1.) } $x all idx
          164  +  #   regexp {~([0-9]+) rows} $x all nrow
          165  +  #   list $idx $nrow
          166  +  # } [list $index $rows]
   166    167   
   167    168     # Verify that the same result is achieved regardless of whether or not
   168    169     # the index is used
   169    170     do_test analyze5-1.${testid}b {
   170    171       set w2 [string map {y +y z +z} $where]
   171    172       set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\
   172    173                        ORDER BY +rowid"]
................................................................................
   198    199     503  {x=1}                               t1x   1
   199    200     504  {x IS NOT NULL}                     t1x   2
   200    201     505  {+x IS NOT NULL}                     {} 500
   201    202     506  {upper(x) IS NOT NULL}               {} 500
   202    203   
   203    204   } {
   204    205     # Verify that the expected index is used with the expected row count
   205         -if {$testid==50299} {breakpoint; set sqlite_where_trace 1}
   206         -  do_test analyze5-1.${testid}a {
   207         -    set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
   208         -    set idx {}
   209         -    regexp {INDEX (t1.) } $x all idx
   210         -    regexp {~([0-9]+) rows} $x all nrow
   211         -    list $idx $nrow
   212         -  } [list $index $rows]
   213         -if {$testid==50299} exit
          206  +  # No longer valid due to an EXPLAIN QUERY PLAN format change
          207  +  # do_test analyze5-1.${testid}a {
          208  +  #   set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
          209  +  #   set idx {}
          210  +  #   regexp {INDEX (t1.) } $x all idx
          211  +  #   regexp {~([0-9]+) rows} $x all nrow
          212  +  #   list $idx $nrow
          213  +  # } [list $index $rows]
   214    214   
   215    215     # Verify that the same result is achieved regardless of whether or not
   216    216     # the index is used
   217    217     do_test analyze5-1.${testid}b {
   218    218       set w2 [string map {y +y z +z} $where]
   219    219       set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\
   220    220                        ORDER BY +rowid"]

Changes to test/analyze6.test.

    57     57   # The lowest cost plan is to scan CAT and for each integer there, do a single
    58     58   # lookup of the first corresponding entry in EV then read off the equal values
    59     59   # in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
    60     60   # have used EV for the outer loop instead of CAT - which was about 3x slower.)
    61     61   #
    62     62   do_test analyze6-1.1 {
    63     63     eqp {SELECT count(*) FROM ev, cat WHERE x=y}
    64         -} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
           64  +} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}}
    65     65   
    66     66   # The same plan is chosen regardless of the order of the tables in the
    67     67   # FROM clause.
    68     68   #
    69     69   do_test analyze6-1.2 {
    70     70     eqp {SELECT count(*) FROM cat, ev WHERE x=y}
    71         -} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
           71  +} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}}
    72     72   
    73     73   
    74     74   # Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
    75     75   # If ANALYZE is run on an empty table, make sure indices are used
    76     76   # on the table.
    77     77   #
    78     78   do_test analyze6-2.1 {
    79     79     execsql {
    80     80       CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z);
    81     81       CREATE INDEX t201z ON t201(z);
    82     82       ANALYZE;
    83     83     }
    84     84     eqp {SELECT * FROM t201 WHERE z=5}
    85         -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
           85  +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
    86     86   do_test analyze6-2.2 {
    87     87     eqp {SELECT * FROM t201 WHERE y=5}
    88         -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
           88  +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
    89     89   do_test analyze6-2.3 {
    90     90     eqp {SELECT * FROM t201 WHERE x=5}
    91         -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
           91  +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}
    92     92   do_test analyze6-2.4 {
    93     93     execsql {
    94     94       INSERT INTO t201 VALUES(1,2,3);
    95     95       ANALYZE t201;
    96     96     }
    97     97     eqp {SELECT * FROM t201 WHERE z=5}
    98         -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
           98  +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
    99     99   do_test analyze6-2.5 {
   100    100     eqp {SELECT * FROM t201 WHERE y=5}
   101         -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
          101  +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
   102    102   do_test analyze6-2.6 {
   103    103     eqp {SELECT * FROM t201 WHERE x=5}
   104         -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
          104  +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}
   105    105   do_test analyze6-2.7 {
   106    106     execsql {
   107    107       INSERT INTO t201 VALUES(4,5,7);
   108    108       INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201;
   109    109       INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201;
   110    110       INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201;
   111    111       ANALYZE t201;
   112    112     }
   113    113     eqp {SELECT * FROM t201 WHERE z=5}
   114         -} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?) (~10 rows)}}
          114  +} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
   115    115   do_test analyze6-2.8 {
   116    116     eqp {SELECT * FROM t201 WHERE y=5}
   117         -} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?) (~1 rows)}}
          117  +} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
   118    118   do_test analyze6-2.9 {
   119    119     eqp {SELECT * FROM t201 WHERE x=5}
   120         -} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
          120  +} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}
   121    121   
   122    122   finish_test

Changes to test/analyze7.test.

    33     33       CREATE INDEX t1b ON t1(b);
    34     34       CREATE INDEX t1cd ON t1(c,d);
    35     35       CREATE VIRTUAL TABLE nums USING wholenumber;
    36     36       INSERT INTO t1 SELECT value, value, value/100, value FROM nums
    37     37                       WHERE value BETWEEN 1 AND 256;
    38     38       EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
    39     39     }
    40         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~10 rows)}}
           40  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    41     41   do_test analyze7-1.1 {
    42     42     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    43         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
           43  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
    44     44   do_test analyze7-1.2 {
    45     45     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    46         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}
           46  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
    47     47   
    48     48   # Run an analyze on one of the three indices.  Verify that this
    49     49   # effects the row-count estimate on the one query that uses that
    50     50   # one index.
    51     51   #
    52     52   do_test analyze7-2.0 {
    53     53     execsql {ANALYZE t1a;}
    54     54     db cache flush
    55     55     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
    56         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           56  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    57     57   do_test analyze7-2.1 {
    58     58     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    59         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
           59  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
    60     60   do_test analyze7-2.2 {
    61     61     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    62         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}
           62  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
    63     63   
    64     64   # Verify that since the query planner now things that t1a is more
    65     65   # selective than t1b, it prefers to use t1a.
    66     66   #
    67     67   do_test analyze7-2.3 {
    68     68     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
    69         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           69  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    70     70   
    71     71   # Run an analysis on another of the three indices.  Verify  that this
    72     72   # new analysis works and does not disrupt the previous analysis.
    73     73   #
    74     74   do_test analyze7-3.0 {
    75     75     execsql {ANALYZE t1cd;}
    76     76     db cache flush;
    77     77     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
    78         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           78  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    79     79   do_test analyze7-3.1 {
    80     80     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    81         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
           81  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
    82     82   do_test analyze7-3.2.1 {
    83     83     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
    84         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
           84  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
    85     85   ifcapable stat3 {
    86     86     # If ENABLE_STAT3 is defined, SQLite comes up with a different estimated
    87     87     # row count for (c=2) than it does for (c=?).
    88     88     do_test analyze7-3.2.2 {
    89     89       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    90         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~57 rows)}}
           90  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
    91     91   } else {
    92     92     # If ENABLE_STAT3 is not defined, the expected row count for (c=2) is the
    93     93     # same as that for (c=?).
    94     94     do_test analyze7-3.2.3 {
    95     95       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    96         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
           96  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
    97     97   }
    98     98   do_test analyze7-3.3 {
    99     99     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
   100         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
          100  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
   101    101   ifcapable {!stat3} {
   102    102     do_test analyze7-3.4 {
   103    103       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
   104         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
          104  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
   105    105     do_test analyze7-3.5 {
   106    106       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
   107         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
          107  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
   108    108   }
   109    109   do_test analyze7-3.6 {
   110    110     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
   111         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}
          111  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}}
   112    112   
   113    113   finish_test

Changes to test/analyze8.test.

    57     57   # with a==100.  And so for those cases, choose the t1b index.
    58     58   #
    59     59   # Buf ro a==99 and a==101, there are far fewer rows so choose
    60     60   # the t1a index.
    61     61   #
    62     62   do_test 1.1 {
    63     63     eqp {SELECT * FROM t1 WHERE a=100 AND b=55}
    64         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
           64  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
    65     65   do_test 1.2 {
    66     66     eqp {SELECT * FROM t1 WHERE a=99 AND b=55}
    67         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           67  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    68     68   do_test 1.3 {
    69     69     eqp {SELECT * FROM t1 WHERE a=101 AND b=55}
    70         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           70  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    71     71   do_test 1.4 {
    72     72     eqp {SELECT * FROM t1 WHERE a=100 AND b=56}
    73         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
           73  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
    74     74   do_test 1.5 {
    75     75     eqp {SELECT * FROM t1 WHERE a=99 AND b=56}
    76         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           76  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    77     77   do_test 1.6 {
    78     78     eqp {SELECT * FROM t1 WHERE a=101 AND b=56}
    79         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           79  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    80     80   do_test 2.1 {
    81     81     eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54}
    82         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~2 rows)}}
           82  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
    83     83   
    84     84   # There are many more values of c between 0 and 100000 than there are
    85     85   # between 800000 and 900000.  So t1c is more selective for the latter
    86     86   # range.
    87     87   #
    88     88   do_test 3.1 {
    89     89     eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000}
    90         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~6 rows)}}
           90  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
    91     91   do_test 3.2 {
    92     92     eqp {SELECT * FROM t1
    93     93          WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000}
    94         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~4 rows)}}
           94  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
    95     95   do_test 3.3 {
    96     96     eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
    97         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~63 rows)}}
           97  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
    98     98   do_test 3.4 {
    99     99     eqp {SELECT * FROM t1
   100    100          WHERE a=100 AND c BETWEEN 800000 AND 900000}
   101         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~2 rows)}}
          101  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
   102    102   
   103    103   finish_test

Changes to test/async5.test.

    62     62   sqlite3async_control halt idle
    63     63   sqlite3async_start
    64     64   sqlite3async_wait
    65     65   sqlite3async_control halt never
    66     66   sqlite3async_shutdown
    67     67   set sqlite3async_trace 0
    68     68   finish_test
    69         -

Changes to test/autoindex1.test.

   143    143   do_execsql_test autoindex1-500 {
   144    144     CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
   145    145     CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
   146    146     EXPLAIN QUERY PLAN
   147    147     SELECT b FROM t501
   148    148      WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
   149    149   } {
   150         -  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
          150  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
   151    151     0 0 0 {EXECUTE LIST SUBQUERY 1} 
   152         -  1 0 0 {SCAN TABLE t502 (~100000 rows)}
          152  +  1 0 0 {SCAN TABLE t502}
   153    153   }
   154    154   do_execsql_test autoindex1-501 {
   155    155     EXPLAIN QUERY PLAN
   156    156     SELECT b FROM t501
   157    157      WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   158    158   } {
   159         -  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
          159  +  0 0 0 {SCAN TABLE t501} 
   160    160     0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
   161         -  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
          161  +  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)}
   162    162   }
   163    163   do_execsql_test autoindex1-502 {
   164    164     EXPLAIN QUERY PLAN
   165    165     SELECT b FROM t501
   166    166      WHERE t501.a=123
   167    167        AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   168    168   } {
   169         -  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          169  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
   170    170     0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
   171         -  1 0 0 {SCAN TABLE t502 (~100000 rows)}
          171  +  1 0 0 {SCAN TABLE t502}
   172    172   }
   173    173   
   174    174   
   175    175   # The following code checks a performance regression reported on the
   176    176   # mailing list on 2010-10-19.  The problem is that the nRowEst field
   177    177   # of ephermeral tables was not being initialized correctly and so no
   178    178   # automatic index was being created for the emphemeral table when it was
................................................................................
   236    236              WHERE prev.flock_no = later.flock_no
   237    237              AND later.owner_change_date > prev.owner_change_date
   238    238              AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
   239    239          ) y ON x.sheep_no = y.sheep_no
   240    240      WHERE y.sheep_no IS NULL
   241    241      ORDER BY x.registering_flock;
   242    242   } {
   243         -  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
   244         -  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 
          243  +  1 0 0 {SCAN TABLE sheep AS s} 
          244  +  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
   245    245     1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
   246         -  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 
   247         -  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
   248         -  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
          246  +  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 
          247  +  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
          248  +  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
   249    249   }
   250    250   
   251    251   
   252    252   do_execsql_test autoindex1-700 {
   253    253     CREATE TABLE t5(a, b, c);
   254    254     EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
   255    255   } {
   256         -  0 0 0 {SCAN TABLE t5 (~100000 rows)} 
          256  +  0 0 0 {SCAN TABLE t5} 
   257    257     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   258    258   }
   259    259   
   260    260   # The following checks a performance issue reported on the sqlite-dev
   261    261   # mailing list on 2013-01-10
   262    262   #
   263    263   do_execsql_test autoindex1-800 {

Changes to test/backup4.test.

    97     97     db1 close
    98     98     file size test.db
    99     99   } {1024}
   100    100   
   101    101   do_test 3.4 { file size test.db2 } 0
   102    102   
   103    103   finish_test
   104         -

Changes to test/between.test.

    54     54     set ::sqlite_sort_count 0
    55     55     set data [execsql $sql]
    56     56     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    57     57     lappend data $x
    58     58     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
    59     59     # puts eqp=$eqp
    60     60     foreach {a b c x} $eqp {
    61         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
           61  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
    62     62           $x all as tab idx]} {
    63     63         lappend data $tab $idx
    64         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
           64  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
    65     65         lappend data $tab *
    66     66       }
    67     67     }
    68     68     return $data   
    69     69   }
    70     70   
    71     71   do_test between-1.1.1 {

Changes to test/btreefault.test.

    51     51   } -test {
    52     52     sqlite3_finalize $::STMT
    53     53     faultsim_test_result {0 {}} 
    54     54     faultsim_integrity_check
    55     55   }
    56     56   
    57     57   finish_test
    58         -

Changes to test/capi3e.test.

    56     56   # capi3e-1.*: Test sqlite3_open with various UTF8 filenames
    57     57   # capi3e-2.*: Test sqlite3_open16 with various UTF8 filenames
    58     58   # capi3e-3.*: Test ATTACH with various UTF8 filenames
    59     59   
    60     60   db close
    61     61   
    62     62   # here's the list of file names we're testing
    63         -set names {t 1 t. 1. t.d 1.d t-1 1-1 t.db .db .db .db .db .db}
           63  +set names {t 1 t. 1. t.d 1.d t-1 1-1 t.db ä.db ë.db ö.db ü.db ÿ.db}
    64     64   
    65     65   set i 0
    66     66   foreach name $names {
    67     67     incr i
    68     68     do_test capi3e-1.1.$i {
    69     69       set db2 [sqlite3_open $name {}]
    70     70       sqlite3_errcode $db2

Changes to test/close.test.

    72     72   } {1 {(21) library routine called out of sequence}}
    73     73   
    74     74   do_test 1.4.4 {
    75     75     sqlite3_finalize $STMT
    76     76   } {SQLITE_OK}
    77     77   
    78     78   finish_test
    79         -

Changes to test/corruptF.test.

   143    143         set res ""
   144    144       }
   145    145       set res
   146    146     } {}
   147    147   }
   148    148   
   149    149   finish_test
   150         -

Changes to test/e_createtable.test.

  1364   1364   #
  1365   1365   do_execsql_test 4.10.0 {
  1366   1366     CREATE TABLE t1(a, b PRIMARY KEY);
  1367   1367     CREATE TABLE t2(a, b, c, UNIQUE(b, c));
  1368   1368   }
  1369   1369   do_createtable_tests 4.10 {
  1370   1370     1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
  1371         -       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
         1371  +       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}}
  1372   1372   
  1373   1373     2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
  1374         -       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
         1374  +       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}}
  1375   1375   
  1376   1376     3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
  1377         -       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
         1377  +       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}}
  1378   1378   }
  1379   1379   
  1380   1380   # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
  1381   1381   # column definition or specified as a table constraint. In practice it
  1382   1382   # makes no difference.
  1383   1383   #
  1384   1384   #   All the tests that deal with CHECK constraints below (4.11.* and 

Changes to test/e_fkey.test.

   970    970     }
   971    971   } {}
   972    972   do_execsql_test e_fkey-25.2 {
   973    973     PRAGMA foreign_keys = OFF;
   974    974     EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
   975    975     EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
   976    976   } {
   977         -  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
   978         -  0 0 0 {SCAN TABLE track (~100000 rows)}
          977  +  0 0 0 {SCAN TABLE artist} 
          978  +  0 0 0 {SCAN TABLE track}
   979    979   }
   980    980   do_execsql_test e_fkey-25.3 {
   981    981     PRAGMA foreign_keys = ON;
   982    982     EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
   983    983   } {
   984         -  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
   985         -  0 0 0 {SCAN TABLE track (~100000 rows)}
          984  +  0 0 0 {SCAN TABLE artist} 
          985  +  0 0 0 {SCAN TABLE track}
   986    986   }
   987    987   do_test e_fkey-25.4 {
   988    988     execsql {
   989    989       INSERT INTO artist VALUES(5, 'artist 5');
   990    990       INSERT INTO artist VALUES(6, 'artist 6');
   991    991       INSERT INTO artist VALUES(7, 'artist 7');
   992    992       INSERT INTO track VALUES(1, 'track 1', 5);
................................................................................
  1095   1095   } {}
  1096   1096   do_test e_fkey-27.2 {
  1097   1097     eqp { INSERT INTO artist VALUES(?, ?) }
  1098   1098   } {}
  1099   1099   do_execsql_test e_fkey-27.3 {
  1100   1100     EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
  1101   1101   } {
  1102         -  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  1103         -  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 
  1104         -  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)}
         1102  +  0 0 0 {SCAN TABLE artist} 
         1103  +  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)} 
         1104  +  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
  1105   1105   }
  1106   1106   do_execsql_test e_fkey-27.4 {
  1107   1107     EXPLAIN QUERY PLAN DELETE FROM artist
  1108   1108   } {
  1109         -  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  1110         -  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)}
         1109  +  0 0 0 {SCAN TABLE artist} 
         1110  +  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?)}
  1111   1111   }
  1112   1112   
  1113   1113   
  1114   1114   ###########################################################################
  1115   1115   ### SECTION 4.1: Composite Foreign Key Constraints
  1116   1116   ###########################################################################
  1117   1117   

Changes to test/eqp.test.

    39     39     CREATE TABLE t2(a, b);
    40     40     CREATE TABLE t3(a, b);
    41     41   }
    42     42   
    43     43   do_eqp_test 1.2 {
    44     44     SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
    45     45   } {
    46         -  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
    47         -  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
    48         -  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
           46  +  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
           47  +  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 
           48  +  0 1 0 {SCAN TABLE t2}
    49     49   }
    50     50   do_eqp_test 1.3 {
    51     51     SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
    52     52   } {
    53         -  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
    54         -  0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
    55         -  0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
           53  +  0 0 0 {SCAN TABLE t2}
           54  +  0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
           55  +  0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 
    56     56   }
    57     57   do_eqp_test 1.3 {
    58     58     SELECT a FROM t1 ORDER BY a
    59     59   } {
    60         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
           60  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
    61     61   }
    62     62   do_eqp_test 1.4 {
    63     63     SELECT a FROM t1 ORDER BY +a
    64     64   } {
    65         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
           65  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
    66     66     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    67     67   }
    68     68   do_eqp_test 1.5 {
    69     69     SELECT a FROM t1 WHERE a=4
    70     70   } {
    71         -  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
           71  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}
    72     72   }
    73     73   do_eqp_test 1.6 {
    74     74     SELECT DISTINCT count(*) FROM t3 GROUP BY a;
    75     75   } {
    76         -  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
           76  +  0 0 0 {SCAN TABLE t3}
    77     77     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    78     78     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
    79     79   }
    80     80   
    81     81   do_eqp_test 1.7 {
    82     82     SELECT * FROM t3 JOIN (SELECT 1)
    83     83   } {
    84         -  0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
    85         -  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
           84  +  0 0 1 {SCAN SUBQUERY 1}
           85  +  0 1 0 {SCAN TABLE t3}
    86     86   }
    87     87   do_eqp_test 1.8 {
    88     88     SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
    89     89   } {
    90     90     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
    91         -  0 0 1 {SCAN SUBQUERY 1 (~2 rows)}
    92         -  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
           91  +  0 0 1 {SCAN SUBQUERY 1}
           92  +  0 1 0 {SCAN TABLE t3}
    93     93   }
    94     94   do_eqp_test 1.9 {
    95     95     SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
    96     96   } {
    97         -  3 0 0 {SCAN TABLE t3 (~1000000 rows)}
           97  +  3 0 0 {SCAN TABLE t3}
    98     98     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)}
    99         -  0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
   100         -  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
           99  +  0 0 1 {SCAN SUBQUERY 1}
          100  +  0 1 0 {SCAN TABLE t3}
   101    101   }
   102    102   do_eqp_test 1.10 {
   103    103     SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
   104    104   } {
   105         -  3 0 0 {SCAN TABLE t3 (~1000000 rows)}
          105  +  3 0 0 {SCAN TABLE t3}
   106    106     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)}
   107         -  0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
   108         -  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
          107  +  0 0 1 {SCAN SUBQUERY 1}
          108  +  0 1 0 {SCAN TABLE t3}
   109    109   }
   110    110   
   111    111   do_eqp_test 1.11 {
   112    112     SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
   113    113   } {
   114         -  3 0 0 {SCAN TABLE t3 (~1000000 rows)}
          114  +  3 0 0 {SCAN TABLE t3}
   115    115     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)}
   116         -  0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
   117         -  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
          116  +  0 0 1 {SCAN SUBQUERY 1}
          117  +  0 1 0 {SCAN TABLE t3}
   118    118   }
   119    119   
   120    120   #-------------------------------------------------------------------------
   121    121   # Test cases eqp-2.* - tests for single select statements.
   122    122   #
   123    123   drop_all_tables
   124    124   do_execsql_test 2.1 {
................................................................................
   125    125     CREATE TABLE t1(x, y);
   126    126   
   127    127     CREATE TABLE t2(x, y);
   128    128     CREATE INDEX t2i1 ON t2(x);
   129    129   }
   130    130   
   131    131   det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
   132         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          132  +  0 0 0 {SCAN TABLE t1}
   133    133     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
   134    134     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   135    135     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   136    136   }
   137    137   det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
   138         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
          138  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
   139    139     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   140    140     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   141    141   }
   142    142   det 2.2.3 "SELECT DISTINCT * FROM t1" {
   143         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          143  +  0 0 0 {SCAN TABLE t1}
   144    144     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   145    145   }
   146    146   det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
   147         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
   148         -  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          147  +  0 0 0 {SCAN TABLE t1}
          148  +  0 1 1 {SCAN TABLE t2}
   149    149     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   150    150   }
   151    151   det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
   152         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
   153         -  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          152  +  0 0 0 {SCAN TABLE t1}
          153  +  0 1 1 {SCAN TABLE t2}
   154    154     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   155    155     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   156    156   }
   157    157   det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
   158         -  0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
   159         -  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
          158  +  0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1}
          159  +  0 1 0 {SCAN TABLE t1}
   160    160   }
   161    161   
   162    162   det 2.3.1 "SELECT max(x) FROM t2" {
   163         -  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
          163  +  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1}
   164    164   }
   165    165   det 2.3.2 "SELECT min(x) FROM t2" {
   166         -  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
          166  +  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1}
   167    167   }
   168    168   det 2.3.3 "SELECT min(x), max(x) FROM t2" {
   169         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
          169  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
   170    170   }
   171    171   
   172    172   det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
   173         -  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          173  +  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
   174    174   }
   175    175   
   176    176   
   177    177   
   178    178   #-------------------------------------------------------------------------
   179    179   # Test cases eqp-3.* - tests for select statements that use sub-selects.
   180    180   #
   181    181   do_eqp_test 3.1.1 {
   182    182     SELECT (SELECT x FROM t1 AS sub) FROM t1;
   183    183   } {
   184         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          184  +  0 0 0 {SCAN TABLE t1}
   185    185     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   186         -  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          186  +  1 0 0 {SCAN TABLE t1 AS sub}
   187    187   }
   188    188   do_eqp_test 3.1.2 {
   189    189     SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
   190    190   } {
   191         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          191  +  0 0 0 {SCAN TABLE t1}
   192    192     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   193         -  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          193  +  1 0 0 {SCAN TABLE t1 AS sub}
   194    194   }
   195    195   do_eqp_test 3.1.3 {
   196    196     SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
   197    197   } {
   198         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          198  +  0 0 0 {SCAN TABLE t1}
   199    199     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   200         -  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          200  +  1 0 0 {SCAN TABLE t1 AS sub}
   201    201     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   202    202   }
   203    203   do_eqp_test 3.1.4 {
   204    204     SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
   205    205   } {
   206         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          206  +  0 0 0 {SCAN TABLE t1}
   207    207     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   208         -  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
          208  +  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
   209    209   }
   210    210   
   211    211   det 3.2.1 {
   212    212     SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
   213    213   } {
   214         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          214  +  1 0 0 {SCAN TABLE t1} 
   215    215     1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   216         -  0 0 0 {SCAN SUBQUERY 1 (~10 rows)} 
          216  +  0 0 0 {SCAN SUBQUERY 1} 
   217    217     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   218    218   }
   219    219   det 3.2.2 {
   220    220     SELECT * FROM 
   221    221       (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
   222    222       (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
   223    223     ORDER BY x2.y LIMIT 5
   224    224   } {
   225         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          225  +  1 0 0 {SCAN TABLE t1} 
   226    226     1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   227         -  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
   228         -  0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)} 
   229         -  0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)} 
          227  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 
          228  +  0 0 0 {SCAN SUBQUERY 1 AS x1} 
          229  +  0 1 1 {SCAN SUBQUERY 2 AS x2} 
   230    230     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   231    231   }
   232    232   
   233    233   det 3.3.1 {
   234    234     SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
   235    235   } {
   236         -  0 0 0 {SCAN TABLE t1 (~100000 rows)} 
          236  +  0 0 0 {SCAN TABLE t1} 
   237    237     0 0 0 {EXECUTE LIST SUBQUERY 1} 
   238         -  1 0 0 {SCAN TABLE t2 (~1000000 rows)}
          238  +  1 0 0 {SCAN TABLE t2}
   239    239   }
   240    240   det 3.3.2 {
   241    241     SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
   242    242   } {
   243         -  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          243  +  0 0 0 {SCAN TABLE t1} 
   244    244     0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
   245         -  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          245  +  1 0 0 {SCAN TABLE t2}
   246    246   }
   247    247   det 3.3.3 {
   248    248     SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
   249    249   } {
   250         -  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          250  +  0 0 0 {SCAN TABLE t1} 
   251    251     0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 
   252         -  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          252  +  1 0 0 {SCAN TABLE t2}
   253    253   }
   254    254   
   255    255   #-------------------------------------------------------------------------
   256    256   # Test cases eqp-4.* - tests for composite select statements.
   257    257   #
   258    258   do_eqp_test 4.1.1 {
   259    259     SELECT * FROM t1 UNION ALL SELECT * FROM t2
   260    260   } {
   261         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   262         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          261  +  1 0 0 {SCAN TABLE t1} 
          262  +  2 0 0 {SCAN TABLE t2} 
   263    263     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   264    264   }
   265    265   do_eqp_test 4.1.2 {
   266    266     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
   267    267   } {
   268         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          268  +  1 0 0 {SCAN TABLE t1} 
   269    269     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   270         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          270  +  2 0 0 {SCAN TABLE t2} 
   271    271     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   272    272     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   273    273   }
   274    274   do_eqp_test 4.1.3 {
   275    275     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
   276    276   } {
   277         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          277  +  1 0 0 {SCAN TABLE t1} 
   278    278     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   279         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          279  +  2 0 0 {SCAN TABLE t2} 
   280    280     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   281    281     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
   282    282   }
   283    283   do_eqp_test 4.1.4 {
   284    284     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
   285    285   } {
   286         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          286  +  1 0 0 {SCAN TABLE t1} 
   287    287     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   288         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          288  +  2 0 0 {SCAN TABLE t2} 
   289    289     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   290    290     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
   291    291   }
   292    292   do_eqp_test 4.1.5 {
   293    293     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
   294    294   } {
   295         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          295  +  1 0 0 {SCAN TABLE t1} 
   296    296     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   297         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          297  +  2 0 0 {SCAN TABLE t2} 
   298    298     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   299    299     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
   300    300   }
   301    301   
   302    302   do_eqp_test 4.2.2 {
   303    303     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
   304    304   } {
   305         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          305  +  1 0 0 {SCAN TABLE t1} 
   306    306     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   307         -  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
          307  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 
   308    308     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   309    309   }
   310    310   do_eqp_test 4.2.3 {
   311    311     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
   312    312   } {
   313         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          313  +  1 0 0 {SCAN TABLE t1} 
   314    314     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   315         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          315  +  2 0 0 {SCAN TABLE t2} 
   316    316     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   317    317     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
   318    318   }
   319    319   do_eqp_test 4.2.4 {
   320    320     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
   321    321   } {
   322         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          322  +  1 0 0 {SCAN TABLE t1} 
   323    323     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   324         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          324  +  2 0 0 {SCAN TABLE t2} 
   325    325     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   326    326     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
   327    327   }
   328    328   do_eqp_test 4.2.5 {
   329    329     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
   330    330   } {
   331         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          331  +  1 0 0 {SCAN TABLE t1} 
   332    332     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   333         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          333  +  2 0 0 {SCAN TABLE t2} 
   334    334     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   335    335     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
   336    336   }
   337    337   
   338    338   do_eqp_test 4.3.1 {
   339    339     SELECT x FROM t1 UNION SELECT x FROM t2
   340    340   } {
   341         -  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   342         -  2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
          341  +  1 0 0 {SCAN TABLE t1} 
          342  +  2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 
   343    343     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
   344    344   }
   345    345   
   346    346   do_eqp_test 4.3.2 {
   347    347     SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
   348    348   } {
   349         -  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   350         -  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
          349  +  2 0 0 {SCAN TABLE t1} 
          350  +  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 
   351    351     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
   352         -  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          352  +  4 0 0 {SCAN TABLE t1} 
   353    353     0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
   354    354   }
   355    355   do_eqp_test 4.3.3 {
   356    356     SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
   357    357   } {
   358         -  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          358  +  2 0 0 {SCAN TABLE t1} 
   359    359     2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   360         -  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
          360  +  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 
   361    361     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
   362         -  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          362  +  4 0 0 {SCAN TABLE t1} 
   363    363     4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   364    364     0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
   365    365   }
   366    366   
   367    367   #-------------------------------------------------------------------------
   368    368   # This next block of tests verifies that the examples on the 
   369    369   # lang_explain.html page are correct.
   370    370   #
   371    371   drop_all_tables
   372    372   
   373    373   # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
   374         -# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
          374  +# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1
   375    375   do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
   376    376   det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
   377         -  0 0 0 {SCAN TABLE t1 (~100000 rows)}
          377  +  0 0 0 {SCAN TABLE t1}
   378    378   }
   379    379   
   380    380   # EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
   381    381   # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
   382         -# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
          382  +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?)
   383    383   do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
   384    384   det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
   385         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
          385  +  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
   386    386   }
   387    387   
   388    388   # EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
   389    389   # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
   390         -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
          390  +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
   391    391   do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
   392    392   det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
   393         -  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
          393  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
   394    394   }
   395    395   
   396    396   # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
   397    397   # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
   398         -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
   399         -# (~1000000 rows)
          398  +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|1|SCAN TABLE t2
          399  +#
   400    400   do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
   401    401   det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
   402         -  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
   403         -  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          402  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
          403  +  0 1 1 {SCAN TABLE t2}
   404    404   }
   405    405   
   406    406   # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
   407    407   # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
   408         -# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
   409         -# (~1000000 rows)
          408  +# USING COVERING INDEX i2 (a=? AND b>?) 0|1|0|SCAN TABLE t2
          409  +#
   410    410   det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
   411         -  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
   412         -  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
          411  +  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
          412  +  0 1 0 {SCAN TABLE t2}
   413    413   }
   414    414   
   415    415   # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
   416    416   # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
   417         -# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
   418         -# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
          417  +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
          418  +# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
   419    419   do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
   420    420   det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
   421         -  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
   422         -  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
          421  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
          422  +  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
   423    423   }
   424    424   
   425    425   # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
   426         -# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
          426  +# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP
   427    427   # B-TREE FOR ORDER BY
   428    428   det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
   429         -  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
          429  +  0 0 0 {SCAN TABLE t2}
   430    430     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   431    431   }
   432    432   
   433    433   # EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
   434    434   # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
   435         -# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
          435  +# 0|0|0|SCAN TABLE t2 USING INDEX i4
   436    436   do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
   437    437   det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
   438         -  0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
          438  +  0 0 0 {SCAN TABLE t2 USING INDEX i4}
   439    439   }
   440    440   
   441    441   # EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
   442    442   # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
   443         -# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
   444         -# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
          443  +# 0|0|0|SCAN TABLE t2 0|0|0|EXECUTE SCALAR SUBQUERY 1
          444  +# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
   445    445   # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
   446         -# INDEX i3 (b=?) (~10 rows)
          446  +# INDEX i3 (b=?)
   447    447   det 5.9 {
   448    448     SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
   449    449   } {
   450         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
          450  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
   451    451     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   452         -  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
          452  +  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
   453    453     0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
   454         -  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
          454  +  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
   455    455   }
   456    456   
   457    457   # EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
   458    458   # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
   459         -# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
   460         -# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
          459  +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 0|0|0|SCAN
          460  +# SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR GROUP BY
   461    461   det 5.10 {
   462    462     SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
   463    463   } {
   464         -  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
   465         -  0 0 0 {SCAN SUBQUERY 1 (~100 rows)}
          464  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
          465  +  0 0 0 {SCAN SUBQUERY 1}
   466    466     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
   467    467   }
   468    468   
   469    469   # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
   470    470   # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
   471         -# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
          471  +# (c=?) 0|1|1|SCAN TABLE t1
   472    472   det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
   473         -  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
   474         -  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
          473  +  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
          474  +  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
   475    475   }
   476    476   
   477    477   # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
   478         -# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
   479         -# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
          478  +# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1
          479  +# 2|0|0|SCAN TABLE t2 0|0|0|COMPOUND SUBQUERIES 1 AND 2
   480    480   # USING TEMP B-TREE (UNION)
   481    481   det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
   482         -  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
   483         -  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
          482  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
          483  +  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
   484    484     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
   485    485   }
   486    486   
   487    487   # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
   488    488   # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
   489         -# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
          489  +# COVERING INDEX i2 2|0|0|SCAN TABLE t2
   490    490   # 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2
   491    491   # (EXCEPT)
   492    492   det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
   493         -  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
   494         -  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
          493  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
          494  +  2 0 0 {SCAN TABLE t2}
   495    495     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   496    496     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
   497    497   }
   498    498   
   499    499   
   500    500   #-------------------------------------------------------------------------
   501    501   # The following tests - eqp-6.* - test that the example C code on 
................................................................................
   527    527       set data
   528    528     }] [list $res]
   529    529   }
   530    530   
   531    531   do_peqp_test 6.1 {
   532    532     SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1
   533    533   } [string trimleft {
   534         -1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
   535         -2 0 0 SCAN TABLE t2 (~1000000 rows)
          534  +1 0 0 SCAN TABLE t1 USING COVERING INDEX i2
          535  +2 0 0 SCAN TABLE t2
   536    536   2 0 0 USE TEMP B-TREE FOR ORDER BY
   537    537   0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
   538    538   }]
   539    539   
   540    540   #-------------------------------------------------------------------------
   541    541   # The following tests - eqp-7.* - test that queries that use the OP_Count
   542    542   # optimization return something sensible with EQP.
................................................................................
   546    546   do_execsql_test 7.0 {
   547    547     CREATE TABLE t1(a, b);
   548    548     CREATE TABLE t2(a, b);
   549    549     CREATE INDEX i1 ON t2(a);
   550    550   }
   551    551   
   552    552   det 7.1 "SELECT count(*) FROM t1" {
   553         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          553  +  0 0 0 {SCAN TABLE t1}
   554    554   }
   555    555   
   556    556   det 7.2 "SELECT count(*) FROM t2" {
   557         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)}
          557  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
   558    558   }
   559    559   
   560    560   do_execsql_test 7.3 {
   561    561     INSERT INTO t1 VALUES(1, 2);
   562    562     INSERT INTO t1 VALUES(3, 4);
   563    563   
   564    564     INSERT INTO t2 VALUES(1, 2);
................................................................................
   568    568     ANALYZE;
   569    569   }
   570    570   
   571    571   db close
   572    572   sqlite3 db test.db
   573    573   
   574    574   det 7.4 "SELECT count(*) FROM t1" {
   575         -  0 0 0 {SCAN TABLE t1 (~2 rows)}
          575  +  0 0 0 {SCAN TABLE t1}
   576    576   }
   577    577   
   578    578   det 7.5 "SELECT count(*) FROM t2" {
   579         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)}
          579  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
   580    580   }
   581    581   
   582    582   
   583    583   finish_test

Changes to test/exclusive.test.

   502    502   
   503    503   do_execsql_test exclusive-6.5 {
   504    504     PRAGMA locking_mode = EXCLUSIVE;
   505    505     SELECT * FROM sqlite_master;
   506    506   } {exclusive}
   507    507   
   508    508   finish_test
   509         -

Changes to test/fallocate.test.

   139    139       execsql { PRAGMA wal_checkpoint }
   140    140       file size test.db
   141    141     } [expr 32*1024]
   142    142   }
   143    143   
   144    144   
   145    145   finish_test
   146         -

Changes to test/filefmt.test.

   244    244   do_test filefmt-4.4 { 
   245    245     sqlite3 db2 bak.db
   246    246     db2 eval { PRAGMA integrity_check }
   247    247   } {ok}
   248    248   db2 close
   249    249   
   250    250   finish_test
   251         -

Changes to test/fts3aa.test.

   220    220   } {}
   221    221   do_catchsql_test fts3aa-7.5 {
   222    222     CREATE VIRTUAL TABLE t4 USING fts4(tokenize=simple, tokenize=simple);
   223    223   } {1 {unrecognized parameter: tokenize=simple}}
   224    224   
   225    225   
   226    226   finish_test
   227         -

Changes to test/fts3ao.test.

   216    216   do_execsql_test 5.2 {
   217    217     ALTER TABLE t7 RENAME TO t8;
   218    218     SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%';
   219    219     SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%';
   220    220   } {0 6}
   221    221   
   222    222   finish_test
   223         -

Changes to test/fts3atoken.test.

   189    189   
   190    190   do_test fts3token-internal {
   191    191     execsql { SELECT fts3_tokenizer_internal_test() }
   192    192   } {ok}
   193    193   
   194    194   
   195    195   finish_test
   196         -
   197         -

Changes to test/fts3auto.test.

   703    703     do_fts3query_test 7.$tn.1             t1 {"M B"}
   704    704     do_fts3query_test 7.$tn.2             t1 {"B D"}
   705    705     do_fts3query_test 7.$tn.3 -deferred B t1 {"M B D"}
   706    706   }
   707    707   
   708    708   set sqlite_fts3_enable_parentheses $sfep
   709    709   finish_test
   710         -

Changes to test/fts3aux1.test.

   101    101   db func rec rec
   102    102   
   103    103   # Use EQP to show that the WHERE expression "term='braid'" uses a different
   104    104   # index number (1) than "+term='braid'" (0).
   105    105   #
   106    106   do_execsql_test 2.1.1.1 {
   107    107     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
   108         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} }
          108  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} }
   109    109   do_execsql_test 2.1.1.2 {
   110    110     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
   111         -} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}}
          111  +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}}
   112    112   
   113    113   # Now show that using "term='braid'" means the virtual table returns
   114    114   # only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
   115    115   #
   116    116   do_test 2.1.2.1 {
   117    117     set cnt 0
   118    118     execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
................................................................................
   150    150   
   151    151   # Special case: term=NULL
   152    152   #
   153    153   do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
   154    154   
   155    155   do_execsql_test 2.2.1.1 {
   156    156     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
   157         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~25 rows)} }
          157  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2:} }
   158    158   do_execsql_test 2.2.1.2 {
   159    159     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
   160         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
          160  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
   161    161   
   162    162   do_execsql_test 2.2.1.3 {
   163    163     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
   164         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~25 rows)} }
          164  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4:} }
   165    165   do_execsql_test 2.2.1.4 {
   166    166     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
   167         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
          167  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
   168    168   
   169    169   do_execsql_test 2.2.1.5 {
   170    170     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
   171         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~25 rows)} }
          171  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6:} }
   172    172   do_execsql_test 2.2.1.6 {
   173    173     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
   174         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
          174  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }
   175    175   
   176    176   do_test 2.2.2.1 {
   177    177     set cnt 0
   178    178     execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
   179    179     set cnt
   180    180   } {18}
   181    181   do_test 2.2.2.2 {
................................................................................
   331    331     5    1    "ORDER BY documents"
   332    332     6    1    "ORDER BY documents DESC"
   333    333     7    1    "ORDER BY occurrences ASC"
   334    334     8    1    "ORDER BY occurrences"
   335    335     9    1    "ORDER BY occurrences DESC"
   336    336   } {
   337    337   
   338         -  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}]
          338  +  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}]
   339    339     if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
   340    340   
   341    341     set sql "SELECT * FROM terms $orderby"
   342    342     do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
   343    343   }
   344    344   
   345    345   #-------------------------------------------------------------------------
................................................................................
   406    406   proc do_plansql_test {tn sql r} {
   407    407     uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
   408    408   }
   409    409   
   410    410   do_plansql_test 4.2 {
   411    411     SELECT y FROM x2, terms WHERE y = term AND col = '*'
   412    412   } {
   413         -  0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
   414         -  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 
          413  +  0 0 0 {SCAN TABLE x2} 
          414  +  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
   415    415     a b c d e f g h i j k l
   416    416   }
   417    417   
   418    418   do_plansql_test 4.3 {
   419    419     SELECT y FROM terms, x2 WHERE y = term AND col = '*'
   420    420   } {
   421         -  0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
   422         -  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 
          421  +  0 0 1 {SCAN TABLE x2} 
          422  +  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
   423    423     a b c d e f g h i j k l
   424    424   }
   425    425   
   426    426   do_plansql_test 4.4 {
   427    427     SELECT y FROM x3, terms WHERE y = term AND col = '*'
   428    428   } {
   429         -  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 
   430         -  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
          429  +  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
          430  +  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
   431    431     a b c d e f g h i j k l
   432    432   }
   433    433   
   434    434   do_plansql_test 4.5 {
   435    435     SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
   436    436   } {
   437         -  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 
   438         -  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
          437  +  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
          438  +  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
   439    439     a k l
   440    440   }
   441    441   
   442    442   #-------------------------------------------------------------------------
   443    443   # The following tests check that fts4aux can handle an fts table with an
   444    444   # odd name (one that requires quoting for use in SQL statements). And that
   445    445   # the argument to the fts4aux constructor is properly dequoted before use.

Changes to test/fts3corrupt.test.

   162    162     UPDATE t1_stat SET value = NULL;
   163    163     SELECT matchinfo(t1, 'nxa') FROM t1 WHERE t1 MATCH 't*';
   164    164   } {1 {database disk image is malformed}}
   165    165   do_test 5.3.1 { sqlite3_extended_errcode db } SQLITE_CORRUPT_VTAB
   166    166   
   167    167   
   168    168   finish_test
   169         -

Changes to test/fts3defer2.test.

   149    149     do_execsql_test 2.4.$tn {
   150    150       SELECT docid, mit(matchinfo(t3, 'pcxnal')) FROM t3 WHERE t3 MATCH '"a b c"';
   151    151     } {1 {1 1 1 4 4 11 912 6} 3 {1 1 1 4 4 11 912 6}}
   152    152   }
   153    153   
   154    154   
   155    155   finish_test
   156         -

Changes to test/fts3expr3.test.

   200    200     test_fts3expr2 $::query
   201    201   } -test {
   202    202     faultsim_test_result [list 0 $::result]
   203    203   }
   204    204   
   205    205   set sqlite_fts3_enable_parentheses 0
   206    206   finish_test
   207         -
   208         -
   209         -
   210         -

Changes to test/fts3malloc.test.

   297    297   
   298    298   do_write_test fts3_malloc-5.3 ft_content {
   299    299     INSERT INTO ft8 VALUES('short alongertoken reallyquitealotlongerimeanit andthistokenisjustsolongthatonemightbeforgivenforimaginingthatitwasmerelyacontrivedexampleandnotarealtoken')
   300    300   }
   301    301   
   302    302   
   303    303   finish_test
   304         -

Changes to test/fts3matchinfo.test.

   423    423     INSERT INTO t12 VALUES('a d d a');
   424    424     SELECT mit(matchinfo(t12, 'x')) FROM t12 WHERE t12 MATCH 'a NEAR/1 d OR a';
   425    425   } {
   426    426     {0 3 2 0 3 2 1 4 3} {1 3 2 1 3 2 1 4 3} {2 3 2 2 3 2 2 4 3}
   427    427   }
   428    428   
   429    429   finish_test
   430         -

Changes to test/fts3prefix2.test.

    55     55     {T TX T TX T TX T TX T TX}
    56     56     {T TX T TX T TX T TX T TX}
    57     57     {T TX T TX T TX T TX T TX}
    58     58     {T TX T TX T TX T TX T TX}
    59     59   }
    60     60   
    61     61   finish_test
    62         -

Changes to test/fts3query.test.

   114    114       CREATE VIRTUAL TABLE ft USING fts3(title);
   115    115       CREATE TABLE bt(title);
   116    116     }
   117    117   } {}
   118    118   do_eqp_test fts3query-4.2 {
   119    119     SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date
   120    120   } {
   121         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   122         -  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)}
          121  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 
          122  +  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:}
   123    123   }
   124    124   do_eqp_test fts3query-4.3 {
   125    125     SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date
   126    126   } {
   127         -  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   128         -  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)}
          127  +  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 
          128  +  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:}
   129    129   }
   130    130   do_eqp_test fts3query-4.4 {
   131    131     SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date
   132    132   } {
   133         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   134         -  0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          133  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 
          134  +  0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)}
   135    135   }
   136    136   do_eqp_test fts3query-4.5 {
   137    137     SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date
   138    138   } {
   139         -  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   140         -  0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          139  +  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 
          140  +  0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)}
   141    141   }
   142    142   
   143    143   
   144    144   # Test that calling matchinfo() with the wrong number of arguments, or with
   145    145   # an invalid argument returns an error.
   146    146   #
   147    147   do_execsql_test 5.1 {

Changes to test/fts3shared.test.

   170    170     execsql ROLLBACK dbW 
   171    171   }
   172    172   
   173    173   dbW close
   174    174   dbR close
   175    175   sqlite3_enable_shared_cache $::enable_shared_cache
   176    176   finish_test
   177         -

Changes to test/fts3snippet.test.


Changes to test/fts3sort.test.

   178    178     INSERT INTO t4(docid, x) VALUES(1, 'ab');
   179    179     SELECT rowid FROM t4 WHERE x MATCH 'a*';
   180    180   } {-113382409004785664 1}
   181    181   
   182    182   
   183    183   
   184    184   finish_test
   185         -

Changes to test/fts3tok1.test.

   109    109   do_catchsql_test 2.1 {
   110    110     CREATE VIRTUAL TABLE t4 USING fts3tokenize;
   111    111     SELECT * FROM t4;
   112    112   } {1 {SQL logic error or missing database}}
   113    113   
   114    114   
   115    115   finish_test
   116         -
   117         -

Changes to test/fts3tok_err.test.

    41     41     execsql { SELECT token FROM t1 WHERE input = 'A galaxy far, far away' } 
    42     42   } -test {
    43     43     faultsim_test_result {0 {a galaxy far far away}} 
    44     44   }
    45     45   
    46     46   
    47     47   finish_test
    48         -
    49         -

Changes to test/fts4content.test.

   619    619   do_execsql_test 10.7 {
   620    620     SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
   621    621   } {
   622    622     {...c d [e] f g...}
   623    623   }
   624    624   
   625    625   finish_test
   626         -

Changes to test/incrblob3.test.

   265    265     sqlite3_db_config_lookaside db 0 0 0
   266    266     list [catch {db incrblob blobs v 1} msg] $msg
   267    267   } {1 {database schema has changed}}
   268    268   db close
   269    269   tvfs delete
   270    270   
   271    271   finish_test
   272         -

Changes to test/incrblob4.test.

    83     83     set new [string repeat % 900]
    84     84     execsql { UPDATE t1 SET v = $new WHERE k = 20 }
    85     85     execsql { DELETE FROM t1 WHERE k=19 }
    86     86     execsql { INSERT INTO t1(v) VALUES($new) }
    87     87   } {}
    88     88   
    89     89   finish_test
    90         -

Changes to test/incrblobfault.test.

    63     63     gets $::blob
    64     64   } -test {
    65     65     faultsim_test_result {0 {hello world}}
    66     66     catch { close $::blob }
    67     67   }
    68     68   
    69     69   finish_test
    70         -

Changes to test/incrvacuum3.test.

   147    147     }
   148    148   
   149    149     do_execsql_test $T.1.x.1 { PRAGMA freelist_count   } 0
   150    150     do_execsql_test $T.1.x.2 { SELECT count(*) FROM t1 } 128
   151    151   }
   152    152   
   153    153   finish_test
   154         -

Changes to test/indexedby.test.

    38     38     uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
    39     39   }
    40     40   
    41     41   # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
    42     42   #
    43     43   do_execsql_test indexedby-1.2 {
    44     44     EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
    45         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}}
           45  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    46     46   do_execsql_test indexedby-1.3 {
    47     47     EXPLAIN QUERY PLAN select * from t1 ; 
    48         -} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
           48  +} {0 0 0 {SCAN TABLE t1}}
    49     49   do_execsql_test indexedby-1.4 {
    50     50     EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
    51     51   } {
    52         -  0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 
    53         -  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
           52  +  0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 
           53  +  0 1 0 {SCAN TABLE t1}
    54     54   }
    55     55   
    56     56   # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
    57     57   # attached to a table in the FROM clause, but not to a sub-select or
    58     58   # SQL view. Also test that specifying an index that does not exist or
    59     59   # is attached to a different table is detected as an error.
    60     60   # 
................................................................................
    81     81     catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
    82     82   } {1 {no such index: i1}}
    83     83   
    84     84   # Tests for single table cases.
    85     85   #
    86     86   do_execsql_test indexedby-3.1 {
    87     87     EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
    88         -} {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
           88  +} {0 0 0 {SCAN TABLE t1}}
    89     89   do_execsql_test indexedby-3.2 {
    90     90     EXPLAIN QUERY PLAN 
    91     91     SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
    92         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
           92  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    93     93   do_execsql_test indexedby-3.3 {
    94     94     EXPLAIN QUERY PLAN 
    95     95     SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
    96         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
           96  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
    97     97   do_test indexedby-3.4 {
    98     98     catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
    99     99   } {1 {cannot use index: i2}}
   100    100   do_test indexedby-3.5 {
   101    101     catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
   102    102   } {1 {cannot use index: i2}}
   103    103   do_test indexedby-3.6 {
................................................................................
   106    106   do_test indexedby-3.7 {
   107    107     catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
   108    108   } {0 {}}
   109    109   
   110    110   do_execsql_test indexedby-3.8 {
   111    111     EXPLAIN QUERY PLAN 
   112    112     SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
   113         -} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
          113  +} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}}
   114    114   do_execsql_test indexedby-3.9 {
   115    115     EXPLAIN QUERY PLAN 
   116    116     SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
   117         -} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
          117  +} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}}
   118    118   do_test indexedby-3.10 {
   119    119     catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
   120    120   } {1 {cannot use index: sqlite_autoindex_t3_1}}
   121    121   do_test indexedby-3.11 {
   122    122     catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
   123    123   } {1 {no such index: sqlite_autoindex_t3_2}}
   124    124   
   125    125   # Tests for multiple table cases.
   126    126   #
   127    127   do_execsql_test indexedby-4.1 {
   128    128     EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
   129    129   } {
   130         -  0 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   131         -  0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
          130  +  0 0 0 {SCAN TABLE t1} 
          131  +  0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)}
   132    132   }
   133    133   do_execsql_test indexedby-4.2 {
   134    134     EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
   135    135   } {
   136         -  0 0 1 {SCAN TABLE t2 (~1000000 rows)} 
   137         -  0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
          136  +  0 0 1 {SCAN TABLE t2} 
          137  +  0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
   138    138   }
   139    139   do_test indexedby-4.3 {
   140    140     catchsql {
   141    141       SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
   142    142     }
   143    143   } {1 {cannot use index: i1}}
   144    144   do_test indexedby-4.4 {
................................................................................
   150    150   # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
   151    151   # also tests that nothing bad happens if an index refered to by
   152    152   # a CREATE VIEW statement is dropped and recreated.
   153    153   #
   154    154   do_execsql_test indexedby-5.1 {
   155    155     CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
   156    156     EXPLAIN QUERY PLAN SELECT * FROM v2 
   157         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}}
          157  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
   158    158   do_execsql_test indexedby-5.2 {
   159    159     EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
   160         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}}
          160  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
   161    161   do_test indexedby-5.3 {
   162    162     execsql { DROP INDEX i1 }
   163    163     catchsql { SELECT * FROM v2 }
   164    164   } {1 {no such index: i1}}
   165    165   do_test indexedby-5.4 {
   166    166     # Recreate index i1 in such a way as it cannot be used by the view query.
   167    167     execsql { CREATE INDEX i1 ON t1(b) }
................................................................................
   174    174     catchsql { SELECT * FROM v2 }
   175    175   } {0 {}}
   176    176   
   177    177   # Test that "NOT INDEXED" may use the rowid index, but not others.
   178    178   # 
   179    179   do_execsql_test indexedby-6.1 {
   180    180     EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
   181         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}}
          181  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
   182    182   do_execsql_test indexedby-6.2 {
   183    183     EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
   184         -} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}}
          184  +} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY}}
   185    185   
   186    186   # Test that "INDEXED BY" can be used in a DELETE statement.
   187    187   # 
   188    188   do_execsql_test indexedby-7.1 {
   189    189     EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
   190         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
          190  +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   191    191   do_execsql_test indexedby-7.2 {
   192    192     EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
   193         -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
          193  +} {0 0 0 {SCAN TABLE t1}}
   194    194   do_execsql_test indexedby-7.3 {
   195    195     EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
   196         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
          196  +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   197    197   do_execsql_test indexedby-7.4 {
   198    198     EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
   199         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
          199  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   200    200   do_execsql_test indexedby-7.5 {
   201    201     EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
   202         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
          202  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
   203    203   do_test indexedby-7.6 {
   204    204     catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
   205    205   } {1 {cannot use index: i2}}
   206    206   
   207    207   # Test that "INDEXED BY" can be used in an UPDATE statement.
   208    208   # 
   209    209   do_execsql_test indexedby-8.1 {
   210    210     EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
   211         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
          211  +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   212    212   do_execsql_test indexedby-8.2 {
   213    213     EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
   214         -} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
          214  +} {0 0 0 {SCAN TABLE t1}}
   215    215   do_execsql_test indexedby-8.3 {
   216    216     EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
   217         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
          217  +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   218    218   do_execsql_test indexedby-8.4 {
   219    219     EXPLAIN QUERY PLAN 
   220    220     UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
   221         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
          221  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   222    222   do_execsql_test indexedby-8.5 {
   223    223     EXPLAIN QUERY PLAN 
   224    224     UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
   225         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
          225  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
   226    226   do_test indexedby-8.6 {
   227    227     catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
   228    228   } {1 {cannot use index: i2}}
   229    229   
   230    230   # Test that bug #3560 is fixed.
   231    231   #
   232    232   do_test indexedby-9.1 {

Changes to test/io.test.

   637    637     hexio_write test.db [expr 1024 * 5] [string repeat 00 2048]
   638    638     do_execsql_test 6.2.$tn.3 { PRAGMA integrity_check } {ok}
   639    639     db close
   640    640   }
   641    641   
   642    642   sqlite3_simulate_device -char {} -sectorsize 0
   643    643   finish_test
   644         -

Changes to test/ioerr6.test.

    85     85     db eval { CREATE TABLE t3(x) }
    86     86     if {[db one { PRAGMA integrity_check }] != "ok"} {
    87     87       error "integrity check failed"
    88     88     }
    89     89   }
    90     90   
    91     91   finish_test
    92         -

Changes to test/like.test.

   162    162     set ::sqlite_sort_count 0
   163    163     set data [execsql $sql]
   164    164     if {$::sqlite_sort_count} {set x sort} {set x nosort}
   165    165     lappend data $x
   166    166     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
   167    167     # puts eqp=$eqp
   168    168     foreach {a b c x} $eqp {
   169         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \
          169  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
   170    170           $x all as tab idx]} {
   171    171         lappend data {} $idx
   172         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          172  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
   173    173           $x all as tab idx]} {
   174    174         lappend data $tab $idx
   175         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          175  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
   176    176         lappend data $tab *
   177    177       }
   178    178     }
   179    179     return $data   
   180    180   }
   181    181   
   182    182   # Perform tests on the like optimization.

Changes to test/lock7.test.

    54     54     execsql { COMMIT } db1
    55     55   } {}
    56     56   
    57     57   db1 close
    58     58   db2 close
    59     59   
    60     60   finish_test
    61         -

Changes to test/misc7.test.

   265    265   sqlite3 db test.db
   266    266   
   267    267   ifcapable explain {
   268    268     do_execsql_test misc7-14.1 {
   269    269       CREATE TABLE abc(a PRIMARY KEY, b, c);
   270    270       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
   271    271     } {
   272         -    0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          272  +    0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)}
   273    273     }
   274    274     do_execsql_test misc7-14.2 {
   275    275       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
   276    276     } {0 0 0 
   277         -     {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?) (~1 rows)}
          277  +     {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)}
   278    278     }
   279    279     do_execsql_test misc7-14.3 {
   280    280       EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
   281    281     } {0 0 0 
   282         -     {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (~1000000 rows)}
          282  +     {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1}
   283    283     }
   284    284   }
   285    285   
   286    286   db close
   287    287   forcedelete test.db
   288    288   forcedelete test.db-journal
   289    289   sqlite3 db test.db

Changes to test/notify3.test.

   146    146   }
   147    147   catch { db1 close }
   148    148   catch { db2 close }
   149    149   
   150    150   
   151    151   sqlite3_enable_shared_cache $esc
   152    152   finish_test
   153         -

Changes to test/pager1.test.

  2811   2811   
  2812   2812   do_test 43.3 {
  2813   2813     db eval { SELECT * FROM t3 }
  2814   2814     sqlite3_db_status db CACHE_MISS 0
  2815   2815   } {0 1 0}
  2816   2816   
  2817   2817   finish_test
  2818         -

Changes to test/pagerfault.test.

  1542   1542     catch { db2 close }
  1543   1543   }
  1544   1544   
  1545   1545   sqlite3_shutdown
  1546   1546   sqlite3_config_uri 0
  1547   1547   
  1548   1548   finish_test
  1549         -

Changes to test/pagerfault2.test.

    92     92     execsql { INSERT INTO t1 VALUES (a_string(2000000), a_string(2500000)) }
    93     93   } -test {
    94     94     faultsim_test_result {0 {}}
    95     95   }
    96     96   
    97     97   sqlite3_memdebug_vfs_oom_test 1
    98     98   finish_test
    99         -

Changes to test/pagerfault3.test.

    57     57     }
    58     58   } -test {
    59     59     faultsim_test_result {0 {}} 
    60     60     faultsim_integrity_check
    61     61   }
    62     62   
    63     63   finish_test
    64         -

Changes to test/securedel2.test.

    88     88     for {set i 2} {$i <= 850} {incr i 5} {
    89     89       incr n [detect_blob {} $i]
    90     90     }
    91     91     set n
    92     92   } {0}
    93     93   
    94     94   finish_test
    95         -

Changes to test/shared8.test.

   106    106     catchsql { SELECT * FROM v1 } db4
   107    107   } {1 {no such table: v1}}
   108    108   
   109    109   
   110    110   foreach db {db1 db2 db3 db4} { catch { $db close } }
   111    111   sqlite3_enable_shared_cache $::enable_shared_cache
   112    112   finish_test
   113         -

Changes to test/sharedlock.test.

    48     48   } {1 one 2 two 3 three}
    49     49   
    50     50   db close
    51     51   db2 close
    52     52   
    53     53   sqlite3_enable_shared_cache $::enable_shared_cache
    54     54   finish_test
    55         -

Changes to test/tkt-385a5b56b9.test.

    31     31   do_execsql_test 2.0 {
    32     32     CREATE TABLE t2(x, y NOT NULL);
    33     33     CREATE UNIQUE INDEX t2x ON t2(x);
    34     34     CREATE UNIQUE INDEX t2y ON t2(y);
    35     35   }
    36     36   
    37     37   do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
    38         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)}
           38  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x}
    39     39   }
    40     40   
    41     41   do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
    42         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)}
           42  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y}
    43     43   }
    44     44   
    45     45   do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
    46         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)}
           46  +  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)}
    47     47   }
    48     48   
    49     49   do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
    50         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)}
           50  +  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?)}
    51     51   }
    52     52   
    53     53   finish_test

Changes to test/tkt-3a77c9714e.test.

    66     66           WHERE Connected=SrcWord LIMIT 1
    67     67         )
    68     68       )
    69     69   } {FACTORING FACTOR SWIMMING SWIMM} 
    70     70   
    71     71   
    72     72   finish_test
    73         -

Changes to test/tkt-3fe897352e.test.


Changes to test/tkt-78e04e52ea.test.

    40     40       CREATE INDEX i1 ON ""("" COLLATE nocase);
    41     41     }
    42     42   } {}
    43     43   do_test tkt-78e04-1.4 {
    44     44     execsql {
    45     45       EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
    46     46     }
    47         -} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1 (~500000 rows)}}
           47  +} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1}}
    48     48   do_test tkt-78e04-1.5 {
    49     49     execsql {
    50     50       DROP TABLE "";
    51     51       SELECT name FROM sqlite_master;
    52     52     }
    53     53   } {t2}
    54     54   
    55     55   do_test tkt-78e04-2.1 {
    56     56     execsql {
    57     57       CREATE INDEX "" ON t2(x);
    58     58       EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5;
    59     59     }
    60         -} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX  (x=?) (~10 rows)}}
           60  +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX  (x=?)}}
    61     61   do_test tkt-78e04-2.2 {
    62     62     execsql {
    63     63       DROP INDEX "";
    64     64       EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
    65     65     }
    66         -} {0 0 0 {SCAN TABLE t2 (~100000 rows)}}
           66  +} {0 0 0 {SCAN TABLE t2}}
    67     67   
    68     68   finish_test

Changes to test/tkt-7a31705a7e6.test.

    19     19   
    20     20   do_execsql_test tkt-7a31705a7e6-1.1 {
    21     21     CREATE TABLE t1 (a INTEGER PRIMARY KEY);
    22     22     CREATE TABLE t2 (a INTEGER PRIMARY KEY, b INTEGER);
    23     23     CREATE TABLE t2x (b INTEGER PRIMARY KEY);
    24     24     SELECT t1.a FROM ((t1 JOIN t2 ON t1.a=t2.a) AS x JOIN t2x ON x.b=t2x.b) as y;
    25     25   } {}
    26         -

Changes to test/tkt-7bbfb7d442.test.

   148    148   do_execsql_test 2.3 {
   149    149     SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END 
   150    150     FROM InventoryControl WHERE SKU=31; 
   151    151   } {{TEST PASSED!}}
   152    152   
   153    153   
   154    154   finish_test
   155         -
   156         -

Changes to test/tkt-c48d99d690.test.

    19     19   do_test 1.1 {
    20     20     execsql { INSERT INTO t2 SELECT * FROM t1 }
    21     21   } {4}
    22     22   
    23     23   do_test 1.2 { execsql VACUUM } {}
    24     24   
    25     25   finish_test
    26         -

Changes to test/tkt-d11f09d36e.test.

    55     55     }
    56     56   } {}
    57     57   do_test tkt-d11f09d36e.5 {
    58     58     execsql { PRAGMA integrity_check }
    59     59   } {ok}
    60     60   
    61     61   finish_test
    62         -

Changes to test/tkt-f3e5abed55.test.

   110    110         SELECT * FROM t2;
   111    111       }
   112    112     } {1 2 3 4 1 2 3 4}
   113    113   }
   114    114   
   115    115   
   116    116   finish_test
   117         -

Changes to test/tkt-f973c7ac31.test.

    80     80         SELECT * FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC 
    81     81       }
    82     82     } {5 4 5 5}
    83     83   } 
    84     84   
    85     85   
    86     86   finish_test
    87         -

Changes to test/tkt3442.test.

    45     45   # These tests perform an EXPLAIN QUERY PLAN on both versions of the 
    46     46   # SELECT referenced in ticket #3442 (both '5000' and "5000") 
    47     47   # and verify that the query plan is the same.
    48     48   #
    49     49   ifcapable explain {
    50     50     do_test tkt3442-1.2 {
    51     51       EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; }
    52         -  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
           52  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
    53     53     do_test tkt3442-1.3 {
    54     54       EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
    55         -  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
           55  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
    56     56   }
    57     57   
    58     58   
    59     59   # Some extra tests testing other permutations of 5000.
    60     60   #
    61     61   ifcapable explain {
    62     62     do_test tkt3442-1.4 {
    63     63       EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; }
    64         -  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
           64  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
    65     65   }
    66     66   do_test tkt3442-1.5 {
    67     67     catchsql {
    68     68       SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
    69     69     }
    70     70   } {1 {no such column: 5000}}
    71     71   
    72     72   finish_test

Changes to test/tkt3918.test.

    53     53   # page 4 from the database free-list. Bug 3918 caused sqlite to
    54     54   # incorrectly report corruption here.
    55     55   do_test tkt3918.5 {
    56     56     execsql { CREATE TABLE t2(a, b) }
    57     57   } {}
    58     58   
    59     59   finish_test
    60         -

Changes to test/tkt3929.test.

    46     46     for {set i 3} {$i < 100} {incr i} {
    47     47       execsql { INSERT INTO t1(a) VALUES($i) }
    48     48     }
    49     49   } {}
    50     50   
    51     51   integrity_check tkt3930-1.3
    52     52   finish_test
    53         -

Changes to test/unordered.test.

    36     36     if {$idxmode == "unordered"} {
    37     37       execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' }
    38     38     }
    39     39     db close
    40     40     sqlite3 db test.db
    41     41     foreach {tn sql r(ordered) r(unordered)} {
    42     42       1   "SELECT * FROM t1 ORDER BY a"
    43         -        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
    44         -        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
           43  +        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
           44  +        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    45     45       2   "SELECT * FROM t1 WHERE a >?"
    46         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~32 rows)}}
    47         -        {0 0 0 {SCAN TABLE t1 (~42 rows)}}
           46  +        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
           47  +        {0 0 0 {SCAN TABLE t1}}
    48     48       3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
    49         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
    50         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)} 
           49  +        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
           50  +        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
    51     51            0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    52     52       4   "SELECT max(a) FROM t1"
    53         -        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
    54         -        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
           53  +        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}}
           54  +        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}}
    55     55       5   "SELECT group_concat(b) FROM t1 GROUP BY a"
    56         -        {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
    57         -        {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
           56  +        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
           57  +        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
    58     58   
    59     59       6   "SELECT * FROM t1 WHERE a = ?"
    60         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
    61         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
           60  +        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
           61  +        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    62     62       7   "SELECT count(*) FROM t1"
    63     63           {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1(~128 rows)}}
    64         -        {0 0 0 {SCAN TABLE t1 (~128 rows)}}
           64  +        {0 0 0 {SCAN TABLE t1}}
    65     65     } {
    66     66       do_eqp_test 1.$idxmode.$tn $sql $r($idxmode)
    67     67     }
    68     68   }
    69     69   
    70     70   finish_test

Changes to test/veryquick.test.

    12     12   
    13     13   set testdir [file dirname $argv0]
    14     14   source $testdir/permutations.test
    15     15   
    16     16   run_test_suite veryquick
    17     17   
    18     18   finish_test
    19         -

Changes to test/wal8.test.

    84     84   
    85     85   do_execsql_test 3.1 {
    86     86     PRAGMA page_size = 4096;
    87     87     SELECT name FROM sqlite_master;
    88     88   } {t1}
    89     89   
    90     90   finish_test
    91         -

Changes to test/walcksum.test.

   386    386         db2 close
   387    387       }
   388    388       set FAIL
   389    389     } {0}
   390    390   }
   391    391     
   392    392   finish_test
   393         -

Changes to test/walcrash.test.

   289    289     do_test walcrash-7.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
   290    290     do_test walcrash-7.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
   291    291   
   292    292     db close
   293    293   }
   294    294   
   295    295   finish_test
   296         -

Changes to test/walcrash2.test.

    92     92   do_test walcrash2-1.3 {
    93     93     sqlite3 db2 test.db
    94     94     execsql { SELECT count(*) FROM t1 } db2
    95     95   } {0}
    96     96   catch { db2 close }
    97     97   
    98     98   finish_test
    99         -

Changes to test/walcrash3.test.

   122    122     do_test 2.$i.2 {
   123    123       sqlite3 db test.db
   124    124       execsql { PRAGMA integrity_check } 
   125    125     } {ok}
   126    126   }
   127    127   
   128    128   finish_test
   129         -

Changes to test/walro.test.

   287    287     do_test 2.1.5 {
   288    288       code1 { db close }
   289    289       code1 { tv delete }
   290    290     } {}
   291    291   }
   292    292   
   293    293   finish_test
   294         -
   295         -

Changes to test/walshared.test.

    56     56     execsql { PRAGMA integrity_check } db2
    57     57   } {ok}
    58     58   
    59     59   
    60     60   
    61     61   sqlite3_enable_shared_cache $::enable_shared_cache
    62     62   finish_test
    63         -

Changes to test/where.test.

    63     63   # small we can be assured that indices are being used properly.
    64     64   #
    65     65   do_test where-1.1.1 {
    66     66     count {SELECT x, y, w FROM t1 WHERE w=10}
    67     67   } {3 121 10 3}
    68     68   do_eqp_test where-1.1.2 {
    69     69     SELECT x, y, w FROM t1 WHERE w=10
    70         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
           70  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
    71     71   do_test where-1.1.3 {
    72     72     db status step
    73     73   } {0}
    74     74   do_test where-1.1.4 {
    75     75     db eval {SELECT x, y, w FROM t1 WHERE +w=10}
    76     76   } {3 121 10}
    77     77   do_test where-1.1.5 {
    78     78     db status step
    79     79   } {99}
    80     80   do_eqp_test where-1.1.6 {
    81     81     SELECT x, y, w FROM t1 WHERE +w=10
    82         -} {*SCAN TABLE t1 *}
           82  +} {*SCAN TABLE t1*}
    83     83   do_test where-1.1.7 {
    84     84     count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
    85     85   } {3 121 10 3}
    86     86   do_eqp_test where-1.1.8 {
    87     87     SELECT x, y, w AS abc FROM t1 WHERE abc=10
    88         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
           88  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
    89     89   do_test where-1.1.9 {
    90     90     db status step
    91     91   } {0}
    92     92   do_test where-1.2.1 {
    93     93     count {SELECT x, y, w FROM t1 WHERE w=11}
    94     94   } {3 144 11 3}
    95     95   do_test where-1.2.2 {
................................................................................
   102    102     count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
   103    103   } {3 144 11 3}
   104    104   do_test where-1.4.1 {
   105    105     count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
   106    106   } {11 3 144 3}
   107    107   do_eqp_test where-1.4.2 {
   108    108     SELECT w, x, y FROM t1 WHERE 11=w AND x>2
   109         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          109  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   110    110   do_test where-1.4.3 {
   111    111     count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
   112    112   } {11 3 144 3}
   113    113   do_eqp_test where-1.4.4 {
   114    114     SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
   115         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          115  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   116    116   do_test where-1.5 {
   117    117     count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
   118    118   } {3 144 3}
   119    119   do_eqp_test where-1.5.2 {
   120    120     SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
   121         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          121  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   122    122   do_test where-1.6 {
   123    123     count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
   124    124   } {3 144 3}
   125    125   do_test where-1.7 {
   126    126     count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
   127    127   } {3 144 3}
   128    128   do_test where-1.8 {
   129    129     count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
   130    130   } {3 144 3}
   131    131   do_eqp_test where-1.8.2 {
   132    132     SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
   133         -} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?) *}
          133  +} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
   134    134   do_eqp_test where-1.8.3 {
   135    135     SELECT x, y FROM t1 WHERE y=144 AND x=3
   136         -} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?) *}
          136  +} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
   137    137   do_test where-1.9 {
   138    138     count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
   139    139   } {3 144 3}
   140    140   do_test where-1.10 {
   141    141     count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
   142    142   } {3 121 3}
   143    143   do_test where-1.11 {

Changes to test/where2.test.

    72     72     set ::sqlite_sort_count 0
    73     73     set data [execsql $sql]
    74     74     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    75     75     lappend data $x
    76     76     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
    77     77     # puts eqp=$eqp
    78     78     foreach {a b c x} $eqp {
    79         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
           79  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
    80     80           $x all as tab idx]} {
    81     81         lappend data $tab $idx
    82         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
           82  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
    83     83         lappend data $tab *
    84     84       }
    85     85     }
    86     86     return $data   
    87     87   }
    88     88   
    89     89   

Changes to test/where3.test.

   107    107   #
   108    108   proc queryplan {sql} {
   109    109     set ::sqlite_sort_count 0
   110    110     set data [execsql $sql]
   111    111     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
   112    112     # puts eqp=$eqp
   113    113     foreach {a b c x} $eqp {
   114         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          114  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
   115    115           $x all as tab idx]} {
   116    116         lappend data $tab $idx
   117         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          117  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
   118    118         lappend data $tab *
   119    119       }
   120    120     }
   121    121     return $data   
   122    122   }
   123    123   
   124    124   
................................................................................
   232    232     CREATE INDEX t301c ON t301(c);
   233    233     INSERT INTO t301 VALUES(1,2,3);
   234    234     CREATE TABLE t302(x, y);
   235    235     INSERT INTO t302 VALUES(4,5);
   236    236     ANALYZE;
   237    237     explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
   238    238   } {
   239         -  0 0 0 {SCAN TABLE t302 (~1 rows)} 
   240         -  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          239  +  0 0 0 {SCAN TABLE t302} 
          240  +  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
   241    241   }
   242    242   do_execsql_test where3-3.1 {
   243    243     explain query plan
   244    244     SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
   245    245   } {
   246         -  0 0 1 {SCAN TABLE t302 (~1 rows)} 
   247         -  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          246  +  0 0 1 {SCAN TABLE t302} 
          247  +  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
   248    248   }
   249    249   
   250    250   # Verify that when there are multiple tables in a join which must be
   251    251   # full table scans that the query planner attempts put the table with
   252    252   # the fewest number of output rows as the outer loop.
   253    253   #
   254    254   do_execsql_test where3-4.0 {
   255    255     CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
   256    256     CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
   257    257     CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
   258    258     EXPLAIN QUERY PLAN
   259    259     SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
   260    260   } {
   261         -  0 0 2 {SCAN TABLE t402 (~500000 rows)} 
   262         -  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
   263         -  0 2 1 {SCAN TABLE t401 (~1000000 rows)}
          261  +  0 0 2 {SCAN TABLE t402} 
          262  +  0 1 0 {SCAN TABLE t400} 
          263  +  0 2 1 {SCAN TABLE t401}
   264    264   }
   265    265   do_execsql_test where3-4.1 {
   266    266     EXPLAIN QUERY PLAN
   267    267     SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
   268    268   } {
   269         -  0 0 1 {SCAN TABLE t401 (~500000 rows)} 
   270         -  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
   271         -  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
          269  +  0 0 1 {SCAN TABLE t401} 
          270  +  0 1 0 {SCAN TABLE t400} 
          271  +  0 2 2 {SCAN TABLE t402}
   272    272   }
   273    273   do_execsql_test where3-4.2 {
   274    274     EXPLAIN QUERY PLAN
   275    275     SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
   276    276   } {
   277         -  0 0 0 {SCAN TABLE t400 (~500000 rows)} 
   278         -  0 1 1 {SCAN TABLE t401 (~1000000 rows)} 
   279         -  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
          277  +  0 0 0 {SCAN TABLE t400} 
          278  +  0 1 1 {SCAN TABLE t401} 
          279  +  0 2 2 {SCAN TABLE t402}
   280    280   }
   281    281   
   282    282   # Verify that a performance regression encountered by firefox
   283    283   # has been fixed.
   284    284   #
   285    285   do_execsql_test where3-5.0 {
   286    286     CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
................................................................................
   304    304      SELECT bbb.title AS tag_title 
   305    305        FROM aaa JOIN bbb ON bbb.id = aaa.parent  
   306    306       WHERE aaa.fk = 'constant'
   307    307         AND LENGTH(bbb.title) > 0
   308    308         AND bbb.parent = 4
   309    309       ORDER BY bbb.title COLLATE NOCASE ASC;
   310    310   } {
   311         -  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
   312         -  0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          311  +  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
          312  +  0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 
   313    313     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   314    314   }
   315    315   do_execsql_test where3-5.1 {
   316    316     EXPLAIN QUERY PLAN
   317    317      SELECT bbb.title AS tag_title 
   318    318        FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
   319    319       WHERE aaa.fk = 'constant'
   320    320         AND LENGTH(bbb.title) > 0
   321    321         AND bbb.parent = 4
   322    322       ORDER BY bbb.title COLLATE NOCASE ASC;
   323    323   } {
   324         -  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
   325         -  0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          324  +  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
          325  +  0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 
   326    326     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   327    327   }
   328    328   do_execsql_test where3-5.2 {
   329    329     EXPLAIN QUERY PLAN
   330    330      SELECT bbb.title AS tag_title 
   331    331        FROM bbb JOIN aaa ON bbb.id = aaa.parent  
   332    332       WHERE aaa.fk = 'constant'
   333    333         AND LENGTH(bbb.title) > 0
   334    334         AND bbb.parent = 4
   335    335       ORDER BY bbb.title COLLATE NOCASE ASC;
   336    336   } {
   337         -  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
   338         -  0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          337  +  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
          338  +  0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 
   339    339     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   340    340   }
   341    341   do_execsql_test where3-5.3 {
   342    342     EXPLAIN QUERY PLAN
   343    343      SELECT bbb.title AS tag_title 
   344    344        FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
   345    345       WHERE aaa.fk = 'constant'
   346    346         AND LENGTH(bbb.title) > 0
   347    347         AND bbb.parent = 4
   348    348       ORDER BY bbb.title COLLATE NOCASE ASC;
   349    349   } {
   350         -  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
   351         -  0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          350  +  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
          351  +  0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 
   352    352     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   353    353   }
   354    354   
   355    355   # Name resolution with NATURAL JOIN and USING
   356    356   #
   357    357   do_test where3-6.setup {
   358    358     db eval {

Changes to test/where7.test.

 23335  23335       FROM t302 JOIN t301 ON t302.c8 = t301.c8
 23336  23336       WHERE t302.c2 = 19571
 23337  23337         AND t302.c3 > 1287603136
 23338  23338         AND (t301.c4 = 1407449685622784
 23339  23339              OR t301.c8 = 1407424651264000)
 23340  23340      ORDER BY t302.c5 LIMIT 200;
 23341  23341   } {
 23342         -  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
 23343         -  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
 23344         -  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
        23342  +  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)} 
        23343  +  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} 
        23344  +  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)} 
 23345  23345     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 23346  23346   }
 23347  23347   
 23348  23348   finish_test

Changes to test/where9.test.

   358    358   
   359    359   ifcapable explain {
   360    360     do_execsql_test where9-3.1 {
   361    361       EXPLAIN QUERY PLAN
   362    362       SELECT t2.a FROM t1, t2
   363    363       WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   364    364     } {
   365         -    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   366         -    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   367         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          365  +    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
          366  +    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
          367  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   368    368     }
   369    369     do_execsql_test where9-3.2 {
   370    370       EXPLAIN QUERY PLAN
   371    371       SELECT coalesce(t2.a,9999)
   372    372       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
   373    373       WHERE t1.a=80
   374    374     } {
   375         -    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   376         -    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   377         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          375  +    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
          376  +    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
          377  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   378    378     }
   379    379   } 
   380    380   
   381    381   # Make sure that INDEXED BY and multi-index OR clauses play well with
   382    382   # one another.
   383    383   #
   384    384   do_test where9-4.1 {
................................................................................
   449    449   ifcapable explain {
   450    450     # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   451    451     # the former is an equality test which is expected to return fewer rows.
   452    452     #
   453    453     do_execsql_test where9-5.1 {
   454    454       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
   455    455     } {
   456         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~3 rows)} 
   457         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~3 rows)}
          456  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} 
          457  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
   458    458     }
   459    459   
   460    460     # In contrast, b=1000 is preferred over any OR-clause.
   461    461     #
   462    462     do_execsql_test where9-5.2 {
   463    463       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
   464    464     } {
   465         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)}
          465  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
   466    466     }
   467    467   
   468    468     # Likewise, inequalities in an AND are preferred over inequalities in
   469    469     # an OR.
   470    470     #
   471    471     do_execsql_test where9-5.3 {
   472    472       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
   473    473     } {
   474         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)}
          474  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
   475    475     }
   476    476   }
   477    477   
   478    478   ############################################################################
   479    479   # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
   480    480   
   481    481   do_test where9-6.2.1 {

Changes to test/whereC.test.

    63     63     do_execsql_test 1.$tn.1 $sql $res
    64     64     do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC"  [lsort -integer -inc  $res]
    65     65     do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec  $res]
    66     66   }
    67     67   
    68     68   
    69     69   finish_test
    70         -

Changes to test/whereE.test.

    43     43     INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2;
    44     44     INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2;
    45     45     ALTER TABLE t2 ADD COLUMN z;
    46     46     UPDATE t2 SET z=2;
    47     47     CREATE UNIQUE INDEX t2zx ON t2(z,x);
    48     48   
    49     49     EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
    50         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           50  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    51     51   do_execsql_test 1.2 {
    52     52     EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
    53         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           53  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    54     54   do_execsql_test 1.3 {
    55     55     ANALYZE;
    56     56     EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
    57         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           57  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    58     58   do_execsql_test 1.4 {
    59     59     EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
    60         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           60  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    61     61   
    62     62   finish_test

Added tool/wherecosttest.c.

            1  +/*
            2  +** 2013-06-10
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** This file contains a simple command-line utility for converting from
           13  +** integers and WhereCost values and back again and for doing simple
           14  +** arithmetic operations (multiple and add) on WhereCost values.
           15  +**
           16  +** Usage:
           17  +**
           18  +**      ./wherecosttest ARGS
           19  +**
           20  +** Arguments:
           21  +**
           22  +**    'x'    Multiple the top two elements of the stack
           23  +**    '+'    Add the top two elements of the stack
           24  +**    NUM    Convert NUM from integer to WhereCost and push onto the stack
           25  +**   ^NUM    Interpret NUM as a WhereCost and push onto stack.
           26  +**
           27  +** Examples:
           28  +**
           29  +** To convert 123 from WhereCost to integer:
           30  +** 
           31  +**         ./wherecosttest ^123
           32  +**
           33  +** To convert 123456 from integer to WhereCost:
           34  +**
           35  +**         ./wherecosttest 123456
           36  +**
           37  +*/
           38  +#include <stdio.h>
           39  +#include <stdlib.h>
           40  +#include <ctype.h>
           41  +
           42  +typedef unsigned short int WhereCost;  /* 10 times log2() */
           43  +
           44  +WhereCost whereCostMultiply(WhereCost a, WhereCost b){ return a+b; }
           45  +WhereCost whereCostAdd(WhereCost a, WhereCost b){
           46  +  static const unsigned char x[] = {
           47  +     10, 10,                         /* 0,1 */
           48  +      9, 9,                          /* 2,3 */
           49  +      8, 8,                          /* 4,5 */
           50  +      7, 7, 7,                       /* 6,7,8 */
           51  +      6, 6, 6,                       /* 9,10,11 */
           52  +      5, 5, 5,                       /* 12-14 */
           53  +      4, 4, 4, 4,                    /* 15-18 */
           54  +      3, 3, 3, 3, 3, 3,              /* 19-24 */
           55  +      2, 2, 2, 2, 2, 2, 2,           /* 25-31 */
           56  +  };
           57  +  if( a<b ){ WhereCost t = a; a = b; b = t; }
           58  +  if( a>b+49 ) return a;
           59  +  if( a>b+31 ) return a+1;
           60  +  return a+x[a-b];
           61  +}
           62  +WhereCost whereCostFromInteger(int x){
           63  +  static WhereCost a[] = { 0, 2, 3, 5, 6, 7, 8, 9 };
           64  +  WhereCost y = 40;
           65  +  if( x<8 ){
           66  +    if( x<2 ) return 0;
           67  +    while( x<8 ){  y -= 10; x <<= 1; }
           68  +  }else{
           69  +    while( x>255 ){ y += 40; x >>= 4; }
           70  +    while( x>15 ){  y += 10; x >>= 1; }
           71  +  }
           72  +  return a[x&7] + y - 10;
           73  +}
           74  +static unsigned long int whereCostToInt(WhereCost x){
           75  +  unsigned long int n;
           76  +  if( x<=10 ) return 1;
           77  +  n = x%10;
           78  +  x /= 10;
           79  +  if( n>=5 ) n -= 2;
           80  +  else if( n>=1 ) n -= 1;
           81  +  if( x>=3 ) return (n+8)<<(x-3);
           82  +  return (n+8)>>(3-x);
           83  +}
           84  +
           85  +int main(int argc, char **argv){
           86  +  int i;
           87  +  int n = 0;
           88  +  WhereCost a[100];
           89  +  for(i=1; i<argc; i++){
           90  +    const char *z = argv[i];
           91  +    if( z[0]=='+' ){
           92  +      if( n>=2 ){
           93  +        a[n-2] = whereCostAdd(a[n-2],a[n-1]);
           94  +        n--;
           95  +      }
           96  +    }else if( z[0]=='x' ){
           97  +      if( n>=2 ){
           98  +        a[n-2] = whereCostMultiply(a[n-2],a[n-1]);
           99  +        n--;
          100  +      }
          101  +    }else if( z[0]=='^' ){
          102  +      a[n++] = atoi(z+1);
          103  +    }else{
          104  +      a[n++] = whereCostFromInteger(atoi(z));
          105  +    }
          106  +  }
          107  +  for(i=n-1; i>=0; i--){
          108  +    printf("%d (%lu)\n", a[i], whereCostToInt(a[i]));
          109  +  }
          110  +  return 0;
          111  +}