Debian Codegraph @

test/indexedby.test

raw
1 # 2008-10-04
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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set ::testprefix indexedby
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_eqp_test indexedby-1.2 {
44 select * from t1 WHERE a = 10;
45 } {SEARCH t1 USING INDEX i1 (a=?)}
46 do_eqp_test indexedby-1.3 {
47 select * from t1 ;
48 } {SCAN t1}
49 do_eqp_test indexedby-1.4 {
50 select * from t1, t2 WHERE c = 10;
51 } {
52 QUERY PLAN
53 |--SEARCH t2 USING INDEX i3 (c=?)
54 `--SCAN t1
55 }
56
57 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be
58 # attached to a table in the FROM clause, but not to a sub-select or
59 # SQL view. Also test that specifying an index that does not exist or
60 # is attached to a different table is detected as an error.
61 #
62 # X-EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
63 #
64 # EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase
65 # specifies that the named index must be used in order to look up values
66 # on the preceding table.
67 #
68 do_test indexedby-2.1 {
69 execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
70 } {}
71 do_test indexedby-2.1b {
72 execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
73 } {}
74 do_test indexedby-2.2 {
75 execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
76 } {}
77 do_test indexedby-2.2b {
78 execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
79 } {}
80 do_test indexedby-2.3 {
81 execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
82 } {}
83 # EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the
84 # optimizer hints about which index to use; it gives the optimizer a
85 # requirement of which index to use.
86 # EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be
87 # used for the query, then the preparation of the SQL statement fails.
88 #
89 do_test indexedby-2.4 {
90 catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
91 } {1 {no such index: i3}}
92
93 # EVIDENCE-OF: R-05301-32681 If the query optimizer is unable to use the
94 # index specified by the INDEXED BY clause, then the query will fail
95 # with an error.
96 do_test indexedby-2.4.1 {
97 catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }
98 } {0 {}}
99
100 do_test indexedby-2.5 {
101 catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
102 } {1 {no such index: i5}}
103 do_test indexedby-2.6 {
104 catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
105 } {1 {near "WHERE": syntax error}}
106 do_test indexedby-2.7 {
107 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
108 } {1 {no such index: i1}}
109
110
111 # Tests for single table cases.
112 #
113 # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
114 # index shall be used when accessing the preceding table, including
115 # implied indices create by UNIQUE and PRIMARY KEY constraints. However,
116 # the rowid can still be used to look up entries even when "NOT INDEXED"
117 # is specified.
118 #
119 do_eqp_test indexedby-3.1 {
120 SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
121 } {/SEARCH t1 USING INDEX/}
122 do_eqp_test indexedby-3.1.1 {
123 SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
124 } {SCAN t1}
125 do_eqp_test indexedby-3.1.2 {
126 SELECT * FROM t1 NOT INDEXED WHERE rowid=1
127 } {/SEARCH t1 USING INTEGER PRIMARY KEY .rowid=/}
128
129
130 do_eqp_test indexedby-3.2 {
131 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
132 } {SEARCH t1 USING INDEX i1 (a=?)}
133 do_eqp_test indexedby-3.3 {
134 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
135 } {SEARCH t1 USING INDEX i2 (b=?)}
136 do_test indexedby-3.4 {
137 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
138 } {0 {}}
139 do_test indexedby-3.5 {
140 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
141 } {0 {}}
142 do_test indexedby-3.6 {
143 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
144 } {0 {}}
145 do_test indexedby-3.7 {
146 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
147 } {0 {}}
148
149 do_eqp_test indexedby-3.8 {
150 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e
151 } {SCAN t3 USING INDEX sqlite_autoindex_t3_1}
152 do_eqp_test indexedby-3.9 {
153 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10
154 } {SEARCH t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}
155 do_test indexedby-3.10 {
156 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
157 } {0 {}}
158 do_test indexedby-3.11 {
159 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
160 } {1 {no such index: sqlite_autoindex_t3_2}}
161
162 # Tests for multiple table cases.
163 #
164 do_eqp_test indexedby-4.1 {
165 SELECT * FROM t1, t2 WHERE a = c
166 } {
167 QUERY PLAN
168 |--SCAN t1
169 `--SEARCH t2 USING INDEX i3 (c=?)
170 }
171 do_eqp_test indexedby-4.2 {
172 SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c
173 } {
174 QUERY PLAN
175 |--SCAN t1 USING INDEX i1
176 `--SEARCH t2 USING INDEX i3 (c=?)
177 }
178 do_test indexedby-4.3 {
179 catchsql {
180 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
181 }
182 } {0 {}}
183 do_test indexedby-4.4 {
184 catchsql {
185 SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
186 }
187 } {0 {}}
188
189 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
190 # also tests that nothing bad happens if an index referred to by
191 # a CREATE VIEW statement is dropped and recreated.
192 #
193 do_execsql_test indexedby-5.1 {
194 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
195 EXPLAIN QUERY PLAN SELECT * FROM v2
196 } {/*SEARCH t1 USING INDEX i1 (a>?)*/}
197 do_execsql_test indexedby-5.2 {
198 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10
199 } {/*SEARCH t1 USING INDEX i1 (a>?)*/}
200 do_test indexedby-5.3 {
201 execsql { DROP INDEX i1 }
202 catchsql { SELECT * FROM v2 }
203 } {1 {no such index: i1}}
204 do_test indexedby-5.4 {
205 # Recreate index i1 in such a way as it cannot be used by the view query.
206 execsql { CREATE INDEX i1 ON t1(b) }
207 catchsql { SELECT * FROM v2 }
208 } {0 {}}
209 do_test indexedby-5.5 {
210 # Drop and recreate index i1 again. This time, create it so that it can
211 # be used by the query.
212 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
213 catchsql { SELECT * FROM v2 }
214 } {0 {}}
215
216 # Test that "NOT INDEXED" may use the rowid index, but not others.
217 #
218 do_eqp_test indexedby-6.1 {
219 SELECT * FROM t1 WHERE b = 10 ORDER BY rowid
220 } {SEARCH t1 USING INDEX i2 (b=?)}
221 do_eqp_test indexedby-6.2 {
222 SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid
223 } {SCAN t1}
224
225 # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
226 # query planner to use a particular named index on a DELETE, SELECT, or
227 # UPDATE statement.
228 #
229 # Test that "INDEXED BY" can be used in a DELETE statement.
230 #
231 do_eqp_test indexedby-7.1 {
232 DELETE FROM t1 WHERE a = 5
233 } {SEARCH t1 USING INDEX i1 (a=?)}
234 do_eqp_test indexedby-7.2 {
235 DELETE FROM t1 NOT INDEXED WHERE a = 5
236 } {SCAN t1}
237 do_eqp_test indexedby-7.3 {
238 DELETE FROM t1 INDEXED BY i1 WHERE a = 5
239 } {SEARCH t1 USING INDEX i1 (a=?)}
240 do_eqp_test indexedby-7.4 {
241 DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
242 } {SEARCH t1 USING INDEX i1 (a=?)}
243 do_eqp_test indexedby-7.5 {
244 DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
245 } {SEARCH t1 USING INDEX i2 (b=?)}
246 do_test indexedby-7.6 {
247 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
248 } {0 {}}
249
250 # Test that "INDEXED BY" can be used in an UPDATE statement.
251 #
252 do_eqp_test indexedby-8.1 {
253 UPDATE t1 SET rowid=rowid+1 WHERE a = 5
254 } {SEARCH t1 USING COVERING INDEX i1 (a=?)}
255 do_eqp_test indexedby-8.2 {
256 UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5
257 } {SCAN t1}
258 do_eqp_test indexedby-8.3 {
259 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5
260 } {SEARCH t1 USING COVERING INDEX i1 (a=?)}
261 do_eqp_test indexedby-8.4 {
262 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
263 } {SEARCH t1 USING INDEX i1 (a=?)}
264 do_eqp_test indexedby-8.5 {
265 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
266 } {SEARCH t1 USING INDEX i2 (b=?)}
267 do_test indexedby-8.6 {
268 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
269 } {0 {}}
270
271 # Test that bug #3560 is fixed.
272 #
273 do_test indexedby-9.1 {
274 execsql {
275 CREATE TABLE maintable( id integer);
276 CREATE TABLE joinme(id_int integer, id_text text);
277 CREATE INDEX joinme_id_text_idx on joinme(id_text);
278 CREATE INDEX joinme_id_int_idx on joinme(id_int);
279 }
280 } {}
281 do_test indexedby-9.2 {
282 catchsql {
283 select * from maintable as m inner join
284 joinme as j indexed by joinme_id_text_idx
285 on ( m.id = j.id_int)
286 }
287 } {0 {}}
288 do_test indexedby-9.3 {
289 catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
290 } {0 {}}
291
292 # Make sure we can still create tables, indices, and columns whose name
293 # is "indexed".
294 #
295 do_test indexedby-10.1 {
296 execsql {
297 CREATE TABLE indexed(x,y);
298 INSERT INTO indexed VALUES(1,2);
299 SELECT * FROM indexed;
300 }
301 } {1 2}
302 do_test indexedby-10.2 {
303 execsql {
304 CREATE INDEX i10 ON indexed(x);
305 SELECT * FROM indexed indexed by i10 where x>0;
306 }
307 } {1 2}
308 do_test indexedby-10.3 {
309 execsql {
310 DROP TABLE indexed;
311 CREATE TABLE t10(indexed INTEGER);
312 INSERT INTO t10 VALUES(1);
313 CREATE INDEX indexed ON t10(indexed);
314 SELECT * FROM t10 indexed by indexed WHERE indexed>0
315 }
316 } {1}
317
318 #-------------------------------------------------------------------------
319 # Ensure that the rowid at the end of each index entry may be used
320 # for equality constraints in the same way as other indexed fields.
321 #
322 do_execsql_test 11.1 {
323 CREATE TABLE x1(a, b TEXT);
324 CREATE INDEX x1i ON x1(a, b);
325 INSERT INTO x1 VALUES(1, 1);
326 INSERT INTO x1 VALUES(1, 1);
327 INSERT INTO x1 VALUES(1, 1);
328 INSERT INTO x1 VALUES(1, 1);
329 }
330 do_execsql_test 11.2 {
331 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid=3;
332 } {1 1 3}
333 do_execsql_test 11.3 {
334 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3';
335 } {1 1 3}
336 do_execsql_test 11.4 {
337 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
338 } {1 1 3}
339 do_eqp_test 11.5 {
340 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
341 } {SEARCH x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}
342
343 do_execsql_test 11.6 {
344 CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT);
345 CREATE INDEX x2i ON x2(a, b);
346 INSERT INTO x2 VALUES(1, 1, 1);
347 INSERT INTO x2 VALUES(2, 1, 1);
348 INSERT INTO x2 VALUES(3, 1, 1);
349 INSERT INTO x2 VALUES(4, 1, 1);
350 }
351 do_execsql_test 11.7 {
352 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c=3;
353 } {1 1 3}
354 do_execsql_test 11.8 {
355 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3';
356 } {1 1 3}
357 do_execsql_test 11.9 {
358 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
359 } {1 1 3}
360 do_eqp_test 11.10 {
361 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
362 } {SEARCH x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}
363
364 #-------------------------------------------------------------------------
365 # Check INDEXED BY works (throws an exception) with partial indexes that
366 # cannot be used.
367 do_execsql_test 12.1 {
368 CREATE TABLE o1(x INTEGER PRIMARY KEY, y, z);
369 CREATE INDEX p1 ON o1(z);
370 CREATE INDEX p2 ON o1(y) WHERE z=1;
371 }
372 do_catchsql_test 12.2 {
373 SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;
374 } {1 {no query solution}}
375 do_execsql_test 12.3 {
376 DROP INDEX p1;
377 DROP INDEX p2;
378 CREATE INDEX p2 ON o1(y) WHERE z=1;
379 CREATE INDEX p1 ON o1(z);
380 }
381 do_catchsql_test 12.4 {
382 SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;
383 } {1 {no query solution}}
384
385 finish_test