/ Check-in [6611b76b]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 6611b76b0296875fb9903b25dfaa783a9c12eaa1
User & Date: dan 2010-11-13 16:42:27
Context
2010-11-15
14:44
Merge the EXPLAIN QUERY PLAN changes from experimental into trunk. check-in: ce27bf38 user: drh 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
2010-11-12
17:41
Add EXPLAIN QUERY PLAN test cases to check that the examples in the documentation work. check-in: 85fdad85 user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3217   3217       u32 flags = pLevel->plan.wsFlags;
  3218   3218       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  3219   3219       Vdbe *v = pParse->pVdbe;      /* VM being constructed */
  3220   3220       sqlite3 *db = pParse->db;     /* Database handle */
  3221   3221       char *zMsg;                   /* Text to add to EQP output */
  3222   3222       sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
  3223   3223       int iId = pParse->iSelectId;  /* Select id (left-most output column) */
         3224  +    int isSearch;                 /* True for a SEARCH. False for SCAN. */
  3224   3225   
  3225   3226       if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;
  3226   3227   
         3228  +    isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT));
         3229  +
         3230  +    zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN");
  3227   3231       if( pItem->pSelect ){
  3228         -      zMsg = sqlite3MPrintf(db, "SCAN SUBQUERY %d", pItem->iSelectId);
         3232  +      zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId);
  3229   3233       }else{
  3230         -      zMsg = sqlite3MPrintf(db, "SCAN TABLE %s", pItem->zName);
         3234  +      zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
  3231   3235       }
  3232   3236   
  3233   3237       if( pItem->zAlias ){
  3234   3238         zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
  3235   3239       }
  3236   3240       if( (flags & WHERE_INDEXED)!=0 ){
  3237   3241         char *zWhere = explainIndexRange(db, pLevel, pItem->pTab);
  3238         -      zMsg = sqlite3MAppendf(db, zMsg, "%s BY %s%sINDEX%s%s%s", zMsg, 
         3242  +      zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 
  3239   3243             ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
  3240   3244             ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
  3241   3245             ((flags & WHERE_TEMP_INDEX)?"":" "),
  3242   3246             ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
  3243   3247             zWhere
  3244   3248         );
  3245   3249         sqlite3DbFree(db, zWhere);
  3246   3250       }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
  3247         -      zMsg = sqlite3MAppendf(db, zMsg, "%s BY INTEGER PRIMARY KEY", zMsg);
         3251  +      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
  3248   3252   
  3249   3253         if( flags&WHERE_ROWID_EQ ){
  3250   3254           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
  3251   3255         }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
  3252   3256           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
  3253   3257         }else if( flags&WHERE_BTM_LIMIT ){
  3254   3258           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);

Changes to test/autoindex1.test.

   143    143   do_execsql_test autoindex1-500 {
   144    144     CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
   145    145     CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
   146    146     EXPLAIN QUERY PLAN
   147    147     SELECT b FROM t501
   148    148      WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
   149    149   } {
   150         -  0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
          150  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
   151    151     0 0 0 {EXECUTE LIST SUBQUERY 1} 
   152    152     1 0 0 {SCAN TABLE t502 (~100000 rows)}
   153    153   }
   154    154   do_execsql_test autoindex1-501 {
   155    155     EXPLAIN QUERY PLAN
   156    156     SELECT b FROM t501
   157    157      WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   158    158   } {
   159    159     0 0 0 {SCAN TABLE t501 (~500000 rows)} 
   160    160     0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
   161         -  1 0 0 {SCAN TABLE t502 BY AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
          161  +  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
   162    162   }
   163    163   do_execsql_test autoindex1-502 {
   164    164     EXPLAIN QUERY PLAN
   165    165     SELECT b FROM t501
   166    166      WHERE t501.a=123
   167    167        AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
   168    168   } {
   169         -  0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          169  +  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   170    170     0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
   171    171     1 0 0 {SCAN TABLE t502 (~100000 rows)}
   172    172   }
   173    173   
   174    174   
   175    175   # The following code checks a performance regression reported on the
   176    176   # mailing list on 2010-10-19.  The problem is that the nRowEst field
................................................................................
   237    237              AND later.owner_change_date > prev.owner_change_date
   238    238              AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
   239    239          ) y ON x.sheep_no = y.sheep_no
   240    240      WHERE y.sheep_no IS NULL
   241    241      ORDER BY x.registering_flock;
   242    242   } {
   243    243     1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
   244         -  1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 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    245     1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
   246         -  2 0 0 {SCAN TABLE flock_owner AS later BY 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 BY INDEX sheep_reg_flock_index (~1000000 rows)} 
   248         -  0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)}
          246  +  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~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    249   }
   250    250   
   251    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 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
         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 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
         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 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}}
         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.

  1095   1095   do_test e_fkey-27.2 {
  1096   1096     eqp { INSERT INTO artist VALUES(?, ?) }
  1097   1097   } {}
  1098   1098   do_execsql_test e_fkey-27.3 {
  1099   1099     EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
  1100   1100   } {
  1101   1101     0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  1102         -  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 
  1103         -  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 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   1104   }
  1105   1105   do_execsql_test e_fkey-27.4 {
  1106   1106     EXPLAIN QUERY PLAN DELETE FROM artist
  1107   1107   } {
  1108   1108     0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  1109         -  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)}
         1109  +  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)}
  1110   1110   }
  1111   1111   
  1112   1112   
  1113   1113   ###########################################################################
  1114   1114   ### SECTION 4.1: Composite Foreign Key Constraints
  1115   1115   ###########################################################################
  1116   1116   

Changes to test/eqp.test.

    35     35     CREATE TABLE t2(a, b);
    36     36     CREATE TABLE t3(a, b);
    37     37   }
    38     38   
    39     39   do_eqp_test 1.2 {
    40     40     SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
    41     41   } {
    42         -  0 0 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
    43         -  0 0 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 
           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     44     0 1 0 {SCAN TABLE t2 (~1000000 rows)}
    45     45   }
    46     46   do_eqp_test 1.3 {
    47     47     SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
    48     48   } {
    49     49     0 0 0 {SCAN TABLE t2 (~1000000 rows)}
    50         -  0 1 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
    51         -  0 1 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 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     52   }
    53     53   do_eqp_test 1.3 {
    54     54     SELECT a FROM t1 ORDER BY a
    55     55   } {
    56         -  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (~1000000 rows)}
           56  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
    57     57   }
    58     58   do_eqp_test 1.4 {
    59     59     SELECT a FROM t1 ORDER BY +a
    60     60   } {
    61     61     0 0 0 {SCAN TABLE t1 (~1000000 rows)}
    62     62     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    63     63   }
    64     64   do_eqp_test 1.5 {
    65     65     SELECT a FROM t1 WHERE a=4
    66     66   } {
    67         -  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}
           67  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
    68     68   }
    69     69   do_eqp_test 1.6 {
    70     70     SELECT DISTINCT count(*) FROM t3 GROUP BY a;
    71     71   } {
    72     72     0 0 0 {SCAN TABLE t3 (~1000000 rows)}
    73     73     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    74     74     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
................................................................................
    88     88   det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
    89     89     0 0 0 {SCAN TABLE t1 (~1000000 rows)}
    90     90     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    91     91     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
    92     92     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    93     93   }
    94     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 BY COVERING INDEX t2i1 (~1000000 rows)}
           95  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
    96     96     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
    97     97     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    98     98   }
    99     99   det 2.2.3 "SELECT DISTINCT * FROM t1" {
   100    100     0 0 0 {SCAN TABLE t1 (~1000000 rows)}
   101    101     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   102    102   }
................................................................................
   108    108   det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
   109    109     0 0 0 {SCAN TABLE t1 (~1000000 rows)}
   110    110     0 1 1 {SCAN TABLE t2 (~1000000 rows)}
   111    111     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   112    112     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   113    113   }
   114    114   det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
   115         -  0 0 1 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
          115  +  0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
   116    116     0 1 0 {SCAN TABLE t1 (~1000000 rows)}
   117    117   }
   118    118   
   119    119   det 2.3.1 "SELECT max(x) FROM t2" {
   120         -  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
          120  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
   121    121   }
   122    122   det 2.3.2 "SELECT min(x) FROM t2" {
   123         -  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
          123  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
   124    124   }
   125    125   det 2.3.3 "SELECT min(x), max(x) FROM t2" {
   126    126     0 0 0 {SCAN TABLE t2 (~1000000 rows)}
   127    127   }
   128    128   
   129    129   det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
   130         -  0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          130  +  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   131    131   }
   132    132   
   133    133   
   134    134   
   135    135   #-------------------------------------------------------------------------
   136    136   # Test cases eqp-3.* - tests for select statements that use sub-selects.
   137    137   #
................................................................................
   158    158     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   159    159   }
   160    160   do_eqp_test 3.1.4 {
   161    161     SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
   162    162   } {
   163    163     0 0 0 {SCAN TABLE t1 (~1000000 rows)}
   164    164     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   165         -  1 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
          165  +  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
   166    166   }
   167    167   
   168    168   det 3.2.1 {
   169    169     SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
   170    170   } {
   171    171     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   172    172     1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
................................................................................
   177    177     SELECT * FROM 
   178    178       (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
   179    179       (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
   180    180     ORDER BY x2.y LIMIT 5
   181    181   } {
   182    182     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   183    183     1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   184         -  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
          184  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
   185    185     0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
   186    186     0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
   187    187     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   188    188   }
   189    189   
   190    190   det 3.3.1 {
   191    191     SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
................................................................................
   257    257   }
   258    258   
   259    259   do_eqp_test 4.2.2 {
   260    260     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
   261    261   } {
   262    262     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   263    263     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   264         -  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
          264  +  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
   265    265     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   266    266   }
   267    267   do_eqp_test 4.2.3 {
   268    268     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
   269    269   } {
   270    270     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   271    271     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
................................................................................
   310    310     0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
   311    311   }
   312    312   do_eqp_test 4.3.3 {
   313    313     SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
   314    314   } {
   315    315     2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   316    316     2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   317         -  3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 
          317  +  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
   318    318     1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
   319    319     4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   320    320     4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   321    321     0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
   322    322   }
   323    323   
   324    324   #-------------------------------------------------------------------------
................................................................................
   325    325   # This next block of tests verifies that the examples on the 
   326    326   # lang_explain.html page are correct.
   327    327   #
   328    328   drop_all_tables
   329    329   
   330    330   # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
   331    331   # FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
   332         -#
   333    332   do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
   334    333   det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
   335    334     0 0 0 {SCAN TABLE t1 (~100000 rows)}
   336    335   }
   337    336   
   338         -# EVIDENCE-OF: R-03114-52867 sqlite> CREATE INDEX i1 ON t1(a);
          337  +# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
   339    338   # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
   340         -# 0|0|0|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)
          339  +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
   341    340   do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
   342    341   det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
   343         -  0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}
          342  +  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
   344    343   }
   345    344   
   346         -# EVIDENCE-OF: R-20407-61322 sqlite> CREATE INDEX i2 ON t1(a, b);
          345  +# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
   347    346   # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
   348         -# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
          347  +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
   349    348   do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
   350    349   det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
   351         -  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
          350  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
   352    351   }
   353    352   
   354         -# EVIDENCE-OF: R-01893-00096 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
   355         -# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SCAN TABLE t1 BY
   356         -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
          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
   357    356   # (~1000000 rows)
   358    357   do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
   359    358   det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
   360         -  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
          359  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
   361    360     0 1 1 {SCAN TABLE t2 (~1000000 rows)}
   362    361   }
   363    362   
   364         -# EVIDENCE-OF: R-26531-36629 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
   365         -# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SCAN TABLE t1 BY
   366         -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
          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
   367    366   # (~1000000 rows)
   368    367   det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
   369         -  0 0 1 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
          368  +  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
   370    369     0 1 0 {SCAN TABLE t2 (~1000000 rows)}
   371    370   }
   372    371   
   373         -# EVIDENCE-OF: R-17671-37431 sqlite> CREATE INDEX i3 ON t1(b);
          372  +# EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
   374    373   # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
   375         -# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 0|0|0|SCAN
   376         -# TABLE t1 BY INDEX i3 (b=?) (~10 rows)
          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)
   377    376   do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
   378    377   det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
   379         -  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
   380         -  0 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)}
          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)}
   381    380   }
   382    381   
   383    382   # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
   384    383   # FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
   385    384   # B-TREE FOR ORDER BY
   386    385   det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
   387    386     0 0 0 {SCAN TABLE t2 (~1000000 rows)}
   388    387     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   389    388   }
   390    389   
   391         -# EVIDENCE-OF: R-08354-12138 sqlite> CREATE INDEX i4 ON t2(c);
          390  +# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
   392    391   # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
   393         -# 0|0|0|SCAN TABLE t2 BY INDEX i4 (~1000000 rows)
          392  +# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
   394    393   do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
   395    394   det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
   396         -  0 0 0 {SCAN TABLE t2 BY INDEX i4 (~1000000 rows)}
          395  +  0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
   397    396   }
   398    397   
   399         -# EVIDENCE-OF: R-01895-58356 sqlite> EXPLAIN QUERY PLAN SELECT
          398  +# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
   400    399   # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
   401    400   # 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
   402         -# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
   403         -# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SCAN TABLE t1 BY
          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
   404    403   # INDEX i3 (b=?) (~10 rows)
   405    404   det 5.9 {
   406    405     SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
   407    406   } {
   408    407     0 0 0 {SCAN TABLE t2 (~1000000 rows)}
   409    408     0 0 0 {EXECUTE SCALAR SUBQUERY 1}
   410         -  1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
          409  +  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
   411    410     0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
   412         -  2 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)}
          411  +  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
   413    412   }
   414    413   
   415         -# EVIDENCE-OF: R-43933-45972 sqlite> EXPLAIN QUERY PLAN SELECT
          414  +# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
   416    415   # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
   417         -# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
          416  +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
   418    417   # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
   419    418   det 5.10 {
   420    419     SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
   421    420   } {
   422         -  1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)}
          421  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
   423    422     0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)}
   424    423     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
   425    424   }
   426    425   
   427         -# EVIDENCE-OF: R-15989-23611 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
   428         -# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SCAN TABLE t2 BY INDEX i4
          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
   429    428   # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
   430    429   det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
   431         -  0 0 0 {SCAN TABLE t2 BY INDEX i4 (c=?) (~10 rows)}
          430  +  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
   432    431     0 1 1 {SCAN TABLE t1 (~1000000 rows)}
   433    432   }
   434    433   
   435    434   # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
   436    435   # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
   437    436   # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
   438    437   # USING TEMP B-TREE (UNION)
   439    438   det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
   440    439     1 0 0 {SCAN TABLE t1 (~1000000 rows)}
   441    440     2 0 0 {SCAN TABLE t2 (~1000000 rows)}
   442    441     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
   443    442   }
   444    443   
   445         -# EVIDENCE-OF: R-34523-61710 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
   446         -# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 BY COVERING
   447         -# INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) 2|0|0|USE
   448         -# TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
          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    449   det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
   450         -  1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)}
          450  +  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
   451    451     2 0 0 {SCAN TABLE t2 (~1000000 rows)}
   452    452     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   453    453     0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
   454    454   }
   455    455   
   456    456   
   457    457   
   458    458   
   459    459   
   460    460   finish_test
   461    461   

Changes to test/indexedby.test.

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

Changes to test/tkt-78e04e52ea.test.

    53     53   } {t2}
    54     54   
    55     55   do_test tkt-78e04-2.1 {
    56     56     execsql {
    57     57       CREATE INDEX "" ON t2(x);
    58     58       EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5;
    59     59     }
    60         -} {0 0 0 {SCAN TABLE t2 BY COVERING INDEX  (x=?) (~10 rows)}}
           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     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 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
           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 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
           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 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
           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.

   222    222     CREATE INDEX t301c ON t301(c);
   223    223     INSERT INTO t301 VALUES(1,2,3);
   224    224     CREATE TABLE t302(x, y);
   225    225     ANALYZE;
   226    226     explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
   227    227   } {
   228    228     0 0 0 {SCAN TABLE t302 (~0 rows)} 
   229         -  0 1 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          229  +  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   230    230   }
   231    231   do_execsql_test where3-3.1 {
   232    232     explain query plan
   233    233     SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
   234    234   } {
   235    235     0 0 1 {SCAN TABLE t302 (~0 rows)} 
   236         -  0 1 0 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          236  +  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   237    237   }
   238    238   
   239    239   # Verify that when there are multiple tables in a join which must be
   240    240   # full table scans that the query planner attempts put the table with
   241    241   # the fewest number of output rows as the outer loop.
   242    242   #
   243    243   do_execsql_test where3-4.0 {
................................................................................
   293    293      SELECT bbb.title AS tag_title 
   294    294        FROM aaa JOIN bbb ON bbb.id = aaa.parent  
   295    295       WHERE aaa.fk = 'constant'
   296    296         AND LENGTH(bbb.title) > 0
   297    297         AND bbb.parent = 4
   298    298       ORDER BY bbb.title COLLATE NOCASE ASC;
   299    299   } {
   300         -  0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
   301         -  0 1 1 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          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    302     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   303    303   }
   304    304   do_execsql_test where3-5.1 {
   305    305     EXPLAIN QUERY PLAN
   306    306      SELECT bbb.title AS tag_title 
   307    307        FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
   308    308       WHERE aaa.fk = 'constant'
   309    309         AND LENGTH(bbb.title) > 0
   310    310         AND bbb.parent = 4
   311    311       ORDER BY bbb.title COLLATE NOCASE ASC;
   312    312   } {
   313         -  0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
   314         -  0 1 1 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          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    315     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   316    316   }
   317    317   do_execsql_test where3-5.2 {
   318    318     EXPLAIN QUERY PLAN
   319    319      SELECT bbb.title AS tag_title 
   320    320        FROM bbb JOIN aaa ON bbb.id = aaa.parent  
   321    321       WHERE aaa.fk = 'constant'
   322    322         AND LENGTH(bbb.title) > 0
   323    323         AND bbb.parent = 4
   324    324       ORDER BY bbb.title COLLATE NOCASE ASC;
   325    325   } {
   326         -  0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
   327         -  0 1 0 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          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    328     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   329    329   }
   330    330   do_execsql_test where3-5.3 {
   331    331     EXPLAIN QUERY PLAN
   332    332      SELECT bbb.title AS tag_title 
   333    333        FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
   334    334       WHERE aaa.fk = 'constant'
   335    335         AND LENGTH(bbb.title) > 0
   336    336         AND bbb.parent = 4
   337    337       ORDER BY bbb.title COLLATE NOCASE ASC;
   338    338   } {
   339         -  0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
   340         -  0 1 0 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
          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    341     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   342    342   }
   343    343   
   344    344   
   345    345   finish_test

Changes to test/where7.test.

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

Changes to test/where9.test.

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