2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing the CREATE INDEX statement.
2N/Aset testdir [file dirname $argv0]
2N/A# Create a basic index and verify it is added to sqlite_master
2N/A execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
2N/A execsql {CREATE INDEX index1 ON test1(f1)}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
2N/A WHERE name='index1'}
2N/A} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
2N/A execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
2N/A WHERE name='index1'}
2N/A} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A# Verify that the index dies with the table
2N/A execsql {DROP TABLE test1}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A# Try adding an index to a table that does not exist
2N/A set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
2N/A} {1 {no such table: test1}}
2N/A# Try adding an index on a column of a table where the table
2N/A# exists but the column does not.
2N/A execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
2N/A set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
2N/A} {1 {table test1 has no column named f4}}
2N/A# Try an index with some columns that match and others that do now.
2N/A set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
2N/A execsql {DROP TABLE test1}
2N/A} {1 {table test1 has no column named f4}}
2N/A# Try creating a bunch of indices on the same table
2N/Afor {set i 1} {$i<100} {incr i} {
2N/A lappend r [format index%02d $i]
2N/A execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
2N/A for {set i 1} {$i<100} {incr i} {
2N/A set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
2N/A execsql {SELECT name FROM sqlite_master
2N/A WHERE type='index' AND tbl_name='test1'
2N/A# Verify that all the indices go away when we drop the table.
2N/A execsql {DROP TABLE test1}
2N/A execsql {SELECT name FROM sqlite_master
2N/A WHERE type='index' AND tbl_name='test1'
2N/A# Create a table and insert values into that table. Then create
2N/A# an index on that table. Verify that we can select values
2N/A# from the table correctly using the index.
2N/A# Note that the index names "index9" and "indext" are chosen because
2N/A# they both have the same hash.
2N/A execsql {CREATE TABLE test1(cnt int, power int)}
2N/A for {set i 1} {$i<20} {incr i} {
2N/A execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
2N/A execsql {CREATE INDEX index9 ON test1(cnt)}
2N/A execsql {CREATE INDEX indext ON test1(power)}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A} {index9 indext test1}
2N/A execsql {SELECT cnt FROM test1 WHERE power=4}
2N/A execsql {SELECT cnt FROM test1 WHERE power=1024}
2N/A execsql {SELECT power FROM test1 WHERE cnt=6}
2N/A execsql {DROP INDEX indext}
2N/A execsql {SELECT power FROM test1 WHERE cnt=6}
2N/A execsql {SELECT cnt FROM test1 WHERE power=1024}
2N/A execsql {CREATE INDEX indext ON test1(cnt)}
2N/A execsql {SELECT power FROM test1 WHERE cnt=6}
2N/A execsql {SELECT cnt FROM test1 WHERE power=1024}
2N/A execsql {DROP INDEX index9}
2N/A execsql {SELECT power FROM test1 WHERE cnt=6}
2N/A execsql {SELECT cnt FROM test1 WHERE power=1024}
2N/A execsql {DROP INDEX indext}
2N/A execsql {SELECT power FROM test1 WHERE cnt=6}
2N/A execsql {SELECT cnt FROM test1 WHERE power=1024}
2N/A execsql {DROP TABLE test1}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/Aintegrity_check index-4.14
2N/A# Do not allow indices to be added to sqlite_master
2N/A set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
2N/A} {1 {table sqlite_master may not be indexed}}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2N/A# Do not allow indices with duplicate names to be added
2N/A execsql {CREATE TABLE test1(f1 int, f2 int)}
2N/A execsql {CREATE TABLE test2(g1 real, g2 real)}
2N/A execsql {CREATE INDEX index1 ON test1(f1)}
2N/A set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
2N/A} {1 {index index1 already exists}}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A} {index1 test1 test2}
2N/A set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
2N/A} {1 {there is already a table named test1}}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A} {index1 test1 test2}
2N/A execsql {DROP TABLE test1}
2N/A execsql {DROP TABLE test2}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2N/A CREATE TABLE test1(a,b);
2N/A CREATE INDEX index1 ON test1(a);
2N/A CREATE INDEX index2 ON test1(b);
2N/A CREATE INDEX index3 ON test1(a,b);
2N/A SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
2N/Aintegrity_check index-6.5
2N/A# Create a primary key
2N/A execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
2N/A for {set i 1} {$i<20} {incr i} {
2N/A execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
2N/A execsql {SELECT count(*) FROM test1}
2N/A execsql {SELECT f1 FROM test1 WHERE f2=65536}
2N/A SELECT name FROM sqlite_master
2N/A WHERE type='index' AND tbl_name='test1'
2N/A} {{(test1 autoindex 1)}}
2N/A execsql {DROP table test1}
2N/A execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2N/Aintegrity_check index-7.5
2N/A# Make sure we cannot drop a non-existant index.
2N/A set v [catch {execsql {DROP INDEX index1}} msg]
2N/A} {1 {no such index: index1}}
2N/A# Make sure we don't actually create an index when the EXPLAIN keyword
2N/A execsql {CREATE TABLE tab1(a int)}
2N/A execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
2N/A execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
2N/A execsql {CREATE INDEX idx1 ON tab1(a)}
2N/A execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
2N/Aintegrity_check index-9.3
2N/A# Allow more than one entry with the same key.
2N/A CREATE TABLE t1(a int, b int);
2N/A CREATE INDEX i1 ON t1(a);
2N/A INSERT INTO t1 VALUES(1,2);
2N/A INSERT INTO t1 VALUES(2,4);
2N/A INSERT INTO t1 VALUES(3,8);
2N/A INSERT INTO t1 VALUES(1,12);
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A SELECT b FROM t1 WHERE a=2 ORDER BY b;
2N/A DELETE FROM t1 WHERE b=12;
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A DELETE FROM t1 WHERE b=2;
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A INSERT INTO t1 VALUES (1,1);
2N/A INSERT INTO t1 VALUES (1,2);
2N/A INSERT INTO t1 VALUES (1,3);
2N/A INSERT INTO t1 VALUES (1,4);
2N/A INSERT INTO t1 VALUES (1,5);
2N/A INSERT INTO t1 VALUES (1,6);
2N/A INSERT INTO t1 VALUES (1,7);
2N/A INSERT INTO t1 VALUES (1,8);
2N/A INSERT INTO t1 VALUES (1,9);
2N/A INSERT INTO t1 VALUES (2,0);
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A} {1 2 3 4 5 6 7 8 9}
2N/A DELETE FROM t1 WHERE b IN (2, 4, 6, 8);
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A DELETE FROM t1 WHERE b>2;
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A DELETE FROM t1 WHERE b=1;
2N/A SELECT b FROM t1 WHERE a=1 ORDER BY b;
2N/A SELECT b FROM t1 ORDER BY b;
2N/Aintegrity_check index-10.9
2N/A# Automatically create an index when we specify a primary key.
2N/A for {set i 1} {$i<=50} {incr i} {
2N/A execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
2N/A set sqlite_search_count 0
2N/A concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
2N/Aintegrity_check index-11.2
2N/A# Numeric strings should compare as if they were numbers. So even if the
2N/A# strings are not character-by-character the same, if they represent the
2N/A# same number they should compare equal to one another. Verify that this
2N/A# is true in indices.
2N/A CREATE TABLE t4(a,b);
2N/A INSERT INTO t4 VALUES('0.0',1);
2N/A INSERT INTO t4 VALUES('0.00',2);
2N/A INSERT INTO t4 VALUES('abc',3);
2N/A INSERT INTO t4 VALUES('-1.0',4);
2N/A INSERT INTO t4 VALUES('+1.0',5);
2N/A INSERT INTO t4 VALUES('0',6);
2N/A INSERT INTO t4 VALUES('00000',7);
2N/A SELECT a FROM t4 ORDER BY b;
2N/A} {0.0 0.00 abc -1.0 +1.0 0 00000}
2N/A SELECT a FROM t4 WHERE a==0 ORDER BY b
2N/A SELECT a FROM t4 WHERE a<0.5 ORDER BY b
2N/A} {0.0 0.00 -1.0 0 00000}
2N/A SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
2N/A} {0.0 0.00 abc +1.0 0 00000}
2N/A CREATE INDEX t4i1 ON t4(a);
2N/A SELECT a FROM t4 WHERE a==0 ORDER BY b
2N/A SELECT a FROM t4 WHERE a<0.5 ORDER BY b
2N/A} {0.0 0.00 -1.0 0 00000}
2N/A SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
2N/A} {0.0 0.00 abc +1.0 0 00000}
2N/Aintegrity_check index-12.8
2N/A# Make sure we cannot drop an automatically created index.
2N/A b float PRIMARY KEY,
2N/A INSERT INTO t5 VALUES(1,2,3);
2N/A set ::idxlist [execsql {
2N/A SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
2N/Afor {set i 0} {$i<[llength $::idxlist]} {incr i} {
2N/A do_test index-13.3.$i {
2N/A DROP INDEX '[lindex $::idxlist $i]';
2N/A } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
2N/A INSERT INTO t5 VALUES('a','b','c');
2N/Aintegrity_check index-13.5
2N/A# Check the sort order of data in an index.
2N/A CREATE TABLE t6(a,b,c);
2N/A CREATE INDEX t6i1 ON t6(a,b);
2N/A INSERT INTO t6 VALUES('','',1);
2N/A INSERT INTO t6 VALUES('',NULL,2);
2N/A INSERT INTO t6 VALUES(NULL,'',3);
2N/A INSERT INTO t6 VALUES('abc',123,4);
2N/A INSERT INTO t6 VALUES(123,'abc',5);
2N/A SELECT c FROM t6 ORDER BY a,b;
2N/A SELECT c FROM t6 WHERE a='';
2N/A SELECT c FROM t6 WHERE b='';
2N/A SELECT c FROM t6 WHERE a>'';
2N/A SELECT c FROM t6 WHERE a>='';
2N/A SELECT c FROM t6 WHERE a>123;
2N/A SELECT c FROM t6 WHERE a>=123;
2N/A SELECT c FROM t6 WHERE a<'abc';
2N/A SELECT c FROM t6 WHERE a<='abc';
2N/Ado_test index-14.10 {
2N/A SELECT c FROM t6 WHERE a<='';
2N/Ado_test index-14.11 {
2N/A SELECT c FROM t6 WHERE a<'';
2N/Aintegrity_check index-14.12
2N/A INSERT INTO t1 VALUES('1.234e5',1);
2N/A INSERT INTO t1 VALUES('12.33e04',2);
2N/A INSERT INTO t1 VALUES('12.35E4',3);
2N/A INSERT INTO t1 VALUES('12.34e',4);
2N/A INSERT INTO t1 VALUES('12.32e+4',5);
2N/A INSERT INTO t1 VALUES('12.36E+04',6);
2N/A INSERT INTO t1 VALUES('12.36E+',7);
2N/A INSERT INTO t1 VALUES('+123.10000E+0003',8);
2N/A INSERT INTO t1 VALUES('+',9);
2N/A INSERT INTO t1 VALUES('+12347E+02',11);
2N/A SELECT b FROM t1 ORDER BY a;
2N/A} {8 5 2 1 3 6 11 9 10 4 7}
2N/Aintegrity_check index-15.1
2N/A# Drop index with a quoted name. Ticket #695.
2N/A CREATE INDEX "t6i2" ON t6(c);