1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing the CREATE INDEX statement. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: index.test,v 1.24.2.1 2004/07/20 00:50:30 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gate# Create a basic index and verify it is added to sqlite_master 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gatedo_test index-1.1 { 237c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} 247c478bd9Sstevel@tonic-gate execsql {CREATE INDEX index1 ON test1(f1)} 257c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 267c478bd9Sstevel@tonic-gate} {index1 test1} 277c478bd9Sstevel@tonic-gatedo_test index-1.1b { 28*1da57d55SToomas Soome execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 297c478bd9Sstevel@tonic-gate WHERE name='index1'} 307c478bd9Sstevel@tonic-gate} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} 317c478bd9Sstevel@tonic-gatedo_test index-1.1c { 327c478bd9Sstevel@tonic-gate db close 337c478bd9Sstevel@tonic-gate sqlite db test.db 34*1da57d55SToomas Soome execsql {SELECT name, sql, tbl_name, type FROM sqlite_master 357c478bd9Sstevel@tonic-gate WHERE name='index1'} 367c478bd9Sstevel@tonic-gate} {index1 {CREATE INDEX index1 ON test1(f1)} test1 index} 377c478bd9Sstevel@tonic-gatedo_test index-1.1d { 387c478bd9Sstevel@tonic-gate db close 397c478bd9Sstevel@tonic-gate sqlite db test.db 407c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 417c478bd9Sstevel@tonic-gate} {index1 test1} 427c478bd9Sstevel@tonic-gate 437c478bd9Sstevel@tonic-gate# Verify that the index dies with the table 447c478bd9Sstevel@tonic-gate# 457c478bd9Sstevel@tonic-gatedo_test index-1.2 { 467c478bd9Sstevel@tonic-gate execsql {DROP TABLE test1} 477c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 487c478bd9Sstevel@tonic-gate} {} 497c478bd9Sstevel@tonic-gate 507c478bd9Sstevel@tonic-gate# Try adding an index to a table that does not exist 517c478bd9Sstevel@tonic-gate# 527c478bd9Sstevel@tonic-gatedo_test index-2.1 { 537c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg] 547c478bd9Sstevel@tonic-gate lappend v $msg 557c478bd9Sstevel@tonic-gate} {1 {no such table: test1}} 567c478bd9Sstevel@tonic-gate 577c478bd9Sstevel@tonic-gate# Try adding an index on a column of a table where the table 587c478bd9Sstevel@tonic-gate# exists but the column does not. 597c478bd9Sstevel@tonic-gate# 607c478bd9Sstevel@tonic-gatedo_test index-2.1 { 617c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)} 627c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg] 637c478bd9Sstevel@tonic-gate lappend v $msg 647c478bd9Sstevel@tonic-gate} {1 {table test1 has no column named f4}} 657c478bd9Sstevel@tonic-gate 667c478bd9Sstevel@tonic-gate# Try an index with some columns that match and others that do now. 677c478bd9Sstevel@tonic-gate# 687c478bd9Sstevel@tonic-gatedo_test index-2.2 { 697c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg] 707c478bd9Sstevel@tonic-gate execsql {DROP TABLE test1} 717c478bd9Sstevel@tonic-gate lappend v $msg 727c478bd9Sstevel@tonic-gate} {1 {table test1 has no column named f4}} 737c478bd9Sstevel@tonic-gate 747c478bd9Sstevel@tonic-gate# Try creating a bunch of indices on the same table 757c478bd9Sstevel@tonic-gate# 767c478bd9Sstevel@tonic-gateset r {} 777c478bd9Sstevel@tonic-gatefor {set i 1} {$i<100} {incr i} { 787c478bd9Sstevel@tonic-gate lappend r [format index%02d $i] 797c478bd9Sstevel@tonic-gate} 807c478bd9Sstevel@tonic-gatedo_test index-3.1 { 817c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)} 827c478bd9Sstevel@tonic-gate for {set i 1} {$i<100} {incr i} { 837c478bd9Sstevel@tonic-gate set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])" 847c478bd9Sstevel@tonic-gate execsql $sql 857c478bd9Sstevel@tonic-gate } 86*1da57d55SToomas Soome execsql {SELECT name FROM sqlite_master 877c478bd9Sstevel@tonic-gate WHERE type='index' AND tbl_name='test1' 887c478bd9Sstevel@tonic-gate ORDER BY name} 897c478bd9Sstevel@tonic-gate} $r 907c478bd9Sstevel@tonic-gate 917c478bd9Sstevel@tonic-gate 927c478bd9Sstevel@tonic-gate# Verify that all the indices go away when we drop the table. 937c478bd9Sstevel@tonic-gate# 947c478bd9Sstevel@tonic-gatedo_test index-3.3 { 957c478bd9Sstevel@tonic-gate execsql {DROP TABLE test1} 96*1da57d55SToomas Soome execsql {SELECT name FROM sqlite_master 977c478bd9Sstevel@tonic-gate WHERE type='index' AND tbl_name='test1' 987c478bd9Sstevel@tonic-gate ORDER BY name} 997c478bd9Sstevel@tonic-gate} {} 1007c478bd9Sstevel@tonic-gate 1017c478bd9Sstevel@tonic-gate# Create a table and insert values into that table. Then create 1027c478bd9Sstevel@tonic-gate# an index on that table. Verify that we can select values 1037c478bd9Sstevel@tonic-gate# from the table correctly using the index. 1047c478bd9Sstevel@tonic-gate# 1057c478bd9Sstevel@tonic-gate# Note that the index names "index9" and "indext" are chosen because 1067c478bd9Sstevel@tonic-gate# they both have the same hash. 1077c478bd9Sstevel@tonic-gate# 1087c478bd9Sstevel@tonic-gatedo_test index-4.1 { 1097c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(cnt int, power int)} 1107c478bd9Sstevel@tonic-gate for {set i 1} {$i<20} {incr i} { 1117c478bd9Sstevel@tonic-gate execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" 1127c478bd9Sstevel@tonic-gate } 1137c478bd9Sstevel@tonic-gate execsql {CREATE INDEX index9 ON test1(cnt)} 1147c478bd9Sstevel@tonic-gate execsql {CREATE INDEX indext ON test1(power)} 1157c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 1167c478bd9Sstevel@tonic-gate} {index9 indext test1} 1177c478bd9Sstevel@tonic-gatedo_test index-4.2 { 1187c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=4} 1197c478bd9Sstevel@tonic-gate} {2} 1207c478bd9Sstevel@tonic-gatedo_test index-4.3 { 1217c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=1024} 1227c478bd9Sstevel@tonic-gate} {10} 1237c478bd9Sstevel@tonic-gatedo_test index-4.4 { 1247c478bd9Sstevel@tonic-gate execsql {SELECT power FROM test1 WHERE cnt=6} 1257c478bd9Sstevel@tonic-gate} {64} 1267c478bd9Sstevel@tonic-gatedo_test index-4.5 { 1277c478bd9Sstevel@tonic-gate execsql {DROP INDEX indext} 1287c478bd9Sstevel@tonic-gate execsql {SELECT power FROM test1 WHERE cnt=6} 1297c478bd9Sstevel@tonic-gate} {64} 1307c478bd9Sstevel@tonic-gatedo_test index-4.6 { 1317c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=1024} 1327c478bd9Sstevel@tonic-gate} {10} 1337c478bd9Sstevel@tonic-gatedo_test index-4.7 { 1347c478bd9Sstevel@tonic-gate execsql {CREATE INDEX indext ON test1(cnt)} 1357c478bd9Sstevel@tonic-gate execsql {SELECT power FROM test1 WHERE cnt=6} 1367c478bd9Sstevel@tonic-gate} {64} 1377c478bd9Sstevel@tonic-gatedo_test index-4.8 { 1387c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=1024} 1397c478bd9Sstevel@tonic-gate} {10} 1407c478bd9Sstevel@tonic-gatedo_test index-4.9 { 1417c478bd9Sstevel@tonic-gate execsql {DROP INDEX index9} 1427c478bd9Sstevel@tonic-gate execsql {SELECT power FROM test1 WHERE cnt=6} 1437c478bd9Sstevel@tonic-gate} {64} 1447c478bd9Sstevel@tonic-gatedo_test index-4.10 { 1457c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=1024} 1467c478bd9Sstevel@tonic-gate} {10} 1477c478bd9Sstevel@tonic-gatedo_test index-4.11 { 1487c478bd9Sstevel@tonic-gate execsql {DROP INDEX indext} 1497c478bd9Sstevel@tonic-gate execsql {SELECT power FROM test1 WHERE cnt=6} 1507c478bd9Sstevel@tonic-gate} {64} 1517c478bd9Sstevel@tonic-gatedo_test index-4.12 { 1527c478bd9Sstevel@tonic-gate execsql {SELECT cnt FROM test1 WHERE power=1024} 1537c478bd9Sstevel@tonic-gate} {10} 1547c478bd9Sstevel@tonic-gatedo_test index-4.13 { 1557c478bd9Sstevel@tonic-gate execsql {DROP TABLE test1} 1567c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 1577c478bd9Sstevel@tonic-gate} {} 1587c478bd9Sstevel@tonic-gateintegrity_check index-4.14 1597c478bd9Sstevel@tonic-gate 1607c478bd9Sstevel@tonic-gate# Do not allow indices to be added to sqlite_master 1617c478bd9Sstevel@tonic-gate# 1627c478bd9Sstevel@tonic-gatedo_test index-5.1 { 1637c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg] 1647c478bd9Sstevel@tonic-gate lappend v $msg 1657c478bd9Sstevel@tonic-gate} {1 {table sqlite_master may not be indexed}} 1667c478bd9Sstevel@tonic-gatedo_test index-5.2 { 1677c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 1687c478bd9Sstevel@tonic-gate} {} 1697c478bd9Sstevel@tonic-gate 1707c478bd9Sstevel@tonic-gate# Do not allow indices with duplicate names to be added 1717c478bd9Sstevel@tonic-gate# 1727c478bd9Sstevel@tonic-gatedo_test index-6.1 { 1737c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(f1 int, f2 int)} 1747c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test2(g1 real, g2 real)} 1757c478bd9Sstevel@tonic-gate execsql {CREATE INDEX index1 ON test1(f1)} 1767c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg] 1777c478bd9Sstevel@tonic-gate lappend v $msg 1787c478bd9Sstevel@tonic-gate} {1 {index index1 already exists}} 1797c478bd9Sstevel@tonic-gatedo_test index-6.1b { 1807c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 1817c478bd9Sstevel@tonic-gate} {index1 test1 test2} 1827c478bd9Sstevel@tonic-gatedo_test index-6.2 { 1837c478bd9Sstevel@tonic-gate set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg] 1847c478bd9Sstevel@tonic-gate lappend v $msg 1857c478bd9Sstevel@tonic-gate} {1 {there is already a table named test1}} 1867c478bd9Sstevel@tonic-gatedo_test index-6.2b { 1877c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 1887c478bd9Sstevel@tonic-gate} {index1 test1 test2} 1897c478bd9Sstevel@tonic-gatedo_test index-6.3 { 1907c478bd9Sstevel@tonic-gate execsql {DROP TABLE test1} 1917c478bd9Sstevel@tonic-gate execsql {DROP TABLE test2} 1927c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 1937c478bd9Sstevel@tonic-gate} {} 1947c478bd9Sstevel@tonic-gatedo_test index-6.4 { 1957c478bd9Sstevel@tonic-gate execsql { 1967c478bd9Sstevel@tonic-gate CREATE TABLE test1(a,b); 1977c478bd9Sstevel@tonic-gate CREATE INDEX index1 ON test1(a); 1987c478bd9Sstevel@tonic-gate CREATE INDEX index2 ON test1(b); 1997c478bd9Sstevel@tonic-gate CREATE INDEX index3 ON test1(a,b); 2007c478bd9Sstevel@tonic-gate DROP TABLE test1; 2017c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name; 2027c478bd9Sstevel@tonic-gate } 2037c478bd9Sstevel@tonic-gate} {} 2047c478bd9Sstevel@tonic-gateintegrity_check index-6.5 2057c478bd9Sstevel@tonic-gate 2067c478bd9Sstevel@tonic-gate 2077c478bd9Sstevel@tonic-gate# Create a primary key 2087c478bd9Sstevel@tonic-gate# 2097c478bd9Sstevel@tonic-gatedo_test index-7.1 { 2107c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(f1 int, f2 int primary key)} 2117c478bd9Sstevel@tonic-gate for {set i 1} {$i<20} {incr i} { 2127c478bd9Sstevel@tonic-gate execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])" 2137c478bd9Sstevel@tonic-gate } 2147c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM test1} 2157c478bd9Sstevel@tonic-gate} {19} 2167c478bd9Sstevel@tonic-gatedo_test index-7.2 { 2177c478bd9Sstevel@tonic-gate execsql {SELECT f1 FROM test1 WHERE f2=65536} 2187c478bd9Sstevel@tonic-gate} {16} 2197c478bd9Sstevel@tonic-gatedo_test index-7.3 { 2207c478bd9Sstevel@tonic-gate execsql { 221*1da57d55SToomas Soome SELECT name FROM sqlite_master 2227c478bd9Sstevel@tonic-gate WHERE type='index' AND tbl_name='test1' 2237c478bd9Sstevel@tonic-gate } 2247c478bd9Sstevel@tonic-gate} {{(test1 autoindex 1)}} 2257c478bd9Sstevel@tonic-gatedo_test index-7.4 { 2267c478bd9Sstevel@tonic-gate execsql {DROP table test1} 2277c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 2287c478bd9Sstevel@tonic-gate} {} 2297c478bd9Sstevel@tonic-gateintegrity_check index-7.5 2307c478bd9Sstevel@tonic-gate 2317c478bd9Sstevel@tonic-gate# Make sure we cannot drop a non-existant index. 2327c478bd9Sstevel@tonic-gate# 2337c478bd9Sstevel@tonic-gatedo_test index-8.1 { 2347c478bd9Sstevel@tonic-gate set v [catch {execsql {DROP INDEX index1}} msg] 2357c478bd9Sstevel@tonic-gate lappend v $msg 2367c478bd9Sstevel@tonic-gate} {1 {no such index: index1}} 2377c478bd9Sstevel@tonic-gate 2387c478bd9Sstevel@tonic-gate# Make sure we don't actually create an index when the EXPLAIN keyword 2397c478bd9Sstevel@tonic-gate# is used. 2407c478bd9Sstevel@tonic-gate# 2417c478bd9Sstevel@tonic-gatedo_test index-9.1 { 2427c478bd9Sstevel@tonic-gate execsql {CREATE TABLE tab1(a int)} 2437c478bd9Sstevel@tonic-gate execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)} 2447c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'} 2457c478bd9Sstevel@tonic-gate} {tab1} 2467c478bd9Sstevel@tonic-gatedo_test index-9.2 { 2477c478bd9Sstevel@tonic-gate execsql {CREATE INDEX idx1 ON tab1(a)} 2487c478bd9Sstevel@tonic-gate execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name} 2497c478bd9Sstevel@tonic-gate} {idx1 tab1} 2507c478bd9Sstevel@tonic-gateintegrity_check index-9.3 2517c478bd9Sstevel@tonic-gate 2527c478bd9Sstevel@tonic-gate# Allow more than one entry with the same key. 2537c478bd9Sstevel@tonic-gate# 2547c478bd9Sstevel@tonic-gatedo_test index-10.0 { 2557c478bd9Sstevel@tonic-gate execsql { 2567c478bd9Sstevel@tonic-gate CREATE TABLE t1(a int, b int); 2577c478bd9Sstevel@tonic-gate CREATE INDEX i1 ON t1(a); 2587c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2); 2597c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,4); 2607c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(3,8); 2617c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,12); 2627c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 2637c478bd9Sstevel@tonic-gate } 2647c478bd9Sstevel@tonic-gate} {2 12} 2657c478bd9Sstevel@tonic-gatedo_test index-10.1 { 2667c478bd9Sstevel@tonic-gate execsql { 2677c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=2 ORDER BY b; 2687c478bd9Sstevel@tonic-gate } 2697c478bd9Sstevel@tonic-gate} {4} 2707c478bd9Sstevel@tonic-gatedo_test index-10.2 { 2717c478bd9Sstevel@tonic-gate execsql { 2727c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE b=12; 2737c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 2747c478bd9Sstevel@tonic-gate } 2757c478bd9Sstevel@tonic-gate} {2} 2767c478bd9Sstevel@tonic-gatedo_test index-10.3 { 2777c478bd9Sstevel@tonic-gate execsql { 2787c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE b=2; 2797c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 2807c478bd9Sstevel@tonic-gate } 2817c478bd9Sstevel@tonic-gate} {} 2827c478bd9Sstevel@tonic-gatedo_test index-10.4 { 2837c478bd9Sstevel@tonic-gate execsql { 2847c478bd9Sstevel@tonic-gate DELETE FROM t1; 2857c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,1); 2867c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,2); 2877c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,3); 2887c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,4); 2897c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,5); 2907c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,6); 2917c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,7); 2927c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,8); 2937c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (1,9); 2947c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES (2,0); 2957c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 2967c478bd9Sstevel@tonic-gate } 2977c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9} 2987c478bd9Sstevel@tonic-gatedo_test index-10.5 { 2997c478bd9Sstevel@tonic-gate execsql { 3007c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE b IN (2, 4, 6, 8); 3017c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 3027c478bd9Sstevel@tonic-gate } 3037c478bd9Sstevel@tonic-gate} {1 3 5 7 9} 3047c478bd9Sstevel@tonic-gatedo_test index-10.6 { 3057c478bd9Sstevel@tonic-gate execsql { 3067c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE b>2; 3077c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 3087c478bd9Sstevel@tonic-gate } 3097c478bd9Sstevel@tonic-gate} {1} 3107c478bd9Sstevel@tonic-gatedo_test index-10.7 { 3117c478bd9Sstevel@tonic-gate execsql { 3127c478bd9Sstevel@tonic-gate DELETE FROM t1 WHERE b=1; 3137c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE a=1 ORDER BY b; 3147c478bd9Sstevel@tonic-gate } 3157c478bd9Sstevel@tonic-gate} {} 3167c478bd9Sstevel@tonic-gatedo_test index-10.8 { 3177c478bd9Sstevel@tonic-gate execsql { 3187c478bd9Sstevel@tonic-gate SELECT b FROM t1 ORDER BY b; 3197c478bd9Sstevel@tonic-gate } 3207c478bd9Sstevel@tonic-gate} {0} 3217c478bd9Sstevel@tonic-gateintegrity_check index-10.9 3227c478bd9Sstevel@tonic-gate 3237c478bd9Sstevel@tonic-gate# Automatically create an index when we specify a primary key. 3247c478bd9Sstevel@tonic-gate# 3257c478bd9Sstevel@tonic-gatedo_test index-11.1 { 3267c478bd9Sstevel@tonic-gate execsql { 3277c478bd9Sstevel@tonic-gate CREATE TABLE t3( 3287c478bd9Sstevel@tonic-gate a text, 3297c478bd9Sstevel@tonic-gate b int, 3307c478bd9Sstevel@tonic-gate c float, 3317c478bd9Sstevel@tonic-gate PRIMARY KEY(b) 3327c478bd9Sstevel@tonic-gate ); 3337c478bd9Sstevel@tonic-gate } 3347c478bd9Sstevel@tonic-gate for {set i 1} {$i<=50} {incr i} { 3357c478bd9Sstevel@tonic-gate execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)" 3367c478bd9Sstevel@tonic-gate } 3377c478bd9Sstevel@tonic-gate set sqlite_search_count 0 3387c478bd9Sstevel@tonic-gate concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count 3397c478bd9Sstevel@tonic-gate} {0.10 3} 3407c478bd9Sstevel@tonic-gateintegrity_check index-11.2 3417c478bd9Sstevel@tonic-gate 3427c478bd9Sstevel@tonic-gate 3437c478bd9Sstevel@tonic-gate# Numeric strings should compare as if they were numbers. So even if the 3447c478bd9Sstevel@tonic-gate# strings are not character-by-character the same, if they represent the 3457c478bd9Sstevel@tonic-gate# same number they should compare equal to one another. Verify that this 3467c478bd9Sstevel@tonic-gate# is true in indices. 3477c478bd9Sstevel@tonic-gate# 3487c478bd9Sstevel@tonic-gatedo_test index-12.1 { 3497c478bd9Sstevel@tonic-gate execsql { 3507c478bd9Sstevel@tonic-gate CREATE TABLE t4(a,b); 3517c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('0.0',1); 3527c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('0.00',2); 3537c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('abc',3); 3547c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('-1.0',4); 3557c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('+1.0',5); 3567c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('0',6); 3577c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES('00000',7); 3587c478bd9Sstevel@tonic-gate SELECT a FROM t4 ORDER BY b; 3597c478bd9Sstevel@tonic-gate } 3607c478bd9Sstevel@tonic-gate} {0.0 0.00 abc -1.0 +1.0 0 00000} 3617c478bd9Sstevel@tonic-gatedo_test index-12.2 { 3627c478bd9Sstevel@tonic-gate execsql { 3637c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a==0 ORDER BY b 3647c478bd9Sstevel@tonic-gate } 3657c478bd9Sstevel@tonic-gate} {0.0 0.00 0 00000} 3667c478bd9Sstevel@tonic-gatedo_test index-12.3 { 3677c478bd9Sstevel@tonic-gate execsql { 3687c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a<0.5 ORDER BY b 3697c478bd9Sstevel@tonic-gate } 3707c478bd9Sstevel@tonic-gate} {0.0 0.00 -1.0 0 00000} 3717c478bd9Sstevel@tonic-gatedo_test index-12.4 { 3727c478bd9Sstevel@tonic-gate execsql { 3737c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a>-0.5 ORDER BY b 3747c478bd9Sstevel@tonic-gate } 3757c478bd9Sstevel@tonic-gate} {0.0 0.00 abc +1.0 0 00000} 3767c478bd9Sstevel@tonic-gatedo_test index-12.5 { 3777c478bd9Sstevel@tonic-gate execsql { 3787c478bd9Sstevel@tonic-gate CREATE INDEX t4i1 ON t4(a); 3797c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a==0 ORDER BY b 3807c478bd9Sstevel@tonic-gate } 3817c478bd9Sstevel@tonic-gate} {0.0 0.00 0 00000} 3827c478bd9Sstevel@tonic-gatedo_test index-12.6 { 3837c478bd9Sstevel@tonic-gate execsql { 3847c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a<0.5 ORDER BY b 3857c478bd9Sstevel@tonic-gate } 3867c478bd9Sstevel@tonic-gate} {0.0 0.00 -1.0 0 00000} 3877c478bd9Sstevel@tonic-gatedo_test index-12.7 { 3887c478bd9Sstevel@tonic-gate execsql { 3897c478bd9Sstevel@tonic-gate SELECT a FROM t4 WHERE a>-0.5 ORDER BY b 3907c478bd9Sstevel@tonic-gate } 3917c478bd9Sstevel@tonic-gate} {0.0 0.00 abc +1.0 0 00000} 3927c478bd9Sstevel@tonic-gateintegrity_check index-12.8 3937c478bd9Sstevel@tonic-gate 3947c478bd9Sstevel@tonic-gate# Make sure we cannot drop an automatically created index. 3957c478bd9Sstevel@tonic-gate# 3967c478bd9Sstevel@tonic-gatedo_test index-13.1 { 3977c478bd9Sstevel@tonic-gate execsql { 3987c478bd9Sstevel@tonic-gate CREATE TABLE t5( 3997c478bd9Sstevel@tonic-gate a int UNIQUE, 4007c478bd9Sstevel@tonic-gate b float PRIMARY KEY, 4017c478bd9Sstevel@tonic-gate c varchar(10), 4027c478bd9Sstevel@tonic-gate UNIQUE(a,c) 4037c478bd9Sstevel@tonic-gate ); 4047c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,2,3); 4057c478bd9Sstevel@tonic-gate SELECT * FROM t5; 4067c478bd9Sstevel@tonic-gate } 4077c478bd9Sstevel@tonic-gate} {1 2 3} 4087c478bd9Sstevel@tonic-gatedo_test index-13.2 { 4097c478bd9Sstevel@tonic-gate set ::idxlist [execsql { 4107c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5"; 4117c478bd9Sstevel@tonic-gate }] 4127c478bd9Sstevel@tonic-gate llength $::idxlist 4137c478bd9Sstevel@tonic-gate} {3} 4147c478bd9Sstevel@tonic-gatefor {set i 0} {$i<[llength $::idxlist]} {incr i} { 4157c478bd9Sstevel@tonic-gate do_test index-13.3.$i { 4167c478bd9Sstevel@tonic-gate catchsql " 4177c478bd9Sstevel@tonic-gate DROP INDEX '[lindex $::idxlist $i]'; 4187c478bd9Sstevel@tonic-gate " 4197c478bd9Sstevel@tonic-gate } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}} 4207c478bd9Sstevel@tonic-gate} 4217c478bd9Sstevel@tonic-gatedo_test index-13.4 { 4227c478bd9Sstevel@tonic-gate execsql { 4237c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES('a','b','c'); 4247c478bd9Sstevel@tonic-gate SELECT * FROM t5; 4257c478bd9Sstevel@tonic-gate } 4267c478bd9Sstevel@tonic-gate} {1 2 3 a b c} 4277c478bd9Sstevel@tonic-gateintegrity_check index-13.5 4287c478bd9Sstevel@tonic-gate 4297c478bd9Sstevel@tonic-gate# Check the sort order of data in an index. 4307c478bd9Sstevel@tonic-gate# 4317c478bd9Sstevel@tonic-gatedo_test index-14.1 { 4327c478bd9Sstevel@tonic-gate execsql { 4337c478bd9Sstevel@tonic-gate CREATE TABLE t6(a,b,c); 4347c478bd9Sstevel@tonic-gate CREATE INDEX t6i1 ON t6(a,b); 4357c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES('','',1); 4367c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES('',NULL,2); 4377c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES(NULL,'',3); 4387c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES('abc',123,4); 4397c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES(123,'abc',5); 4407c478bd9Sstevel@tonic-gate SELECT c FROM t6 ORDER BY a,b; 4417c478bd9Sstevel@tonic-gate } 4427c478bd9Sstevel@tonic-gate} {3 5 2 1 4} 4437c478bd9Sstevel@tonic-gatedo_test index-14.2 { 4447c478bd9Sstevel@tonic-gate execsql { 4457c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a=''; 4467c478bd9Sstevel@tonic-gate } 4477c478bd9Sstevel@tonic-gate} {2 1} 4487c478bd9Sstevel@tonic-gatedo_test index-14.3 { 4497c478bd9Sstevel@tonic-gate execsql { 4507c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE b=''; 4517c478bd9Sstevel@tonic-gate } 4527c478bd9Sstevel@tonic-gate} {1 3} 4537c478bd9Sstevel@tonic-gatedo_test index-14.4 { 4547c478bd9Sstevel@tonic-gate execsql { 4557c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a>''; 4567c478bd9Sstevel@tonic-gate } 4577c478bd9Sstevel@tonic-gate} {4} 4587c478bd9Sstevel@tonic-gatedo_test index-14.5 { 4597c478bd9Sstevel@tonic-gate execsql { 4607c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a>=''; 4617c478bd9Sstevel@tonic-gate } 4627c478bd9Sstevel@tonic-gate} {2 1 4} 4637c478bd9Sstevel@tonic-gatedo_test index-14.6 { 4647c478bd9Sstevel@tonic-gate execsql { 4657c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a>123; 4667c478bd9Sstevel@tonic-gate } 4677c478bd9Sstevel@tonic-gate} {2 1 4} 4687c478bd9Sstevel@tonic-gatedo_test index-14.7 { 4697c478bd9Sstevel@tonic-gate execsql { 4707c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a>=123; 4717c478bd9Sstevel@tonic-gate } 4727c478bd9Sstevel@tonic-gate} {5 2 1 4} 4737c478bd9Sstevel@tonic-gatedo_test index-14.8 { 4747c478bd9Sstevel@tonic-gate execsql { 4757c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a<'abc'; 4767c478bd9Sstevel@tonic-gate } 4777c478bd9Sstevel@tonic-gate} {5 2 1} 4787c478bd9Sstevel@tonic-gatedo_test index-14.9 { 4797c478bd9Sstevel@tonic-gate execsql { 4807c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a<='abc'; 4817c478bd9Sstevel@tonic-gate } 4827c478bd9Sstevel@tonic-gate} {5 2 1 4} 4837c478bd9Sstevel@tonic-gatedo_test index-14.10 { 4847c478bd9Sstevel@tonic-gate execsql { 4857c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a<=''; 4867c478bd9Sstevel@tonic-gate } 4877c478bd9Sstevel@tonic-gate} {5 2 1} 4887c478bd9Sstevel@tonic-gatedo_test index-14.11 { 4897c478bd9Sstevel@tonic-gate execsql { 4907c478bd9Sstevel@tonic-gate SELECT c FROM t6 WHERE a<''; 4917c478bd9Sstevel@tonic-gate } 4927c478bd9Sstevel@tonic-gate} {5} 4937c478bd9Sstevel@tonic-gateintegrity_check index-14.12 4947c478bd9Sstevel@tonic-gate 4957c478bd9Sstevel@tonic-gatedo_test index-15.1 { 4967c478bd9Sstevel@tonic-gate execsql { 4977c478bd9Sstevel@tonic-gate DELETE FROM t1; 4987c478bd9Sstevel@tonic-gate SELECT * FROM t1; 4997c478bd9Sstevel@tonic-gate } 5007c478bd9Sstevel@tonic-gate} {} 5017c478bd9Sstevel@tonic-gatedo_test index-15.2 { 5027c478bd9Sstevel@tonic-gate execsql { 5037c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('1.234e5',1); 5047c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.33e04',2); 5057c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.35E4',3); 5067c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.34e',4); 5077c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.32e+4',5); 5087c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.36E+04',6); 5097c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('12.36E+',7); 5107c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('+123.10000E+0003',8); 5117c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('+',9); 5127c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('+12347.E+02',10); 5137c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES('+12347E+02',11); 5147c478bd9Sstevel@tonic-gate SELECT b FROM t1 ORDER BY a; 5157c478bd9Sstevel@tonic-gate } 5167c478bd9Sstevel@tonic-gate} {8 5 2 1 3 6 11 9 10 4 7} 5177c478bd9Sstevel@tonic-gateintegrity_check index-15.1 5187c478bd9Sstevel@tonic-gate 5197c478bd9Sstevel@tonic-gate# Drop index with a quoted name. Ticket #695. 5207c478bd9Sstevel@tonic-gate# 5217c478bd9Sstevel@tonic-gatedo_test index-16.1 { 5227c478bd9Sstevel@tonic-gate execsql { 5237c478bd9Sstevel@tonic-gate CREATE INDEX "t6i2" ON t6(c); 5247c478bd9Sstevel@tonic-gate DROP INDEX "t6i2"; 5257c478bd9Sstevel@tonic-gate } 5267c478bd9Sstevel@tonic-gate} {} 5277c478bd9Sstevel@tonic-gatedo_test index-16.2 { 5287c478bd9Sstevel@tonic-gate execsql { 5297c478bd9Sstevel@tonic-gate DROP INDEX "t6i1"; 5307c478bd9Sstevel@tonic-gate } 5317c478bd9Sstevel@tonic-gate} {} 532*1da57d55SToomas Soome 5337c478bd9Sstevel@tonic-gate 5347c478bd9Sstevel@tonic-gatefinish_test 535