/ Check-in [ce27bf38]
Login

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

Overview
Comment:Merge the EXPLAIN QUERY PLAN changes from experimental into trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ce27bf38405ce805dad95ec22cbe68ddc7af544a
User & Date: drh 2010-11-15 14:44:30
Context
2010-11-15
14:51
Test some example code from documentation page eqp.html. check-in: 547bc2c2 user: dan tags: trunk
14:44
Merge the EXPLAIN QUERY PLAN changes from experimental into trunk. check-in: ce27bf38 user: drh tags: trunk
11:35
Minor additions to vacuum.test. check-in: a397ed16 user: dan tags: trunk
2010-11-13
16:42
Change the EXPLAIN QUERY PLAN output to use "USING INDEX" instead of "BY INDEX", and to use "SEARCH" instead of "SCAN" for loops that are not full-table scans. Closed-Leaf check-in: 6611b76b user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1595   1595     */
  1596   1596     if( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->pTriggerTab ){
  1597   1597       int mem = ++pParse->nMem;
  1598   1598       sqlite3VdbeAddOp1(v, OP_If, mem);
  1599   1599       testAddr = sqlite3VdbeAddOp2(v, OP_Integer, 1, mem);
  1600   1600       assert( testAddr>0 || pParse->db->mallocFailed );
  1601   1601     }
         1602  +
         1603  +#ifndef SQLITE_OMIT_EXPLAIN
         1604  +  if( pParse->explain==2 ){
         1605  +    char *zMsg = sqlite3MPrintf(
         1606  +        pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr?"":"CORRELATED ",
         1607  +        pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId
         1608  +    );
         1609  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
         1610  +  }
         1611  +#endif
  1602   1612   
  1603   1613     switch( pExpr->op ){
  1604   1614       case TK_IN: {
  1605   1615         char affinity;              /* Affinity of the LHS of the IN */
  1606   1616         KeyInfo keyInfo;            /* Keyinfo for the generated table */
  1607   1617         int addr;                   /* Address of OP_OpenEphemeral instruction */
  1608   1618         Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */

Changes to src/prepare.c.

   624    624     }
   625    625     rc = pParse->rc;
   626    626   
   627    627   #ifndef SQLITE_OMIT_EXPLAIN
   628    628     if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){
   629    629       static const char * const azColName[] = {
   630    630          "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment",
   631         -       "order", "from", "detail"
          631  +       "selectid", "order", "from", "detail"
   632    632       };
   633    633       int iFirst, mx;
   634    634       if( pParse->explain==2 ){
   635         -      sqlite3VdbeSetNumCols(pParse->pVdbe, 3);
          635  +      sqlite3VdbeSetNumCols(pParse->pVdbe, 4);
   636    636         iFirst = 8;
   637         -      mx = 11;
          637  +      mx = 12;
   638    638       }else{
   639    639         sqlite3VdbeSetNumCols(pParse->pVdbe, 8);
   640    640         iFirst = 0;
   641    641         mx = 8;
   642    642       }
   643    643       for(i=iFirst; i<mx; i++){
   644    644         sqlite3VdbeSetColName(pParse->pVdbe, i-iFirst, COLNAME_NAME,

Changes to src/select.c.

   767    767         pInfo->aColl[i] = pColl;
   768    768         pInfo->aSortOrder[i] = pItem->sortOrder;
   769    769       }
   770    770     }
   771    771     return pInfo;
   772    772   }
   773    773   
          774  +#ifndef SQLITE_OMIT_COMPOUND_SELECT
          775  +/*
          776  +** Name of the connection operator, used for error messages.
          777  +*/
          778  +static const char *selectOpName(int id){
          779  +  char *z;
          780  +  switch( id ){
          781  +    case TK_ALL:       z = "UNION ALL";   break;
          782  +    case TK_INTERSECT: z = "INTERSECT";   break;
          783  +    case TK_EXCEPT:    z = "EXCEPT";      break;
          784  +    default:           z = "UNION";       break;
          785  +  }
          786  +  return z;
          787  +}
          788  +#endif /* SQLITE_OMIT_COMPOUND_SELECT */
          789  +
          790  +#ifndef SQLITE_OMIT_EXPLAIN
          791  +/*
          792  +** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
          793  +** is a no-op. Otherwise, it adds a single row of output to the EQP result,
          794  +** where the caption is of the form:
          795  +**
          796  +**   "USE TEMP B-TREE FOR xxx"
          797  +**
          798  +** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
          799  +** is determined by the zUsage argument.
          800  +*/
          801  +static void explainTempTable(Parse *pParse, const char *zUsage){
          802  +  if( pParse->explain==2 ){
          803  +    Vdbe *v = pParse->pVdbe;
          804  +    char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
          805  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
          806  +  }
          807  +}
          808  +
          809  +/*
          810  +** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
          811  +** is a no-op. Otherwise, it adds a single row of output to the EQP result,
          812  +** where the caption is of one of the two forms:
          813  +**
          814  +**   "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
          815  +**   "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
          816  +**
          817  +** where iSub1 and iSub2 are the integers passed as the corresponding
          818  +** function parameters, and op is the text representation of the parameter
          819  +** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
          820  +** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is 
          821  +** false, or the second form if it is true.
          822  +*/
          823  +static void explainComposite(
          824  +  Parse *pParse,                  /* Parse context */
          825  +  int op,                         /* One of TK_UNION, TK_EXCEPT etc. */
          826  +  int iSub1,                      /* Subquery id 1 */
          827  +  int iSub2,                      /* Subquery id 2 */
          828  +  int bUseTmp                     /* True if a temp table was used */
          829  +){
          830  +  assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
          831  +  if( pParse->explain==2 ){
          832  +    Vdbe *v = pParse->pVdbe;
          833  +    char *zMsg = sqlite3MPrintf(
          834  +        pParse->db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
          835  +        bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
          836  +    );
          837  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
          838  +  }
          839  +}
          840  +
          841  +/*
          842  +** Assign expression b to lvalue a. A second, no-op, version of this macro
          843  +** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
          844  +** in sqlite3Select() to assign values to structure member variables that
          845  +** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
          846  +** code with #ifndef directives.
          847  +*/
          848  +# define explainSetInteger(a, b) a = b
          849  +
          850  +#else
          851  +/* No-op versions of the explainXXX() functions and macros. */
          852  +# define explainTempTable(y,z)
          853  +# define explainComposite(v,w,x,y,z)
          854  +# define explainSetInteger(y,z)
          855  +#endif
   774    856   
   775    857   /*
   776    858   ** If the inner loop was generated using a non-null pOrderBy argument,
   777    859   ** then the results were placed in a sorter.  After the loop is terminated
   778    860   ** we need to run the sorter and output the results.  The following
   779    861   ** routine generates the code needed to do that.
   780    862   */
................................................................................
  1114   1196         sqlite3VdbeSetColName(v, i, COLNAME_NAME, 
  1115   1197             sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
  1116   1198       }
  1117   1199     }
  1118   1200     generateColumnTypes(pParse, pTabList, pEList);
  1119   1201   }
  1120   1202   
  1121         -#ifndef SQLITE_OMIT_COMPOUND_SELECT
  1122         -/*
  1123         -** Name of the connection operator, used for error messages.
  1124         -*/
  1125         -static const char *selectOpName(int id){
  1126         -  char *z;
  1127         -  switch( id ){
  1128         -    case TK_ALL:       z = "UNION ALL";   break;
  1129         -    case TK_INTERSECT: z = "INTERSECT";   break;
  1130         -    case TK_EXCEPT:    z = "EXCEPT";      break;
  1131         -    default:           z = "UNION";       break;
  1132         -  }
  1133         -  return z;
  1134         -}
  1135         -#endif /* SQLITE_OMIT_COMPOUND_SELECT */
  1136         -
  1137   1203   /*
  1138   1204   ** Given a an expression list (which is really the list of expressions
  1139   1205   ** that form the result set of a SELECT statement) compute appropriate
  1140   1206   ** column names for a table that would hold the expression list.
  1141   1207   **
  1142   1208   ** All column names will be unique.
  1143   1209   **
................................................................................
  1463   1529   ){
  1464   1530     int rc = SQLITE_OK;   /* Success code from a subroutine */
  1465   1531     Select *pPrior;       /* Another SELECT immediately to our left */
  1466   1532     Vdbe *v;              /* Generate code to this VDBE */
  1467   1533     SelectDest dest;      /* Alternative data destination */
  1468   1534     Select *pDelete = 0;  /* Chain of simple selects to delete */
  1469   1535     sqlite3 *db;          /* Database connection */
         1536  +#ifndef SQLITE_OMIT_EXPLAIN
         1537  +  int iSub1;            /* EQP id of left-hand query */
         1538  +  int iSub2;            /* EQP id of right-hand query */
         1539  +#endif
  1470   1540   
  1471   1541     /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  1472   1542     ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1473   1543     */
  1474   1544     assert( p && p->pPrior );  /* Calling function guarantees this much */
  1475   1545     db = pParse->db;
  1476   1546     pPrior = p->pPrior;
................................................................................
  1523   1593     */
  1524   1594     switch( p->op ){
  1525   1595       case TK_ALL: {
  1526   1596         int addr = 0;
  1527   1597         assert( !pPrior->pLimit );
  1528   1598         pPrior->pLimit = p->pLimit;
  1529   1599         pPrior->pOffset = p->pOffset;
         1600  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1530   1601         rc = sqlite3Select(pParse, pPrior, &dest);
  1531   1602         p->pLimit = 0;
  1532   1603         p->pOffset = 0;
  1533   1604         if( rc ){
  1534   1605           goto multi_select_end;
  1535   1606         }
  1536   1607         p->pPrior = 0;
  1537   1608         p->iLimit = pPrior->iLimit;
  1538   1609         p->iOffset = pPrior->iOffset;
  1539   1610         if( p->iLimit ){
  1540   1611           addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
  1541   1612           VdbeComment((v, "Jump ahead if LIMIT reached"));
  1542   1613         }
         1614  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1543   1615         rc = sqlite3Select(pParse, p, &dest);
  1544   1616         testcase( rc!=SQLITE_OK );
  1545   1617         pDelete = p->pPrior;
  1546   1618         p->pPrior = pPrior;
  1547   1619         if( addr ){
  1548   1620           sqlite3VdbeJumpHere(v, addr);
  1549   1621         }
................................................................................
  1583   1655           assert( p->pEList );
  1584   1656         }
  1585   1657   
  1586   1658         /* Code the SELECT statements to our left
  1587   1659         */
  1588   1660         assert( !pPrior->pOrderBy );
  1589   1661         sqlite3SelectDestInit(&uniondest, priorOp, unionTab);
         1662  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1590   1663         rc = sqlite3Select(pParse, pPrior, &uniondest);
  1591   1664         if( rc ){
  1592   1665           goto multi_select_end;
  1593   1666         }
  1594   1667   
  1595   1668         /* Code the current SELECT statement
  1596   1669         */
................................................................................
  1602   1675         }
  1603   1676         p->pPrior = 0;
  1604   1677         pLimit = p->pLimit;
  1605   1678         p->pLimit = 0;
  1606   1679         pOffset = p->pOffset;
  1607   1680         p->pOffset = 0;
  1608   1681         uniondest.eDest = op;
         1682  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1609   1683         rc = sqlite3Select(pParse, p, &uniondest);
  1610   1684         testcase( rc!=SQLITE_OK );
  1611   1685         /* Query flattening in sqlite3Select() might refill p->pOrderBy.
  1612   1686         ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
  1613   1687         sqlite3ExprListDelete(db, p->pOrderBy);
  1614   1688         pDelete = p->pPrior;
  1615   1689         p->pPrior = pPrior;
................................................................................
  1667   1741         p->addrOpenEphm[0] = addr;
  1668   1742         p->pRightmost->selFlags |= SF_UsesEphemeral;
  1669   1743         assert( p->pEList );
  1670   1744   
  1671   1745         /* Code the SELECTs to our left into temporary table "tab1".
  1672   1746         */
  1673   1747         sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1);
         1748  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1674   1749         rc = sqlite3Select(pParse, pPrior, &intersectdest);
  1675   1750         if( rc ){
  1676   1751           goto multi_select_end;
  1677   1752         }
  1678   1753   
  1679   1754         /* Code the current SELECT into temporary table "tab2"
  1680   1755         */
................................................................................
  1683   1758         p->addrOpenEphm[1] = addr;
  1684   1759         p->pPrior = 0;
  1685   1760         pLimit = p->pLimit;
  1686   1761         p->pLimit = 0;
  1687   1762         pOffset = p->pOffset;
  1688   1763         p->pOffset = 0;
  1689   1764         intersectdest.iParm = tab2;
         1765  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1690   1766         rc = sqlite3Select(pParse, p, &intersectdest);
  1691   1767         testcase( rc!=SQLITE_OK );
  1692   1768         pDelete = p->pPrior;
  1693   1769         p->pPrior = pPrior;
  1694   1770         sqlite3ExprDelete(db, p->pLimit);
  1695   1771         p->pLimit = pLimit;
  1696   1772         p->pOffset = pOffset;
................................................................................
  1718   1794         sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart);
  1719   1795         sqlite3VdbeResolveLabel(v, iBreak);
  1720   1796         sqlite3VdbeAddOp2(v, OP_Close, tab2, 0);
  1721   1797         sqlite3VdbeAddOp2(v, OP_Close, tab1, 0);
  1722   1798         break;
  1723   1799       }
  1724   1800     }
         1801  +
         1802  +  explainComposite(pParse, p->op, iSub1, iSub2, p->op!=TK_ALL);
  1725   1803   
  1726   1804     /* Compute collating sequences used by 
  1727   1805     ** temporary tables needed to implement the compound select.
  1728   1806     ** Attach the KeyInfo structure to all temporary tables.
  1729   1807     **
  1730   1808     ** This section is run by the right-most SELECT statement only.
  1731   1809     ** SELECT statements to the left always skip this part.  The right-most
................................................................................
  2062   2140     int op;               /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
  2063   2141     KeyInfo *pKeyDup = 0; /* Comparison information for duplicate removal */
  2064   2142     KeyInfo *pKeyMerge;   /* Comparison information for merging rows */
  2065   2143     sqlite3 *db;          /* Database connection */
  2066   2144     ExprList *pOrderBy;   /* The ORDER BY clause */
  2067   2145     int nOrderBy;         /* Number of terms in the ORDER BY clause */
  2068   2146     int *aPermute;        /* Mapping from ORDER BY terms to result set columns */
         2147  +#ifndef SQLITE_OMIT_EXPLAIN
         2148  +  int iSub1;            /* EQP id of left-hand query */
         2149  +  int iSub2;            /* EQP id of right-hand query */
         2150  +#endif
  2069   2151   
  2070   2152     assert( p->pOrderBy!=0 );
  2071   2153     assert( pKeyDup==0 ); /* "Managed" code needs this.  Ticket #3382. */
  2072   2154     db = pParse->db;
  2073   2155     v = pParse->pVdbe;
  2074   2156     assert( v!=0 );       /* Already thrown the error if VDBE alloc failed */
  2075   2157     labelEnd = sqlite3VdbeMakeLabel(v);
................................................................................
  2215   2297   
  2216   2298   
  2217   2299     /* Generate a coroutine to evaluate the SELECT statement to the
  2218   2300     ** left of the compound operator - the "A" select.
  2219   2301     */
  2220   2302     VdbeNoopComment((v, "Begin coroutine for left SELECT"));
  2221   2303     pPrior->iLimit = regLimitA;
         2304  +  explainSetInteger(iSub1, pParse->iNextSelectId);
  2222   2305     sqlite3Select(pParse, pPrior, &destA);
  2223   2306     sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofA);
  2224   2307     sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2225   2308     VdbeNoopComment((v, "End coroutine for left SELECT"));
  2226   2309   
  2227   2310     /* Generate a coroutine to evaluate the SELECT statement on 
  2228   2311     ** the right - the "B" select
................................................................................
  2229   2312     */
  2230   2313     addrSelectB = sqlite3VdbeCurrentAddr(v);
  2231   2314     VdbeNoopComment((v, "Begin coroutine for right SELECT"));
  2232   2315     savedLimit = p->iLimit;
  2233   2316     savedOffset = p->iOffset;
  2234   2317     p->iLimit = regLimitB;
  2235   2318     p->iOffset = 0;  
         2319  +  explainSetInteger(iSub2, pParse->iNextSelectId);
  2236   2320     sqlite3Select(pParse, p, &destB);
  2237   2321     p->iLimit = savedLimit;
  2238   2322     p->iOffset = savedOffset;
  2239   2323     sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofB);
  2240   2324     sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2241   2325     VdbeNoopComment((v, "End coroutine for right SELECT"));
  2242   2326   
................................................................................
  2359   2443     if( p->pPrior ){
  2360   2444       sqlite3SelectDelete(db, p->pPrior);
  2361   2445     }
  2362   2446     p->pPrior = pPrior;
  2363   2447   
  2364   2448     /*** TBD:  Insert subroutine calls to close cursors on incomplete
  2365   2449     **** subqueries ****/
         2450  +  explainComposite(pParse, p->op, iSub1, iSub2, 0);
  2366   2451     return SQLITE_OK;
  2367   2452   }
  2368   2453   #endif
  2369   2454   
  2370   2455   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  2371   2456   /* Forward Declarations */
  2372   2457   static void substExprList(sqlite3*, ExprList*, int, ExprList*);
................................................................................
  3585   3670     int isDistinct;        /* True if the DISTINCT keyword is present */
  3586   3671     int distinct;          /* Table to use for the distinct set */
  3587   3672     int rc = 1;            /* Value to return from this function */
  3588   3673     int addrSortIndex;     /* Address of an OP_OpenEphemeral instruction */
  3589   3674     AggInfo sAggInfo;      /* Information used by aggregate queries */
  3590   3675     int iEnd;              /* Address of the end of the query */
  3591   3676     sqlite3 *db;           /* The database connection */
         3677  +
         3678  +#ifndef SQLITE_OMIT_EXPLAIN
         3679  +  int iRestoreSelectId = pParse->iSelectId;
         3680  +  pParse->iSelectId = pParse->iNextSelectId++;
         3681  +#endif
  3592   3682   
  3593   3683     db = pParse->db;
  3594   3684     if( p==0 || db->mallocFailed || pParse->nErr ){
  3595   3685       return 1;
  3596   3686     }
  3597   3687     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  3598   3688     memset(&sAggInfo, 0, sizeof(sAggInfo));
................................................................................
  3657   3747           isAgg = 1;
  3658   3748           p->selFlags |= SF_Aggregate;
  3659   3749         }
  3660   3750         i = -1;
  3661   3751       }else{
  3662   3752         sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3663   3753         assert( pItem->isPopulated==0 );
         3754  +      explainSetInteger(pItem->iSelectId, pParse->iNextSelectId);
  3664   3755         sqlite3Select(pParse, pSub, &dest);
  3665   3756         pItem->isPopulated = 1;
  3666   3757       }
  3667   3758       if( /*pParse->nErr ||*/ db->mallocFailed ){
  3668   3759         goto select_end;
  3669   3760       }
  3670   3761       pParse->nHeight -= sqlite3SelectExprHeight(p);
................................................................................
  3692   3783           pLoop->pRightmost = p;
  3693   3784           pLoop->pNext = pRight;
  3694   3785           pRight = pLoop;
  3695   3786         }
  3696   3787         mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
  3697   3788         if( mxSelect && cnt>mxSelect ){
  3698   3789           sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  3699         -        return 1;
         3790  +        goto select_end;
  3700   3791         }
  3701   3792       }
  3702         -    return multiSelect(pParse, p, pDest);
         3793  +    rc = multiSelect(pParse, p, pDest);
         3794  +    explainSetInteger(pParse->iSelectId, iRestoreSelectId);
         3795  +    return rc;
  3703   3796     }
  3704   3797   #endif
  3705   3798   
  3706   3799     /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  3707   3800     ** GROUP BY might use an index, DISTINCT never does.
  3708   3801     */
  3709   3802     assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 );
  3710   3803     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){
  3711   3804       p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  3712   3805       pGroupBy = p->pGroupBy;
  3713   3806       p->selFlags &= ~SF_Distinct;
  3714         -    isDistinct = 0;
  3715   3807     }
  3716   3808   
  3717   3809     /* If there is both a GROUP BY and an ORDER BY clause and they are
  3718   3810     ** identical, then disable the ORDER BY clause since the GROUP BY
  3719   3811     ** will cause elements to come out in the correct order.  This is
  3720   3812     ** an optimization - the correct answer should result regardless.
  3721   3813     ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
................................................................................
  3754   3846     /* Set the limiter.
  3755   3847     */
  3756   3848     iEnd = sqlite3VdbeMakeLabel(v);
  3757   3849     computeLimitRegisters(pParse, p, iEnd);
  3758   3850   
  3759   3851     /* Open a virtual index to use for the distinct set.
  3760   3852     */
  3761         -  if( isDistinct ){
         3853  +  if( p->selFlags & SF_Distinct ){
  3762   3854       KeyInfo *pKeyInfo;
  3763   3855       assert( isAgg || pGroupBy );
  3764   3856       distinct = pParse->nTab++;
  3765   3857       pKeyInfo = keyInfoFromExprList(pParse, p->pEList);
  3766   3858       sqlite3VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0,
  3767   3859                           (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  3768   3860       sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
................................................................................
  3913   4005           ** in sorted order
  3914   4006           */
  3915   4007           int regBase;
  3916   4008           int regRecord;
  3917   4009           int nCol;
  3918   4010           int nGroupBy;
  3919   4011   
         4012  +        explainTempTable(pParse, 
         4013  +            isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY");
         4014  +
  3920   4015           groupBySort = 1;
  3921   4016           nGroupBy = pGroupBy->nExpr;
  3922   4017           nCol = nGroupBy + 1;
  3923   4018           j = nGroupBy+1;
  3924   4019           for(i=0; i<sAggInfo.nColumn; i++){
  3925   4020             if( sAggInfo.aCol[i].iSorterColumn>=j ){
  3926   4021               nCol++;
................................................................................
  4173   4268         selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
  4174   4269                         pDest, addrEnd, addrEnd);
  4175   4270         sqlite3ExprListDelete(db, pDel);
  4176   4271       }
  4177   4272       sqlite3VdbeResolveLabel(v, addrEnd);
  4178   4273       
  4179   4274     } /* endif aggregate query */
         4275  +
         4276  +  if( distinct>=0 ){
         4277  +    explainTempTable(pParse, "DISTINCT");
         4278  +  }
  4180   4279   
  4181   4280     /* If there is an ORDER BY clause, then we need to sort the results
  4182   4281     ** and send them to the callback one by one.
  4183   4282     */
  4184   4283     if( pOrderBy ){
         4284  +    explainTempTable(pParse, "ORDER BY");
  4185   4285       generateSortTail(pParse, p, v, pEList->nExpr, pDest);
  4186   4286     }
  4187   4287   
  4188   4288     /* Jump here to skip this query
  4189   4289     */
  4190   4290     sqlite3VdbeResolveLabel(v, iEnd);
  4191   4291   
................................................................................
  4194   4294     */
  4195   4295     rc = 0;
  4196   4296   
  4197   4297     /* Control jumps to here if an error is encountered above, or upon
  4198   4298     ** successful coding of the SELECT.
  4199   4299     */
  4200   4300   select_end:
         4301  +  explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  4201   4302   
  4202   4303     /* Identify column names if results of the SELECT are to be output.
  4203   4304     */
  4204   4305     if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){
  4205   4306       generateColumnNames(pParse, pTabList, pEList);
  4206   4307     }
  4207   4308   

Changes to src/sqliteInt.h.

  1823   1823       u8 notIndexed;    /* True if there is a NOT INDEXED clause */
  1824   1824       int iCursor;      /* The VDBE cursor number used to access this table */
  1825   1825       Expr *pOn;        /* The ON clause of a join */
  1826   1826       IdList *pUsing;   /* The USING clause of a join */
  1827   1827       Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
  1828   1828       char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
  1829   1829       Index *pIndex;    /* Index structure corresponding to zIndex, if any */
         1830  +#ifndef SQLITE_OMIT_EXPLAIN
         1831  +    int iSelectId;    /* If pSelect!=0, the id of the sub-select in EQP */
         1832  +#endif
  1830   1833     } a[1];             /* One entry for each identifier on the list */
  1831   1834   };
  1832   1835   
  1833   1836   /*
  1834   1837   ** Permitted values of the SrcList.a.jointype field
  1835   1838   */
  1836   1839   #define JT_INNER     0x0001    /* Any kind of inner or cross join */
................................................................................
  1855   1858   ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true.  And pVtabIdx
  1856   1859   ** is only used when wsFlags&WHERE_VIRTUALTABLE is true.  It is never the
  1857   1860   ** case that more than one of these conditions is true.
  1858   1861   */
  1859   1862   struct WherePlan {
  1860   1863     u32 wsFlags;                   /* WHERE_* flags that describe the strategy */
  1861   1864     u32 nEq;                       /* Number of == constraints */
         1865  +  double nRow;                   /* Estimated number of rows (for EQP) */
  1862   1866     union {
  1863   1867       Index *pIdx;                   /* Index when WHERE_INDEXED is true */
  1864   1868       struct WhereTerm *pTerm;       /* WHERE clause term for OR-search */
  1865   1869       sqlite3_index_info *pVtabIdx;  /* Virtual table index to use */
  1866   1870     } u;
  1867   1871   };
  1868   1872   
................................................................................
  2209   2213     u8 declareVtab;            /* True if inside sqlite3_declare_vtab() */
  2210   2214     int nVtabLock;             /* Number of virtual tables to lock */
  2211   2215     Table **apVtabLock;        /* Pointer to virtual tables needing locking */
  2212   2216   #endif
  2213   2217     int nHeight;            /* Expression tree height of current sub-select */
  2214   2218     Table *pZombieTab;      /* List of Table objects to delete after code gen */
  2215   2219     TriggerPrg *pTriggerPrg;    /* Linked list of coded triggers */
         2220  +
         2221  +#ifndef SQLITE_OMIT_EXPLAIN
         2222  +  int iSelectId;
         2223  +  int iNextSelectId;
         2224  +#endif
  2216   2225   };
  2217   2226   
  2218   2227   #ifdef SQLITE_OMIT_VIRTUALTABLE
  2219   2228     #define IN_DECLARE_VTAB 0
  2220   2229   #else
  2221   2230     #define IN_DECLARE_VTAB (pParse->declareVtab)
  2222   2231   #endif

Changes to src/vdbeaux.c.

  1178   1178       pMem++;
  1179   1179   
  1180   1180       pMem->flags = MEM_Int;
  1181   1181       pMem->u.i = pOp->p2;                          /* P2 */
  1182   1182       pMem->type = SQLITE_INTEGER;
  1183   1183       pMem++;
  1184   1184   
  1185         -    if( p->explain==1 ){
  1186         -      pMem->flags = MEM_Int;
  1187         -      pMem->u.i = pOp->p3;                          /* P3 */
  1188         -      pMem->type = SQLITE_INTEGER;
  1189         -      pMem++;
  1190         -    }
         1185  +    pMem->flags = MEM_Int;
         1186  +    pMem->u.i = pOp->p3;                          /* P3 */
         1187  +    pMem->type = SQLITE_INTEGER;
         1188  +    pMem++;
  1191   1189   
  1192   1190       if( sqlite3VdbeMemGrow(pMem, 32, 0) ){            /* P4 */
  1193   1191         assert( p->db->mallocFailed );
  1194   1192         return SQLITE_ERROR;
  1195   1193       }
  1196   1194       pMem->flags = MEM_Dyn|MEM_Str|MEM_Term;
  1197   1195       z = displayP4(pOp, pMem->z, 32);
................................................................................
  1228   1226   #endif
  1229   1227         {
  1230   1228           pMem->flags = MEM_Null;                       /* Comment */
  1231   1229           pMem->type = SQLITE_NULL;
  1232   1230         }
  1233   1231       }
  1234   1232   
  1235         -    p->nResColumn = 8 - 5*(p->explain-1);
         1233  +    p->nResColumn = 8 - 4*(p->explain-1);
  1236   1234       p->rc = SQLITE_OK;
  1237   1235       rc = SQLITE_ROW;
  1238   1236     }
  1239   1237     return rc;
  1240   1238   }
  1241   1239   #endif /* SQLITE_OMIT_EXPLAIN */
  1242   1240   

Changes to src/where.c.

   188    188   /*
   189    189   ** A WhereCost object records a lookup strategy and the estimated
   190    190   ** cost of pursuing that strategy.
   191    191   */
   192    192   struct WhereCost {
   193    193     WherePlan plan;    /* The lookup strategy */
   194    194     double rCost;      /* Overall cost of pursuing this search strategy */
   195         -  double nRow;       /* Estimated number of output rows */
   196    195     Bitmask used;      /* Bitmask of cursors used by this plan */
   197    196   };
   198    197   
   199    198   /*
   200    199   ** Bitmasks for the operators that indices are able to exploit.  An
   201    200   ** OR-ed combination of these values can be used when searching for
   202    201   ** terms in the where clause.
................................................................................
  1617   1616             tempWC.a = pOrTerm;
  1618   1617             tempWC.nTerm = 1;
  1619   1618             bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost);
  1620   1619           }else{
  1621   1620             continue;
  1622   1621           }
  1623   1622           rTotal += sTermCost.rCost;
  1624         -        nRow += sTermCost.nRow;
         1623  +        nRow += sTermCost.plan.nRow;
  1625   1624           used |= sTermCost.used;
  1626   1625           if( rTotal>=pCost->rCost ) break;
  1627   1626         }
  1628   1627   
  1629   1628         /* If there is an ORDER BY clause, increase the scan cost to account 
  1630   1629         ** for the cost of the sort. */
  1631   1630         if( pOrderBy!=0 ){
................................................................................
  1636   1635   
  1637   1636         /* If the cost of scanning using this OR term for optimization is
  1638   1637         ** less than the current cost stored in pCost, replace the contents
  1639   1638         ** of pCost. */
  1640   1639         WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow));
  1641   1640         if( rTotal<pCost->rCost ){
  1642   1641           pCost->rCost = rTotal;
  1643         -        pCost->nRow = nRow;
  1644   1642           pCost->used = used;
         1643  +        pCost->plan.nRow = nRow;
  1645   1644           pCost->plan.wsFlags = flags;
  1646   1645           pCost->plan.u.pTerm = pTerm;
  1647   1646         }
  1648   1647       }
  1649   1648     }
  1650   1649   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  1651   1650   }
................................................................................
  1721   1720     /* Search for any equality comparison term */
  1722   1721     pWCEnd = &pWC->a[pWC->nTerm];
  1723   1722     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1724   1723       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
  1725   1724         WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
  1726   1725                       pCost->rCost, costTempIdx));
  1727   1726         pCost->rCost = costTempIdx;
  1728         -      pCost->nRow = logN + 1;
         1727  +      pCost->plan.nRow = logN + 1;
  1729   1728         pCost->plan.wsFlags = WHERE_TEMP_INDEX;
  1730   1729         pCost->used = pTerm->prereqRight;
  1731   1730         break;
  1732   1731       }
  1733   1732     }
  1734   1733   }
  1735   1734   #else
................................................................................
  2794   2793         notReady, nRow, cost, used
  2795   2794       ));
  2796   2795   
  2797   2796       /* If this index is the best we have seen so far, then record this
  2798   2797       ** index and its cost in the pCost structure.
  2799   2798       */
  2800   2799       if( (!pIdx || wsFlags)
  2801         -     && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->nRow))
         2800  +     && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow))
  2802   2801       ){
  2803   2802         pCost->rCost = cost;
  2804         -      pCost->nRow = nRow;
  2805   2803         pCost->used = used;
         2804  +      pCost->plan.nRow = nRow;
  2806   2805         pCost->plan.wsFlags = (wsFlags&wsFlagMask);
  2807   2806         pCost->plan.nEq = nEq;
  2808   2807         pCost->plan.u.pIdx = pIdx;
  2809   2808       }
  2810   2809   
  2811   2810       /* If there was an INDEXED BY clause, then only that one index is
  2812   2811       ** considered. */
................................................................................
  3126   3125           }
  3127   3126         }
  3128   3127       }
  3129   3128     }
  3130   3129     *pzAff = zAff;
  3131   3130     return regBase;
  3132   3131   }
         3132  +
         3133  +#ifndef SQLITE_OMIT_EXPLAIN
         3134  +/*
         3135  +** This routine is a helper for explainIndexRange() below
         3136  +**
         3137  +** pStr holds the text of an expression that we are building up one term
         3138  +** at a time.  This routine adds a new term to the end of the expression.
         3139  +** Terms are separated by AND so add the "AND" text for second and subsequent
         3140  +** terms only.
         3141  +*/
         3142  +static void explainAppendTerm(
         3143  +  StrAccum *pStr,             /* The text expression being built */
         3144  +  int iTerm,                  /* Index of this term.  First is zero */
         3145  +  const char *zColumn,        /* Name of the column */
         3146  +  const char *zOp             /* Name of the operator */
         3147  +){
         3148  +  if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
         3149  +  sqlite3StrAccumAppend(pStr, zColumn, -1);
         3150  +  sqlite3StrAccumAppend(pStr, zOp, 1);
         3151  +  sqlite3StrAccumAppend(pStr, "?", 1);
         3152  +}
         3153  +
         3154  +/*
         3155  +** Argument pLevel describes a strategy for scanning table pTab. This 
         3156  +** function returns a pointer to a string buffer containing a description
         3157  +** of the subset of table rows scanned by the strategy in the form of an
         3158  +** SQL expression. Or, if all rows are scanned, NULL is returned.
         3159  +**
         3160  +** For example, if the query:
         3161  +**
         3162  +**   SELECT * FROM t1 WHERE a=1 AND b>2;
         3163  +**
         3164  +** is run and there is an index on (a, b), then this function returns a
         3165  +** string similar to:
         3166  +**
         3167  +**   "a=? AND b>?"
         3168  +**
         3169  +** The returned pointer points to memory obtained from sqlite3DbMalloc().
         3170  +** It is the responsibility of the caller to free the buffer when it is
         3171  +** no longer required.
         3172  +*/
         3173  +static char *explainIndexRange(sqlite3 *db, WhereLevel *pLevel, Table *pTab){
         3174  +  WherePlan *pPlan = &pLevel->plan;
         3175  +  Index *pIndex = pPlan->u.pIdx;
         3176  +  int nEq = pPlan->nEq;
         3177  +  int i, j;
         3178  +  Column *aCol = pTab->aCol;
         3179  +  int *aiColumn = pIndex->aiColumn;
         3180  +  StrAccum txt;
         3181  +
         3182  +  if( nEq==0 && (pPlan->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){
         3183  +    return 0;
         3184  +  }
         3185  +  sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH);
         3186  +  sqlite3StrAccumAppend(&txt, " (", 2);
         3187  +  for(i=0; i<nEq; i++){
         3188  +    explainAppendTerm(&txt, i, aCol[aiColumn[i]].zName, "=");
         3189  +  }
         3190  +
         3191  +  j = i;
         3192  +  if( pPlan->wsFlags&WHERE_BTM_LIMIT ){
         3193  +    explainAppendTerm(&txt, i++, aCol[aiColumn[j]].zName, ">");
         3194  +  }
         3195  +  if( pPlan->wsFlags&WHERE_TOP_LIMIT ){
         3196  +    explainAppendTerm(&txt, i, aCol[aiColumn[j]].zName, "<");
         3197  +  }
         3198  +  sqlite3StrAccumAppend(&txt, ")", 1);
         3199  +  return sqlite3StrAccumFinish(&txt);
         3200  +}
         3201  +
         3202  +/*
         3203  +** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
         3204  +** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single
         3205  +** record is added to the output to describe the table scan strategy in 
         3206  +** pLevel.
         3207  +*/
         3208  +static void explainOneScan(
         3209  +  Parse *pParse,                  /* Parse context */
         3210  +  SrcList *pTabList,              /* Table list this loop refers to */
         3211  +  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
         3212  +  int iLevel,                     /* Value for "level" column of output */
         3213  +  int iFrom,                      /* Value for "from" column of output */
         3214  +  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
         3215  +){
         3216  +  if( pParse->explain==2 ){
         3217  +    u32 flags = pLevel->plan.wsFlags;
         3218  +    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
         3219  +    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
         3220  +    sqlite3 *db = pParse->db;     /* Database handle */
         3221  +    char *zMsg;                   /* Text to add to EQP output */
         3222  +    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
         3223  +    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
         3224  +    int isSearch;                 /* True for a SEARCH. False for SCAN. */
         3225  +
         3226  +    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;
         3227  +
         3228  +    isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT));
         3229  +
         3230  +    zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN");
         3231  +    if( pItem->pSelect ){
         3232  +      zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId);
         3233  +    }else{
         3234  +      zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
         3235  +    }
         3236  +
         3237  +    if( pItem->zAlias ){
         3238  +      zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
         3239  +    }
         3240  +    if( (flags & WHERE_INDEXED)!=0 ){
         3241  +      char *zWhere = explainIndexRange(db, pLevel, pItem->pTab);
         3242  +      zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 
         3243  +          ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
         3244  +          ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
         3245  +          ((flags & WHERE_TEMP_INDEX)?"":" "),
         3246  +          ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
         3247  +          zWhere
         3248  +      );
         3249  +      sqlite3DbFree(db, zWhere);
         3250  +    }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
         3251  +      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
         3252  +
         3253  +      if( flags&WHERE_ROWID_EQ ){
         3254  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
         3255  +      }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
         3256  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
         3257  +      }else if( flags&WHERE_BTM_LIMIT ){
         3258  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
         3259  +      }else if( flags&WHERE_TOP_LIMIT ){
         3260  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
         3261  +      }
         3262  +    }
         3263  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         3264  +    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
         3265  +      sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
         3266  +      zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
         3267  +                  pVtabIdx->idxNum, pVtabIdx->idxStr);
         3268  +    }
         3269  +#endif
         3270  +    if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){
         3271  +      nRow = 1;
         3272  +    }else{
         3273  +      nRow = (sqlite3_int64)pLevel->plan.nRow;
         3274  +    }
         3275  +    zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow);
         3276  +    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
         3277  +  }
         3278  +}
         3279  +#else
         3280  +# define explainOneScan(u,v,w,x,y,z)
         3281  +#endif /* SQLITE_OMIT_EXPLAIN */
         3282  +
  3133   3283   
  3134   3284   /*
  3135   3285   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3136   3286   ** implementation described by pWInfo.
  3137   3287   */
  3138   3288   static Bitmask codeOneLoopStart(
  3139   3289     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
................................................................................
  3668   3818         if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
  3669   3819           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
  3670   3820           /* Loop through table entries that match term pOrTerm. */
  3671   3821           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0,
  3672   3822                           WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
  3673   3823                           WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  3674   3824           if( pSubWInfo ){
         3825  +          explainOneScan(
         3826  +              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
         3827  +          );
  3675   3828             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3676   3829               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  3677   3830               int r;
  3678   3831               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
  3679   3832                                            regRowid);
  3680   3833               sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
  3681   3834                                    sqlite3VdbeCurrentAddr(v)+2, r, iSet);
................................................................................
  4180   4333           */
  4181   4334           if( (sCost.used&notReady)==0                       /* (1) */
  4182   4335               && (bestJ<0 || (notIndexed&m)!=0               /* (2) */
  4183   4336                   || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
  4184   4337               && (nUnconstrained==0 || pTabItem->pIndex==0   /* (3) */
  4185   4338                   || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
  4186   4339               && (bestJ<0 || sCost.rCost<bestPlan.rCost      /* (4) */
  4187         -                || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
         4340  +                || (sCost.rCost<=bestPlan.rCost 
         4341  +                 && sCost.plan.nRow<bestPlan.plan.nRow))
  4188   4342           ){
  4189   4343             WHERETRACE(("=== table %d is best so far"
  4190   4344                         " with cost=%g and nRow=%g\n",
  4191         -                      j, sCost.rCost, sCost.nRow));
         4345  +                      j, sCost.rCost, sCost.plan.nRow));
  4192   4346             bestPlan = sCost;
  4193   4347             bestJ = j;
  4194   4348           }
  4195   4349           if( doNotReorder ) break;
  4196   4350         }
  4197   4351       }
  4198   4352       assert( bestJ>=0 );
  4199   4353       assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
  4200   4354       WHERETRACE(("*** Optimizer selects table %d for loop %d"
  4201   4355                   " with cost=%g and nRow=%g\n",
  4202         -                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.nRow));
         4356  +                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow));
  4203   4357       if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
  4204   4358         *ppOrderBy = 0;
  4205   4359       }
  4206   4360       andFlags &= bestPlan.plan.wsFlags;
  4207   4361       pLevel->plan = bestPlan.plan;
  4208   4362       testcase( bestPlan.plan.wsFlags & WHERE_INDEXED );
  4209   4363       testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX );
................................................................................
  4210   4364       if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){
  4211   4365         pLevel->iIdxCur = pParse->nTab++;
  4212   4366       }else{
  4213   4367         pLevel->iIdxCur = -1;
  4214   4368       }
  4215   4369       notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor);
  4216   4370       pLevel->iFrom = (u8)bestJ;
  4217         -    if( bestPlan.nRow>=(double)1 ) pParse->nQueryLoop *= bestPlan.nRow;
         4371  +    if( bestPlan.plan.nRow>=(double)1 ){
         4372  +      pParse->nQueryLoop *= bestPlan.plan.nRow;
         4373  +    }
  4218   4374   
  4219   4375       /* Check that if the table scanned by this loop iteration had an
  4220   4376       ** INDEXED BY clause attached to it, that the named index is being
  4221   4377       ** used for the scan. If not, then query compilation has failed.
  4222   4378       ** Return an error.
  4223   4379       */
  4224   4380       pIdx = pTabList->a[bestJ].pIndex;
................................................................................
  4262   4418     */
  4263   4419     sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  4264   4420     notReady = ~(Bitmask)0;
  4265   4421     for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){
  4266   4422       Table *pTab;     /* Table to open */
  4267   4423       int iDb;         /* Index of database containing table/index */
  4268   4424   
  4269         -#ifndef SQLITE_OMIT_EXPLAIN
  4270         -    if( pParse->explain==2 ){
  4271         -      char *zMsg;
  4272         -      struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  4273         -      zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
  4274         -      if( pItem->zAlias ){
  4275         -        zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
  4276         -      }
  4277         -      if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
  4278         -        zMsg = sqlite3MAppendf(db, zMsg, "%s WITH AUTOMATIC INDEX", zMsg);
  4279         -      }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
  4280         -        zMsg = sqlite3MAppendf(db, zMsg, "%s WITH INDEX %s",
  4281         -           zMsg, pLevel->plan.u.pIdx->zName);
  4282         -      }else if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
  4283         -        zMsg = sqlite3MAppendf(db, zMsg, "%s VIA MULTI-INDEX UNION", zMsg);
  4284         -      }else if( pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
  4285         -        zMsg = sqlite3MAppendf(db, zMsg, "%s USING PRIMARY KEY", zMsg);
  4286         -      }
  4287         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  4288         -      else if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  4289         -        sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
  4290         -        zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
  4291         -                    pVtabIdx->idxNum, pVtabIdx->idxStr);
  4292         -      }
  4293         -#endif
  4294         -      if( pLevel->plan.wsFlags & WHERE_ORDERBY ){
  4295         -        zMsg = sqlite3MAppendf(db, zMsg, "%s ORDER BY", zMsg);
  4296         -      }
  4297         -      sqlite3VdbeAddOp4(v, OP_Explain, i, pLevel->iFrom, 0, zMsg, P4_DYNAMIC);
  4298         -    }
  4299         -#endif /* SQLITE_OMIT_EXPLAIN */
  4300   4425       pTabItem = &pTabList->a[pLevel->iFrom];
  4301   4426       pTab = pTabItem->pTab;
  4302   4427       pLevel->iTabCur = pTabItem->iCursor;
  4303   4428       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  4304   4429       if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
  4305   4430         /* Do nothing */
  4306   4431       }else
................................................................................
  4351   4476   
  4352   4477     /* Generate the code to do the search.  Each iteration of the for
  4353   4478     ** loop below generates code for a single nested loop of the VM
  4354   4479     ** program.
  4355   4480     */
  4356   4481     notReady = ~(Bitmask)0;
  4357   4482     for(i=0; i<nTabList; i++){
         4483  +    WhereLevel *pLevel = &pWInfo->a[i];
         4484  +    explainOneScan(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags);
  4358   4485       notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
  4359         -    pWInfo->iContinue = pWInfo->a[i].addrCont;
         4486  +    pWInfo->iContinue = pLevel->addrCont;
  4360   4487     }
  4361   4488   
  4362   4489   #ifdef SQLITE_TEST  /* For testing and debugging use only */
  4363   4490     /* Record in the query plan information about the current table
  4364   4491     ** and the index used to access it (if any).  If the table itself
  4365   4492     ** is not used, its name is just '{}'.  If no index is used
  4366   4493     ** the index is listed as "{}".  If the primary key is used the

Changes to test/autoindex1.test.

   136    136     }
   137    137   } {4087}
   138    138   
   139    139   # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
   140    140   # Make sure automatic indices are not created for the RHS of an IN expression
   141    141   # that is not a correlated subquery.
   142    142   #
   143         -do_test autoindex1-500 {
   144         -  db eval {
   145         -    CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
   146         -    CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
   147         -    EXPLAIN QUERY PLAN
   148         -    SELECT b FROM t501
   149         -     WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
   150         -  }
   151         -} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
   152         -do_test autoindex1-501 {
   153         -  db eval {
   154         -    EXPLAIN QUERY PLAN
   155         -    SELECT b FROM t501
   156         -     WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   157         -  }
   158         -} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}}
   159         -do_test autoindex1-502 {
   160         -  db eval {
   161         -    EXPLAIN QUERY PLAN
   162         -    SELECT b FROM t501
   163         -     WHERE t501.a=123
   164         -       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   165         -  }
   166         -} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
          143  +do_execsql_test autoindex1-500 {
          144  +  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
          145  +  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
          146  +  EXPLAIN QUERY PLAN
          147  +  SELECT b FROM t501
          148  +   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
          149  +} {
          150  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
          151  +  0 0 0 {EXECUTE LIST SUBQUERY 1} 
          152  +  1 0 0 {SCAN TABLE t502 (~100000 rows)}
          153  +}
          154  +do_execsql_test autoindex1-501 {
          155  +  EXPLAIN QUERY PLAN
          156  +  SELECT b FROM t501
          157  +   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
          158  +} {
          159  +  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
          160  +  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
          161  +  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
          162  +}
          163  +do_execsql_test autoindex1-502 {
          164  +  EXPLAIN QUERY PLAN
          165  +  SELECT b FROM t501
          166  +   WHERE t501.a=123
          167  +     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
          168  +} {
          169  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          170  +  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
          171  +  1 0 0 {SCAN TABLE t502 (~100000 rows)}
          172  +}
   167    173   
   168    174   
   169    175   # The following code checks a performance regression reported on the
   170    176   # mailing list on 2010-10-19.  The problem is that the nRowEst field
   171    177   # of ephermeral tables was not being initialized correctly and so no
   172    178   # automatic index was being created for the emphemeral table when it was
   173    179   # used as part of a join.
   174    180   #
   175         -do_test autoindex1-600 {
   176         -  db eval {
   177         -    CREATE TABLE flock_owner(
   178         -      owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
   179         -      flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
   180         -      owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
   181         -      owner_change_date TEXT, last_changed TEXT NOT NULL,
   182         -      CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
   183         -    );
   184         -    CREATE TABLE sheep (
   185         -      Sheep_No char(7) NOT NULL,
   186         -      Date_of_Birth char(8),
   187         -      Sort_DoB text,
   188         -      Flock_Book_Vol char(2),
   189         -      Breeder_No char(6),
   190         -      Breeder_Person integer,
   191         -      Originating_Flock char(6),
   192         -      Registering_Flock char(6),
   193         -      Tag_Prefix char(9),
   194         -      Tag_No char(15),
   195         -      Sort_Tag_No integer,
   196         -      Breeders_Temp_Tag char(15),
   197         -      Sex char(1),
   198         -      Sheep_Name char(32),
   199         -      Sire_No char(7),
   200         -      Dam_No char(7),
   201         -      Register_Code char(1),
   202         -      Colour char(48),
   203         -      Colour_Code char(2),
   204         -      Pattern_Code char(8),
   205         -      Horns char(1),
   206         -      Litter_Size char(1),
   207         -      Coeff_of_Inbreeding real,
   208         -      Date_of_Registration text,
   209         -      Date_Last_Changed text,
   210         -      UNIQUE(Sheep_No));
   211         -    CREATE INDEX fo_flock_no_index  
   212         -                ON flock_owner (flock_no);
   213         -    CREATE INDEX fo_owner_change_date_index  
   214         -                ON flock_owner (owner_change_date);
   215         -    CREATE INDEX fo_owner_person_id_index  
   216         -                ON flock_owner (owner_person_id);
   217         -    CREATE INDEX sheep_org_flock_index  
   218         -             ON sheep (originating_flock);
   219         -    CREATE INDEX sheep_reg_flock_index  
   220         -             ON sheep (registering_flock);
   221         -    EXPLAIN QUERY PLAN
   222         -    SELECT x.sheep_no, x.registering_flock, x.date_of_registration
   223         -     FROM sheep x LEFT JOIN
   224         -         (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
   225         -         s.date_of_registration, prev.owner_change_date
   226         -         FROM sheep s JOIN flock_owner prev ON s.registering_flock =
   227         -     prev.flock_no
   228         -         AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
   229         -         WHERE NOT EXISTS
   230         -             (SELECT 'x' FROM flock_owner later
   231         -             WHERE prev.flock_no = later.flock_no
   232         -             AND later.owner_change_date > prev.owner_change_date
   233         -             AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
   234         -         ) y ON x.sheep_no = y.sheep_no
   235         -     WHERE y.sheep_no IS NULL
   236         -     ORDER BY x.registering_flock;
   237         -  }
   238         -} {0 0 {TABLE sheep AS s} 1 1 {TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE sheep AS x WITH INDEX sheep_reg_flock_index ORDER BY} 1 1 {TABLE  AS y WITH AUTOMATIC INDEX}}
          181  +do_execsql_test autoindex1-600 {
          182  +  CREATE TABLE flock_owner(
          183  +    owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
          184  +    flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
          185  +    owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
          186  +    owner_change_date TEXT, last_changed TEXT NOT NULL,
          187  +    CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
          188  +  );
          189  +  CREATE TABLE sheep (
          190  +    Sheep_No char(7) NOT NULL,
          191  +    Date_of_Birth char(8),
          192  +    Sort_DoB text,
          193  +    Flock_Book_Vol char(2),
          194  +    Breeder_No char(6),
          195  +    Breeder_Person integer,
          196  +    Originating_Flock char(6),
          197  +    Registering_Flock char(6),
          198  +    Tag_Prefix char(9),
          199  +    Tag_No char(15),
          200  +    Sort_Tag_No integer,
          201  +    Breeders_Temp_Tag char(15),
          202  +    Sex char(1),
          203  +    Sheep_Name char(32),
          204  +    Sire_No char(7),
          205  +    Dam_No char(7),
          206  +    Register_Code char(1),
          207  +    Colour char(48),
          208  +    Colour_Code char(2),
          209  +    Pattern_Code char(8),
          210  +    Horns char(1),
          211  +    Litter_Size char(1),
          212  +    Coeff_of_Inbreeding real,
          213  +    Date_of_Registration text,
          214  +    Date_Last_Changed text,
          215  +    UNIQUE(Sheep_No));
          216  +  CREATE INDEX fo_flock_no_index  
          217  +              ON flock_owner (flock_no);
          218  +  CREATE INDEX fo_owner_change_date_index  
          219  +              ON flock_owner (owner_change_date);
          220  +  CREATE INDEX fo_owner_person_id_index  
          221  +              ON flock_owner (owner_person_id);
          222  +  CREATE INDEX sheep_org_flock_index  
          223  +           ON sheep (originating_flock);
          224  +  CREATE INDEX sheep_reg_flock_index  
          225  +           ON sheep (registering_flock);
          226  +  EXPLAIN QUERY PLAN
          227  +  SELECT x.sheep_no, x.registering_flock, x.date_of_registration
          228  +   FROM sheep x LEFT JOIN
          229  +       (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
          230  +       s.date_of_registration, prev.owner_change_date
          231  +       FROM sheep s JOIN flock_owner prev ON s.registering_flock =
          232  +   prev.flock_no
          233  +       AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
          234  +       WHERE NOT EXISTS
          235  +           (SELECT 'x' FROM flock_owner later
          236  +           WHERE prev.flock_no = later.flock_no
          237  +           AND later.owner_change_date > prev.owner_change_date
          238  +           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
          239  +       ) y ON x.sheep_no = y.sheep_no
          240  +   WHERE y.sheep_no IS NULL
          241  +   ORDER BY x.registering_flock;
          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)} 
          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=?) (~7 rows)}
          249  +}
   239    250   
   240    251   finish_test

Changes to test/e_createtable.test.

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

Changes to test/e_fkey.test.

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

Added test/eqp.test.

            1  +# 2010 November 6
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +
           16  +set testprefix eqp
           17  +
           18  +#-------------------------------------------------------------------------
           19  +#
           20  +# eqp-1.*:        Assorted tests.
           21  +# eqp-2.*:        Tests for single select statements.
           22  +# eqp-3.*:        Select statements that execute sub-selects.
           23  +# eqp-4.*:        Compound select statements.
           24  +#
           25  +
           26  +proc do_eqp_test {name sql res} {
           27  +  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
           28  +}
           29  +proc det {args} { uplevel do_eqp_test $args }
           30  +
           31  +do_execsql_test 1.1 {
           32  +  CREATE TABLE t1(a, b);
           33  +  CREATE INDEX i1 ON t1(a);
           34  +  CREATE INDEX i2 ON t1(b);
           35  +  CREATE TABLE t2(a, b);
           36  +  CREATE TABLE t3(a, b);
           37  +}
           38  +
           39  +do_eqp_test 1.2 {
           40  +  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
           41  +} {
           42  +  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
           43  +  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
           44  +  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
           45  +}
           46  +do_eqp_test 1.3 {
           47  +  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
           48  +} {
           49  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
           50  +  0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
           51  +  0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
           52  +}
           53  +do_eqp_test 1.3 {
           54  +  SELECT a FROM t1 ORDER BY a
           55  +} {
           56  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
           57  +}
           58  +do_eqp_test 1.4 {
           59  +  SELECT a FROM t1 ORDER BY +a
           60  +} {
           61  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
           62  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           63  +}
           64  +do_eqp_test 1.5 {
           65  +  SELECT a FROM t1 WHERE a=4
           66  +} {
           67  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
           68  +}
           69  +do_eqp_test 1.6 {
           70  +  SELECT DISTINCT count(*) FROM t3 GROUP BY a;
           71  +} {
           72  +  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
           73  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           74  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           75  +}
           76  +
           77  +#-------------------------------------------------------------------------
           78  +# Test cases eqp-2.* - tests for single select statements.
           79  +#
           80  +drop_all_tables
           81  +do_execsql_test 2.1 {
           82  +  CREATE TABLE t1(x, y);
           83  +
           84  +  CREATE TABLE t2(x, y);
           85  +  CREATE INDEX t2i1 ON t2(x);
           86  +}
           87  +
           88  +det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
           89  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
           90  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           91  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           92  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           93  +}
           94  +det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
           95  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
           96  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           97  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           98  +}
           99  +det 2.2.3 "SELECT DISTINCT * FROM t1" {
          100  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          101  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          102  +}
          103  +det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
          104  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          105  +  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          106  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          107  +}
          108  +det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
          109  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          110  +  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          111  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          112  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          113  +}
          114  +det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
          115  +  0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
          116  +  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
          117  +}
          118  +
          119  +det 2.3.1 "SELECT max(x) FROM t2" {
          120  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
          121  +}
          122  +det 2.3.2 "SELECT min(x) FROM t2" {
          123  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
          124  +}
          125  +det 2.3.3 "SELECT min(x), max(x) FROM t2" {
          126  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
          127  +}
          128  +
          129  +det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
          130  +  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          131  +}
          132  +
          133  +
          134  +
          135  +#-------------------------------------------------------------------------
          136  +# Test cases eqp-3.* - tests for select statements that use sub-selects.
          137  +#
          138  +do_eqp_test 3.1.1 {
          139  +  SELECT (SELECT x FROM t1 AS sub) FROM t1;
          140  +} {
          141  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          142  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          143  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          144  +}
          145  +do_eqp_test 3.1.2 {
          146  +  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
          147  +} {
          148  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          149  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          150  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          151  +}
          152  +do_eqp_test 3.1.3 {
          153  +  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
          154  +} {
          155  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          156  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          157  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          158  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          159  +}
          160  +do_eqp_test 3.1.4 {
          161  +  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
          162  +} {
          163  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          164  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          165  +  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
          166  +}
          167  +
          168  +det 3.2.1 {
          169  +  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
          170  +} {
          171  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          172  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          173  +  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)} 
          174  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          175  +}
          176  +det 3.2.2 {
          177  +  SELECT * FROM 
          178  +    (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
          179  +    (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
          180  +  ORDER BY x2.y LIMIT 5
          181  +} {
          182  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          183  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          184  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
          185  +  0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
          186  +  0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
          187  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          188  +}
          189  +
          190  +det 3.3.1 {
          191  +  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
          192  +} {
          193  +  0 0 0 {SCAN TABLE t1 (~100000 rows)} 
          194  +  0 0 0 {EXECUTE LIST SUBQUERY 1} 
          195  +  1 0 0 {SCAN TABLE t2 (~1000000 rows)}
          196  +}
          197  +det 3.3.2 {
          198  +  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
          199  +} {
          200  +  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          201  +  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
          202  +  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          203  +}
          204  +det 3.3.3 {
          205  +  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
          206  +} {
          207  +  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          208  +  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 
          209  +  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          210  +}
          211  +
          212  +#-------------------------------------------------------------------------
          213  +# Test cases eqp-4.* - tests for composite select statements.
          214  +#
          215  +do_eqp_test 4.1.1 {
          216  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2
          217  +} {
          218  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          219  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          220  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
          221  +}
          222  +do_eqp_test 4.1.2 {
          223  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
          224  +} {
          225  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          226  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          227  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          228  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          229  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
          230  +}
          231  +do_eqp_test 4.1.3 {
          232  +  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
          233  +} {
          234  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          235  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          236  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          237  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          238  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
          239  +}
          240  +do_eqp_test 4.1.4 {
          241  +  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
          242  +} {
          243  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          244  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          245  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          246  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          247  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
          248  +}
          249  +do_eqp_test 4.1.5 {
          250  +  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
          251  +} {
          252  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          253  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          254  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          255  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          256  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
          257  +}
          258  +
          259  +do_eqp_test 4.2.2 {
          260  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
          261  +} {
          262  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          263  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          264  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
          265  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
          266  +}
          267  +do_eqp_test 4.2.3 {
          268  +  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
          269  +} {
          270  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          271  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          272  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          273  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          274  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
          275  +}
          276  +do_eqp_test 4.2.4 {
          277  +  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
          278  +} {
          279  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          280  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          281  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          282  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          283  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
          284  +}
          285  +do_eqp_test 4.2.5 {
          286  +  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
          287  +} {
          288  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          289  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          290  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          291  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          292  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
          293  +}
          294  +
          295  +do_eqp_test 4.3.1 {
          296  +  SELECT x FROM t1 UNION SELECT x FROM t2
          297  +} {
          298  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          299  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          300  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
          301  +}
          302  +
          303  +do_eqp_test 4.3.2 {
          304  +  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
          305  +} {
          306  +  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          307  +  3 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          308  +  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
          309  +  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          310  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
          311  +}
          312  +do_eqp_test 4.3.3 {
          313  +  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
          314  +} {
          315  +  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          316  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          317  +  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
          318  +  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
          319  +  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          320  +  4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          321  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
          322  +}
          323  +
          324  +#-------------------------------------------------------------------------
          325  +# This next block of tests verifies that the examples on the 
          326  +# lang_explain.html page are correct.
          327  +#
          328  +drop_all_tables
          329  +
          330  +# EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
          331  +# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
          332  +do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
          333  +det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
          334  +  0 0 0 {SCAN TABLE t1 (~100000 rows)}
          335  +}
          336  +
          337  +# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
          338  +# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
          339  +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
          340  +do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
          341  +det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
          342  +  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
          343  +}
          344  +
          345  +# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
          346  +# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
          347  +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
          348  +do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
          349  +det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
          350  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
          351  +}
          352  +
          353  +# EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
          354  +# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
          355  +# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
          356  +# (~1000000 rows)
          357  +do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
          358  +det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
          359  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
          360  +  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          361  +}
          362  +
          363  +# EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
          364  +# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
          365  +# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
          366  +# (~1000000 rows)
          367  +det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
          368  +  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
          369  +  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
          370  +}
          371  +
          372  +# EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
          373  +# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
          374  +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
          375  +# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
          376  +do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
          377  +det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
          378  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
          379  +  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
          380  +}
          381  +
          382  +# EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
          383  +# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
          384  +# B-TREE FOR ORDER BY
          385  +det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
          386  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
          387  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          388  +}
          389  +
          390  +# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
          391  +# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
          392  +# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
          393  +do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
          394  +det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
          395  +  0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
          396  +}
          397  +
          398  +# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
          399  +# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
          400  +# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
          401  +# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
          402  +# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
          403  +# INDEX i3 (b=?) (~10 rows)
          404  +det 5.9 {
          405  +  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
          406  +} {
          407  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
          408  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          409  +  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
          410  +  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
          411  +  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
          412  +}
          413  +
          414  +# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
          415  +# count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
          416  +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
          417  +# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
          418  +det 5.10 {
          419  +  SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
          420  +} {
          421  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
          422  +  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)}
          423  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
          424  +}
          425  +
          426  +# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
          427  +# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
          428  +# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
          429  +det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
          430  +  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
          431  +  0 1 1 {SCAN TABLE t1 (~1000000 rows)}
          432  +}
          433  +
          434  +# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
          435  +# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
          436  +# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
          437  +# USING TEMP B-TREE (UNION)
          438  +det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
          439  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)}
          440  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
          441  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
          442  +}
          443  +
          444  +# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
          445  +# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
          446  +# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
          447  +# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2
          448  +# (EXCEPT)
          449  +det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
          450  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
          451  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
          452  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          453  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
          454  +}
          455  +
          456  +
          457  +
          458  +
          459  +
          460  +finish_test
          461  +

Changes to test/fts3fault.test.

    10     10   #***********************************************************************
    11     11   #
    12     12   
    13     13   set testdir [file dirname $argv0]
    14     14   source $testdir/tester.tcl
    15     15   
    16     16   set ::testprefix fts3fault
           17  +
           18  +# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
           19  +ifcapable !fts3 { finish_test ; return }
    17     20   
    18     21   # Test error handling in the sqlite3Fts3Init() function. This is the 
    19     22   # function that registers the FTS3 module and various support functions
    20     23   # with SQLite.
    21     24   #
    22     25   do_faultsim_test 1 -body { 
    23     26     sqlite3 db test.db 

Changes to test/fts3matchinfo.test.

    10     10   #***********************************************************************
    11     11   # This file implements regression tests for the FTS3 module. The focus
    12     12   # of this file is tables created with the "matchinfo=fts3" option.
    13     13   #
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
           17  +
           18  +# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
           19  +ifcapable !fts3 { finish_test ; return }
    17     20   
    18     21   set testprefix fts3matchinfo
    19     22   
    20     23   proc mit {blob} {
    21     24     set scan(littleEndian) i*
    22     25     set scan(bigEndian) I*
    23     26     binary scan $blob $scan($::tcl_platform(byteOrder)) r

Changes to test/indexedby.test.

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

Changes to test/tester.tcl.

   345    345     } {
   346    346       set testname "${::testprefix}-$testname"
   347    347     }
   348    348   }
   349    349       
   350    350   proc do_execsql_test {testname sql {result {}}} {
   351    351     fix_testname testname
   352         -  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
          352  +  uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]]
   353    353   }
   354    354   proc do_catchsql_test {testname sql result} {
   355    355     fix_testname testname
   356    356     uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
   357    357   }
   358    358   
   359    359   #-------------------------------------------------------------------------

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 {TABLE }}
           47  +} {0 0 0 {SCAN TABLE  (~500000 rows)}}
    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 {TABLE t2 WITH INDEX }}
           60  +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX  (x=?) (~10 rows)}}
    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 {TABLE t2}}
           66  +} {0 0 0 {SCAN TABLE t2 (~100000 rows)}}
    67     67   
    68     68   finish_test

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 {TABLE listhash WITH INDEX ididx}}
           52  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
    53     53     do_test tkt3442-1.3 {
    54     54       EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
    55         -  } {0 0 {TABLE listhash WITH INDEX ididx}}
           55  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
    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 {TABLE listhash WITH INDEX ididx}}
           64  +  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
    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/where3.test.

   213    213   } {tB {} tC * tA * tD *}
   214    214   
   215    215   # Ticket [13f033c865f878953]
   216    216   # If the outer loop must be a full table scan, do not let ANALYZE trick
   217    217   # the planner into use a table for the outer loop that might be indexable
   218    218   # if held until an inner loop.
   219    219   # 
   220         -do_test where3-3.0 {
   221         -  execsql {
   222         -    CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
   223         -    CREATE INDEX t301c ON t301(c);
   224         -    INSERT INTO t301 VALUES(1,2,3);
   225         -    CREATE TABLE t302(x, y);
   226         -    ANALYZE;
   227         -    explain query plan
   228         -    SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
   229         -  }
   230         -} {0 0 {TABLE t302} 1 1 {TABLE t301 USING PRIMARY KEY}}
   231         -do_test where3-3.1 {
   232         -  execsql {
   233         -    explain query plan
   234         -    SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
   235         -  }
   236         -} {0 1 {TABLE t302} 1 0 {TABLE t301 USING PRIMARY KEY}}
          220  +do_execsql_test where3-3.0 {
          221  +  CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
          222  +  CREATE INDEX t301c ON t301(c);
          223  +  INSERT INTO t301 VALUES(1,2,3);
          224  +  CREATE TABLE t302(x, y);
          225  +  ANALYZE;
          226  +  explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
          227  +} {
          228  +  0 0 0 {SCAN TABLE t302 (~0 rows)} 
          229  +  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          230  +}
          231  +do_execsql_test where3-3.1 {
          232  +  explain query plan
          233  +  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
          234  +} {
          235  +  0 0 1 {SCAN TABLE t302 (~0 rows)} 
          236  +  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          237  +}
   237    238   
   238    239   # Verify that when there are multiple tables in a join which must be
   239    240   # full table scans that the query planner attempts put the table with
   240    241   # the fewest number of output rows as the outer loop.
   241    242   #
   242         -do_test where3-4.0 {
   243         -  execsql {
   244         -    CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
   245         -    CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
   246         -    CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
   247         -    EXPLAIN QUERY PLAN
   248         -    SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
   249         -  }
   250         -} {0 2 {TABLE t402} 1 0 {TABLE t400} 2 1 {TABLE t401}}
   251         -do_test where3-4.1 {
   252         -  execsql {
   253         -    EXPLAIN QUERY PLAN
   254         -    SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
   255         -  }
   256         -} {0 1 {TABLE t401} 1 0 {TABLE t400} 2 2 {TABLE t402}}
   257         -do_test where3-4.2 {
   258         -  execsql {
   259         -    EXPLAIN QUERY PLAN
   260         -    SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
   261         -  }
   262         -} {0 0 {TABLE t400} 1 1 {TABLE t401} 2 2 {TABLE t402}}
          243  +do_execsql_test where3-4.0 {
          244  +  CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
          245  +  CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
          246  +  CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
          247  +  EXPLAIN QUERY PLAN
          248  +  SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
          249  +} {
          250  +  0 0 2 {SCAN TABLE t402 (~500000 rows)} 
          251  +  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
          252  +  0 2 1 {SCAN TABLE t401 (~1000000 rows)}
          253  +}
          254  +do_execsql_test where3-4.1 {
          255  +  EXPLAIN QUERY PLAN
          256  +  SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
          257  +} {
          258  +  0 0 1 {SCAN TABLE t401 (~500000 rows)} 
          259  +  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
          260  +  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
          261  +}
          262  +do_execsql_test where3-4.2 {
          263  +  EXPLAIN QUERY PLAN
          264  +  SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
          265  +} {
          266  +  0 0 0 {SCAN TABLE t400 (~500000 rows)} 
          267  +  0 1 1 {SCAN TABLE t401 (~1000000 rows)} 
          268  +  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
          269  +}
   263    270   
   264    271   # Verify that a performance regression encountered by firefox
   265    272   # has been fixed.
   266    273   #
   267         -do_test where3-5.0 {
   268         -  execsql {
   269         -     CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
   270         -                       fk INTEGER DEFAULT NULL, parent INTEGER,
   271         -                       position INTEGER, title LONGVARCHAR,
   272         -                       keyword_id INTEGER, folder_type TEXT,
   273         -                       dateAdded INTEGER, lastModified INTEGER);
   274         -     CREATE INDEX aaa_111 ON aaa (fk, type);
   275         -     CREATE INDEX aaa_222 ON aaa (parent, position);
   276         -     CREATE INDEX aaa_333 ON aaa (fk, lastModified);
   277         -     CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
   278         -                       fk INTEGER DEFAULT NULL, parent INTEGER,
   279         -                       position INTEGER, title LONGVARCHAR,
   280         -                       keyword_id INTEGER, folder_type TEXT,
   281         -                       dateAdded INTEGER, lastModified INTEGER);
   282         -     CREATE INDEX bbb_111 ON bbb (fk, type);
   283         -     CREATE INDEX bbb_222 ON bbb (parent, position);
   284         -     CREATE INDEX bbb_333 ON bbb (fk, lastModified);
   285         -  }
          274  +do_execsql_test where3-5.0 {
          275  +  CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
          276  +                    fk INTEGER DEFAULT NULL, parent INTEGER,
          277  +                    position INTEGER, title LONGVARCHAR,
          278  +                    keyword_id INTEGER, folder_type TEXT,
          279  +                    dateAdded INTEGER, lastModified INTEGER);
          280  +  CREATE INDEX aaa_111 ON aaa (fk, type);
          281  +  CREATE INDEX aaa_222 ON aaa (parent, position);
          282  +  CREATE INDEX aaa_333 ON aaa (fk, lastModified);
          283  +  CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
          284  +                    fk INTEGER DEFAULT NULL, parent INTEGER,
          285  +                    position INTEGER, title LONGVARCHAR,
          286  +                    keyword_id INTEGER, folder_type TEXT,
          287  +                    dateAdded INTEGER, lastModified INTEGER);
          288  +  CREATE INDEX bbb_111 ON bbb (fk, type);
          289  +  CREATE INDEX bbb_222 ON bbb (parent, position);
          290  +  CREATE INDEX bbb_333 ON bbb (fk, lastModified);
   286    291   
   287         -  execsql {
   288         -    EXPLAIN QUERY PLAN
   289         -     SELECT bbb.title AS tag_title 
   290         -       FROM aaa JOIN bbb ON bbb.id = aaa.parent  
   291         -      WHERE aaa.fk = 'constant'
   292         -        AND LENGTH(bbb.title) > 0
   293         -        AND bbb.parent = 4
   294         -      ORDER BY bbb.title COLLATE NOCASE ASC;
   295         -  }
   296         -} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE bbb USING PRIMARY KEY}}
   297         -do_test where3-5.1 {
   298         -  execsql {
   299         -    EXPLAIN QUERY PLAN
   300         -     SELECT bbb.title AS tag_title 
   301         -       FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
   302         -      WHERE aaa.fk = 'constant'
   303         -        AND LENGTH(bbb.title) > 0
   304         -        AND bbb.parent = 4
   305         -      ORDER BY bbb.title COLLATE NOCASE ASC;
   306         -  }
   307         -} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE aaa AS bbb USING PRIMARY KEY}}
   308         -do_test where3-5.2 {
   309         -  execsql {
   310         -    EXPLAIN QUERY PLAN
   311         -     SELECT bbb.title AS tag_title 
   312         -       FROM bbb JOIN aaa ON bbb.id = aaa.parent  
   313         -      WHERE aaa.fk = 'constant'
   314         -        AND LENGTH(bbb.title) > 0
   315         -        AND bbb.parent = 4
   316         -      ORDER BY bbb.title COLLATE NOCASE ASC;
   317         -  }
   318         -} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE bbb USING PRIMARY KEY}}
   319         -do_test where3-5.3 {
   320         -  execsql {
   321         -    EXPLAIN QUERY PLAN
   322         -     SELECT bbb.title AS tag_title 
   323         -       FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
   324         -      WHERE aaa.fk = 'constant'
   325         -        AND LENGTH(bbb.title) > 0
   326         -        AND bbb.parent = 4
   327         -      ORDER BY bbb.title COLLATE NOCASE ASC;
   328         -  }
   329         -} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE aaa AS bbb USING PRIMARY KEY}}
          292  +  EXPLAIN QUERY PLAN
          293  +   SELECT bbb.title AS tag_title 
          294  +     FROM aaa JOIN bbb ON bbb.id = aaa.parent  
          295  +    WHERE aaa.fk = 'constant'
          296  +      AND LENGTH(bbb.title) > 0
          297  +      AND bbb.parent = 4
          298  +    ORDER BY bbb.title COLLATE NOCASE ASC;
          299  +} {
          300  +  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
          301  +  0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          302  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          303  +}
          304  +do_execsql_test where3-5.1 {
          305  +  EXPLAIN QUERY PLAN
          306  +   SELECT bbb.title AS tag_title 
          307  +     FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
          308  +    WHERE aaa.fk = 'constant'
          309  +      AND LENGTH(bbb.title) > 0
          310  +      AND bbb.parent = 4
          311  +    ORDER BY bbb.title COLLATE NOCASE ASC;
          312  +} {
          313  +  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
          314  +  0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          315  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          316  +}
          317  +do_execsql_test where3-5.2 {
          318  +  EXPLAIN QUERY PLAN
          319  +   SELECT bbb.title AS tag_title 
          320  +     FROM bbb JOIN aaa ON bbb.id = aaa.parent  
          321  +    WHERE aaa.fk = 'constant'
          322  +      AND LENGTH(bbb.title) > 0
          323  +      AND bbb.parent = 4
          324  +    ORDER BY bbb.title COLLATE NOCASE ASC;
          325  +} {
          326  +  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
          327  +  0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          328  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          329  +}
          330  +do_execsql_test where3-5.3 {
          331  +  EXPLAIN QUERY PLAN
          332  +   SELECT bbb.title AS tag_title 
          333  +     FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
          334  +    WHERE aaa.fk = 'constant'
          335  +      AND LENGTH(bbb.title) > 0
          336  +      AND bbb.parent = 4
          337  +    ORDER BY bbb.title COLLATE NOCASE ASC;
          338  +} {
          339  +  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
          340  +  0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          341  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          342  +}
   330    343   
   331    344   
   332    345   finish_test

Changes to test/where7.test.

 23303  23303   # test case for the performance regression fixed by
 23304  23304   # check-in 28ba6255282b on 2010-10-21 02:05:06
 23305  23305   #
 23306  23306   # The test case that follows is code from an actual
 23307  23307   # application with identifiers change and unused columns
 23308  23308   # remove.
 23309  23309   #
 23310         -do_test where7-3.1 {
 23311         -  db eval {
 23312         -    CREATE TABLE t301 (
 23313         -        c8 INTEGER PRIMARY KEY,
 23314         -        c6 INTEGER,
 23315         -        c4 INTEGER,
 23316         -        c7 INTEGER,
 23317         -        FOREIGN KEY (c4) REFERENCES series(c4)
 23318         -    );
 23319         -    CREATE INDEX t301_c6 on t301(c6);
 23320         -    CREATE INDEX t301_c4 on t301(c4);
 23321         -    CREATE INDEX t301_c7 on t301(c7);
 23322         -    
 23323         -    CREATE TABLE t302 (
 23324         -        c1 INTEGER PRIMARY KEY,
 23325         -        c8 INTEGER,
 23326         -        c5 INTEGER,
 23327         -        c3 INTEGER,
 23328         -        c2 INTEGER,
 23329         -        c4 INTEGER,
 23330         -        FOREIGN KEY (c8) REFERENCES t301(c8)
 23331         -    );
 23332         -    CREATE INDEX t302_c3 on t302(c3);
 23333         -    CREATE INDEX t302_c8_c3 on t302(c8, c3);
 23334         -    CREATE INDEX t302_c5 on t302(c5);
 23335         -    
 23336         -    EXPLAIN QUERY PLAN
 23337         -    SELECT t302.c1 
 23338         -      FROM t302 JOIN t301 ON t302.c8 = t301.c8
 23339         -      WHERE t302.c2 = 19571
 23340         -        AND t302.c3 > 1287603136
 23341         -        AND (t301.c4 = 1407449685622784
 23342         -             OR t301.c8 = 1407424651264000)
 23343         -     ORDER BY t302.c5 LIMIT 200;
 23344         -  }
 23345         -} {0 1 {TABLE t301 VIA MULTI-INDEX UNION} 1 0 {TABLE t302 WITH INDEX t302_c8_c3} 0 0 {TABLE t301 WITH INDEX t301_c4} 0 0 {TABLE t301 USING PRIMARY KEY}}
        23310  +do_execsql_test where7-3.1 {
        23311  +  CREATE TABLE t301 (
        23312  +      c8 INTEGER PRIMARY KEY,
        23313  +      c6 INTEGER,
        23314  +      c4 INTEGER,
        23315  +      c7 INTEGER,
        23316  +      FOREIGN KEY (c4) REFERENCES series(c4)
        23317  +  );
        23318  +  CREATE INDEX t301_c6 on t301(c6);
        23319  +  CREATE INDEX t301_c4 on t301(c4);
        23320  +  CREATE INDEX t301_c7 on t301(c7);
        23321  +  
        23322  +  CREATE TABLE t302 (
        23323  +      c1 INTEGER PRIMARY KEY,
        23324  +      c8 INTEGER,
        23325  +      c5 INTEGER,
        23326  +      c3 INTEGER,
        23327  +      c2 INTEGER,
        23328  +      c4 INTEGER,
        23329  +      FOREIGN KEY (c8) REFERENCES t301(c8)
        23330  +  );
        23331  +  CREATE INDEX t302_c3 on t302(c3);
        23332  +  CREATE INDEX t302_c8_c3 on t302(c8, c3);
        23333  +  CREATE INDEX t302_c5 on t302(c5);
        23334  +  
        23335  +  EXPLAIN QUERY PLAN
        23336  +  SELECT t302.c1 
        23337  +    FROM t302 JOIN t301 ON t302.c8 = t301.c8
        23338  +    WHERE t302.c2 = 19571
        23339  +      AND t302.c3 > 1287603136
        23340  +      AND (t301.c4 = 1407449685622784
        23341  +           OR t301.c8 = 1407424651264000)
        23342  +   ORDER BY t302.c5 LIMIT 200;
        23343  +} {
        23344  +  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
        23345  +  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
        23346  +  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
        23347  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
        23348  +}
 23346  23349   
 23347  23350   finish_test

Changes to test/where9.test.

   354    354        WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
   355    355       ORDER BY 1, 2, 3
   356    356     }
   357    357   } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}
   358    358   
   359    359   
   360    360   ifcapable explain {
   361         -  do_test where9-3.1 {
   362         -    set r [db eval {
   363         -      EXPLAIN QUERY PLAN
   364         -      SELECT t2.a FROM t1, t2
   365         -       WHERE t1.a=80
   366         -         AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   367         -    }]
   368         -    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
   369         -    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
   370         -    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
   371         -                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
   372         -    concat $a $b $c
   373         -  } {1 1 1}
   374         -  do_test where9-3.2 {
   375         -    set r [db eval {
   376         -      EXPLAIN QUERY PLAN
   377         -      SELECT coalesce(t2.a,9999)
   378         -        FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
   379         -       WHERE t1.a=80
   380         -    }]
   381         -    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
   382         -    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
   383         -    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
   384         -                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
   385         -    concat $a $b $c
   386         -  } {1 1 1}
          361  +  do_execsql_test where9-3.1 {
          362  +    EXPLAIN QUERY PLAN
          363  +    SELECT t2.a FROM t1, t2
          364  +    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
          365  +  } {
          366  +    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          367  +    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
          368  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          369  +  }
          370  +  do_execsql_test where9-3.2 {
          371  +    EXPLAIN QUERY PLAN
          372  +    SELECT coalesce(t2.a,9999)
          373  +    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
          374  +    WHERE t1.a=80
          375  +  } {
          376  +    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          377  +    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
          378  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          379  +  }
   387    380   } 
   388    381   
   389    382   # Make sure that INDEXED BY and multi-index OR clauses play well with
   390    383   # one another.
   391    384   #
   392    385   do_test where9-4.1 {
   393    386     count_steps {
................................................................................
   454    447     }
   455    448   } {1 {cannot use index: t1d}}
   456    449   
   457    450   ifcapable explain {
   458    451     # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   459    452     # the former is an equality test which is expected to return fewer rows.
   460    453     #
   461         -  do_test where9-5.1 {
   462         -    set r [db eval {
   463         -      EXPLAIN QUERY PLAN
   464         -      SELECT a FROM t1
   465         -       WHERE b>1000
   466         -         AND (c=31031 OR d IS NULL)
   467         -    }]
   468         -    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
   469         -    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
   470         -    concat $a $b
   471         -  } {1 0}
          454  +  do_execsql_test where9-5.1 {
          455  +    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
          456  +  } {
          457  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
          458  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
          459  +  }
   472    460   
   473    461     # In contrast, b=1000 is preferred over any OR-clause.
   474    462     #
   475         -  do_test where9-5.2 {
   476         -    set r [db eval {
   477         -      EXPLAIN QUERY PLAN
   478         -      SELECT a FROM t1
   479         -       WHERE b=1000
   480         -         AND (c=31031 OR d IS NULL)
   481         -    }]
   482         -    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
   483         -    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
   484         -    concat $a $b
   485         -  } {0 1}
          463  +  do_execsql_test where9-5.2 {
          464  +    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
          465  +  } {
          466  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)}
          467  +  }
   486    468   
   487    469     # Likewise, inequalities in an AND are preferred over inequalities in
   488    470     # an OR.
   489    471     #
   490         -  do_test where9-5.3 {
   491         -    set r [db eval {
   492         -      EXPLAIN QUERY PLAN
   493         -      SELECT a FROM t1
   494         -       WHERE b>1000
   495         -         AND (c>=31031 OR d IS NULL)
   496         -    }]
   497         -    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
   498         -    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
   499         -    concat $a $b
   500         -  } {0 1}
          472  +  do_execsql_test where9-5.3 {
          473  +    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
          474  +  } {
          475  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~165000 rows)}
          476  +  }
   501    477   }
   502    478   
   503    479   ############################################################################
   504    480   # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
   505    481   
   506    482   do_test where9-6.2.1 {
   507    483     db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}