| blob | aabe7f3869d4c26c72db3f480da6a2788455364f |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the CREATE INDEX statement.
16 #
17 # $Id: index.test,v 1.24.2.1 2004/07/20 00:50:30 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Create a basic index and verify it is added to sqlite_master
23 #
24 do_test index-1.1 {
25 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
26 execsql {CREATE INDEX index1 ON test1(f1)}
27 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
28 } {index1 test1}
29 do_test index-1.1b {
30 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
31 WHERE name='index1'}
32 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
33 do_test index-1.1c {
34 db close
35 sqlite db test.db
36 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
37 WHERE name='index1'}
38 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
39 do_test index-1.1d {
40 db close
41 sqlite db test.db
42 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
43 } {index1 test1}
45 # Verify that the index dies with the table
46 #
47 do_test index-1.2 {
48 execsql {DROP TABLE test1}
49 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
50 } {}
52 # Try adding an index to a table that does not exist
53 #
54 do_test index-2.1 {
55 set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
56 lappend v $msg
57 } {1 {no such table: test1}}
59 # Try adding an index on a column of a table where the table
60 # exists but the column does not.
61 #
62 do_test index-2.1 {
63 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
64 set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
65 lappend v $msg
66 } {1 {table test1 has no column named f4}}
68 # Try an index with some columns that match and others that do now.
69 #
70 do_test index-2.2 {
71 set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
72 execsql {DROP TABLE test1}
73 lappend v $msg
74 } {1 {table test1 has no column named f4}}
76 # Try creating a bunch of indices on the same table
77 #
78 set r {}
79 for {set i 1} {$i<100} {incr i} {
80 lappend r [format index%02d $i]
81 }
82 do_test index-3.1 {
83 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
84 for {set i 1} {$i<100} {incr i} {
85 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
86 execsql $sql
87 }
88 execsql {SELECT name FROM sqlite_master
89 WHERE type='index' AND tbl_name='test1'
90 ORDER BY name}
91 } $r
94 # Verify that all the indices go away when we drop the table.
95 #
96 do_test index-3.3 {
97 execsql {DROP TABLE test1}
98 execsql {SELECT name FROM sqlite_master
99 WHERE type='index' AND tbl_name='test1'
100 ORDER BY name}
101 } {}
103 # Create a table and insert values into that table. Then create
104 # an index on that table. Verify that we can select values
105 # from the table correctly using the index.
106 #
107 # Note that the index names "index9" and "indext" are chosen because
108 # they both have the same hash.
109 #
110 do_test index-4.1 {
111 execsql {CREATE TABLE test1(cnt int, power int)}
112 for {set i 1} {$i<20} {incr i} {
113 execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
114 }
115 execsql {CREATE INDEX index9 ON test1(cnt)}
116 execsql {CREATE INDEX indext ON test1(power)}
117 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
118 } {index9 indext test1}
119 do_test index-4.2 {
120 execsql {SELECT cnt FROM test1 WHERE power=4}
121 } {2}
122 do_test index-4.3 {
123 execsql {SELECT cnt FROM test1 WHERE power=1024}
124 } {10}
125 do_test index-4.4 {
126 execsql {SELECT power FROM test1 WHERE cnt=6}
127 } {64}
128 do_test index-4.5 {
129 execsql {DROP INDEX indext}
130 execsql {SELECT power FROM test1 WHERE cnt=6}
131 } {64}
132 do_test index-4.6 {
133 execsql {SELECT cnt FROM test1 WHERE power=1024}
134 } {10}
135 do_test index-4.7 {
136 execsql {CREATE INDEX indext ON test1(cnt)}
137 execsql {SELECT power FROM test1 WHERE cnt=6}
138 } {64}
139 do_test index-4.8 {
140 execsql {SELECT cnt FROM test1 WHERE power=1024}
141 } {10}
142 do_test index-4.9 {
143 execsql {DROP INDEX index9}
144 execsql {SELECT power FROM test1 WHERE cnt=6}
145 } {64}
146 do_test index-4.10 {
147 execsql {SELECT cnt FROM test1 WHERE power=1024}
148 } {10}
149 do_test index-4.11 {
150 execsql {DROP INDEX indext}
151 execsql {SELECT power FROM test1 WHERE cnt=6}
152 } {64}
153 do_test index-4.12 {
154 execsql {SELECT cnt FROM test1 WHERE power=1024}
155 } {10}
156 do_test index-4.13 {
157 execsql {DROP TABLE test1}
158 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
159 } {}
160 integrity_check index-4.14
162 # Do not allow indices to be added to sqlite_master
163 #
164 do_test index-5.1 {
165 set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
166 lappend v $msg
167 } {1 {table sqlite_master may not be indexed}}
168 do_test index-5.2 {
169 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
170 } {}
172 # Do not allow indices with duplicate names to be added
173 #
174 do_test index-6.1 {
175 execsql {CREATE TABLE test1(f1 int, f2 int)}
176 execsql {CREATE TABLE test2(g1 real, g2 real)}
177 execsql {CREATE INDEX index1 ON test1(f1)}
178 set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
179 lappend v $msg
180 } {1 {index index1 already exists}}
181 do_test index-6.1b {
182 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
183 } {index1 test1 test2}
184 do_test index-6.2 {
185 set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
186 lappend v $msg
187 } {1 {there is already a table named test1}}
188 do_test index-6.2b {
189 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
190 } {index1 test1 test2}
191 do_test index-6.3 {
192 execsql {DROP TABLE test1}
193 execsql {DROP TABLE test2}
194 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
195 } {}
196 do_test index-6.4 {
197 execsql {
198 CREATE TABLE test1(a,b);
199 CREATE INDEX index1 ON test1(a);
200 CREATE INDEX index2 ON test1(b);
201 CREATE INDEX index3 ON test1(a,b);
202 DROP TABLE test1;
203 SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
204 }
205 } {}
206 integrity_check index-6.5
209 # Create a primary key
210 #
211 do_test index-7.1 {
212 execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
213 for {set i 1} {$i<20} {incr i} {
214 execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
215 }
216 execsql {SELECT count(*) FROM test1}
217 } {19}
218 do_test index-7.2 {
219 execsql {SELECT f1 FROM test1 WHERE f2=65536}
220 } {16}
221 do_test index-7.3 {
222 execsql {
223 SELECT name FROM sqlite_master
224 WHERE type='index' AND tbl_name='test1'
225 }
226 } {{(test1 autoindex 1)}}
227 do_test index-7.4 {
228 execsql {DROP table test1}
229 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
230 } {}
231 integrity_check index-7.5
233 # Make sure we cannot drop a non-existant index.
234 #
235 do_test index-8.1 {
236 set v [catch {execsql {DROP INDEX index1}} msg]
237 lappend v $msg
238 } {1 {no such index: index1}}
240 # Make sure we don't actually create an index when the EXPLAIN keyword
241 # is used.
242 #
243 do_test index-9.1 {
244 execsql {CREATE TABLE tab1(a int)}
245 execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
246 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
247 } {tab1}
248 do_test index-9.2 {
249 execsql {CREATE INDEX idx1 ON tab1(a)}
250 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
251 } {idx1 tab1}
252 integrity_check index-9.3
254 # Allow more than one entry with the same key.
255 #
256 do_test index-10.0 {
257 execsql {
258 CREATE TABLE t1(a int, b int);
259 CREATE INDEX i1 ON t1(a);
260 INSERT INTO t1 VALUES(1,2);
261 INSERT INTO t1 VALUES(2,4);
262 INSERT INTO t1 VALUES(3,8);
263 INSERT INTO t1 VALUES(1,12);
264 SELECT b FROM t1 WHERE a=1 ORDER BY b;
265 }
266 } {2 12}
267 do_test index-10.1 {
268 execsql {
269 SELECT b FROM t1 WHERE a=2 ORDER BY b;
270 }
271 } {4}
272 do_test index-10.2 {
273 execsql {
274 DELETE FROM t1 WHERE b=12;
275 SELECT b FROM t1 WHERE a=1 ORDER BY b;
276 }
277 } {2}
278 do_test index-10.3 {
279 execsql {
280 DELETE FROM t1 WHERE b=2;
281 SELECT b FROM t1 WHERE a=1 ORDER BY b;
282 }
283 } {}
284 do_test index-10.4 {
285 execsql {
286 DELETE FROM t1;
287 INSERT INTO t1 VALUES (1,1);
288 INSERT INTO t1 VALUES (1,2);
289 INSERT INTO t1 VALUES (1,3);
290 INSERT INTO t1 VALUES (1,4);
291 INSERT INTO t1 VALUES (1,5);
292 INSERT INTO t1 VALUES (1,6);
293 INSERT INTO t1 VALUES (1,7);
294 INSERT INTO t1 VALUES (1,8);
295 INSERT INTO t1 VALUES (1,9);
296 INSERT INTO t1 VALUES (2,0);
297 SELECT b FROM t1 WHERE a=1 ORDER BY b;
298 }
299 } {1 2 3 4 5 6 7 8 9}
300 do_test index-10.5 {
301 execsql {
302 DELETE FROM t1 WHERE b IN (2, 4, 6, 8);
303 SELECT b FROM t1 WHERE a=1 ORDER BY b;
304 }
305 } {1 3 5 7 9}
306 do_test index-10.6 {
307 execsql {
308 DELETE FROM t1 WHERE b>2;
309 SELECT b FROM t1 WHERE a=1 ORDER BY b;
310 }
311 } {1}
312 do_test index-10.7 {
313 execsql {
314 DELETE FROM t1 WHERE b=1;
315 SELECT b FROM t1 WHERE a=1 ORDER BY b;
316 }
317 } {}
318 do_test index-10.8 {
319 execsql {
320 SELECT b FROM t1 ORDER BY b;
321 }
322 } {0}
323 integrity_check index-10.9
325 # Automatically create an index when we specify a primary key.
326 #
327 do_test index-11.1 {
328 execsql {
329 CREATE TABLE t3(
330 a text,
331 b int,
332 c float,
333 PRIMARY KEY(b)
334 );
335 }
336 for {set i 1} {$i<=50} {incr i} {
337 execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
338 }
339 set sqlite_search_count 0
340 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
341 } {0.10 3}
342 integrity_check index-11.2
345 # Numeric strings should compare as if they were numbers. So even if the
346 # strings are not character-by-character the same, if they represent the
347 # same number they should compare equal to one another. Verify that this
348 # is true in indices.
349 #
350 do_test index-12.1 {
351 execsql {
352 CREATE TABLE t4(a,b);
353 INSERT INTO t4 VALUES('0.0',1);
354 INSERT INTO t4 VALUES('0.00',2);
355 INSERT INTO t4 VALUES('abc',3);
356 INSERT INTO t4 VALUES('-1.0',4);
357 INSERT INTO t4 VALUES('+1.0',5);
358 INSERT INTO t4 VALUES('0',6);
359 INSERT INTO t4 VALUES('00000',7);
360 SELECT a FROM t4 ORDER BY b;
361 }
362 } {0.0 0.00 abc -1.0 +1.0 0 00000}
363 do_test index-12.2 {
364 execsql {
365 SELECT a FROM t4 WHERE a==0 ORDER BY b
366 }
367 } {0.0 0.00 0 00000}
368 do_test index-12.3 {
369 execsql {
370 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
371 }
372 } {0.0 0.00 -1.0 0 00000}
373 do_test index-12.4 {
374 execsql {
375 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
376 }
377 } {0.0 0.00 abc +1.0 0 00000}
378 do_test index-12.5 {
379 execsql {
380 CREATE INDEX t4i1 ON t4(a);
381 SELECT a FROM t4 WHERE a==0 ORDER BY b
382 }
383 } {0.0 0.00 0 00000}
384 do_test index-12.6 {
385 execsql {
386 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
387 }
388 } {0.0 0.00 -1.0 0 00000}
389 do_test index-12.7 {
390 execsql {
391 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
392 }
393 } {0.0 0.00 abc +1.0 0 00000}
394 integrity_check index-12.8
396 # Make sure we cannot drop an automatically created index.
397 #
398 do_test index-13.1 {
399 execsql {
400 CREATE TABLE t5(
401 a int UNIQUE,
402 b float PRIMARY KEY,
403 c varchar(10),
404 UNIQUE(a,c)
405 );
406 INSERT INTO t5 VALUES(1,2,3);
407 SELECT * FROM t5;
408 }
409 } {1 2 3}
410 do_test index-13.2 {
411 set ::idxlist [execsql {
412 SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
413 }]
414 llength $::idxlist
415 } {3}
416 for {set i 0} {$i<[llength $::idxlist]} {incr i} {
417 do_test index-13.3.$i {
418 catchsql "
419 DROP INDEX '[lindex $::idxlist $i]';
420 "
421 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
422 }
423 do_test index-13.4 {
424 execsql {
425 INSERT INTO t5 VALUES('a','b','c');
426 SELECT * FROM t5;
427 }
428 } {1 2 3 a b c}
429 integrity_check index-13.5
431 # Check the sort order of data in an index.
432 #
433 do_test index-14.1 {
434 execsql {
435 CREATE TABLE t6(a,b,c);
436 CREATE INDEX t6i1 ON t6(a,b);
437 INSERT INTO t6 VALUES('','',1);
438 INSERT INTO t6 VALUES('',NULL,2);
439 INSERT INTO t6 VALUES(NULL,'',3);
440 INSERT INTO t6 VALUES('abc',123,4);
441 INSERT INTO t6 VALUES(123,'abc',5);
442 SELECT c FROM t6 ORDER BY a,b;
443 }
444 } {3 5 2 1 4}
445 do_test index-14.2 {
446 execsql {
447 SELECT c FROM t6 WHERE a='';
448 }
449 } {2 1}
450 do_test index-14.3 {
451 execsql {
452 SELECT c FROM t6 WHERE b='';
453 }
454 } {1 3}
455 do_test index-14.4 {
456 execsql {
457 SELECT c FROM t6 WHERE a>'';
458 }
459 } {4}
460 do_test index-14.5 {
461 execsql {
462 SELECT c FROM t6 WHERE a>='';
463 }
464 } {2 1 4}
465 do_test index-14.6 {
466 execsql {
467 SELECT c FROM t6 WHERE a>123;
468 }
469 } {2 1 4}
470 do_test index-14.7 {
471 execsql {
472 SELECT c FROM t6 WHERE a>=123;
473 }
474 } {5 2 1 4}
475 do_test index-14.8 {
476 execsql {
477 SELECT c FROM t6 WHERE a<'abc';
478 }
479 } {5 2 1}
480 do_test index-14.9 {
481 execsql {
482 SELECT c FROM t6 WHERE a<='abc';
483 }
484 } {5 2 1 4}
485 do_test index-14.10 {
486 execsql {
487 SELECT c FROM t6 WHERE a<='';
488 }
489 } {5 2 1}
490 do_test index-14.11 {
491 execsql {
492 SELECT c FROM t6 WHERE a<'';
493 }
494 } {5}
495 integrity_check index-14.12
497 do_test index-15.1 {
498 execsql {
499 DELETE FROM t1;
500 SELECT * FROM t1;
501 }
502 } {}
503 do_test index-15.2 {
504 execsql {
505 INSERT INTO t1 VALUES('1.234e5',1);
506 INSERT INTO t1 VALUES('12.33e04',2);
507 INSERT INTO t1 VALUES('12.35E4',3);
508 INSERT INTO t1 VALUES('12.34e',4);
509 INSERT INTO t1 VALUES('12.32e+4',5);
510 INSERT INTO t1 VALUES('12.36E+04',6);
511 INSERT INTO t1 VALUES('12.36E+',7);
512 INSERT INTO t1 VALUES('+123.10000E+0003',8);
513 INSERT INTO t1 VALUES('+',9);
514 INSERT INTO t1 VALUES('+12347.E+02',10);
515 INSERT INTO t1 VALUES('+12347E+02',11);
516 SELECT b FROM t1 ORDER BY a;
517 }
518 } {8 5 2 1 3 6 11 9 10 4 7}
519 integrity_check index-15.1
521 # Drop index with a quoted name. Ticket #695.
522 #
523 do_test index-16.1 {
524 execsql {
525 CREATE INDEX "t6i2" ON t6(c);
526 DROP INDEX "t6i2";
527 }
528 } {}
529 do_test index-16.2 {
530 execsql {
531 DROP INDEX "t6i1";
532 }
533 } {}
536 finish_test