# 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.42 2006/03/29 00:24:07 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}} $rintegrity_check index-3.2.1ifcapable {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 {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.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 {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)}  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