Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add additional test cases for skip-scan. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1ae4915d4d08ee5ce526c04d1d0cda10 |
User & Date: | drh 2013-11-27 01:23:53.318 |
Context
2013-11-27
| ||
04:00 | Avoid using the GetVersionEx functions if they are considered deprecated. (check-in: afdca29966 user: mistachkin tags: trunk) | |
03:01 | Avoid using the GetVersionEx functions if they are considered deprecated. (Closed-Leaf check-in: 0ea9e4722b user: mistachkin tags: vs2013) | |
01:23 | Add additional test cases for skip-scan. (check-in: 1ae4915d4d user: drh tags: trunk) | |
00:45 | Add additional test cases and requirements evidence marks for WITHOUT ROWID. (check-in: b408d78810 user: drh tags: trunk) | |
Changes
Added test/skipscan2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | # 2013-11-27 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements tests of the "skip-scan" query strategy. # # The test cases in this file are derived from the description of # the skip-scan query strategy in the "optoverview.html" document. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test skipscan2-1.1 { CREATE TABLE people( name TEXT PRIMARY KEY, role TEXT NOT NULL, height INT NOT NULL, -- in cm CHECK( role IN ('student','teacher') ) ); CREATE INDEX people_idx1 ON people(role, height); } {} do_execsql_test skipscan2-1.2 { INSERT INTO people VALUES('Alice','student',156); INSERT INTO people VALUES('Bob','student',161); INSERT INTO people VALUES('Cindy','student',155); INSERT INTO people VALUES('David','student',181); INSERT INTO people VALUES('Emily','teacher',158); INSERT INTO people VALUES('Fred','student',163); INSERT INTO people VALUES('Ginny','student',169); INSERT INTO people VALUES('Harold','student',172); INSERT INTO people VALUES('Imma','student',179); INSERT INTO people VALUES('Jack','student',181); INSERT INTO people VALUES('Karen','student',163); INSERT INTO people VALUES('Logan','student',177); INSERT INTO people VALUES('Megan','teacher',159); INSERT INTO people VALUES('Nathan','student',163); INSERT INTO people VALUES('Olivia','student',161); INSERT INTO people VALUES('Patrick','teacher',180); INSERT INTO people VALUES('Quiana','student',182); INSERT INTO people VALUES('Robert','student',159); INSERT INTO people VALUES('Sally','student',166); INSERT INTO people VALUES('Tom','student',171); INSERT INTO people VALUES('Ursula','student',170); INSERT INTO people VALUES('Vance','student',179); INSERT INTO people VALUES('Willma','student',175); INSERT INTO people VALUES('Xavier','teacher',185); INSERT INTO people VALUES('Yvonne','student',149); INSERT INTO people VALUES('Zach','student',170); } # Without ANALYZE, a skip-scan is not used # do_execsql_test skipscan2-1.3 { SELECT name FROM people WHERE height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-1.3eqp { EXPLAIN QUERY PLAN SELECT name FROM people WHERE height>=180 ORDER BY +name; } {~/*INDEX people_idx1 */} # Now do an ANALYZE. A skip-scan can be used after ANALYZE. # do_execsql_test skipscan2-1.4 { ANALYZE; -- We do not have enough people above to actually force the use -- of a skip-scan. So make a manual adjustment to the stat1 table -- to make it seem like there are many more. UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='people_idx1'; ANALYZE sqlite_master; } db cache flush do_execsql_test skipscan2-1.5 { SELECT name FROM people WHERE height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-1.5eqp { EXPLAIN QUERY PLAN SELECT name FROM people WHERE height>=180 ORDER BY +name; } {/*INDEX people_idx1 */} # Same answer with other formulations of the same query # do_execsql_test skipscan2-1.6 { SELECT name FROM people WHERE role IN (SELECT DISTINCT role FROM people) AND height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-1.7 { SELECT name FROM people WHERE role='teacher' AND height>=180 UNION ALL SELECT name FROM people WHERE role='student' AND height>=180 ORDER BY 1; } {David Jack Patrick Quiana Xavier} # Repeat using a WITHOUT ROWID table. # do_execsql_test skipscan2-2.1 { CREATE TABLE peoplew( name TEXT PRIMARY KEY, role TEXT NOT NULL, height INT NOT NULL, -- in cm CHECK( role IN ('student','teacher') ) ) WITHOUT ROWID; CREATE INDEX peoplew_idx1 ON peoplew(role, height); INSERT INTO peoplew(name,role,height) SELECT name, role, height FROM people; DROP TABLE people; SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-2.2 { SELECT name FROM peoplew WHERE role IN (SELECT DISTINCT role FROM peoplew) AND height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-2.2 { SELECT name FROM peoplew WHERE role='teacher' AND height>=180 UNION ALL SELECT name FROM peoplew WHERE role='student' AND height>=180 ORDER BY 1; } {David Jack Patrick Quiana Xavier} # Now do an ANALYZE. A skip-scan can be used after ANALYZE. # do_execsql_test skipscan2-2.4 { ANALYZE; -- We do not have enough people above to actually force the use -- of a skip-scan. So make a manual adjustment to the stat1 table -- to make it seem like there are many more. UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='peoplew_idx1'; ANALYZE sqlite_master; } db cache flush do_execsql_test skipscan2-2.5 { SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; } {David Jack Patrick Quiana Xavier} do_execsql_test skipscan2-2.5eqp { EXPLAIN QUERY PLAN SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; } {/*INDEX peoplew_idx1 */} finish_test |