1# 2008 October 4 2# 3# The author disclaims copyright to this source code. In place of 4# 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# 12# $Id: indexedby.test,v 1.5 2009/03/22 20:36:19 drh Exp $ 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16 17# Create a schema with some indexes. 18# 19do_test indexedby-1.1 { 20 execsql { 21 CREATE TABLE t1(a, b); 22 CREATE INDEX i1 ON t1(a); 23 CREATE INDEX i2 ON t1(b); 24 25 CREATE TABLE t2(c, d); 26 CREATE INDEX i3 ON t2(c); 27 CREATE INDEX i4 ON t2(d); 28 29 CREATE TABLE t3(e PRIMARY KEY, f); 30 31 CREATE VIEW v1 AS SELECT * FROM t1; 32 } 33} {} 34 35# Explain Query Plan 36# 37proc EQP {sql} { 38 uplevel "execsql {EXPLAIN QUERY PLAN $sql}" 39} 40 41# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. 42# 43do_execsql_test indexedby-1.2 { 44 EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 45} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} 46do_execsql_test indexedby-1.3 { 47 EXPLAIN QUERY PLAN select * from t1 ; 48} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} 49do_execsql_test indexedby-1.4 { 50 EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 51} { 52 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 53 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 54} 55 56# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 57# attached to a table in the FROM clause, but not to a sub-select or 58# SQL view. Also test that specifying an index that does not exist or 59# is attached to a different table is detected as an error. 60# 61do_test indexedby-2.1 { 62 execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} 63} {} 64do_test indexedby-2.2 { 65 execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} 66} {} 67do_test indexedby-2.3 { 68 execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'} 69} {} 70 71do_test indexedby-2.4 { 72 catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'} 73} {1 {no such index: i3}} 74do_test indexedby-2.5 { 75 catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'} 76} {1 {no such index: i5}} 77do_test indexedby-2.6 { 78 catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'} 79} {1 {near "WHERE": syntax error}} 80do_test indexedby-2.7 { 81 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' } 82} {1 {no such index: i1}} 83 84# Tests for single table cases. 85# 86do_execsql_test indexedby-3.1 { 87 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' 88} {0 0 0 {SCAN TABLE t1 (~10000 rows)}} 89do_execsql_test indexedby-3.2 { 90 EXPLAIN QUERY PLAN 91 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' 92} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 93do_execsql_test indexedby-3.3 { 94 EXPLAIN QUERY PLAN 95 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' 96} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 97do_test indexedby-3.4 { 98 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 99} {1 {cannot use index: i2}} 100do_test indexedby-3.5 { 101 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 102} {1 {cannot use index: i2}} 103do_test indexedby-3.6 { 104 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } 105} {0 {}} 106do_test indexedby-3.7 { 107 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 108} {0 {}} 109 110do_execsql_test indexedby-3.8 { 111 EXPLAIN QUERY PLAN 112 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 113} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} 114do_execsql_test indexedby-3.9 { 115 EXPLAIN QUERY PLAN 116 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 117} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} 118do_test indexedby-3.10 { 119 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } 120} {1 {cannot use index: sqlite_autoindex_t3_1}} 121do_test indexedby-3.11 { 122 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } 123} {1 {no such index: sqlite_autoindex_t3_2}} 124 125# Tests for multiple table cases. 126# 127do_execsql_test indexedby-4.1 { 128 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 129} { 130 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 131 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 132} 133do_execsql_test indexedby-4.2 { 134 EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 135} { 136 0 0 1 {SCAN TABLE t2 (~1000000 rows)} 137 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 138} 139do_test indexedby-4.3 { 140 catchsql { 141 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c 142 } 143} {1 {cannot use index: i1}} 144do_test indexedby-4.4 { 145 catchsql { 146 SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c 147 } 148} {1 {cannot use index: i3}} 149 150# Test embedding an INDEXED BY in a CREATE VIEW statement. This block 151# also tests that nothing bad happens if an index refered to by 152# a CREATE VIEW statement is dropped and recreated. 153# 154do_execsql_test indexedby-5.1 { 155 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 156 EXPLAIN QUERY PLAN SELECT * FROM v2 157} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} 158do_execsql_test indexedby-5.2 { 159 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 160} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} 161do_test indexedby-5.3 { 162 execsql { DROP INDEX i1 } 163 catchsql { SELECT * FROM v2 } 164} {1 {no such index: i1}} 165do_test indexedby-5.4 { 166 # Recreate index i1 in such a way as it cannot be used by the view query. 167 execsql { CREATE INDEX i1 ON t1(b) } 168 catchsql { SELECT * FROM v2 } 169} {1 {cannot use index: i1}} 170do_test indexedby-5.5 { 171 # Drop and recreate index i1 again. This time, create it so that it can 172 # be used by the query. 173 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) } 174 catchsql { SELECT * FROM v2 } 175} {0 {}} 176 177# Test that "NOT INDEXED" may use the rowid index, but not others. 178# 179do_execsql_test indexedby-6.1 { 180 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 181} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} 182do_execsql_test indexedby-6.2 { 183 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 184} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} 185 186# Test that "INDEXED BY" can be used in a DELETE statement. 187# 188do_execsql_test indexedby-7.1 { 189 EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 190} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 191do_execsql_test indexedby-7.2 { 192 EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 193} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 194do_execsql_test indexedby-7.3 { 195 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 196} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 197do_execsql_test indexedby-7.4 { 198 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 199} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 200do_execsql_test indexedby-7.5 { 201 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 202} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 203do_test indexedby-7.6 { 204 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} 205} {1 {cannot use index: i2}} 206 207# Test that "INDEXED BY" can be used in an UPDATE statement. 208# 209do_execsql_test indexedby-8.1 { 210 EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 211} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 212do_execsql_test indexedby-8.2 { 213 EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 214} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 215do_execsql_test indexedby-8.3 { 216 EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 217} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 218do_execsql_test indexedby-8.4 { 219 EXPLAIN QUERY PLAN 220 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 221} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 222do_execsql_test indexedby-8.5 { 223 EXPLAIN QUERY PLAN 224 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 225} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 226do_test indexedby-8.6 { 227 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} 228} {1 {cannot use index: i2}} 229 230# Test that bug #3560 is fixed. 231# 232do_test indexedby-9.1 { 233 execsql { 234 CREATE TABLE maintable( id integer); 235 CREATE TABLE joinme(id_int integer, id_text text); 236 CREATE INDEX joinme_id_text_idx on joinme(id_text); 237 CREATE INDEX joinme_id_int_idx on joinme(id_int); 238 } 239} {} 240do_test indexedby-9.2 { 241 catchsql { 242 select * from maintable as m inner join 243 joinme as j indexed by joinme_id_text_idx 244 on ( m.id = j.id_int) 245 } 246} {1 {cannot use index: joinme_id_text_idx}} 247do_test indexedby-9.3 { 248 catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx } 249} {1 {cannot use index: joinme_id_text_idx}} 250 251# Make sure we can still create tables, indices, and columns whose name 252# is "indexed". 253# 254do_test indexedby-10.1 { 255 execsql { 256 CREATE TABLE indexed(x,y); 257 INSERT INTO indexed VALUES(1,2); 258 SELECT * FROM indexed; 259 } 260} {1 2} 261do_test indexedby-10.2 { 262 execsql { 263 CREATE INDEX i10 ON indexed(x); 264 SELECT * FROM indexed indexed by i10 where x>0; 265 } 266} {1 2} 267do_test indexedby-10.3 { 268 execsql { 269 DROP TABLE indexed; 270 CREATE TABLE t10(indexed INTEGER); 271 INSERT INTO t10 VALUES(1); 272 CREATE INDEX indexed ON t10(indexed); 273 SELECT * FROM t10 indexed by indexed WHERE indexed>0 274 } 275} {1} 276 277finish_test 278