SQLite

Check-in [1ae4915d4d]
Login

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: 1ae4915d4d08ee5ce526c04d1d0cda1078641793
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
Unified Diff Ignore Whitespace Patch
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