AryaWu/sqlite

1# 2013-11-272#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11#12# This file implements tests of the "skip-scan" query strategy.13#14# The test cases in this file are derived from the description of15# the skip-scan query strategy in the "optoverview.html" document.16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21do_execsql_test skipscan2-1.1 {22  CREATE TABLE people(23    name TEXT PRIMARY KEY,24    role TEXT NOT NULL,25    height INT NOT NULL, -- in cm26    CHECK( role IN ('student','teacher') )27  );28  CREATE INDEX people_idx1 ON people(role, height);29} {}30do_execsql_test skipscan2-1.2 {31  INSERT INTO people VALUES('Alice','student',156);32  INSERT INTO people VALUES('Bob','student',161);33  INSERT INTO people VALUES('Cindy','student',155);34  INSERT INTO people VALUES('David','student',181);35  INSERT INTO people VALUES('Emily','teacher',158);36  INSERT INTO people VALUES('Fred','student',163);37  INSERT INTO people VALUES('Ginny','student',169);38  INSERT INTO people VALUES('Harold','student',172);39  INSERT INTO people VALUES('Imma','student',179);40  INSERT INTO people VALUES('Jack','student',181);41  INSERT INTO people VALUES('Karen','student',163);42  INSERT INTO people VALUES('Logan','student',177);43  INSERT INTO people VALUES('Megan','teacher',159);44  INSERT INTO people VALUES('Nathan','student',163);45  INSERT INTO people VALUES('Olivia','student',161);46  INSERT INTO people VALUES('Patrick','teacher',180);47  INSERT INTO people VALUES('Quiana','student',182);48  INSERT INTO people VALUES('Robert','student',159);49  INSERT INTO people VALUES('Sally','student',166);50  INSERT INTO people VALUES('Tom','student',171);51  INSERT INTO people VALUES('Ursula','student',170);52  INSERT INTO people VALUES('Vance','student',179);53  INSERT INTO people VALUES('Willma','student',175);54  INSERT INTO people VALUES('Xavier','teacher',185);55  INSERT INTO people VALUES('Yvonne','student',149);56  INSERT INTO people VALUES('Zach','student',170);57}58 59# Without ANALYZE, a skip-scan is not used60#61do_execsql_test skipscan2-1.3 {62  SELECT name FROM people WHERE height>=180 ORDER BY +name;63} {David Jack Patrick Quiana Xavier}64do_execsql_test skipscan2-1.3eqp {65  EXPLAIN QUERY PLAN66  SELECT name FROM people WHERE height>=180 ORDER BY +name;67} {~/*INDEX people_idx1 */}68 69# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.70#71do_execsql_test skipscan2-1.4 {72  ANALYZE;73  -- We do not have enough people above to actually force the use74  -- of a skip-scan.  So make a manual adjustment to the stat1 table75  -- to make it seem like there are many more.76  UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='people_idx1';77  UPDATE sqlite_stat1 SET stat='10000 1' WHERE idx='sqlite_autoindex_people_1';78  ANALYZE sqlite_master;79}80db cache flush81do_execsql_test skipscan2-1.5 {82  SELECT name FROM people WHERE height>=180 ORDER BY +name;83} {David Jack Patrick Quiana Xavier}84do_execsql_test skipscan2-1.5eqp {85  EXPLAIN QUERY PLAN86  SELECT name FROM people WHERE height>=180 ORDER BY +name;87} {/*INDEX people_idx1 */}88 89# Same answer with other formulations of the same query90#91do_execsql_test skipscan2-1.6 {92  SELECT name FROM people93   WHERE role IN (SELECT DISTINCT role FROM people)94     AND height>=180 ORDER BY +name;95} {David Jack Patrick Quiana Xavier}96do_execsql_test skipscan2-1.7 {97  SELECT name FROM people WHERE role='teacher' AND height>=18098  UNION ALL99  SELECT name FROM people WHERE role='student' AND height>=180100  ORDER BY 1;101} {David Jack Patrick Quiana Xavier}102 103# Add 8 more people, bringing the total to 34.  Then the number of104# duplicates in the left-column of the index will be 17 and 105# skip-scan should not be used after an (unfudged) ANALYZE.106#107do_execsql_test skipscan2-1.8 {108  INSERT INTO people VALUES('Angie','student',166);109  INSERT INTO people VALUES('Brad','student',176);110  INSERT INTO people VALUES('Claire','student',168);111  INSERT INTO people VALUES('Donald','student',162);112  INSERT INTO people VALUES('Elaine','student',177);113  INSERT INTO people VALUES('Frazier','student',159);114  INSERT INTO people VALUES('Grace','student',179);115  INSERT INTO people VALUES('Horace','student',166);116  ANALYZE;117  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';118} {{34 17 2}}119db cache flush120do_execsql_test skipscan2-1.9 {121  SELECT name FROM people WHERE height>=180 ORDER BY +name;122} {David Jack Patrick Quiana Xavier}123do_execsql_test skipscan2-1.9eqp {124  EXPLAIN QUERY PLAN125  SELECT name FROM people WHERE height>=180 ORDER BY +name;126} {~/*INDEX people_idx1 */}127 128# Add 2 more people, bringing the total to 36.  Then the number of129# duplicates in the left-column of the index will be 18 and 130# skip-scan will be used after an (unfudged) ANALYZE.131#132do_execsql_test skipscan2-1.10 {133  INSERT INTO people VALUES('Ingrad','student',155);134  INSERT INTO people VALUES('Jacob','student',179);135  ANALYZE;136  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';137} {{36 18 2}}138db cache flush139do_execsql_test skipscan2-1.11 {140  SELECT name FROM people WHERE height>=180 ORDER BY +name;141} {David Jack Patrick Quiana Xavier}142do_execsql_test skipscan2-1.11eqp {143  EXPLAIN QUERY PLAN144  SELECT name FROM people WHERE height>=180 ORDER BY +name;145} {/*INDEX people_idx1 */}146 147 148# Repeat using a WITHOUT ROWID table.149#150do_execsql_test skipscan2-2.1 {151  CREATE TABLE peoplew(152    name TEXT PRIMARY KEY,153    role TEXT NOT NULL,154    height INT NOT NULL, -- in cm155    CHECK( role IN ('student','teacher') )156  ) WITHOUT ROWID;157  CREATE INDEX peoplew_idx1 ON peoplew(role, height);158  INSERT INTO peoplew(name,role,height)159     SELECT name, role, height FROM  people;160  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;161} {David Jack Patrick Quiana Xavier}162do_execsql_test skipscan2-2.2 {163  SELECT name FROM peoplew164   WHERE role IN (SELECT DISTINCT role FROM peoplew)165     AND height>=180 ORDER BY +name;166} {David Jack Patrick Quiana Xavier}167do_execsql_test skipscan2-2.2 {168  SELECT name FROM peoplew WHERE role='teacher' AND height>=180169  UNION ALL170  SELECT name FROM peoplew WHERE role='student' AND height>=180171  ORDER BY 1;172} {David Jack Patrick Quiana Xavier}173 174# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.175#176do_execsql_test skipscan2-2.4 {177  ANALYZE;178}179db cache flush180do_execsql_test skipscan2-2.5 {181  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;182} {David Jack Patrick Quiana Xavier}183do_execsql_test skipscan2-2.5eqp {184  EXPLAIN QUERY PLAN185  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;186} {/*INDEX peoplew_idx1 */}187 188# A skip-scan on a PK index of a WITHOUT ROWID table.189#190do_execsql_test skipscan2-3.1 {191  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;192}193do_test skipscan2-3.2 {194  for {set i 0} {$i < 1000} {incr i} {195    execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }196  }197  execsql { ANALYZE }198} {}199do_eqp_test skipscan2-3.3eqp {200  SELECT * FROM t3 WHERE b=42;201} {SEARCH t3 USING PRIMARY KEY (ANY(a) AND b=?)}202 203 204 205finish_test206