wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2008 October 4 |
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 | # $Id: indexedby.test,v 1.5 2009/03/22 20:36:19 drh Exp $ |
||
13 | |||
14 | set testdir [file dirname $argv0] |
||
15 | source $testdir/tester.tcl |
||
16 | |||
17 | # Create a schema with some indexes. |
||
18 | # |
||
19 | do_test indexedby-1.1 { |
||
20 | execsql { |
||
21 | CREATE TABLE t1(a, b); |
||
22 | CREATE INDEX i1 ON t1(a); |
||
23 | CREATE INDEX i2 ON t1(b); |
||
24 | |||
25 | CREATE TABLE t2(c, d); |
||
26 | CREATE INDEX i3 ON t2(c); |
||
27 | CREATE INDEX i4 ON t2(d); |
||
28 | |||
29 | CREATE TABLE t3(e PRIMARY KEY, f); |
||
30 | |||
31 | CREATE VIEW v1 AS SELECT * FROM t1; |
||
32 | } |
||
33 | } {} |
||
34 | |||
35 | # Explain Query Plan |
||
36 | # |
||
37 | proc EQP {sql} { |
||
38 | uplevel "execsql {EXPLAIN QUERY PLAN $sql}" |
||
39 | } |
||
40 | |||
41 | # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. |
||
42 | # |
||
43 | do_execsql_test indexedby-1.2 { |
||
44 | EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; |
||
45 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} |
||
46 | do_execsql_test indexedby-1.3 { |
||
47 | EXPLAIN QUERY PLAN select * from t1 ; |
||
48 | } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} |
||
49 | do_execsql_test indexedby-1.4 { |
||
50 | EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; |
||
51 | } { |
||
52 | |||
53 | |||
54 | } |
||
55 | |||
56 | # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be |
||
57 | # attached to a table in the FROM clause, but not to a sub-select or |
||
58 | # SQL view. Also test that specifying an index that does not exist or |
||
59 | # is attached to a different table is detected as an error. |
||
60 | # |
||
61 | do_test indexedby-2.1 { |
||
62 | execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} |
||
63 | } {} |
||
64 | do_test indexedby-2.2 { |
||
65 | execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} |
||
66 | } {} |
||
67 | do_test indexedby-2.3 { |
||
68 | execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'} |
||
69 | } {} |
||
70 | |||
71 | do_test indexedby-2.4 { |
||
72 | catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'} |
||
73 | } {1 {no such index: i3}} |
||
74 | do_test indexedby-2.5 { |
||
75 | catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'} |
||
76 | } {1 {no such index: i5}} |
||
77 | do_test indexedby-2.6 { |
||
78 | catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'} |
||
79 | } {1 {near "WHERE": syntax error}} |
||
80 | do_test indexedby-2.7 { |
||
81 | catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' } |
||
82 | } {1 {no such index: i1}} |
||
83 | |||
84 | # Tests for single table cases. |
||
85 | # |
||
86 | do_execsql_test indexedby-3.1 { |
||
87 | EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' |
||
88 | } {0 0 0 {SCAN TABLE t1 (~10000 rows)}} |
||
89 | do_execsql_test indexedby-3.2 { |
||
90 | EXPLAIN QUERY PLAN |
||
91 | SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' |
||
92 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} |
||
93 | do_execsql_test indexedby-3.3 { |
||
94 | EXPLAIN QUERY PLAN |
||
95 | SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' |
||
96 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} |
||
97 | do_test indexedby-3.4 { |
||
98 | catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } |
||
99 | } {1 {cannot use index: i2}} |
||
100 | do_test indexedby-3.5 { |
||
101 | catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } |
||
102 | } {1 {cannot use index: i2}} |
||
103 | do_test indexedby-3.6 { |
||
104 | catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } |
||
105 | } {0 {}} |
||
106 | do_test indexedby-3.7 { |
||
107 | catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } |
||
108 | } {0 {}} |
||
109 | |||
110 | do_execsql_test indexedby-3.8 { |
||
111 | EXPLAIN QUERY PLAN |
||
112 | SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e |
||
113 | } {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} |
||
114 | do_execsql_test indexedby-3.9 { |
||
115 | EXPLAIN QUERY PLAN |
||
116 | SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 |
||
117 | } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} |
||
118 | do_test indexedby-3.10 { |
||
119 | catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } |
||
120 | } {1 {cannot use index: sqlite_autoindex_t3_1}} |
||
121 | do_test indexedby-3.11 { |
||
122 | catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } |
||
123 | } {1 {no such index: sqlite_autoindex_t3_2}} |
||
124 | |||
125 | # Tests for multiple table cases. |
||
126 | # |
||
127 | do_execsql_test indexedby-4.1 { |
||
128 | EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c |
||
129 | } { |
||
130 | |||
131 | |||
132 | } |
||
133 | do_execsql_test indexedby-4.2 { |
||
134 | EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c |
||
135 | } { |
||
136 | |||
137 | |||
138 | } |
||
139 | do_test indexedby-4.3 { |
||
140 | catchsql { |
||
141 | SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c |
||
142 | } |
||
143 | } {1 {cannot use index: i1}} |
||
144 | do_test indexedby-4.4 { |
||
145 | catchsql { |
||
146 | SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c |
||
147 | } |
||
148 | } {1 {cannot use index: i3}} |
||
149 | |||
150 | # Test embedding an INDEXED BY in a CREATE VIEW statement. This block |
||
151 | # also tests that nothing bad happens if an index refered to by |
||
152 | # a CREATE VIEW statement is dropped and recreated. |
||
153 | # |
||
154 | do_execsql_test indexedby-5.1 { |
||
155 | CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; |
||
156 | EXPLAIN QUERY PLAN SELECT * FROM v2 |
||
157 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} |
||
158 | do_execsql_test indexedby-5.2 { |
||
159 | EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 |
||
160 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} |
||
161 | do_test indexedby-5.3 { |
||
162 | execsql { DROP INDEX i1 } |
||
163 | catchsql { SELECT * FROM v2 } |
||
164 | } {1 {no such index: i1}} |
||
165 | do_test indexedby-5.4 { |
||
166 | # Recreate index i1 in such a way as it cannot be used by the view query. |
||
167 | execsql { CREATE INDEX i1 ON t1(b) } |
||
168 | catchsql { SELECT * FROM v2 } |
||
169 | } {1 {cannot use index: i1}} |
||
170 | do_test indexedby-5.5 { |
||
171 | # Drop and recreate index i1 again. This time, create it so that it can |
||
172 | # be used by the query. |
||
173 | execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) } |
||
174 | catchsql { SELECT * FROM v2 } |
||
175 | } {0 {}} |
||
176 | |||
177 | # Test that "NOT INDEXED" may use the rowid index, but not others. |
||
178 | # |
||
179 | do_execsql_test indexedby-6.1 { |
||
180 | EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid |
||
181 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} |
||
182 | do_execsql_test indexedby-6.2 { |
||
183 | EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid |
||
184 | } {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} |
||
185 | |||
186 | # Test that "INDEXED BY" can be used in a DELETE statement. |
||
187 | # |
||
188 | do_execsql_test indexedby-7.1 { |
||
189 | EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 |
||
190 | } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} |
||
191 | do_execsql_test indexedby-7.2 { |
||
192 | EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 |
||
193 | } {0 0 0 {SCAN TABLE t1 (~100000 rows)}} |
||
194 | do_execsql_test indexedby-7.3 { |
||
195 | EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 |
||
196 | } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} |
||
197 | do_execsql_test indexedby-7.4 { |
||
198 | EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 |
||
199 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} |
||
200 | do_execsql_test indexedby-7.5 { |
||
201 | EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 |
||
202 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} |
||
203 | do_test indexedby-7.6 { |
||
204 | catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} |
||
205 | } {1 {cannot use index: i2}} |
||
206 | |||
207 | # Test that "INDEXED BY" can be used in an UPDATE statement. |
||
208 | # |
||
209 | do_execsql_test indexedby-8.1 { |
||
210 | EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 |
||
211 | } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} |
||
212 | do_execsql_test indexedby-8.2 { |
||
213 | EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 |
||
214 | } {0 0 0 {SCAN TABLE t1 (~100000 rows)}} |
||
215 | do_execsql_test indexedby-8.3 { |
||
216 | EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 |
||
217 | } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} |
||
218 | do_execsql_test indexedby-8.4 { |
||
219 | EXPLAIN QUERY PLAN |
||
220 | UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 |
||
221 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} |
||
222 | do_execsql_test indexedby-8.5 { |
||
223 | EXPLAIN QUERY PLAN |
||
224 | UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 |
||
225 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} |
||
226 | do_test indexedby-8.6 { |
||
227 | catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} |
||
228 | } {1 {cannot use index: i2}} |
||
229 | |||
230 | # Test that bug #3560 is fixed. |
||
231 | # |
||
232 | do_test indexedby-9.1 { |
||
233 | execsql { |
||
234 | CREATE TABLE maintable( id integer); |
||
235 | CREATE TABLE joinme(id_int integer, id_text text); |
||
236 | CREATE INDEX joinme_id_text_idx on joinme(id_text); |
||
237 | CREATE INDEX joinme_id_int_idx on joinme(id_int); |
||
238 | } |
||
239 | } {} |
||
240 | do_test indexedby-9.2 { |
||
241 | catchsql { |
||
242 | select * from maintable as m inner join |
||
243 | joinme as j indexed by joinme_id_text_idx |
||
244 | on ( m.id = j.id_int) |
||
245 | } |
||
246 | } {1 {cannot use index: joinme_id_text_idx}} |
||
247 | do_test indexedby-9.3 { |
||
248 | catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx } |
||
249 | } {1 {cannot use index: joinme_id_text_idx}} |
||
250 | |||
251 | # Make sure we can still create tables, indices, and columns whose name |
||
252 | # is "indexed". |
||
253 | # |
||
254 | do_test indexedby-10.1 { |
||
255 | execsql { |
||
256 | CREATE TABLE indexed(x,y); |
||
257 | INSERT INTO indexed VALUES(1,2); |
||
258 | SELECT * FROM indexed; |
||
259 | } |
||
260 | } {1 2} |
||
261 | do_test indexedby-10.2 { |
||
262 | execsql { |
||
263 | CREATE INDEX i10 ON indexed(x); |
||
264 | SELECT * FROM indexed indexed by i10 where x>0; |
||
265 | } |
||
266 | } {1 2} |
||
267 | do_test indexedby-10.3 { |
||
268 | execsql { |
||
269 | DROP TABLE indexed; |
||
270 | CREATE TABLE t10(indexed INTEGER); |
||
271 | INSERT INTO t10 VALUES(1); |
||
272 | CREATE INDEX indexed ON t10(indexed); |
||
273 | SELECT * FROM t10 indexed by indexed WHERE indexed>0 |
||
274 | } |
||
275 | } {1} |
||
276 | |||
277 | finish_test |