# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE INDEX statement. # # $Id: index.test,v 1.31 2004/07/20 00:52:44 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic index and verify it is added to sqlite_master # do_test index-1.1 { execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} execsql {CREATE INDEX index1 ON test1(f1)} execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {index1 test1} do_test index-1.1b { execsql {SELECT name, sql, tbl_name, type FROM sqlite_master WHERE name='index1'} } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} do_test index-1.1c { db close sqlite3 db test.db execsql {SELECT name, sql, tbl_name, type FROM sqlite_master WHERE name='index1'} } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} do_test index-1.1d { db close sqlite3 db test.db execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {index1 test1} # Verify that the index dies with the table # do_test index-1.2 { execsql {DROP TABLE test1} execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {} # Try adding an index to a table that does not exist # do_test index-2.1 { set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg] lappend v $msg } {1 {no such table: main.test1}} # Try adding an index on a column of a table where the table # exists but the column does not. # do_test index-2.1 { execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg] lappend v $msg } {1 {table test1 has no column named f4}} # Try an index with some columns that match and others that do now. # do_test index-2.2 { set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg] execsql {DROP TABLE test1} lappend v $msg } {1 {table test1 has no column named f4}} # Try creating a bunch of indices on the same table # set r {} for {set i 1} {$i<100} {incr i} { lappend r [format index%02d $i] } do_test index-3.1 { execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)} for {set i 1} {$i<100} {incr i} { set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])" execsql $sql } execsql {SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='test1' ORDER BY name} } $r # Verify that all the indices go away when we drop the table. # do_test index-3.3 { execsql {DROP TABLE test1} execsql {SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='test1' ORDER BY name} } {} # Create a table and insert values into that table. Then create # an index on that table. Verify that we can select values # from the table correctly using the index. # # Note that the index names "index9" and "indext" are chosen because # they both have the same hash. # do_test index-4.1 { execsql {CREATE TABLE test1(cnt int, power int)} for {set i 1} {$i<20} {incr i} { execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" } execsql {CREATE INDEX index9 ON test1(cnt)} execsql {CREATE INDEX indext ON test1(power)} execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {index9 indext test1} do_test index-4.2 { execsql {SELECT cnt FROM test1 WHERE power=4} } {2} do_test index-4.3 { execsql {SELECT cnt FROM test1 WHERE power=1024} } {10} do_test index-4.4 { execsql {SELECT power FROM test1 WHERE cnt=6} } {64} do_test index-4.5 { execsql {DROP INDEX indext} execsql {SELECT power FROM test1 WHERE cnt=6} } {64} do_test index-4.6 { execsql {SELECT cnt FROM test1 WHERE power=1024} } {10} do_test index-4.7 { execsql {CREATE INDEX indext ON test1(cnt)} execsql {SELECT power FROM test1 WHERE cnt=6} } {64} do_test index-4.8 { execsql {SELECT cnt FROM test1 WHERE power=1024} } {10} do_test index-4.9 { execsql {DROP INDEX index9} execsql {SELECT power FROM test1 WHERE cnt=6} } {64} do_test index-4.10 { execsql {SELECT cnt FROM test1 WHERE power=1024} } {10} do_test index-4.11 { execsql {DROP INDEX indext} execsql {SELECT power FROM test1 WHERE cnt=6} } {64} do_test index-4.12 { execsql {SELECT cnt FROM test1 WHERE power=1024} } {10} do_test index-4.13 { execsql {DROP TABLE test1} execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {} integrity_check index-4.14 # Do not allow indices to be added to sqlite_master # do_test index-5.1 { set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg] lappend v $msg } {1 {table sqlite_master may not be indexed}} do_test index-5.2 { execsql {SELECT name FROM sqlite_master WHERE type!='meta'} } {} # Do not allow indices with duplicate names to be added # do_test index-6.1 { execsql {CREATE TABLE test1(f1 int, f2 int)} execsql {CREATE TABLE test2(g1 real, g2 real)} execsql {CREATE INDEX index1 ON test1(f1)} set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg] lappend v $msg } {1 {index index1 already exists}} do_test index-6.1b { execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {index1 test1 test2} do_test index-6.2 { set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg] lappend v $msg } {1 {there is already a table named test1}} do_test index-6.2b { execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {index1 test1 test2} do_test index-6.3 { execsql {DROP TABLE test1} execsql {DROP TABLE test2} execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} } {} do_test index-6.4 { execsql { CREATE TABLE test1(a,b); CREATE INDEX index1 ON test1(a); CREATE INDEX index2 ON test1(b); CREATE INDEX index3 ON test1(a,b); DROP TABLE test1; SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name; } } {} integrity_check index-6.5 # Create a primary key # do_test index-7.1 { execsql {CREATE TABLE test1(f1 int, f2 int primary key)} for {set i 1} {$i<20} {incr i} { execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" } execsql {SELECT count(*) FROM test1} } {19} do_test index-7.2 { execsql {SELECT f1 FROM test1 WHERE f2=65536} } {16} do_test index-7.3 { execsql { SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='test1' } } {sqlite_autoindex_test1_1} do_test index-7.4 { execsql {DROP table test1} execsql {SELECT name FROM sqlite_master WHERE type!='meta'} } {} integrity_check index-7.5 # Make sure we cannot drop a non-existant index. # do_test index-8.1 { set v [catch {execsql {DROP INDEX index1}} msg] lappend v $msg } {1 {no such index: index1}} # Make sure we don't actually create an index when the EXPLAIN keyword # is used. # do_test index-9.1 { execsql {CREATE TABLE tab1(a int)} execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)} execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'} } {tab1} do_test index-9.2 { execsql {CREATE INDEX idx1 ON tab1(a)} execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name} } {idx1 tab1} integrity_check index-9.3 # Allow more than one entry with the same key. # do_test index-10.0 { execsql { CREATE TABLE t1(a int, b int); CREATE INDEX i1 ON t1(a); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(2,4); INSERT INTO t1 VALUES(3,8); INSERT INTO t1 VALUES(1,12); SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {2 12} do_test index-10.1 { execsql { SELECT b FROM t1 WHERE a=2 ORDER BY b; } } {4} do_test index-10.2 { execsql { DELETE FROM t1 WHERE b=12; SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {2} do_test index-10.3 { execsql { DELETE FROM t1 WHERE b=2; SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {} do_test index-10.4 { execsql { DELETE FROM t1; INSERT INTO t1 VALUES (1,1); INSERT INTO t1 VALUES (1,2); INSERT INTO t1 VALUES (1,3); INSERT INTO t1 VALUES (1,4); INSERT INTO t1 VALUES (1,5); INSERT INTO t1 VALUES (1,6); INSERT INTO t1 VALUES (1,7); INSERT INTO t1 VALUES (1,8); INSERT INTO t1 VALUES (1,9); INSERT INTO t1 VALUES (2,0); SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {1 2 3 4 5 6 7 8 9} do_test index-10.5 { execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {1 3 5 7 9} do_test index-10.6 { execsql { DELETE FROM t1 WHERE b>2; SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {1} do_test index-10.7 { execsql { DELETE FROM t1 WHERE b=1; SELECT b FROM t1 WHERE a=1 ORDER BY b; } } {} do_test index-10.8 { execsql { SELECT b FROM t1 ORDER BY b; } } {0} integrity_check index-10.9 # Automatically create an index when we specify a primary key. # do_test index-11.1 { execsql { CREATE TABLE t3( a text, b int, c float, PRIMARY KEY(b) ); } for {set i 1} {$i<=50} {incr i} { execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)" } set sqlite_search_count 0 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count } {0.1 3} integrity_check index-11.2 # Numeric strings should compare as if they were numbers. So even if the # strings are not character-by-character the same, if they represent the # same number they should compare equal to one another. Verify that this # is true in indices. # # Updated for sqlite3 v3: SQLite will now store these values as numbers # (because the affinity of column a is NUMERIC) so the quirky # representations are not retained. i.e. '+1.0' becomes '1'. do_test index-12.1 { execsql { CREATE TABLE t4(a NUM,b); INSERT INTO t4 VALUES('0.0',1); INSERT INTO t4 VALUES('0.00',2); INSERT INTO t4 VALUES('abc',3); INSERT INTO t4 VALUES('-1.0',4); INSERT INTO t4 VALUES('+1.0',5); INSERT INTO t4 VALUES('0',6); INSERT INTO t4 VALUES('00000',7); SELECT a FROM t4 ORDER BY b; } } {0 0 abc -1 1 0 0} do_test index-12.2 { execsql { SELECT a FROM t4 WHERE a==0 ORDER BY b } } {0 0 0 0} do_test index-12.3 { execsql { SELECT a FROM t4 WHERE a<0.5 ORDER BY b } } {0 0 -1 0 0} do_test index-12.4 { execsql { SELECT a FROM t4 WHERE a>-0.5 ORDER BY b } } {0 0 abc 1 0 0} do_test index-12.5 { execsql { CREATE INDEX t4i1 ON t4(a); SELECT a FROM t4 WHERE a==0 ORDER BY b } } {0 0 0 0} do_test index-12.6 { execsql { SELECT a FROM t4 WHERE a<0.5 ORDER BY b } } {0 0 -1 0 0} do_test index-12.7 { execsql { SELECT a FROM t4 WHERE a>-0.5 ORDER BY b } } {0 0 abc 1 0 0} integrity_check index-12.8 # Make sure we cannot drop an automatically created index. # do_test index-13.1 { execsql { CREATE TABLE t5( a int UNIQUE, b float PRIMARY KEY, c varchar(10), UNIQUE(a,c) ); INSERT INTO t5 VALUES(1,2,3); SELECT * FROM t5; } } {1 2 3} do_test index-13.2 { set ::idxlist [execsql { SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5"; }] llength $::idxlist } {3} for {set i 0} {$i<[llength $::idxlist]} {incr i} { do_test index-13.3.$i { catchsql " DROP INDEX '[lindex $::idxlist $i]'; " } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} } do_test index-13.4 { execsql { INSERT INTO t5 VALUES('a','b','c'); SELECT * FROM t5; } } {1 2 3 a b c} integrity_check index-13.5 # Check the sort order of data in an index. # do_test index-14.1 { execsql { CREATE TABLE t6(a,b,c); CREATE INDEX t6i1 ON t6(a,b); INSERT INTO t6 VALUES('','',1); INSERT INTO t6 VALUES('',NULL,2); INSERT INTO t6 VALUES(NULL,'',3); INSERT INTO t6 VALUES('abc',123,4); INSERT INTO t6 VALUES(123,'abc',5); SELECT c FROM t6 ORDER BY a,b; } } {3 5 2 1 4} do_test index-14.2 { execsql { SELECT c FROM t6 WHERE a=''; } } {2 1} do_test index-14.3 { execsql { SELECT c FROM t6 WHERE b=''; } } {1 3} do_test index-14.4 { execsql { SELECT c FROM t6 WHERE a>''; } } {4} do_test index-14.5 { execsql { SELECT c FROM t6 WHERE a>=''; } } {2 1 4} do_test index-14.6 { execsql { SELECT c FROM t6 WHERE a>123; } } {2 1 4} do_test index-14.7 { execsql { SELECT c FROM t6 WHERE a>=123; } } {5 2 1 4} do_test index-14.8 { execsql { SELECT c FROM t6 WHERE a<'abc'; } } {5 2 1} do_test index-14.9 { execsql { SELECT c FROM t6 WHERE a<='abc'; } } {5 2 1 4} do_test index-14.10 { execsql { SELECT c FROM t6 WHERE a<=''; } } {5 2 1} do_test index-14.11 { execsql { SELECT c FROM t6 WHERE a<''; } } {5} integrity_check index-14.12 do_test index-15.1 { execsql { DELETE FROM t1; SELECT * FROM t1; } } {} do_test index-15.2 { execsql { INSERT INTO t1 VALUES('1.234e5',1); INSERT INTO t1 VALUES('12.33e04',2); INSERT INTO t1 VALUES('12.35E4',3); INSERT INTO t1 VALUES('12.34e',4); INSERT INTO t1 VALUES('12.32e+4',5); INSERT INTO t1 VALUES('12.36E+04',6); INSERT INTO t1 VALUES('12.36E+',7); INSERT INTO t1 VALUES('+123.10000E+0003',8); INSERT INTO t1 VALUES('+',9); INSERT INTO t1 VALUES('+12347.E+02',10); INSERT INTO t1 VALUES('+12347E+02',11); SELECT b FROM t1 ORDER BY a; } } {8 5 2 1 3 6 11 9 10 4 7} integrity_check index-15.1 # The following tests - index-16.* - test that when a table definition # includes qualifications that specify the same constraint twice only a # single index is generated to enforce the constraint. # # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );" # do_test index-16.1 { execsql { CREATE TABLE t7(c UNIQUE PRIMARY KEY); SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {1} do_test index-16.2 { execsql { DROP TABLE t7; CREATE TABLE t7(c UNIQUE PRIMARY KEY); SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {1} do_test index-16.3 { execsql { DROP TABLE t7; CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) ); SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {1} do_test index-16.4 { execsql { DROP TABLE t7; CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) ); SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {1} do_test index-16.5 { execsql { DROP TABLE t7; CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) ); SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {2} # Test that automatically create indices are named correctly. The current # convention is: "sqlite_autoindex__" # # Then check that it is an error to try to drop any automtically created # indices. do_test index-17.1 { execsql { DROP TABLE t7; CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) ); SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index'; } } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3} do_test index-17.2 { catchsql { DROP INDEX sqlite_autoindex_t7_1; } } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} # The following tests ensure that it is not possible to explicitly name # a schema object with a name beginning with "sqlite_". Granted that is a # little outside the focus of this test scripts, but this has got to be # tested somewhere. do_test index-18.1 { catchsql { CREATE TABLE sqlite_t1(a, b, c); } } {1 {object name reserved for internal use: sqlite_t1}} do_test index-18.2 { catchsql { CREATE INDEX sqlite_i1 ON t7(c); } } {1 {object name reserved for internal use: sqlite_i1}} do_test index-18.3 { catchsql { CREATE VIEW sqlite_v1 AS SELECT * FROM t7; } } {1 {object name reserved for internal use: sqlite_v1}} do_test index-18.4 { catchsql { CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END; } } {1 {object name reserved for internal use: sqlite_tr1}} do_test index-18.5 { execsql { DROP TABLE t7; } } {} # These tests ensure that if multiple table definition constraints are # implemented by a single indice, the correct ON CONFLICT policy applies. do_test index-19.1 { execsql { CREATE TABLE t7(a UNIQUE PRIMARY KEY); CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK); INSERT INTO t7 VALUES(1); INSERT INTO t8 VALUES(1); } } {} do_test index-19.2 { catchsql { BEGIN; INSERT INTO t7 VALUES(1); } } {1 {column a is not unique}} do_test index-19.3 { catchsql { BEGIN; } } {1 {cannot start a transaction within a transaction}} do_test index-19.4 { catchsql { INSERT INTO t8 VALUES(1); } } {1 {column a is not unique}} do_test index-19.5 { catchsql { BEGIN; COMMIT; } } {0 {}} do_test index-19.6 { catchsql { DROP TABLE t7; DROP TABLE t8; CREATE TABLE t7( a PRIMARY KEY ON CONFLICT FAIL, UNIQUE(a) ON CONFLICT IGNORE ); } } {1 {conflicting ON CONFLICT clauses specified}} # Drop index with a quoted name. Ticket #695. # do_test index-20.1 { execsql { CREATE INDEX "t6i2" ON t6(c); DROP INDEX "t6i2"; } } {} do_test index-20.2 { execsql { DROP INDEX "t6i1"; } } {} finish_test