/ Check-in [9d2b0af2]
Login

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

Overview
Comment:Fix problems in the backport, reducing the number of errors in the TCL tests to just a few dozen. Most of the remaining errors seem to be real and desirable changes of behavior.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.7.2
Files: files | file ages | folders
SHA1:9d2b0af266b85f10823e54ca6417e76950c1d531
User & Date: drh 2011-02-12 05:34:43
Context
2011-02-12
14:23
Fix the expected output on tests so that it corresponds to the new query planner results. All of veryquick.test is now passing with SQLITE_ENABLE_STAT2. check-in: f2a8b5cc user: drh tags: branch-3.7.2
05:34
Fix problems in the backport, reducing the number of errors in the TCL tests to just a few dozen. Most of the remaining errors seem to be real and desirable changes of behavior. check-in: 9d2b0af2 user: drh tags: branch-3.7.2
01:59
This is the beginning of an attempt to backport recent query planner enhancements to version 3.7.2. The code in this version builds and runs and seems to give correct answers, but it generates suboptimal query plans and hence many of the test cases fail. The test script gives up after 1000 errors. check-in: e72cf118 user: drh tags: branch-3.7.2
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   798    798       pParse->nErr++;
   799    799       goto begin_table_error;
   800    800     }
   801    801     pTable->zName = zName;
   802    802     pTable->iPKey = -1;
   803    803     pTable->pSchema = db->aDb[iDb].pSchema;
   804    804     pTable->nRef = 1;
          805  +  pTable->nRowEst = 1000000;
   805    806     assert( pParse->pNewTable==0 );
   806    807     pParse->pNewTable = pTable;
   807    808   
   808    809     /* If this is the magic sqlite_sequence table used by autoincrement,
   809    810     ** then record a pointer to this table in the main database structure
   810    811     ** so that INSERT can find the table easily.
   811    812     */
................................................................................
  2828   2829   ** Apart from that, we have little to go on besides intuition as to
  2829   2830   ** how aiRowEst[] should be initialized.  The numbers generated here
  2830   2831   ** are based on typical values found in actual indices.
  2831   2832   */
  2832   2833   void sqlite3DefaultRowEst(Index *pIdx){
  2833   2834     unsigned *a = pIdx->aiRowEst;
  2834   2835     int i;
         2836  +  unsigned n;
  2835   2837     assert( a!=0 );
  2836         -  a[0] = 1000000;
  2837         -  for(i=pIdx->nColumn; i>=5; i--){
  2838         -    a[i] = 5;
  2839         -  }
  2840         -  while( i>=1 ){
  2841         -    a[i] = 11 - i;
  2842         -    i--;
         2838  +  a[0] = pIdx->pTable->nRowEst;
         2839  +  if( a[0]<10 ) a[0] = 10;
         2840  +  n = 10;
         2841  +  for(i=1; i<=pIdx->nColumn; i++){
         2842  +    a[i] = n;
         2843  +    if( n>5 ) n--;
  2843   2844     }
  2844   2845     if( pIdx->onError!=OE_None ){
  2845   2846       a[pIdx->nColumn] = 1;
  2846   2847     }
  2847   2848   }
  2848   2849   
  2849   2850   /*

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.

  1298   1298       return 0;
  1299   1299     }
  1300   1300     /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  1301   1301     ** is disabled */
  1302   1302     assert( db->lookaside.bEnabled==0 );
  1303   1303     pTab->nRef = 1;
  1304   1304     pTab->zName = 0;
         1305  +  pTab->nRowEst = 1000000;
  1305   1306     selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  1306   1307     selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect);
  1307   1308     pTab->iPKey = -1;
  1308   1309     if( db->mallocFailed ){
  1309   1310       sqlite3DeleteTable(db, pTab);
  1310   1311       return 0;
  1311   1312     }
................................................................................
  1368   1369       v = sqlite3GetVdbe(pParse);
  1369   1370       if( NEVER(v==0) ) return;  /* VDBE should have already been allocated */
  1370   1371       if( sqlite3ExprIsInteger(p->pLimit, &n) ){
  1371   1372         sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
  1372   1373         VdbeComment((v, "LIMIT counter"));
  1373   1374         if( n==0 ){
  1374   1375           sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
         1376  +      }else{
         1377  +        if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n;
  1375   1378         }
  1376   1379       }else{
  1377   1380         sqlite3ExprCode(pParse, p->pLimit, iLimit);
  1378   1381         sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
  1379   1382         VdbeComment((v, "LIMIT counter"));
  1380   1383         sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak);
  1381   1384       }
................................................................................
  1524   1527     }
  1525   1528   
  1526   1529     /* Generate code for the left and right SELECT statements.
  1527   1530     */
  1528   1531     switch( p->op ){
  1529   1532       case TK_ALL: {
  1530   1533         int addr = 0;
         1534  +      int nLimit;
  1531   1535         assert( !pPrior->pLimit );
  1532   1536         pPrior->pLimit = p->pLimit;
  1533   1537         pPrior->pOffset = p->pOffset;
  1534   1538         rc = sqlite3Select(pParse, pPrior, &dest);
  1535   1539         p->pLimit = 0;
  1536   1540         p->pOffset = 0;
  1537   1541         if( rc ){
................................................................................
  1544   1548           addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
  1545   1549           VdbeComment((v, "Jump ahead if LIMIT reached"));
  1546   1550         }
  1547   1551         rc = sqlite3Select(pParse, p, &dest);
  1548   1552         testcase( rc!=SQLITE_OK );
  1549   1553         pDelete = p->pPrior;
  1550   1554         p->pPrior = pPrior;
         1555  +      p->nSelectRow += pPrior->nSelectRow;
         1556  +      if( pPrior->pLimit
         1557  +       && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit)
         1558  +       && p->nSelectRow > (double)nLimit 
         1559  +      ){
         1560  +        p->nSelectRow = (double)nLimit;
         1561  +      }
  1551   1562         if( addr ){
  1552   1563           sqlite3VdbeJumpHere(v, addr);
  1553   1564         }
  1554   1565         break;
  1555   1566       }
  1556   1567       case TK_EXCEPT:
  1557   1568       case TK_UNION: {
................................................................................
  1614   1625         testcase( rc!=SQLITE_OK );
  1615   1626         /* Query flattening in sqlite3Select() might refill p->pOrderBy.
  1616   1627         ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
  1617   1628         sqlite3ExprListDelete(db, p->pOrderBy);
  1618   1629         pDelete = p->pPrior;
  1619   1630         p->pPrior = pPrior;
  1620   1631         p->pOrderBy = 0;
         1632  +      if( p->op==TK_UNION ) p->nSelectRow += pPrior->nSelectRow;
  1621   1633         sqlite3ExprDelete(db, p->pLimit);
  1622   1634         p->pLimit = pLimit;
  1623   1635         p->pOffset = pOffset;
  1624   1636         p->iLimit = 0;
  1625   1637         p->iOffset = 0;
  1626   1638   
  1627   1639         /* Convert the data in the temporary table into whatever form
................................................................................
  1691   1703         pOffset = p->pOffset;
  1692   1704         p->pOffset = 0;
  1693   1705         intersectdest.iParm = tab2;
  1694   1706         rc = sqlite3Select(pParse, p, &intersectdest);
  1695   1707         testcase( rc!=SQLITE_OK );
  1696   1708         pDelete = p->pPrior;
  1697   1709         p->pPrior = pPrior;
         1710  +      if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
  1698   1711         sqlite3ExprDelete(db, p->pLimit);
  1699   1712         p->pLimit = pLimit;
  1700   1713         p->pOffset = pOffset;
  1701   1714   
  1702   1715         /* Generate code to take the intersection of the two temporary
  1703   1716         ** tables.
  1704   1717         */
................................................................................
  2270   2283     if( op==TK_EXCEPT || op==TK_INTERSECT ){
  2271   2284       addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd);
  2272   2285     }else{  
  2273   2286       addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd);
  2274   2287       sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB);
  2275   2288       sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2276   2289       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA);
         2290  +    p->nSelectRow += pPrior->nSelectRow;
  2277   2291     }
  2278   2292   
  2279   2293     /* Generate a subroutine to run when the results from select B
  2280   2294     ** are exhausted and only data in select A remains.
  2281   2295     */
  2282   2296     if( op==TK_INTERSECT ){
  2283   2297       addrEofB = addrEofA;
         2298  +    if( p->nSelectRow > pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
  2284   2299     }else{  
  2285   2300       VdbeNoopComment((v, "eof-B subroutine"));
  2286   2301       addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd);
  2287   2302       sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA);
  2288   2303       sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2289   2304       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB);
  2290   2305     }
................................................................................
  3097   3112         pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  3098   3113         if( pTab==0 ) return WRC_Abort;
  3099   3114         pTab->nRef = 1;
  3100   3115         pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab);
  3101   3116         while( pSel->pPrior ){ pSel = pSel->pPrior; }
  3102   3117         selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
  3103   3118         pTab->iPKey = -1;
         3119  +      pTab->nRowEst = 1000000;
  3104   3120         pTab->tabFlags |= TF_Ephemeral;
  3105   3121   #endif
  3106   3122       }else{
  3107   3123         /* An ordinary table or view name in the FROM clause */
  3108   3124         assert( pFrom->pTab==0 );
  3109   3125         pFrom->pTab = pTab = 
  3110   3126           sqlite3LocateTable(pParse,0,pFrom->zName,pFrom->zDatabase);
................................................................................
  3654   3670         }
  3655   3671         i = -1;
  3656   3672       }else{
  3657   3673         sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3658   3674         assert( pItem->isPopulated==0 );
  3659   3675         sqlite3Select(pParse, pSub, &dest);
  3660   3676         pItem->isPopulated = 1;
         3677  +      pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
  3661   3678       }
  3662   3679       if( /*pParse->nErr ||*/ db->mallocFailed ){
  3663   3680         goto select_end;
  3664   3681       }
  3665   3682       pParse->nHeight -= sqlite3SelectExprHeight(p);
  3666   3683       pTabList = p->pSrc;
  3667   3684       if( !IgnorableOrderby(pDest) ){
................................................................................
  3754   3771     if( pDest->eDest==SRT_EphemTab ){
  3755   3772       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iParm, pEList->nExpr);
  3756   3773     }
  3757   3774   
  3758   3775     /* Set the limiter.
  3759   3776     */
  3760   3777     iEnd = sqlite3VdbeMakeLabel(v);
         3778  +  p->nSelectRow = (double)LARGEST_INT64;
  3761   3779     computeLimitRegisters(pParse, p, iEnd);
  3762   3780   
  3763   3781     /* Open a virtual index to use for the distinct set.
  3764   3782     */
  3765   3783     if( isDistinct ){
  3766   3784       KeyInfo *pKeyInfo;
  3767   3785       assert( isAgg || pGroupBy );
................................................................................
  3776   3794     /* Aggregate and non-aggregate queries are handled differently */
  3777   3795     if( !isAgg && pGroupBy==0 ){
  3778   3796       /* This case is for non-aggregate queries
  3779   3797       ** Begin the database scan
  3780   3798       */
  3781   3799       pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0);
  3782   3800       if( pWInfo==0 ) goto select_end;
         3801  +    if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut;
  3783   3802   
  3784   3803       /* If sorting index that was created by a prior OP_OpenEphemeral 
  3785   3804       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  3786   3805       ** into an OP_Noop.
  3787   3806       */
  3788   3807       if( addrSortIndex>=0 && pOrderBy==0 ){
  3789   3808         sqlite3VdbeChangeToNoop(v, addrSortIndex, 1);
................................................................................
  3820   3839   
  3821   3840         for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
  3822   3841           pItem->iAlias = 0;
  3823   3842         }
  3824   3843         for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
  3825   3844           pItem->iAlias = 0;
  3826   3845         }
         3846  +      if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100;
         3847  +    }else{
         3848  +      p->nSelectRow = (double)1;
  3827   3849       }
  3828   3850   
  3829   3851    
  3830   3852       /* Create a label to jump to when we want to abort the query */
  3831   3853       addrEnd = sqlite3VdbeMakeLabel(v);
  3832   3854   
  3833   3855       /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in

Changes to src/sqliteInt.h.

  1991   1991     Select *pPrior;        /* Prior select in a compound select statement */
  1992   1992     Select *pNext;         /* Next select to the left in a compound */
  1993   1993     Select *pRightmost;    /* Right-most select in a compound select statement */
  1994   1994     Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  1995   1995     Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  1996   1996     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  1997   1997     int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
         1998  +  double nSelectRow;     /* Estimated number of result rows */
  1998   1999   };
  1999   2000   
  2000   2001   /*
  2001   2002   ** Allowed values for Select.selFlags.  The "SF" prefix stands for
  2002   2003   ** "Select Flag".
  2003   2004   */
  2004   2005   #define SF_Distinct        0x0001  /* Output should be DISTINCT */

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 test/analyze2.test.

    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   ifcapable !stat2 {
    21     21     finish_test
    22     22     return
    23     23   }
           24  +
           25  +set testprefix analyze2
    24     26   
    25     27   # Do not use a codec for tests in this file, as the database file is
    26     28   # manipulated directly using tcl scripts (using the [hexio_write] command).
    27     29   #
    28     30   do_not_use_codec
    29     31   
    30     32   #--------------------------------------------------------------------
................................................................................
   115    117     }
   116    118     for {set i 0} {$i < 1000} {incr i} {
   117    119       execsql { INSERT INTO t1 VALUES($i, $i) }
   118    120     }
   119    121     execsql COMMIT
   120    122     execsql ANALYZE
   121    123   } {}
   122         -do_test analyze2-2.2 {
   123         -  eqp "SELECT * FROM t1 WHERE x>500 AND y>700"
   124         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   125         -do_test analyze2-2.3 {
   126         -  eqp "SELECT * FROM t1 WHERE x>700 AND y>500"
   127         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   128         -do_test analyze2-2.3 {
   129         -  eqp "SELECT * FROM t1 WHERE y>700 AND x>500"
   130         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   131         -do_test analyze2-2.4 {
   132         -  eqp "SELECT * FROM t1 WHERE y>500 AND x>700"
   133         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   134         -do_test analyze2-2.5 {
   135         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700"
   136         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   137         -do_test analyze2-2.6 {
   138         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700"
   139         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   140         -do_test analyze2-2.7 {
   141         -  eqp "SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300"
   142         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   143         -do_test analyze2-2.8 {
   144         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300"
   145         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   146         -do_test analyze2-2.9 {
   147         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300"
   148         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   149         -do_test analyze2-2.10 {
   150         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100"
   151         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
          124  +do_eqp_test 2.2 {
          125  +  SELECT * FROM t1 WHERE x>500 AND y>700
          126  +} {
          127  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
          128  +}
          129  +do_eqp_test 2.3 {
          130  +  SELECT * FROM t1 WHERE x>700 AND y>500
          131  +} {
          132  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
          133  +}
          134  +do_eqp_test 2.3 {
          135  +  SELECT * FROM t1 WHERE y>700 AND x>500
          136  +} {
          137  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
          138  +}
          139  +do_eqp_test 2.4 {
          140  +  SELECT * FROM t1 WHERE y>500 AND x>700
          141  +} {
          142  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
          143  +}
          144  +do_eqp_test 2.5 {
          145  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700
          146  +} {
          147  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
          148  +}
          149  +do_eqp_test 2.6 {
          150  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700
          151  +} {
          152  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~75 rows)}
          153  +}
          154  +do_eqp_test 2.7 {
          155  +  SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300
          156  +} {
          157  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
          158  +}
          159  +do_eqp_test 2.8 {
          160  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300
          161  +} {
          162  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
          163  +}
          164  +do_eqp_test 2.9 {
          165  +  SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300
          166  +} {
          167  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
          168  +}
          169  +do_eqp_test 2.10 {
          170  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100
          171  +} {
          172  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
          173  +}
   152    174   
   153    175   do_test analyze2-3.1 {
   154    176     set alphabet [list a b c d e f g h i j]
   155    177     execsql BEGIN
   156    178     for {set i 0} {$i < 1000} {incr i} {
   157    179       set str    [lindex $alphabet [expr ($i/100)%10]] 
   158    180       append str [lindex $alphabet [expr ($i/ 10)%10]]
................................................................................
   173    195       SELECT tbl,idx,group_concat(sample,' ') 
   174    196       FROM sqlite_stat2 
   175    197       WHERE idx = 't1_y' 
   176    198       GROUP BY tbl,idx
   177    199     }
   178    200   } {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}
   179    201   
   180         -do_test analyze2-3.3 {
   181         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'"
   182         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   183         -do_test analyze2-3.4 {
   184         -  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'"
   185         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
   186         -do_test analyze2-3.5 {
   187         -  eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'"
   188         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   189         -do_test analyze2-3.6 {
   190         -  eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'"
   191         -} {0 0 {TABLE t1 WITH INDEX t1_y}}
   192         -do_test analyze2-3.7 {
   193         -  eqp "SELECT * FROM t1 WHERE x<221 AND y>'g'"
   194         -} {0 0 {TABLE t1 WITH INDEX t1_x}}
          202  +do_eqp_test 3.3 {
          203  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
          204  +} {
          205  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
          206  +}
          207  +do_eqp_test 3.4 {
          208  +  SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'
          209  +} {
          210  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~100 rows)}
          211  +}
          212  +do_eqp_test 3.5 {
          213  +  SELECT * FROM t1 WHERE x<'a' AND y>'h'
          214  +} {
          215  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
          216  +}
          217  +do_eqp_test 3.6 {
          218  +  SELECT * FROM t1 WHERE x<444 AND y>'h'
          219  +} {
          220  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
          221  +}
          222  +do_eqp_test 3.7 {
          223  +  SELECT * FROM t1 WHERE x<221 AND y>'g'
          224  +} {
          225  +  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x<?) (~66 rows)}
          226  +}
   195    227   
   196    228   do_test analyze2-4.1 {
   197    229     execsql { CREATE TABLE t3(a COLLATE nocase, b) }
   198    230     execsql { CREATE INDEX t3a ON t3(a) }
   199    231     execsql { CREATE INDEX t3b ON t3(b) }
   200    232     set alphabet [list A b C d E f G h I j]
   201    233     execsql BEGIN
................................................................................
   206    238       execsql { INSERT INTO t3 VALUES($str, $str) }
   207    239     }
   208    240     execsql COMMIT
   209    241     execsql ANALYZE
   210    242   } {}
   211    243   do_test analyze2-4.2 {
   212    244     execsql { 
          245  +    PRAGMA automatic_index=OFF;
   213    246       SELECT tbl,idx,group_concat(sample,' ') 
   214    247       FROM sqlite_stat2 
   215    248       WHERE idx = 't3a' 
   216         -    GROUP BY tbl,idx
          249  +    GROUP BY tbl,idx;
          250  +    PRAGMA automatic_index=ON;
   217    251     }
   218    252   } {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
   219    253   do_test analyze2-4.3 {
   220    254     execsql { 
   221    255       SELECT tbl,idx,group_concat(sample,' ') 
   222    256       FROM sqlite_stat2 
   223    257       WHERE idx = 't3b' 
   224    258       GROUP BY tbl,idx
   225    259     }
   226    260   } {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}
   227    261   
   228         -do_test analyze2-4.4 {
   229         -  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'"
   230         -} {0 0 {TABLE t3 WITH INDEX t3b}}
   231         -do_test analyze2-4.5 {
   232         -  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'"
   233         -} {0 0 {TABLE t3 WITH INDEX t3a}}
          262  +do_eqp_test 4.4 {
          263  +  SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'
          264  +} {
          265  +  0 0 0 {SEARCH TABLE t3 USING INDEX t3b (b>? AND b<?) (~11 rows)}
          266  +}
          267  +do_eqp_test 4.5 {
          268  +  SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'
          269  +} {
          270  +  0 0 0 {SEARCH TABLE t3 USING INDEX t3a (a>? AND a<?) (~22 rows)}
          271  +}
   234    272   
   235    273   ifcapable utf16 {
   236    274     proc test_collate {enc lhs rhs} {
   237    275       # puts $enc
   238    276       return [string compare $lhs $rhs]
   239    277     }
   240    278     do_test analyze2-5.1 {
................................................................................
   256    294       execsql { 
   257    295         SELECT tbl,idx,group_concat(sample,' ') 
   258    296         FROM sqlite_stat2 
   259    297         WHERE tbl = 't4' 
   260    298         GROUP BY tbl,idx
   261    299       }
   262    300     } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
   263         -  do_test analyze2-5.3 {
   264         -    eqp "SELECT * FROM t4 WHERE x>'ccc'"
   265         -  } {0 0 {TABLE t4 WITH INDEX t4x}}
   266         -  do_test analyze2-5.4 {
   267         -    eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'"
   268         -  } {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}}
   269         -  do_test analyze2-5.5 {
   270         -    eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'"
   271         -  } {0 0 {TABLE t4 AS t41 WITH INDEX t4x} 1 1 {TABLE t4 AS t42 WITH INDEX t4x}}
          301  +  do_eqp_test 5.3 {
          302  +    SELECT * FROM t4 WHERE x>'ccc'
          303  +  } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
          304  +  do_eqp_test 5.4 {
          305  +    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'
          306  +  } {
          307  +    0 0 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~300 rows)} 
          308  +    0 1 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~800 rows)}
          309  +  }
          310  +  do_eqp_test 5.5 {
          311  +    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'
          312  +  } {
          313  +    0 0 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~700 rows)} 
          314  +    0 1 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~800 rows)}
          315  +  }
   272    316   }
   273    317   
   274    318   #--------------------------------------------------------------------
   275    319   # These tests, analyze2-6.*, verify that the library behaves correctly
   276    320   # when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
   277    321   #
   278    322   # If the sqlite_stat1 table is not present, then the sqlite_stat2
................................................................................
   302    346   } {}
   303    347   
   304    348   do_test analyze2-6.1.1 {
   305    349     eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   306    350          t5.a = 1 AND
   307    351          t6.a = 1 AND t6.b = 1
   308    352     }
   309         -} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          353  +} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   310    354   do_test analyze2-6.1.2 {
   311    355     db cache flush
   312    356     execsql ANALYZE
   313    357     eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   314    358          t5.a = 1 AND
   315    359          t6.a = 1 AND t6.b = 1
   316    360     }
   317         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          361  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   318    362   do_test analyze2-6.1.3 {
   319    363     sqlite3 db test.db
   320    364     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   321    365          t5.a = 1 AND
   322    366          t6.a = 1 AND t6.b = 1
   323    367     }
   324         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          368  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   325    369   do_test analyze2-6.1.4 {
   326    370     execsql { 
   327    371       PRAGMA writable_schema = 1;
   328    372       DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
   329    373     }
   330    374     sqlite3 db test.db
   331    375     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   332    376          t5.a = 1 AND
   333    377          t6.a = 1 AND t6.b = 1
   334    378     }
   335         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          379  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   336    380   do_test analyze2-6.1.5 {
   337    381     execsql { 
   338    382       PRAGMA writable_schema = 1;
   339    383       DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
   340    384     }
   341    385     sqlite3 db test.db
   342    386     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   343    387          t5.a = 1 AND
   344    388          t6.a = 1 AND t6.b = 1
   345    389     }
   346         -} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          390  +} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   347    391   do_test analyze2-6.1.6 {
   348    392     execsql { 
   349    393       PRAGMA writable_schema = 1;
   350    394       INSERT INTO sqlite_master SELECT * FROM master;
   351    395     }
   352    396     sqlite3 db test.db
   353    397     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   354    398          t5.a = 1 AND
   355    399          t6.a = 1 AND t6.b = 1
   356    400     }
   357         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          401  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   358    402   
   359    403   do_test analyze2-6.2.1 {
   360    404     execsql { 
   361    405       DELETE FROM sqlite_stat1;
   362    406       DELETE FROM sqlite_stat2;
   363    407     }
   364    408     sqlite3 db test.db
   365    409     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   366    410           t5.a>1 AND t5.a<15 AND
   367    411           t6.a>1
   368    412     }
   369         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          413  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   370    414   do_test analyze2-6.2.2 {
   371    415     db cache flush
   372    416     execsql ANALYZE
   373    417     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   374    418           t5.a>1 AND t5.a<15 AND
   375    419           t6.a>1
   376    420     }
   377         -} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          421  +} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   378    422   do_test analyze2-6.2.3 {
   379    423     sqlite3 db test.db
   380    424     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   381    425           t5.a>1 AND t5.a<15 AND
   382    426           t6.a>1
   383    427     }
   384         -} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          428  +} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   385    429   do_test analyze2-6.2.4 {
   386    430     execsql { 
   387    431       PRAGMA writable_schema = 1;
   388    432       DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
   389    433     }
   390    434     sqlite3 db test.db
   391    435     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   392    436           t5.a>1 AND t5.a<15 AND
   393    437           t6.a>1
   394    438     }
   395         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          439  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   396    440   do_test analyze2-6.2.5 {
   397    441     execsql { 
   398    442       PRAGMA writable_schema = 1;
   399    443       DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
   400    444     }
   401    445     sqlite3 db test.db
   402    446     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   403    447           t5.a>1 AND t5.a<15 AND
   404    448           t6.a>1
   405    449     }
   406         -} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          450  +} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   407    451   do_test analyze2-6.2.6 {
   408    452     execsql { 
   409    453       PRAGMA writable_schema = 1;
   410    454       INSERT INTO sqlite_master SELECT * FROM master;
   411    455     }
   412    456     sqlite3 db test.db
   413    457     execsql ANALYZE
   414    458     eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   415    459           t5.a>1 AND t5.a<15 AND
   416    460           t6.a>1
   417    461     }
   418         -} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          462  +} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   419    463   
   420    464   #--------------------------------------------------------------------
   421    465   # These tests, analyze2-7.*, test that the sqlite_stat2 functionality
   422    466   # works in shared-cache mode. Note that these tests reuse the database
   423    467   # created for the analyze2-6.* tests.
   424    468   #
   425    469   ifcapable shared_cache {
................................................................................
   455    499     } {20}
   456    500   
   457    501     do_test analyze2-7.5 {
   458    502       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   459    503             t5.a>1 AND t5.a<15 AND
   460    504             t6.a>1
   461    505       } db1
   462         -  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          506  +  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   463    507     do_test analyze2-7.6 {
   464    508       incr_schema_cookie test.db
   465    509       execsql { SELECT * FROM sqlite_master } db2
   466    510       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   467    511             t5.a>1 AND t5.a<15 AND
   468    512             t6.a>1
   469    513       } db2
   470         -  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          514  +  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   471    515     do_test analyze2-7.7 {
   472    516       incr_schema_cookie test.db
   473    517       execsql { SELECT * FROM sqlite_master } db1
   474    518       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   475    519             t5.a>1 AND t5.a<15 AND
   476    520             t6.a>1
   477    521       } db1
   478         -  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          522  +  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   479    523   
   480    524     do_test analyze2-7.8 {
   481    525       execsql { DELETE FROM sqlite_stat2 } db2
   482    526       execsql { SELECT * FROM sqlite_master } db1
   483    527       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   484    528             t5.a>1 AND t5.a<15 AND
   485    529             t6.a>1
   486    530       } db1
   487         -  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          531  +  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   488    532     do_test analyze2-7.9 {
   489    533       execsql { SELECT * FROM sqlite_master } db2
   490    534       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   491    535             t5.a>1 AND t5.a<15 AND
   492    536             t6.a>1
   493    537       } db2
   494         -  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
          538  +  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   495    539   
   496    540     do_test analyze2-7.10 {
   497    541       incr_schema_cookie test.db
   498    542       execsql { SELECT * FROM sqlite_master } db1
   499    543       eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   500    544             t5.a>1 AND t5.a<15 AND
   501    545             t6.a>1
   502    546       } db1
   503         -  } {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
          547  +  } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   504    548   
   505    549     db1 close
   506    550     db2 close
   507    551     sqlite3_enable_shared_cache $::enable_shared_cache
   508    552   }
   509    553   
   510    554   finish_test

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>?) (~250000 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>?) (~25000 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.

   333    333   }
   334    334       
   335    335   proc do_execsql_test {testname sql result} {
   336    336     uplevel do_test $testname [list "execsql {$sql}"] [list $result]
   337    337   }
   338    338   proc do_catchsql_test {testname sql result} {
   339    339     uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
          340  +}
          341  +proc do_eqp_test {name sql res} {
          342  +  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] \
          343  +     [list [string trim $res]]
   340    344   }
   341    345   
   342    346   
   343    347   # Run an SQL script.  
   344    348   # Return the number of microseconds per statement.
   345    349   #
   346    350   proc speed_trial {name numstmt units sql} {