/ Check-in [ce70803f]
Login

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

Overview
Comment:Additional test cases for skip-scan.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ce70803f5e1bfb4dc495d3a0c2ddd5ee6c3a10fe
User & Date: drh 2013-11-27 13:48:02
Context
2013-11-27
14:50
Fix some harmless compiler warnings in speedtest1.exe. check-in: c75f561f user: drh tags: trunk
13:48
Additional test cases for skip-scan. check-in: ce70803f user: drh tags: trunk
13:24
Make sure the colWidth array is correctly initialized in the ".explain" command of the shell. check-in: ceebcdca user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/skipscan2.test.

    94     94   } {David Jack Patrick Quiana Xavier}
    95     95   do_execsql_test skipscan2-1.7 {
    96     96     SELECT name FROM people WHERE role='teacher' AND height>=180
    97     97     UNION ALL
    98     98     SELECT name FROM people WHERE role='student' AND height>=180
    99     99     ORDER BY 1;
   100    100   } {David Jack Patrick Quiana Xavier}
          101  +
          102  +# Add 8 more people, bringing the total to 34.  Then the number of
          103  +# duplicates in the left-column of the index will be 17 and 
          104  +# skip-scan should not be used after an (unfudged) ANALYZE.
          105  +#
          106  +do_execsql_test skipscan2-1.8 {
          107  +  INSERT INTO people VALUES('Angie','student',166);
          108  +  INSERT INTO people VALUES('Brad','student',176);
          109  +  INSERT INTO people VALUES('Claire','student',168);
          110  +  INSERT INTO people VALUES('Donald','student',162);
          111  +  INSERT INTO people VALUES('Elaine','student',177);
          112  +  INSERT INTO people VALUES('Frazier','student',159);
          113  +  INSERT INTO people VALUES('Grace','student',179);
          114  +  INSERT INTO people VALUES('Horace','student',166);
          115  +  ANALYZE;
          116  +  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
          117  +} {{34 17 2}}
          118  +db cache flush
          119  +do_execsql_test skipscan2-1.9 {
          120  +  SELECT name FROM people WHERE height>=180 ORDER BY +name;
          121  +} {David Jack Patrick Quiana Xavier}
          122  +do_execsql_test skipscan2-1.9eqp {
          123  +  EXPLAIN QUERY PLAN
          124  +  SELECT name FROM people WHERE height>=180 ORDER BY +name;
          125  +} {~/*INDEX people_idx1 */}
          126  +
          127  +# Add 2 more people, bringing the total to 36.  Then the number of
          128  +# duplicates in the left-column of the index will be 18 and 
          129  +# skip-scan will be used after an (unfudged) ANALYZE.
          130  +#
          131  +do_execsql_test skipscan2-1.10 {
          132  +  INSERT INTO people VALUES('Ingrad','student',155);
          133  +  INSERT INTO people VALUES('Jacob','student',179);
          134  +  ANALYZE;
          135  +  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
          136  +} {{36 18 2}}
          137  +db cache flush
          138  +do_execsql_test skipscan2-1.11 {
          139  +  SELECT name FROM people WHERE height>=180 ORDER BY +name;
          140  +} {David Jack Patrick Quiana Xavier}
          141  +do_execsql_test skipscan2-1.11eqp {
          142  +  EXPLAIN QUERY PLAN
          143  +  SELECT name FROM people WHERE height>=180 ORDER BY +name;
          144  +} {/*INDEX people_idx1 */}
          145  +
   101    146   
   102    147   # Repeat using a WITHOUT ROWID table.
   103    148   #
   104    149   do_execsql_test skipscan2-2.1 {
   105    150     CREATE TABLE peoplew(
   106    151       name TEXT PRIMARY KEY,
   107    152       role TEXT NOT NULL,
   108    153       height INT NOT NULL, -- in cm
   109    154       CHECK( role IN ('student','teacher') )
   110    155     ) WITHOUT ROWID;
   111    156     CREATE INDEX peoplew_idx1 ON peoplew(role, height);
   112    157     INSERT INTO peoplew(name,role,height)
   113    158        SELECT name, role, height FROM  people;
   114         -  DROP TABLE people;
          159  +  ALTER TABLE people RENAME TO old_people;
   115    160     SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
   116    161   } {David Jack Patrick Quiana Xavier}
   117    162   do_execsql_test skipscan2-2.2 {
   118    163     SELECT name FROM peoplew
   119    164      WHERE role IN (SELECT DISTINCT role FROM peoplew)
   120    165        AND height>=180 ORDER BY +name;
   121    166   } {David Jack Patrick Quiana Xavier}
................................................................................
   126    171     ORDER BY 1;
   127    172   } {David Jack Patrick Quiana Xavier}
   128    173   
   129    174   # Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
   130    175   #
   131    176   do_execsql_test skipscan2-2.4 {
   132    177     ANALYZE;
   133         -  -- We do not have enough people above to actually force the use
   134         -  -- of a skip-scan.  So make a manual adjustment to the stat1 table
   135         -  -- to make it seem like there are many more.
   136         -  UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='peoplew_idx1';
   137         -  ANALYZE sqlite_master;
   138    178   }
   139    179   db cache flush
   140    180   do_execsql_test skipscan2-2.5 {
   141    181     SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
   142    182   } {David Jack Patrick Quiana Xavier}
   143    183   do_execsql_test skipscan2-2.5eqp {
   144    184     EXPLAIN QUERY PLAN
   145    185     SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
   146    186   } {/*INDEX peoplew_idx1 */}
   147    187   
   148    188   
   149    189   
   150    190   finish_test