1# 2013-11-27 2# 3# The author disclaims copyright to this source code. In place of 4# 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 of 15# the skip-scan query strategy in the "optoverview.html" document. 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 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 cm 26 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 used 60# 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 PLAN 66 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 use 74 -- of a skip-scan. So make a manual adjustment to the stat1 table 75 -- 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 flush 81do_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 PLAN 86 SELECT name FROM people WHERE height>=180 ORDER BY +name; 87} {/*INDEX people_idx1 */} 88 89# Same answer with other formulations of the same query 90# 91do_execsql_test skipscan2-1.6 { 92 SELECT name FROM people 93 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>=180 98 UNION ALL 99 SELECT name FROM people WHERE role='student' AND height>=180 100 ORDER BY 1; 101} {David Jack Patrick Quiana Xavier} 102 103# Add 8 more people, bringing the total to 34. Then the number of 104# 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 flush 120do_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 PLAN 125 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 of 129# 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 flush 139do_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 PLAN 144 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 cm 155 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 ALTER TABLE people RENAME TO old_people; 161 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 162} {David Jack Patrick Quiana Xavier} 163do_execsql_test skipscan2-2.2 { 164 SELECT name FROM peoplew 165 WHERE role IN (SELECT DISTINCT role FROM peoplew) 166 AND height>=180 ORDER BY +name; 167} {David Jack Patrick Quiana Xavier} 168do_execsql_test skipscan2-2.2 { 169 SELECT name FROM peoplew WHERE role='teacher' AND height>=180 170 UNION ALL 171 SELECT name FROM peoplew WHERE role='student' AND height>=180 172 ORDER BY 1; 173} {David Jack Patrick Quiana Xavier} 174 175# Now do an ANALYZE. A skip-scan can be used after ANALYZE. 176# 177do_execsql_test skipscan2-2.4 { 178 ANALYZE; 179} 180db cache flush 181do_execsql_test skipscan2-2.5 { 182 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 183} {David Jack Patrick Quiana Xavier} 184do_execsql_test skipscan2-2.5eqp { 185 EXPLAIN QUERY PLAN 186 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 187} {/*INDEX peoplew_idx1 */} 188 189# A skip-scan on a PK index of a WITHOUT ROWID table. 190# 191do_execsql_test skipscan2-3.1 { 192 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID; 193} 194do_test skipscan2-3.2 { 195 for {set i 0} {$i < 1000} {incr i} { 196 execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') } 197 } 198 execsql { ANALYZE } 199} {} 200do_eqp_test skipscan2-3.3eqp { 201 SELECT * FROM t3 WHERE b=42; 202} {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}} 203 204 205finish_test 206