AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. The12# focus of this file is testing the CREATE INDEX statement.13#14# $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Create a basic index and verify it is added to sqlite_master20#21do_test index-1.1 {22 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}23 execsql {CREATE INDEX index1 ON test1(f1)}24 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}25} {index1 test1}26do_test index-1.1b {27 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 28 WHERE name='index1'}29} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}30do_test index-1.1c {31 db close32 sqlite3 db test.db33 execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 34 WHERE name='index1'}35} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}36do_test index-1.1d {37 db close38 sqlite3 db test.db39 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}40} {index1 test1}41 42# Verify that the index dies with the table43#44do_test index-1.2 {45 execsql {DROP TABLE test1}46 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}47} {}48 49# Try adding an index to a table that does not exist50#51do_test index-2.1 {52 set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]53 lappend v $msg54} {1 {no such table: main.test1}}55 56# Try adding an index on a column of a table where the table57# exists but the column does not.58#59do_test index-2.1b {60 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}61 set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]62 lappend v $msg63} {1 {no such column: f4}}64 65# Try an index with some columns that match and others that do now.66#67do_test index-2.2 {68 set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]69 execsql {DROP TABLE test1}70 lappend v $msg71} {1 {no such column: f4}}72 73# Try creating a bunch of indices on the same table74#75set r {}76for {set i 1} {$i<100} {incr i} {77 lappend r [format index%02d $i]78}79do_test index-3.1 {80 execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}81 for {set i 1} {$i<100} {incr i} {82 set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"83 execsql $sql84 }85 execsql {SELECT name FROM sqlite_master 86 WHERE type='index' AND tbl_name='test1'87 ORDER BY name}88} $r89integrity_check index-3.2.190ifcapable {reindex} {91 do_test index-3.2.2 {92 execsql REINDEX93 } {}94}95integrity_check index-3.2.396 97 98# Verify that all the indices go away when we drop the table.99#100do_test index-3.3 {101 execsql {DROP TABLE test1}102 execsql {SELECT name FROM sqlite_master 103 WHERE type='index' AND tbl_name='test1'104 ORDER BY name}105} {}106 107# Create a table and insert values into that table. Then create108# an index on that table. Verify that we can select values109# from the table correctly using the index.110#111# Note that the index names "index9" and "indext" are chosen because112# they both have the same hash.113#114do_test index-4.1 {115 execsql {CREATE TABLE test1(cnt int, power int)}116 for {set i 1} {$i<20} {incr i} {117 execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"118 }119 execsql {CREATE INDEX index9 ON test1(cnt)}120 execsql {CREATE INDEX indext ON test1(power)}121 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}122} {index9 indext test1}123do_test index-4.2 {124 execsql {SELECT cnt FROM test1 WHERE power=4}125} {2}126do_test index-4.3 {127 execsql {SELECT cnt FROM test1 WHERE power=1024}128} {10}129do_test index-4.4 {130 execsql {SELECT power FROM test1 WHERE cnt=6}131} {64}132do_test index-4.5 {133 execsql {DROP INDEX indext}134 execsql {SELECT power FROM test1 WHERE cnt=6}135} {64}136do_test index-4.6 {137 execsql {SELECT cnt FROM test1 WHERE power=1024}138} {10}139do_test index-4.7 {140 execsql {CREATE INDEX indext ON test1(cnt)}141 execsql {SELECT power FROM test1 WHERE cnt=6}142} {64}143do_test index-4.8 {144 execsql {SELECT cnt FROM test1 WHERE power=1024}145} {10}146do_test index-4.9 {147 execsql {DROP INDEX index9}148 execsql {SELECT power FROM test1 WHERE cnt=6}149} {64}150do_test index-4.10 {151 execsql {SELECT cnt FROM test1 WHERE power=1024}152} {10}153do_test index-4.11 {154 execsql {DROP INDEX indext}155 execsql {SELECT power FROM test1 WHERE cnt=6}156} {64}157do_test index-4.12 {158 execsql {SELECT cnt FROM test1 WHERE power=1024}159} {10}160do_test index-4.13 {161 execsql {DROP TABLE test1}162 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}163} {}164integrity_check index-4.14165 166# Do not allow indices to be added to sqlite_master167#168do_test index-5.1 {169 set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]170 lappend v $msg171} {1 {table sqlite_master may not be indexed}}172do_test index-5.2 {173 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}174} {}175 176# Do not allow indices with duplicate names to be added177#178do_test index-6.1 {179 execsql {CREATE TABLE test1(f1 int, f2 int)}180 execsql {CREATE TABLE test2(g1 real, g2 real)}181 execsql {CREATE INDEX index1 ON test1(f1)}182 set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]183 lappend v $msg184} {1 {index index1 already exists}}185do_test index-6.1.1 {186 catchsql {CREATE INDEX [index1] ON test2(g1)}187} {1 {index index1 already exists}}188do_test index-6.1b {189 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}190} {index1 test1 test2}191do_test index-6.1c {192 catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}193} {0 {}}194do_test index-6.2 {195 set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]196 lappend v $msg197} {1 {there is already a table named test1}}198do_test index-6.2b {199 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}200} {index1 test1 test2}201do_test index-6.3 {202 execsql {DROP TABLE test1}203 execsql {DROP TABLE test2}204 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}205} {}206do_test index-6.4 {207 execsql {208 CREATE TABLE test1(a,b);209 CREATE INDEX index1 ON test1(a);210 CREATE INDEX index2 ON test1(b);211 CREATE INDEX index3 ON test1(a,b);212 DROP TABLE test1;213 SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;214 }215} {}216integrity_check index-6.5217 218 219# Create a primary key220#221do_test index-7.1 {222 execsql {CREATE TABLE test1(f1 int, f2 int primary key)}223 for {set i 1} {$i<20} {incr i} {224 execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"225 }226 execsql {SELECT count(*) FROM test1}227} {19}228do_test index-7.2 {229 execsql {SELECT f1 FROM test1 WHERE f2=65536}230} {16}231do_test index-7.3 {232 execsql {233 SELECT name FROM sqlite_master 234 WHERE type='index' AND tbl_name='test1'235 }236} {sqlite_autoindex_test1_1}237do_test index-7.4 {238 execsql {DROP table test1}239 execsql {SELECT name FROM sqlite_master WHERE type!='meta'}240} {}241integrity_check index-7.5242 243# Make sure we cannot drop a non-existant index.244#245do_test index-8.1 {246 set v [catch {execsql {DROP INDEX index1}} msg]247 lappend v $msg248} {1 {no such index: index1}}249 250# Make sure we don't actually create an index when the EXPLAIN keyword251# is used.252#253do_test index-9.1 {254 execsql {CREATE TABLE tab1(a int)}255 ifcapable {explain} {256 execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}257 }258 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}259} {tab1}260do_test index-9.2 {261 execsql {CREATE INDEX idx1 ON tab1(a)}262 execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}263} {idx1 tab1}264integrity_check index-9.3265 266# Allow more than one entry with the same key.267#268do_test index-10.0 {269 execsql {270 CREATE TABLE t1(a int, b int);271 CREATE INDEX i1 ON t1(a);272 INSERT INTO t1 VALUES(1,2);273 INSERT INTO t1 VALUES(2,4);274 INSERT INTO t1 VALUES(3,8);275 INSERT INTO t1 VALUES(1,12);276 SELECT b FROM t1 WHERE a=1 ORDER BY b;277 }278} {2 12}279do_test index-10.1 {280 execsql {281 SELECT b FROM t1 WHERE a=2 ORDER BY b;282 }283} {4}284do_test index-10.2 {285 execsql {286 DELETE FROM t1 WHERE b=12;287 SELECT b FROM t1 WHERE a=1 ORDER BY b;288 }289} {2}290do_test index-10.3 {291 execsql {292 DELETE FROM t1 WHERE b=2;293 SELECT b FROM t1 WHERE a=1 ORDER BY b;294 }295} {}296do_test index-10.4 {297 execsql {298 DELETE FROM t1;299 INSERT INTO t1 VALUES (1,1);300 INSERT INTO t1 VALUES (1,2);301 INSERT INTO t1 VALUES (1,3);302 INSERT INTO t1 VALUES (1,4);303 INSERT INTO t1 VALUES (1,5);304 INSERT INTO t1 VALUES (1,6);305 INSERT INTO t1 VALUES (1,7);306 INSERT INTO t1 VALUES (1,8);307 INSERT INTO t1 VALUES (1,9);308 INSERT INTO t1 VALUES (2,0);309 SELECT b FROM t1 WHERE a=1 ORDER BY b;310 }311} {1 2 3 4 5 6 7 8 9}312do_test index-10.5 {313 ifcapable subquery {314 execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }315 } else {316 execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }317 }318 execsql {319 SELECT b FROM t1 WHERE a=1 ORDER BY b;320 }321} {1 3 5 7 9}322do_test index-10.6 {323 execsql {324 DELETE FROM t1 WHERE b>2;325 SELECT b FROM t1 WHERE a=1 ORDER BY b;326 }327} {1}328do_test index-10.7 {329 execsql {330 DELETE FROM t1 WHERE b=1;331 SELECT b FROM t1 WHERE a=1 ORDER BY b;332 }333} {}334do_test index-10.8 {335 execsql {336 SELECT b FROM t1 ORDER BY b;337 }338} {0}339integrity_check index-10.9340 341# Automatically create an index when we specify a primary key.342#343do_test index-11.1 {344 execsql {345 CREATE TABLE t3(346 a text,347 b int,348 c float,349 PRIMARY KEY(b)350 );351 }352 for {set i 1} {$i<=50} {incr i} {353 execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"354 }355 set sqlite_search_count 0356 concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count357} {0.1 2}358integrity_check index-11.2359 360 361# Numeric strings should compare as if they were numbers. So even if the362# strings are not character-by-character the same, if they represent the363# same number they should compare equal to one another. Verify that this364# is true in indices.365#366# Updated for sqlite3 v3: SQLite will now store these values as numbers367# (because the affinity of column a is NUMERIC) so the quirky368# representations are not retained. i.e. '+1.0' becomes '1'.369do_test index-12.1 {370 execsql {371 CREATE TABLE t4(a NUM,b);372 INSERT INTO t4 VALUES('0.0',1);373 INSERT INTO t4 VALUES('0.00',2);374 INSERT INTO t4 VALUES('abc',3);375 INSERT INTO t4 VALUES('-1.0',4);376 INSERT INTO t4 VALUES('+1.0',5);377 INSERT INTO t4 VALUES('0',6);378 INSERT INTO t4 VALUES('00000',7);379 SELECT a FROM t4 ORDER BY b;380 }381} {0 0 abc -1 1 0 0}382do_test index-12.2 {383 execsql {384 SELECT a FROM t4 WHERE a==0 ORDER BY b385 }386} {0 0 0 0}387do_test index-12.3 {388 execsql {389 SELECT a FROM t4 WHERE a<0.5 ORDER BY b390 }391} {0 0 -1 0 0}392do_test index-12.4 {393 execsql {394 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b395 }396} {0 0 abc 1 0 0}397do_test index-12.5 {398 execsql {399 CREATE INDEX t4i1 ON t4(a);400 SELECT a FROM t4 WHERE a==0 ORDER BY b401 }402} {0 0 0 0}403do_test index-12.6 {404 execsql {405 SELECT a FROM t4 WHERE a<0.5 ORDER BY b406 }407} {0 0 -1 0 0}408do_test index-12.7 {409 execsql {410 SELECT a FROM t4 WHERE a>-0.5 ORDER BY b411 }412} {0 0 abc 1 0 0}413integrity_check index-12.8414 415# Make sure we cannot drop an automatically created index.416#417do_test index-13.1 {418 execsql {419 CREATE TABLE t5(420 a int UNIQUE,421 b float PRIMARY KEY,422 c varchar(10),423 UNIQUE(a,c)424 );425 INSERT INTO t5 VALUES(1,2,3);426 SELECT * FROM t5;427 }428} {1 2.0 3}429do_test index-13.2 {430 set ::idxlist [execsql {431 SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='t5';432 }]433 llength $::idxlist434} {3}435for {set i 0} {$i<[llength $::idxlist]} {incr i} {436 do_test index-13.3.$i {437 catchsql "438 DROP INDEX '[lindex $::idxlist $i]';439 "440 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}441}442do_test index-13.4 {443 execsql {444 INSERT INTO t5 VALUES('a','b','c');445 SELECT * FROM t5;446 }447} {1 2.0 3 a b c}448integrity_check index-13.5449 450# Check the sort order of data in an index.451#452do_test index-14.1 {453 execsql {454 CREATE TABLE t6(a,b,c);455 CREATE INDEX t6i1 ON t6(a,b);456 INSERT INTO t6 VALUES('','',1);457 INSERT INTO t6 VALUES('',NULL,2);458 INSERT INTO t6 VALUES(NULL,'',3);459 INSERT INTO t6 VALUES('abc',123,4);460 INSERT INTO t6 VALUES(123,'abc',5);461 SELECT c FROM t6 ORDER BY a,b;462 }463} {3 5 2 1 4}464do_test index-14.2 {465 execsql {466 SELECT c FROM t6 WHERE a='';467 }468} {2 1}469do_test index-14.3 {470 execsql {471 SELECT c FROM t6 WHERE b='';472 }473} {1 3}474do_test index-14.4 {475 execsql {476 SELECT c FROM t6 WHERE a>'';477 }478} {4}479do_test index-14.5 {480 execsql {481 SELECT c FROM t6 WHERE a>='';482 }483} {2 1 4}484do_test index-14.6 {485 execsql {486 SELECT c FROM t6 WHERE a>123;487 }488} {2 1 4}489do_test index-14.7 {490 execsql {491 SELECT c FROM t6 WHERE a>=123;492 }493} {5 2 1 4}494do_test index-14.8 {495 execsql {496 SELECT c FROM t6 WHERE a<'abc';497 }498} {5 2 1}499do_test index-14.9 {500 execsql {501 SELECT c FROM t6 WHERE a<='abc';502 }503} {5 2 1 4}504do_test index-14.10 {505 execsql {506 SELECT c FROM t6 WHERE a<='';507 }508} {5 2 1}509do_test index-14.11 {510 execsql {511 SELECT c FROM t6 WHERE a<'';512 }513} {5}514integrity_check index-14.12515 516do_test index-15.1 {517 execsql {518 DELETE FROM t1;519 SELECT * FROM t1;520 }521} {}522do_test index-15.2 {523 execsql {524 INSERT INTO t1 VALUES('1.234e5',1);525 INSERT INTO t1 VALUES('12.33e04',2);526 INSERT INTO t1 VALUES('12.35E4',3);527 INSERT INTO t1 VALUES('12.34e',4);528 INSERT INTO t1 VALUES('12.32e+4',5);529 INSERT INTO t1 VALUES('12.36E+04',6);530 INSERT INTO t1 VALUES('12.36E+',7);531 INSERT INTO t1 VALUES('+123.10000E+0003',8);532 INSERT INTO t1 VALUES('+',9);533 INSERT INTO t1 VALUES('+12347.E+02',10);534 INSERT INTO t1 VALUES('+12347E+02',11);535 INSERT INTO t1 VALUES('+.125E+04',12);536 INSERT INTO t1 VALUES('-.125E+04',13);537 INSERT INTO t1 VALUES('.125E+0',14);538 INSERT INTO t1 VALUES('.125',15);539 SELECT b FROM t1 ORDER BY a, b;540 }541} {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7}542do_test index-15.3 {543 execsql {544 SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b;545 }546} {1 2 3 5 6 8 10 11 12 13 14 15}547integrity_check index-15.4548 549# The following tests - index-16.* - test that when a table definition550# includes qualifications that specify the same constraint twice only a551# single index is generated to enforce the constraint.552#553# For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"554#555do_test index-16.1 {556 execsql {557 CREATE TABLE t7(c UNIQUE PRIMARY KEY);558 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';559 }560} {1}561do_test index-16.2 {562 execsql {563 DROP TABLE t7;564 CREATE TABLE t7(c UNIQUE PRIMARY KEY);565 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';566 }567} {1}568do_test index-16.3 {569 execsql {570 DROP TABLE t7;571 CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );572 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';573 }574} {1}575do_test index-16.4 {576 execsql {577 DROP TABLE t7;578 CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );579 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';580 }581} {1}582do_test index-16.5 {583 execsql {584 DROP TABLE t7;585 CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );586 SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';587 }588} {2}589 590# Test that automatically create indices are named correctly. The current591# convention is: "sqlite_autoindex_<table name>_<integer>"592#593# Then check that it is an error to try to drop any automtically created594# indices.595do_test index-17.1 {596 execsql {597 DROP TABLE t7;598 CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );599 SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';600 }601} {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}602do_test index-17.2 {603 catchsql {604 DROP INDEX sqlite_autoindex_t7_1;605 }606} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}607do_test index-17.3 {608 catchsql {609 DROP INDEX IF EXISTS sqlite_autoindex_t7_1;610 }611} {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}612do_test index-17.4 {613 catchsql {614 DROP INDEX IF EXISTS no_such_index;615 }616} {0 {}}617 618 619# The following tests ensure that it is not possible to explicitly name620# a schema object with a name beginning with "sqlite_". Granted that is a621# little outside the focus of this test scripts, but this has got to be622# tested somewhere.623do_test index-18.1 {624 catchsql {625 CREATE TABLE sqlite_t1(a, b, c);626 }627} {1 {object name reserved for internal use: sqlite_t1}}628do_test index-18.1.2 {629 catchsql {630 CREATE TABLE sqlite_t1(a, b, c);631 }632} {1 {object name reserved for internal use: sqlite_t1}}633sqlite3_db_config db DEFENSIVE 0634do_test index-18.2 {635 catchsql {636 CREATE INDEX sqlite_i1 ON t7(c);637 }638} {1 {object name reserved for internal use: sqlite_i1}}639ifcapable view {640do_test index-18.3 {641 catchsql {642 CREATE VIEW sqlite_v1 AS SELECT * FROM t7;643 }644} {1 {object name reserved for internal use: sqlite_v1}}645} ;# ifcapable view646ifcapable {trigger} {647 do_test index-18.4 {648 catchsql {649 CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;650 }651 } {1 {object name reserved for internal use: sqlite_tr1}}652}653do_test index-18.5 {654 execsql {655 DROP TABLE t7;656 }657} {}658 659# These tests ensure that if multiple table definition constraints are660# implemented by a single indice, the correct ON CONFLICT policy applies.661ifcapable conflict {662 do_test index-19.1 {663 execsql {664 CREATE TABLE t7(a UNIQUE PRIMARY KEY);665 CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);666 INSERT INTO t7 VALUES(1);667 INSERT INTO t8 VALUES(1);668 }669 } {}670 do_test index-19.2 {671 catchsql {672 BEGIN;673 INSERT INTO t7 VALUES(1);674 }675 } {1 {UNIQUE constraint failed: t7.a}}676 do_test index-19.3 {677 catchsql {678 BEGIN;679 }680 } {1 {cannot start a transaction within a transaction}}681 do_test index-19.4 {682 catchsql {683 INSERT INTO t8 VALUES(1);684 }685 } {1 {UNIQUE constraint failed: t8.a}}686 do_test index-19.5 {687 catchsql {688 BEGIN;689 COMMIT;690 }691 } {0 {}}692 do_test index-19.6 {693 catchsql {694 DROP TABLE t7;695 DROP TABLE t8;696 CREATE TABLE t7(697 a PRIMARY KEY ON CONFLICT FAIL, 698 UNIQUE(a) ON CONFLICT IGNORE699 );700 }701 } {1 {conflicting ON CONFLICT clauses specified}}702} ; # end of "ifcapable conflict" block703 704ifcapable {reindex} {705 do_test index-19.7 {706 execsql REINDEX707 } {}708}709integrity_check index-19.8710 711# Drop index with a quoted name. Ticket #695.712#713do_test index-20.1 {714 execsql {715 CREATE INDEX "t6i2" ON t6(c);716 DROP INDEX "t6i2";717 }718} {}719do_test index-20.2 {720 execsql {721 DROP INDEX "t6i1";722 }723} {}724 725# Try to create a TEMP index on a non-TEMP table. */726#727do_test index-21.1 {728 catchsql {729 CREATE INDEX temp.i21 ON t6(c);730 }731} {1 {cannot create a TEMP index on non-TEMP table "t6"}}732do_test index-21.2 {733 catchsql {734 CREATE TEMP TABLE t6(x);735 INSERT INTO temp.t6 values(1),(5),(9);736 CREATE INDEX temp.i21 ON t6(x);737 SELECT x FROM t6 ORDER BY x DESC;738 }739} {0 {9 5 1}}740 741# 2019-05-01 ticket https://sqlite.org/src/info/3be1295b264be2fa742do_execsql_test index-22.0 {743 DROP TABLE IF EXISTS t1;744 CREATE TABLE t1(a, b TEXT);745 CREATE UNIQUE INDEX IF NOT EXISTS x1 ON t1(b==0);746 CREATE INDEX IF NOT EXISTS x2 ON t1(a || 0) WHERE b;747 INSERT INTO t1(a,b) VALUES('a',1),('a',0);748 SELECT a, b, '|' FROM t1;749} {a 1 | a 0 |}750 751# 2019-05-10 ticket https://sqlite.org/src/info/ae0f637bddc5290b752do_execsql_test index-23.0 {753 DROP TABLE t1;754 CREATE TABLE t1(a TEXT, b REAL);755 CREATE UNIQUE INDEX t1x1 ON t1(a GLOB b);756 INSERT INTO t1(a,b) VALUES('0.0','1'),('1.0','1');757 SELECT * FROM t1;758 REINDEX;759} {0.0 1.0 1.0 1.0}760do_execsql_test index-23.1 {761 DROP TABLE t1;762 CREATE TABLE t1(a REAL);763 CREATE UNIQUE INDEX index_0 ON t1(TYPEOF(a));764 INSERT OR IGNORE INTO t1(a) VALUES (0.1),(FALSE);765 SELECT * FROM t1;766 REINDEX;767} {0.1}768 769finish_test770