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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 17 # Create a schema with some indexes. 18 # 19 do_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 # 37 proc 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 # 43 do_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)}} 46 do_execsql_test indexedby-1.3 { 47 EXPLAIN QUERY PLAN select * from t1 ; 48 } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} 49 do_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 # 61 do_test indexedby-2.1 { 62 execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} 63 } {} 64 do_test indexedby-2.2 { 65 execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} 66 } {} 67 do_test indexedby-2.3 { 68 execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'} 69 } {} 70 71 do_test indexedby-2.4 { 72 catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'} 73 } {1 {no such index: i3}} 74 do_test indexedby-2.5 { 75 catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'} 76 } {1 {no such index: i5}} 77 do_test indexedby-2.6 { 78 catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'} 79 } {1 {near "WHERE": syntax error}} 80 do_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 # 86 do_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)}} 89 do_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)}} 93 do_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)}} 97 do_test indexedby-3.4 { 98 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 99 } {1 {cannot use index: i2}} 100 do_test indexedby-3.5 { 101 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 102 } {1 {cannot use index: i2}} 103 do_test indexedby-3.6 { 104 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } 105 } {0 {}} 106 do_test indexedby-3.7 { 107 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 108 } {0 {}} 109 110 do_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)}} 114 do_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)}} 118 do_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}} 121 do_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 # 127 do_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 } 133 do_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 } 139 do_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}} 144 do_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 # 154 do_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)}} 158 do_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)}} 161 do_test indexedby-5.3 { 162 execsql { DROP INDEX i1 } 163 catchsql { SELECT * FROM v2 } 164 } {1 {no such index: i1}} 165 do_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}} 170 do_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 # 179 do_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)}} 182 do_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 # 188 do_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)}} 191 do_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)}} 194 do_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)}} 197 do_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)}} 200 do_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)}} 203 do_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 # 209 do_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)}} 212 do_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)}} 215 do_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)}} 218 do_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)}} 222 do_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)}} 226 do_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 # 232 do_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 } {} 240 do_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}} 247 do_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 # 254 do_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} 261 do_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} 267 do_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 277 finish_test 278
