wasCSharpSQLite – Blame information for rev
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 April 07 |
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 | # This file implements regression tests for SQLite library. The |
||
12 | # focus of this script is testing automatic index creation logic. |
||
13 | # |
||
14 | |||
15 | set testdir [file dirname $argv0] |
||
16 | source $testdir/tester.tcl |
||
17 | |||
18 | # If the library is not compiled with automatic index support then |
||
19 | # skip all tests in this file. |
||
20 | # |
||
21 | ifcapable {!autoindex} { |
||
22 | finish_test |
||
23 | return |
||
24 | } |
||
25 | |||
26 | # With automatic index turned off, we do a full scan of the T2 table |
||
27 | do_test autoindex1-100 { |
||
28 | db eval { |
||
29 | CREATE TABLE t1(a,b); |
||
30 | INSERT INTO t1 VALUES(1,11); |
||
31 | INSERT INTO t1 VALUES(2,22); |
||
32 | INSERT INTO t1 SELECT a+2, b+22 FROM t1; |
||
33 | INSERT INTO t1 SELECT a+4, b+44 FROM t1; |
||
34 | CREATE TABLE t2(c,d); |
||
35 | INSERT INTO t2 SELECT a, 900+b FROM t1; |
||
36 | } |
||
37 | db eval { |
||
38 | PRAGMA automatic_index=OFF; |
||
39 | SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; |
||
40 | } |
||
41 | } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
||
42 | do_test autoindex1-101 { |
||
43 | db status step |
||
44 | } {63} |
||
45 | do_test autoindex1-102 { |
||
46 | db status autoindex |
||
47 | } {0} |
||
48 | |||
49 | # With autoindex turned on, we build an index once and then use that index |
||
50 | # to find T2 values. |
||
51 | do_test autoindex1-110 { |
||
52 | db eval { |
||
53 | PRAGMA automatic_index=ON; |
||
54 | SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; |
||
55 | } |
||
56 | } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
||
57 | do_test autoindex1-111 { |
||
58 | db status step |
||
59 | } {7} |
||
60 | do_test autoindex1-112 { |
||
61 | db status autoindex |
||
62 | } {7} |
||
63 | |||
64 | # The same test as above, but this time the T2 query is a subquery rather |
||
65 | # than a join. |
||
66 | do_test autoindex1-200 { |
||
67 | db eval { |
||
68 | PRAGMA automatic_index=OFF; |
||
69 | SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; |
||
70 | } |
||
71 | } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
||
72 | do_test autoindex1-201 { |
||
73 | db status step |
||
74 | } {35} |
||
75 | do_test autoindex1-202 { |
||
76 | db status autoindex |
||
77 | } {0} |
||
78 | do_test autoindex1-210 { |
||
79 | db eval { |
||
80 | PRAGMA automatic_index=ON; |
||
81 | SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; |
||
82 | } |
||
83 | } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
||
84 | do_test autoindex1-211 { |
||
85 | db status step |
||
86 | } {7} |
||
87 | do_test autoindex1-212 { |
||
88 | db status autoindex |
||
89 | } {7} |
||
90 | |||
91 | |||
92 | # Modify the second table of the join while the join is in progress |
||
93 | # |
||
94 | do_test autoindex1-300 { |
||
95 | set r {} |
||
96 | db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} { |
||
97 | lappend r $b $d |
||
98 | db eval {UPDATE t2 SET d=d+1} |
||
99 | } |
||
100 | set r |
||
101 | } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} |
||
102 | do_test autoindex1-310 { |
||
103 | db eval {SELECT d FROM t2 ORDER BY d} |
||
104 | } {919 930 941 952 963 974 985 996} |
||
105 | |||
106 | # The next test does a 10-way join on unindexed tables. Without |
||
107 | # automatic indices, the join will take a long time to complete. |
||
108 | # With automatic indices, it should only take about a second. |
||
109 | # |
||
110 | do_test autoindex1-400 { |
||
111 | db eval { |
||
112 | CREATE TABLE t4(a, b); |
||
113 | INSERT INTO t4 VALUES(1,2); |
||
114 | INSERT INTO t4 VALUES(2,3); |
||
115 | } |
||
116 | for {set n 2} {$n<4096} {set n [expr {$n+$n}]} { |
||
117 | db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4} |
||
118 | } |
||
119 | db eval { |
||
120 | SELECT count(*) FROM t4; |
||
121 | } |
||
122 | } {4096} |
||
123 | do_test autoindex1-401 { |
||
124 | db eval { |
||
125 | SELECT count(*) |
||
126 | FROM t4 AS x1 |
||
127 | JOIN t4 AS x2 ON x2.a=x1.b |
||
128 | JOIN t4 AS x3 ON x3.a=x2.b |
||
129 | JOIN t4 AS x4 ON x4.a=x3.b |
||
130 | JOIN t4 AS x5 ON x5.a=x4.b |
||
131 | JOIN t4 AS x6 ON x6.a=x5.b |
||
132 | JOIN t4 AS x7 ON x7.a=x6.b |
||
133 | JOIN t4 AS x8 ON x8.a=x7.b |
||
134 | JOIN t4 AS x9 ON x9.a=x8.b |
||
135 | JOIN t4 AS x10 ON x10.a=x9.b; |
||
136 | } |
||
137 | } {4087} |
||
138 | |||
139 | # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 |
||
140 | # Make sure automatic indices are not created for the RHS of an IN expression |
||
141 | # that is not a correlated subquery. |
||
142 | # |
||
143 | do_execsql_test autoindex1-500 { |
||
144 | CREATE TABLE t501(a INTEGER PRIMARY KEY, b); |
||
145 | CREATE TABLE t502(x INTEGER PRIMARY KEY, y); |
||
146 | EXPLAIN QUERY PLAN |
||
147 | SELECT b FROM t501 |
||
148 | WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); |
||
149 | } { |
||
150 | |||
151 | |||
152 | 1 0 0 {SCAN TABLE t502 (~100000 rows)} |
||
153 | } |
||
154 | do_execsql_test autoindex1-501 { |
||
155 | EXPLAIN QUERY PLAN |
||
156 | SELECT b FROM t501 |
||
157 | WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); |
||
158 | } { |
||
159 | |||
160 | |||
161 | 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)} |
||
162 | } |
||
163 | do_execsql_test autoindex1-502 { |
||
164 | EXPLAIN QUERY PLAN |
||
165 | SELECT b FROM t501 |
||
166 | WHERE t501.a=123 |
||
167 | AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); |
||
168 | } { |
||
169 | |||
170 | |||
171 | 1 0 0 {SCAN TABLE t502 (~100000 rows)} |
||
172 | } |
||
173 | |||
174 | |||
175 | # The following code checks a performance regression reported on the |
||
176 | # mailing list on 2010-10-19. The problem is that the nRowEst field |
||
177 | # of ephermeral tables was not being initialized correctly and so no |
||
178 | # automatic index was being created for the emphemeral table when it was |
||
179 | # used as part of a join. |
||
180 | # |
||
181 | do_execsql_test autoindex1-600 { |
||
182 | CREATE TABLE flock_owner( |
||
183 | owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, |
||
184 | flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), |
||
185 | owner_person_id INTEGER NOT NULL REFERENCES person (person_id), |
||
186 | owner_change_date TEXT, last_changed TEXT NOT NULL, |
||
187 | CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) |
||
188 | ); |
||
189 | CREATE TABLE sheep ( |
||
190 | Sheep_No char(7) NOT NULL, |
||
191 | Date_of_Birth char(8), |
||
192 | Sort_DoB text, |
||
193 | Flock_Book_Vol char(2), |
||
194 | Breeder_No char(6), |
||
195 | Breeder_Person integer, |
||
196 | Originating_Flock char(6), |
||
197 | Registering_Flock char(6), |
||
198 | Tag_Prefix char(9), |
||
199 | Tag_No char(15), |
||
200 | Sort_Tag_No integer, |
||
201 | Breeders_Temp_Tag char(15), |
||
202 | Sex char(1), |
||
203 | Sheep_Name char(32), |
||
204 | Sire_No char(7), |
||
205 | Dam_No char(7), |
||
206 | Register_Code char(1), |
||
207 | Colour char(48), |
||
208 | Colour_Code char(2), |
||
209 | Pattern_Code char(8), |
||
210 | Horns char(1), |
||
211 | Litter_Size char(1), |
||
212 | Coeff_of_Inbreeding real, |
||
213 | Date_of_Registration text, |
||
214 | Date_Last_Changed text, |
||
215 | UNIQUE(Sheep_No)); |
||
216 | CREATE INDEX fo_flock_no_index |
||
217 | ON flock_owner (flock_no); |
||
218 | CREATE INDEX fo_owner_change_date_index |
||
219 | ON flock_owner (owner_change_date); |
||
220 | CREATE INDEX fo_owner_person_id_index |
||
221 | ON flock_owner (owner_person_id); |
||
222 | CREATE INDEX sheep_org_flock_index |
||
223 | ON sheep (originating_flock); |
||
224 | CREATE INDEX sheep_reg_flock_index |
||
225 | ON sheep (registering_flock); |
||
226 | EXPLAIN QUERY PLAN |
||
227 | SELECT x.sheep_no, x.registering_flock, x.date_of_registration |
||
228 | FROM sheep x LEFT JOIN |
||
229 | (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, |
||
230 | s.date_of_registration, prev.owner_change_date |
||
231 | FROM sheep s JOIN flock_owner prev ON s.registering_flock = |
||
232 | prev.flock_no |
||
233 | AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') |
||
234 | WHERE NOT EXISTS |
||
235 | (SELECT 'x' FROM flock_owner later |
||
236 | WHERE prev.flock_no = later.flock_no |
||
237 | AND later.owner_change_date > prev.owner_change_date |
||
238 | AND later.owner_change_date <= s.date_of_registration||' 00:00:00') |
||
239 | ) y ON x.sheep_no = y.sheep_no |
||
240 | WHERE y.sheep_no IS NULL |
||
241 | ORDER BY x.registering_flock; |
||
242 | } { |
||
243 | 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} |
||
244 | 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} |
||
245 | 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} |
||
246 | 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} |
||
247 | |||
248 | |||
249 | } |
||
250 | |||
251 | finish_test |