From 7bb481fda9ecb134804b49c2ce77ca28f7eea583 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Fri, 30 Mar 2012 20:42:12 -0400 Subject: Imported Upstream version 2.0.3 --- test/index.test | 720 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 720 insertions(+) create mode 100644 test/index.test (limited to 'test/index.test') diff --git a/test/index.test b/test/index.test new file mode 100644 index 0000000..790bed9 --- /dev/null +++ b/test/index.test @@ -0,0 +1,720 @@ +# 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.43 2008/01/16 18:20:42 danielk1977 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 +integrity_check index-3.2.1 +ifcapable {reindex} { + do_test index-3.2.2 { + execsql REINDEX + } {} +} +integrity_check index-3.2.3 + + +# 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 {1<<$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.1.1 { + catchsql {CREATE INDEX [index1] ON test2(g1)} +} {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.1c { + catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)} +} {0 {}} +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 {1<<$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)} + ifcapable {explain} { + 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 { + ifcapable subquery { + execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); } + } else { + execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; } + } + execsql { + 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 2} +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.0 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.0 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); + INSERT INTO t1 VALUES('+.125E+04',12); + INSERT INTO t1 VALUES('-.125E+04',13); + INSERT INTO t1 VALUES('.125E+0',14); + INSERT INTO t1 VALUES('.125',15); + SELECT b FROM t1 ORDER BY a, b; + } +} {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7} +do_test index-15.3 { + execsql { + SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b; + } +} {1 2 3 5 6 8 10 11 12 13 14 15} +integrity_check index-15.4 + +# 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}} +do_test index-17.3 { + catchsql { + DROP INDEX IF EXISTS sqlite_autoindex_t7_1; + } +} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} +do_test index-17.4 { + catchsql { + DROP INDEX IF EXISTS no_such_index; + } +} {0 {}} + + +# 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}} +ifcapable view { +do_test index-18.3 { + catchsql { + CREATE VIEW sqlite_v1 AS SELECT * FROM t7; + } +} {1 {object name reserved for internal use: sqlite_v1}} +} ;# ifcapable view +ifcapable {trigger} { + 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. +ifcapable conflict { + 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}} +} ; # end of "ifcapable conflict" block + +ifcapable {reindex} { + do_test index-19.7 { + execsql REINDEX + } {} +} +integrity_check index-19.8 + +# 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 -- cgit v1.2.3