} {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.12do_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 viewifcapable {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" blockifcapable {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