3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the CREATE INDEX statement.
14 # $Id: index.test,v 1.37 2005/01/21 03:12:16 danielk1977 Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Create a basic index and verify it is added to sqlite_master
22 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
23 execsql {CREATE INDEX index1 ON test1(f1)}
24 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
27 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
29 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
33 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
35 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
39 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
42 # Verify that the index dies with the table
45 execsql {DROP TABLE test1}
46 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
49 # Try adding an index to a table that does not exist
52 set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
54 } {1 {no such table: main.test1}}
56 # Try adding an index on a column of a table where the table
57 # exists but the column does not.
60 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
61 set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
63 } {1 {table test1 has no column named f4}}
65 # Try an index with some columns that match and others that do now.
68 set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
69 execsql {DROP TABLE test1}
71 } {1 {table test1 has no column named f4}}
73 # Try creating a bunch of indices on the same table
76 for {set i 1} {$i<100} {incr i} {
77 lappend r [format index%02d $i]
80 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
81 for {set i 1} {$i<100} {incr i} {
82 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
85 execsql {SELECT name FROM sqlite_master
86 WHERE type='index' AND tbl_name='test1'
89 integrity_check index-3.2.1
95 integrity_check index-3.2.3
98 # Verify that all the indices go away when we drop the table.
101 execsql {DROP TABLE test1}
102 execsql {SELECT name FROM sqlite_master
103 WHERE type='index' AND tbl_name='test1'
107 # Create a table and insert values into that table. Then create
108 # an index on that table. Verify that we can select values
109 # from the table correctly using the index.
111 # Note that the index names "index9" and "indext" are chosen because
112 # they both have the same hash.
115 execsql {CREATE TABLE test1(cnt int, power int)}
116 for {set i 1} {$i<20} {incr i} {
117 execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
119 execsql {CREATE INDEX index9 ON test1(cnt)}
120 execsql {CREATE INDEX indext ON test1(power)}
121 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
122 } {index9 indext test1}
124 execsql {SELECT cnt FROM test1 WHERE power=4}
127 execsql {SELECT cnt FROM test1 WHERE power=1024}
130 execsql {SELECT power FROM test1 WHERE cnt=6}
133 execsql {DROP INDEX indext}
134 execsql {SELECT power FROM test1 WHERE cnt=6}
137 execsql {SELECT cnt FROM test1 WHERE power=1024}
140 execsql {CREATE INDEX indext ON test1(cnt)}
141 execsql {SELECT power FROM test1 WHERE cnt=6}
144 execsql {SELECT cnt FROM test1 WHERE power=1024}
147 execsql {DROP INDEX index9}
148 execsql {SELECT power FROM test1 WHERE cnt=6}
151 execsql {SELECT cnt FROM test1 WHERE power=1024}
154 execsql {DROP INDEX indext}
155 execsql {SELECT power FROM test1 WHERE cnt=6}
158 execsql {SELECT cnt FROM test1 WHERE power=1024}
161 execsql {DROP TABLE test1}
162 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
164 integrity_check index-4.14
166 # Do not allow indices to be added to sqlite_master
169 set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
171 } {1 {table sqlite_master may not be indexed}}
173 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
176 # Do not allow indices with duplicate names to be added
179 execsql {CREATE TABLE test1(f1 int, f2 int)}
180 execsql {CREATE TABLE test2(g1 real, g2 real)}
181 execsql {CREATE INDEX index1 ON test1(f1)}
182 set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
184 } {1 {index index1 already exists}}
186 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
187 } {index1 test1 test2}
189 set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
191 } {1 {there is already a table named test1}}
193 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
194 } {index1 test1 test2}
196 execsql {DROP TABLE test1}
197 execsql {DROP TABLE test2}
198 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
202 CREATE TABLE test1(a,b);
203 CREATE INDEX index1 ON test1(a);
204 CREATE INDEX index2 ON test1(b);
205 CREATE INDEX index3 ON test1(a,b);
207 SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
210 integrity_check index-6.5
213 # Create a primary key
216 execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
217 for {set i 1} {$i<20} {incr i} {
218 execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
220 execsql {SELECT count(*) FROM test1}
223 execsql {SELECT f1 FROM test1 WHERE f2=65536}
227 SELECT name FROM sqlite_master
228 WHERE type='index' AND tbl_name='test1'
230 } {sqlite_autoindex_test1_1}
232 execsql {DROP table test1}
233 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
235 integrity_check index-7.5
237 # Make sure we cannot drop a non-existant index.
240 set v [catch {execsql {DROP INDEX index1}} msg]
242 } {1 {no such index: index1}}
244 # Make sure we don't actually create an index when the EXPLAIN keyword
248 execsql {CREATE TABLE tab1(a int)}
249 ifcapable {explain} {
250 execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
252 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
255 execsql {CREATE INDEX idx1 ON tab1(a)}
256 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
258 integrity_check index-9.3
260 # Allow more than one entry with the same key.
264 CREATE TABLE t1(a int, b int);
265 CREATE INDEX i1 ON t1(a);
266 INSERT INTO t1 VALUES(1,2);
267 INSERT INTO t1 VALUES(2,4);
268 INSERT INTO t1 VALUES(3,8);
269 INSERT INTO t1 VALUES(1,12);
270 SELECT b FROM t1 WHERE a=1 ORDER BY b;
275 SELECT b FROM t1 WHERE a=2 ORDER BY b;
280 DELETE FROM t1 WHERE b=12;
281 SELECT b FROM t1 WHERE a=1 ORDER BY b;
286 DELETE FROM t1 WHERE b=2;
287 SELECT b FROM t1 WHERE a=1 ORDER BY b;
293 INSERT INTO t1 VALUES (1,1);
294 INSERT INTO t1 VALUES (1,2);
295 INSERT INTO t1 VALUES (1,3);
296 INSERT INTO t1 VALUES (1,4);
297 INSERT INTO t1 VALUES (1,5);
298 INSERT INTO t1 VALUES (1,6);
299 INSERT INTO t1 VALUES (1,7);
300 INSERT INTO t1 VALUES (1,8);
301 INSERT INTO t1 VALUES (1,9);
302 INSERT INTO t1 VALUES (2,0);
303 SELECT b FROM t1 WHERE a=1 ORDER BY b;
305 } {1 2 3 4 5 6 7 8 9}
308 execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
310 execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
313 SELECT b FROM t1 WHERE a=1 ORDER BY b;
318 DELETE FROM t1 WHERE b>2;
319 SELECT b FROM t1 WHERE a=1 ORDER BY b;
324 DELETE FROM t1 WHERE b=1;
325 SELECT b FROM t1 WHERE a=1 ORDER BY b;
330 SELECT b FROM t1 ORDER BY b;
333 integrity_check index-10.9
335 # Automatically create an index when we specify a primary key.
346 for {set i 1} {$i<=50} {incr i} {
347 execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
349 set sqlite_search_count 0
350 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
352 integrity_check index-11.2
355 # Numeric strings should compare as if they were numbers. So even if the
356 # strings are not character-by-character the same, if they represent the
357 # same number they should compare equal to one another. Verify that this
358 # is true in indices.
360 # Updated for sqlite3 v3: SQLite will now store these values as numbers
361 # (because the affinity of column a is NUMERIC) so the quirky
362 # representations are not retained. i.e. '+1.0' becomes '1'.
365 CREATE TABLE t4(a NUM,b);
366 INSERT INTO t4 VALUES('0.0',1);
367 INSERT INTO t4 VALUES('0.00',2);
368 INSERT INTO t4 VALUES('abc',3);
369 INSERT INTO t4 VALUES('-1.0',4);
370 INSERT INTO t4 VALUES('+1.0',5);
371 INSERT INTO t4 VALUES('0',6);
372 INSERT INTO t4 VALUES('00000',7);
373 SELECT a FROM t4 ORDER BY b;
375 } {0.0 0.0 abc -1.0 1.0 0 0}
378 SELECT a FROM t4 WHERE a==0 ORDER BY b
383 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
388 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
390 } {0.0 0.0 abc 1.0 0 0}
393 CREATE INDEX t4i1 ON t4(a);
394 SELECT a FROM t4 WHERE a==0 ORDER BY b
399 SELECT a FROM t4 WHERE a<0.5 ORDER BY b
404 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
406 } {0.0 0.0 abc 1.0 0 0}
407 integrity_check index-12.8
409 # Make sure we cannot drop an automatically created index.
419 INSERT INTO t5 VALUES(1,2,3);
424 set ::idxlist [execsql {
425 SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
429 for {set i 0} {$i<[llength $::idxlist]} {incr i} {
430 do_test index-13.3.$i {
432 DROP INDEX '[lindex $::idxlist $i]';
434 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
438 INSERT INTO t5 VALUES('a','b','c');
442 integrity_check index-13.5
444 # Check the sort order of data in an index.
448 CREATE TABLE t6(a,b,c);
449 CREATE INDEX t6i1 ON t6(a,b);
450 INSERT INTO t6 VALUES('','',1);
451 INSERT INTO t6 VALUES('',NULL,2);
452 INSERT INTO t6 VALUES(NULL,'',3);
453 INSERT INTO t6 VALUES('abc',123,4);
454 INSERT INTO t6 VALUES(123,'abc',5);
455 SELECT c FROM t6 ORDER BY a,b;
460 SELECT c FROM t6 WHERE a='';
465 SELECT c FROM t6 WHERE b='';
470 SELECT c FROM t6 WHERE a>'';
475 SELECT c FROM t6 WHERE a>='';
480 SELECT c FROM t6 WHERE a>123;
485 SELECT c FROM t6 WHERE a>=123;
490 SELECT c FROM t6 WHERE a<'abc';
495 SELECT c FROM t6 WHERE a<='abc';
498 do_test index-14.10 {
500 SELECT c FROM t6 WHERE a<='';
503 do_test index-14.11 {
505 SELECT c FROM t6 WHERE a<'';
508 integrity_check index-14.12
518 INSERT INTO t1 VALUES('1.234e5',1);
519 INSERT INTO t1 VALUES('12.33e04',2);
520 INSERT INTO t1 VALUES('12.35E4',3);
521 INSERT INTO t1 VALUES('12.34e',4);
522 INSERT INTO t1 VALUES('12.32e+4',5);
523 INSERT INTO t1 VALUES('12.36E+04',6);
524 INSERT INTO t1 VALUES('12.36E+',7);
525 INSERT INTO t1 VALUES('+123.10000E+0003',8);
526 INSERT INTO t1 VALUES('+',9);
527 INSERT INTO t1 VALUES('+12347.E+02',10);
528 INSERT INTO t1 VALUES('+12347E+02',11);
529 SELECT b FROM t1 ORDER BY a;
531 } {8 5 2 1 3 6 11 9 10 4 7}
532 integrity_check index-15.1
534 # The following tests - index-16.* - test that when a table definition
535 # includes qualifications that specify the same constraint twice only a
536 # single index is generated to enforce the constraint.
538 # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
542 CREATE TABLE t7(c UNIQUE PRIMARY KEY);
543 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
549 CREATE TABLE t7(c UNIQUE PRIMARY KEY);
550 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
556 CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
557 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
563 CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
564 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
570 CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
571 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
575 # Test that automatically create indices are named correctly. The current
576 # convention is: "sqlite_autoindex_<table name>_<integer>"
578 # Then check that it is an error to try to drop any automtically created
583 CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
584 SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
586 } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
589 DROP INDEX sqlite_autoindex_t7_1;
591 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
593 # The following tests ensure that it is not possible to explicitly name
594 # a schema object with a name beginning with "sqlite_". Granted that is a
595 # little outside the focus of this test scripts, but this has got to be
599 CREATE TABLE sqlite_t1(a, b, c);
601 } {1 {object name reserved for internal use: sqlite_t1}}
604 CREATE INDEX sqlite_i1 ON t7(c);
606 } {1 {object name reserved for internal use: sqlite_i1}}
610 CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
612 } {1 {object name reserved for internal use: sqlite_v1}}
614 ifcapable {trigger} {
617 CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
619 } {1 {object name reserved for internal use: sqlite_tr1}}
627 # These tests ensure that if multiple table definition constraints are
628 # implemented by a single indice, the correct ON CONFLICT policy applies.
631 CREATE TABLE t7(a UNIQUE PRIMARY KEY);
632 CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
633 INSERT INTO t7 VALUES(1);
634 INSERT INTO t8 VALUES(1);
640 INSERT INTO t7 VALUES(1);
642 } {1 {column a is not unique}}
647 } {1 {cannot start a transaction within a transaction}}
650 INSERT INTO t8 VALUES(1);
652 } {1 {column a is not unique}}
664 a PRIMARY KEY ON CONFLICT FAIL,
665 UNIQUE(a) ON CONFLICT IGNORE
668 } {1 {conflicting ON CONFLICT clauses specified}}
670 ifcapable {reindex} {
675 integrity_check index-19.8
677 # Drop index with a quoted name. Ticket #695.
681 CREATE INDEX "t6i2" ON t6(c);