/ Check-in [30904ef8]
Login

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

Overview
Comment:Modifications to test cases to account for new EXPLAIN QUERY PLAN output.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 30904ef8412348464e893e9e1551ef22cad24a3e
User & Date: dan 2010-11-11 10:36:26
Context
2010-11-11
11:43
Fix a bug in the EXPLAIN QUERY PLAN code. check-in: 7ae06895 user: dan tags: experimental
10:36
Modifications to test cases to account for new EXPLAIN QUERY PLAN output. check-in: 30904ef8 user: dan tags: experimental
2010-11-09
17:49
Merge with latest trunk changes. check-in: 4b5c93bc user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/autoindex1.test.

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

Changes to test/e_createtable.test.

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

Changes to test/e_fkey.test.

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

Changes to test/eqp.test.

   183    183     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   184    184     1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   185    185     2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
   186    186     0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
   187    187     0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
   188    188     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   189    189   }
          190  +
          191  +det 3.3.1 {
          192  +  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
          193  +} {
          194  +  0 0 0 {SCAN TABLE t1 (~100000 rows)} 
          195  +  0 0 0 {EXECUTE LIST SUBQUERY 1} 
          196  +  1 0 0 {SCAN TABLE t2 (~1000000 rows)}
          197  +}
          198  +det 3.3.2 {
          199  +  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
          200  +} {
          201  +  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          202  +  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
          203  +  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          204  +}
          205  +det 3.3.3 {
          206  +  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
          207  +} {
          208  +  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
          209  +  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 
          210  +  1 0 0 {SCAN TABLE t2 (~500000 rows)}
          211  +}
   190    212   
   191    213   #-------------------------------------------------------------------------
   192    214   # Test cases eqp-4.* - tests for composite select statements.
   193    215   #
   194    216   do_eqp_test 4.1.1 {
   195    217     SELECT * FROM t1 UNION ALL SELECT * FROM t2
   196    218   } {

Changes to test/fts3matchinfo.test.

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

Changes to test/indexedby.test.

    36     36   #
    37     37   proc EQP {sql} {
    38     38     uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
    39     39   }
    40     40   
    41     41   # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
    42     42   #
    43         -do_test indexedby-1.2 {
    44         -  EQP { select * from t1 WHERE a = 10; }
    45         -} {0 0 {TABLE t1 WITH INDEX i1}}
    46         -do_test indexedby-1.3 {
    47         -  EQP { select * from t1 ; }
    48         -} {0 0 {TABLE t1}}
    49         -do_test indexedby-1.4 {
    50         -  EQP { select * from t1, t2 WHERE c = 10; }
    51         -} {0 1 {TABLE t2 WITH INDEX i3} 1 0 {TABLE t1}}
           43  +do_execsql_test indexedby-1.2 {
           44  +  EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
           45  +} {0 0 0 {SCAN TABLE t1 BY 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 {SCAN TABLE t2 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 BY 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 {SCAN TABLE t3 BY 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 {SCAN TABLE t2 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY INDEX i1 (a>?) (~330000 rows)}}
          158  +do_execsql_test indexedby-5.2 {
          159  +  EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
          160  +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~33000 rows)}}
   150    161   do_test indexedby-5.3 {
   151    162     execsql { DROP INDEX i1 }
   152    163     catchsql { SELECT * FROM v2 }
   153    164   } {1 {no such index: i1}}
   154    165   do_test indexedby-5.4 {
   155    166     # Recreate index i1 in such a way as it cannot be used by the view query.
   156    167     execsql { CREATE INDEX i1 ON t1(b) }
................................................................................
   161    172     # be used by the query.
   162    173     execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
   163    174     catchsql { SELECT * FROM v2 }
   164    175   } {0 {}}
   165    176   
   166    177   # Test that "NOT INDEXED" may use the rowid index, but not others.
   167    178   # 
   168         -do_test indexedby-6.1 {
   169         -  EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid }
   170         -} {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}}
   171         -do_test indexedby-6.2 {
   172         -  EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid }
   173         -} {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}}
          179  +do_execsql_test indexedby-6.1 {
          180  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
          181  +} {0 0 0 {SCAN TABLE t1 BY 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 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY 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 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}}
   213    226   do_test indexedby-8.6 {
   214    227     catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
   215    228   } {1 {cannot use index: i2}}
   216    229   
   217    230   # Test that bug #3560 is fixed.
   218    231   #
   219    232   do_test indexedby-9.1 {

Changes to test/tester.tcl.

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

Changes to test/tkt-78e04e52ea.test.

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

Changes to test/tkt3442.test.

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

Changes to test/where3.test.

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

Changes to test/where7.test.

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

Changes to test/where9.test.

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