# 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 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.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);

    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_<table name>_<integer>"

#

# 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