/ Check-in [ae985db4]
Login

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

Overview
Comment:Futher simplifications to the NGQP. Fix some test cases to use EXPLAIN QUERY PLAN rather than the (now obsolete) sqlite_query_plan global variable.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:ae985db4fa08b5efbef5a834e852f0b05101264b
User & Date: drh 2013-05-30 19:29:19
Context
2013-05-30
22:27
Incremental check-in with various NGQP fixes. Many tests still fail. check-in: a51d8c92 user: drh tags: nextgen-query-plan-exp
19:29
Futher simplifications to the NGQP. Fix some test cases to use EXPLAIN QUERY PLAN rather than the (now obsolete) sqlite_query_plan global variable. check-in: ae985db4 user: drh tags: nextgen-query-plan-exp
19:28
The expected result in a test case can be of the form "*glob*" or "~*glob*" to match or not match the GLOB pattern. This is useful for matching EXPLAIN QUERY PLAN output that contains regular expression syntax characters like "?", "(", and ")". check-in: a3b4e261 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/test1.c.

  6298   6298   #endif
  6299   6299   #ifdef SQLITE_DEBUG
  6300   6300     extern int sqlite3WhereTrace;
  6301   6301     extern int sqlite3OSTrace;
  6302   6302     extern int sqlite3WalTrace;
  6303   6303   #endif
  6304   6304   #ifdef SQLITE_TEST
  6305         -  extern char sqlite3_query_plan[];
  6306         -  static char *query_plan = sqlite3_query_plan;
  6307   6305   #ifdef SQLITE_ENABLE_FTS3
  6308   6306     extern int sqlite3_fts3_enable_parentheses;
  6309   6307   #endif
  6310   6308   #endif
  6311   6309   
  6312   6310     for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
  6313   6311       Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
................................................................................
  6353   6351         (char*)&pzNeededCollation, TCL_LINK_STRING|TCL_LINK_READ_ONLY);
  6354   6352   #endif
  6355   6353   #if SQLITE_OS_WIN
  6356   6354     Tcl_LinkVar(interp, "sqlite_os_type",
  6357   6355         (char*)&sqlite3_os_type, TCL_LINK_INT);
  6358   6356   #endif
  6359   6357   #ifdef SQLITE_TEST
         6358  +  {
         6359  +    static const char *query_plan = "*** OBSOLETE VARIABLE ***";
  6360   6360     Tcl_LinkVar(interp, "sqlite_query_plan",
  6361   6361         (char*)&query_plan, TCL_LINK_STRING|TCL_LINK_READ_ONLY);
         6362  +  }
  6362   6363   #endif
  6363   6364   #ifdef SQLITE_DEBUG
  6364   6365     Tcl_LinkVar(interp, "sqlite_where_trace",
  6365   6366         (char*)&sqlite3WhereTrace, TCL_LINK_INT);
  6366   6367     Tcl_LinkVar(interp, "sqlite_os_trace",
  6367   6368         (char*)&sqlite3OSTrace, TCL_LINK_INT);
  6368   6369   #ifndef SQLITE_OMIT_WAL

Changes to src/where.c.

   322    322   #define WO_SINGLE 0x0ff       /* Mask of all non-compound WO_* values */
   323    323   
   324    324   /*
   325    325   ** Value for wsFlags returned by bestIndex() and stored in
   326    326   ** WhereLevel.wsFlags.  These flags determine which search
   327    327   ** strategies are appropriate.
   328    328   */
   329         -#define WHERE_ROWID_EQ     0x00000001  /* rowid=EXPR or rowid IN (...) */
   330         -#define WHERE_ROWID_RANGE  0x00000002  /* rowid<EXPR and/or rowid>EXPR */
   331         -#define WHERE_NULL_OK      0x00000004  /* Ok to use WO_ISNULL */
   332         -#define WHERE_IPK          0x00000008  /* x is the INTEGER PRIMARY KEY */
   333         -#define WHERE_COLUMN_EQ    0x00000010  /* x=EXPR or x IN (...) or x IS NULL */
   334         -#define WHERE_COLUMN_RANGE 0x00000020  /* x<EXPR and/or x>EXPR */
   335         -#define WHERE_COLUMN_IN    0x00000040  /* x IN (...) */
   336         -#define WHERE_COLUMN_NULL  0x00000080  /* x IS NULL */
   337         -#define WHERE_INDEXED      0x000000f0  /* Anything that uses an index */
   338         -#define WHERE_NOT_FULLSCAN 0x000200f3  /* Does not do a full table scan */
   339         -#define WHERE_IN_ABLE      0x000100f1  /* Able to support an IN operator */
   340         -#define WHERE_TOP_LIMIT    0x00000100  /* x<EXPR or x<=EXPR constraint */
   341         -#define WHERE_BTM_LIMIT    0x00000200  /* x>EXPR or x>=EXPR constraint */
   342         -#define WHERE_BOTH_LIMIT   0x00000300  /* Both x>EXPR and x<EXPR */
   343         -#define WHERE_IDX_ONLY     0x00000400  /* Use index only - omit table */
   344         -#define WHERE_ORDERED      0x00000800  /* Output will appear in correct order */
   345         -#define WHERE_REVERSE      0x00001000  /* Scan in reverse order */
   346         -#define WHERE_UNIQUE       0x00002000  /* Selects no more than one row */
   347         -#define WHERE_ALL_UNIQUE   0x00004000  /* This and all prior have one row */
   348         -#define WHERE_OB_UNIQUE    0x00008000  /* Values in ORDER BY columns are 
   349         -                                       ** different for every output row */
   350         -#define WHERE_VIRTUALTABLE 0x00010000  /* Use virtual-table processing */
   351         -#define WHERE_MULTI_OR     0x00020000  /* OR using multiple indices */
   352         -#define WHERE_TEMP_INDEX   0x00040000  /* Uses an ephemeral index */
   353         -#define WHERE_DISTINCT     0x00080000  /* Correct order for DISTINCT */
   354         -#define WHERE_COVER_SCAN   0x00100000  /* Full scan of a covering index */
   355         -#define WHERE_SINGLE_ROW   0x00200000  /* No more than one row guaranteed */
          329  +#define WHERE_COLUMN_EQ    0x00000001  /* x=EXPR or x IN (...) or x IS NULL */
          330  +#define WHERE_COLUMN_RANGE 0x00000002  /* x<EXPR and/or x>EXPR */
          331  +#define WHERE_COLUMN_IN    0x00000004  /* x IN (...) */
          332  +#define WHERE_COLUMN_NULL  0x00000008  /* x IS NULL */
          333  +#define WHERE_TOP_LIMIT    0x00000010  /* x<EXPR or x<=EXPR constraint */
          334  +#define WHERE_BTM_LIMIT    0x00000020  /* x>EXPR or x>=EXPR constraint */
          335  +#define WHERE_BOTH_LIMIT   0x00000030  /* Both x>EXPR and x<EXPR */
          336  +#define WHERE_IDX_ONLY     0x00000040  /* Use index only - omit table */
          337  +#define WHERE_IPK          0x00000100  /* x is the INTEGER PRIMARY KEY */
          338  +#define WHERE_INDEXED      0x00000200  /* WhereLoop.u.btree.pIndex is valid */
          339  +#define WHERE_VIRTUALTABLE 0x00000400  /* WhereLoop.u.vtab is valid */
          340  +#define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
          341  +#define WHERE_UNIQUE       0x00001000  /* Selects no more than one row */
          342  +#define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
          343  +#define WHERE_TEMP_INDEX   0x00004000  /* Uses an ephemeral index */
          344  +#define WHERE_COVER_SCAN   0x00008000  /* Full scan of a covering index */
   356    345   
   357    346   /*
   358    347   ** This module contains many separate subroutines that work together to
   359    348   ** find the best indices to use for accessing a particular table in a query.
   360    349   ** An instance of the following structure holds context information about the
   361    350   ** index search so that it can be more easily passed between the various
   362    351   ** routines.
................................................................................
  2667   2656       eType = sqlite3FindInIndex(pParse, pX, 0);
  2668   2657       if( eType==IN_INDEX_INDEX_DESC ){
  2669   2658         testcase( bRev );
  2670   2659         bRev = !bRev;
  2671   2660       }
  2672   2661       iTab = pX->iTable;
  2673   2662       sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  2674         -    assert( pLoop->wsFlags & WHERE_IN_ABLE );
         2663  +    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
         2664  +    pLoop->wsFlags |= WHERE_IN_ABLE;
  2675   2665       if( pLevel->u.in.nIn==0 ){
  2676   2666         pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  2677   2667       }
  2678   2668       pLevel->u.in.nIn++;
  2679   2669       pLevel->u.in.aInLoop =
  2680   2670          sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop,
  2681   2671                                 sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn);
................................................................................
  3189   3179       if( testOp!=OP_Noop ){
  3190   3180         iRowidReg = iReleaseReg = sqlite3GetTempReg(pParse);
  3191   3181         sqlite3VdbeAddOp2(v, OP_Rowid, iCur, iRowidReg);
  3192   3182         sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3193   3183         sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, iRowidReg);
  3194   3184         sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);
  3195   3185       }
  3196         -  }else if( pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_COLUMN_EQ|WHERE_IDX_ONLY) ){
         3186  +  }else if( pLoop->wsFlags & (WHERE_COLUMN_RANGE | WHERE_COLUMN_NULL |
         3187  +                              WHERE_COLUMN_EQ | WHERE_IDX_ONLY) ){
  3197   3188       /* Case 4: A scan using an index.
  3198   3189       **
  3199   3190       **         The WHERE clause may contain zero or more equality 
  3200   3191       **         terms ("==" or "IN" operators) that refer to the N
  3201   3192       **         left-most columns of the index. It may also contain
  3202   3193       **         inequality constraints (>, <, >= or <=) on the indexed
  3203   3194       **         column that immediately follows the N equalities. Only 
................................................................................
  3742   3733       }
  3743   3734     }
  3744   3735     sqlite3ReleaseTempReg(pParse, iReleaseReg);
  3745   3736   
  3746   3737     return newNotReady;
  3747   3738   }
  3748   3739   
  3749         -#if defined(SQLITE_TEST)
  3750         -/*
  3751         -** The following variable holds a text description of query plan generated
  3752         -** by the most recent call to sqlite3WhereBegin().  Each call to WhereBegin
  3753         -** overwrites the previous.  This information is used for testing and
  3754         -** analysis only.
  3755         -*/
  3756         -char sqlite3_query_plan[BMS*2*40];  /* Text of the join */
  3757         -static int nQPlan = 0;              /* Next free slow in _query_plan[] */
  3758         -
  3759         -#endif /* SQLITE_TEST */
  3760         -
  3761   3740   #ifdef WHERETRACE_ENABLED
  3762   3741   /*
  3763   3742   ** Print a WhereLoop object for debugging purposes
  3764   3743   */
  3765   3744   static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){
  3766   3745     int nb = 2*((pTabList->nSrc+15)/16);
  3767   3746     struct SrcList_item *pItem = pTabList->a + p->iTab;
................................................................................
  3789   3768                   p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask);
  3790   3769       }else{
  3791   3770         z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
  3792   3771       }
  3793   3772       sqlite3DebugPrintf(" %-15s", z);
  3794   3773       sqlite3_free(z);
  3795   3774     }
  3796         -  sqlite3DebugPrintf(" fg %08x N %d", p->wsFlags, p->nTerm);
         3775  +  sqlite3DebugPrintf(" fg %05x N %d", p->wsFlags, p->nTerm);
  3797   3776     sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n",
  3798   3777                        p->prereq, p->rSetup, p->rRun, p->nOut);
  3799   3778   }
  3800   3779   #endif
  3801   3780   
  3802   3781   /*
  3803   3782   ** Deallocate internal memory used by a WhereLoop object
................................................................................
  3806   3785     sqlite3DbFree(db, p->aTerm);
  3807   3786     p->aTerm = 0;
  3808   3787     p->nTerm = 0;
  3809   3788     if( (p->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  3810   3789       if( p->u.vtab.needFree ) sqlite3_free(p->u.vtab.idxStr);
  3811   3790       p->u.vtab.needFree = 0;
  3812   3791       p->u.vtab.idxStr = 0;
         3792  +  }else if( (p->wsFlags & WHERE_TEMP_INDEX)!=0 && p->u.btree.pIndex!=0 ){
         3793  +    sqlite3DbFree(db, p->u.btree.pIndex->zColAff);
         3794  +    sqlite3DbFree(db, p->u.btree.pIndex);
         3795  +    p->u.btree.pIndex = 0;
  3813   3796     }
  3814   3797   }
  3815   3798   
  3816   3799   /*
  3817   3800   ** Delete a WhereLoop object
  3818   3801   */
  3819   3802   static void whereLoopDelete(sqlite3 *db, WhereLoop *p){
................................................................................
  3822   3805   }
  3823   3806   
  3824   3807   /*
  3825   3808   ** Free a WhereInfo structure
  3826   3809   */
  3827   3810   static void whereInfoFree(sqlite3 *db, WhereInfo *pWInfo){
  3828   3811     if( ALWAYS(pWInfo) ){
  3829         -    int i;
  3830         -    for(i=0; i<pWInfo->nLevel; i++){
  3831         -      if( pWInfo->a[i].pWLoop->wsFlags & WHERE_TEMP_INDEX ){
  3832         -        Index *pIdx = pWInfo->a[i].pWLoop->u.btree.pIndex;
  3833         -        if( pIdx ){
  3834         -          sqlite3DbFree(db, pIdx->zColAff);
  3835         -          sqlite3DbFree(db, pIdx);
  3836         -        }
  3837         -      }
  3838         -    }
  3839   3812       whereClauseClear(pWInfo->pWC);
  3840   3813       while( pWInfo->pLoops ){
  3841   3814         WhereLoop *p = pWInfo->pLoops;
  3842   3815         pWInfo->pLoops = p->pNextLoop;
  3843   3816         whereLoopDelete(db, p);
  3844   3817       }
  3845   3818       sqlite3DbFree(db, pWInfo);
................................................................................
  4022   3995           nIn = 25;
  4023   3996         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4024   3997           /* "x IN (value, value, ...)" */
  4025   3998           nIn = pExpr->x.pList->nExpr;
  4026   3999         }
  4027   4000         pNew->u.btree.nEq++;
  4028   4001         pNew->nOut = (double)iRowEst * nInMul * nIn;
  4029         -    }else if( pTerm->eOperator & (WO_EQ|WO_ISNULL) ){
         4002  +    }else if( pTerm->eOperator & (WO_EQ) ){
  4030   4003         pNew->wsFlags |= WHERE_COLUMN_EQ;
         4004  +      pNew->u.btree.nEq++;
         4005  +      pNew->nOut = (double)iRowEst * nInMul;
         4006  +    }else if( pTerm->eOperator & (WO_ISNULL) ){
         4007  +      pNew->wsFlags |= WHERE_COLUMN_NULL;
  4031   4008         pNew->u.btree.nEq++;
  4032   4009         pNew->nOut = (double)iRowEst * nInMul;
  4033   4010       }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
  4034   4011         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
  4035   4012         pNew->nOut = savedLoop.nOut/3;
  4036   4013       }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
  4037   4014         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
................................................................................
  4193   4170         int j;
  4194   4171         for(j=pProbe->nColumn-1; j>=0; j--){
  4195   4172           int x = pProbe->aiColumn[j];
  4196   4173           if( x<BMS-1 ){
  4197   4174             m &= ~(((Bitmask)1)<<x);
  4198   4175           }
  4199   4176         }
  4200         -      pNew->wsFlags = (m==0) ? WHERE_IDX_ONLY : 0;
         4177  +      pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
  4201   4178   
  4202   4179         /* Full scan via index */
  4203   4180         if( (m==0 || b) && pProbe->bUnordered==0 ){
  4204   4181           pNew->iSortIdx = b ? iSortIdx : 0;
  4205   4182           pNew->nOut = rSize;
  4206   4183           pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize);
  4207   4184           rc = whereLoopInsert(pBuilder, pNew);
................................................................................
  5242   5219     for(ii=0; ii<nTabList; ii++){
  5243   5220       pLevel = &pWInfo->a[ii];
  5244   5221       explainOneScan(pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags);
  5245   5222       notReady = codeOneLoopStart(pWInfo, ii, wctrlFlags, notReady);
  5246   5223       pWInfo->iContinue = pLevel->addrCont;
  5247   5224     }
  5248   5225   
  5249         -#if defined(SQLITE_TEST) && 0  /* For testing and debugging use only */
  5250         -  /* Record in the query plan information about the current table
  5251         -  ** and the index used to access it (if any).  If the table itself
  5252         -  ** is not used, its name is just '{}'.  If no index is used
  5253         -  ** the index is listed as "{}".  If the primary key is used the
  5254         -  ** index name is '*'.
  5255         -  */
  5256         -  for(ii=0; ii<nTabList; ii++){
  5257         -    char *z;
  5258         -    int n;
  5259         -    int w;
  5260         -    struct SrcList_item *pTabItem;
  5261         -
  5262         -    pLevel = &pWInfo->a[ii];
  5263         -    w = pLevel->plan.wsFlags;
  5264         -    pTabItem = &pTabList->a[pLevel->iFrom];
  5265         -    z = pTabItem->zAlias;
  5266         -    if( z==0 ) z = pTabItem->pTab->zName;
  5267         -    n = sqlite3Strlen30(z);
  5268         -    if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
  5269         -      if( (w & WHERE_IDX_ONLY)!=0 && (w & WHERE_COVER_SCAN)==0 ){
  5270         -        memcpy(&sqlite3_query_plan[nQPlan], "{}", 2);
  5271         -        nQPlan += 2;
  5272         -      }else{
  5273         -        memcpy(&sqlite3_query_plan[nQPlan], z, n);
  5274         -        nQPlan += n;
  5275         -      }
  5276         -      sqlite3_query_plan[nQPlan++] = ' ';
  5277         -    }
  5278         -    testcase( w & WHERE_ROWID_EQ );
  5279         -    testcase( w & WHERE_ROWID_RANGE );
  5280         -    if( w & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
  5281         -      memcpy(&sqlite3_query_plan[nQPlan], "* ", 2);
  5282         -      nQPlan += 2;
  5283         -    }else if( (w & WHERE_INDEXED)!=0 && (w & WHERE_COVER_SCAN)==0 ){
  5284         -      n = sqlite3Strlen30(pLevel->plan.u.pIdx->zName);
  5285         -      if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
  5286         -        memcpy(&sqlite3_query_plan[nQPlan], pLevel->plan.u.pIdx->zName, n);
  5287         -        nQPlan += n;
  5288         -        sqlite3_query_plan[nQPlan++] = ' ';
  5289         -      }
  5290         -    }else{
  5291         -      memcpy(&sqlite3_query_plan[nQPlan], "{} ", 3);
  5292         -      nQPlan += 3;
  5293         -    }
  5294         -  }
  5295         -  while( nQPlan>0 && sqlite3_query_plan[nQPlan-1]==' ' ){
  5296         -    sqlite3_query_plan[--nQPlan] = 0;
  5297         -  }
  5298         -  sqlite3_query_plan[nQPlan] = 0;
  5299         -  nQPlan = 0;
  5300         -#endif /* SQLITE_TEST // Testing and debugging use only */
  5301         -
  5302         -  /* Record the continuation address in the WhereInfo structure.  Then
  5303         -  ** clean up and return.
  5304         -  */
         5226  +  /* Done. */
  5305   5227     return pWInfo;
  5306   5228   
  5307   5229     /* Jump here if malloc fails */
  5308   5230   whereBeginError:
  5309   5231     if( pWInfo ){
  5310   5232       pParse->nQueryLoop = pWInfo->savedNQueryLoop;
  5311   5233       whereInfoFree(db, pWInfo);

Changes to test/where.test.

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