gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/indexedby.test (about) 1 # 2008-10-04 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 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set ::testprefix indexedby 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_eqp_test indexedby-1.2 { 44 select * from t1 WHERE a = 10; 45 } {SEARCH t1 USING INDEX i1 (a=?)} 46 do_eqp_test indexedby-1.3 { 47 select * from t1 ; 48 } {SCAN t1} 49 do_eqp_test indexedby-1.4 { 50 select * from t1, t2 WHERE c = 10; 51 } { 52 QUERY PLAN 53 |--SEARCH t2 USING INDEX i3 (c=?) 54 `--SCAN t1 55 } 56 57 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 58 # attached to a table in the FROM clause, but not to a sub-select or 59 # SQL view. Also test that specifying an index that does not exist or 60 # is attached to a different table is detected as an error. 61 # 62 # X-EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name 63 # 64 # EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase 65 # specifies that the named index must be used in order to look up values 66 # on the preceding table. 67 # 68 do_test indexedby-2.1 { 69 execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} 70 } {} 71 do_test indexedby-2.1b { 72 execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} 73 } {} 74 do_test indexedby-2.2 { 75 execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} 76 } {} 77 do_test indexedby-2.2b { 78 execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} 79 } {} 80 do_test indexedby-2.3 { 81 execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'} 82 } {} 83 # EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the 84 # optimizer hints about which index to use; it gives the optimizer a 85 # requirement of which index to use. 86 # EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be 87 # used for the query, then the preparation of the SQL statement fails. 88 # 89 do_test indexedby-2.4 { 90 catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'} 91 } {1 {no such index: i3}} 92 93 # EVIDENCE-OF: R-05301-32681 If the query optimizer is unable to use the 94 # index specified by the INDEXED BY clause, then the query will fail 95 # with an error. 96 do_test indexedby-2.4.1 { 97 catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' } 98 } {0 {}} 99 100 do_test indexedby-2.5 { 101 catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'} 102 } {1 {no such index: i5}} 103 do_test indexedby-2.6 { 104 catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'} 105 } {1 {near "WHERE": syntax error}} 106 do_test indexedby-2.7 { 107 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' } 108 } {1 {no such index: i1}} 109 110 111 # Tests for single table cases. 112 # 113 # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no 114 # index shall be used when accessing the preceding table, including 115 # implied indices create by UNIQUE and PRIMARY KEY constraints. However, 116 # the rowid can still be used to look up entries even when "NOT INDEXED" 117 # is specified. 118 # 119 do_eqp_test indexedby-3.1 { 120 SELECT * FROM t1 WHERE a = 'one' AND b = 'two' 121 } {/SEARCH t1 USING INDEX/} 122 do_eqp_test indexedby-3.1.1 { 123 SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' 124 } {SCAN t1} 125 do_eqp_test indexedby-3.1.2 { 126 SELECT * FROM t1 NOT INDEXED WHERE rowid=1 127 } {/SEARCH t1 USING INTEGER PRIMARY KEY .rowid=/} 128 129 130 do_eqp_test indexedby-3.2 { 131 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' 132 } {SEARCH t1 USING INDEX i1 (a=?)} 133 do_eqp_test indexedby-3.3 { 134 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' 135 } {SEARCH t1 USING INDEX i2 (b=?)} 136 do_test indexedby-3.4 { 137 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 138 } {0 {}} 139 do_test indexedby-3.5 { 140 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 141 } {0 {}} 142 do_test indexedby-3.6 { 143 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } 144 } {0 {}} 145 do_test indexedby-3.7 { 146 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 147 } {0 {}} 148 149 do_eqp_test indexedby-3.8 { 150 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 151 } {SCAN t3 USING INDEX sqlite_autoindex_t3_1} 152 do_eqp_test indexedby-3.9 { 153 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 154 } {SEARCH t3 USING INDEX sqlite_autoindex_t3_1 (e=?)} 155 do_test indexedby-3.10 { 156 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } 157 } {0 {}} 158 do_test indexedby-3.11 { 159 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } 160 } {1 {no such index: sqlite_autoindex_t3_2}} 161 162 # Tests for multiple table cases. 163 # 164 do_eqp_test indexedby-4.1 { 165 SELECT * FROM t1, t2 WHERE a = c 166 } { 167 QUERY PLAN 168 |--SCAN t1 169 `--SEARCH t2 USING INDEX i3 (c=?) 170 } 171 do_eqp_test indexedby-4.2 { 172 SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 173 } { 174 QUERY PLAN 175 |--SCAN t1 USING INDEX i1 176 `--SEARCH t2 USING INDEX i3 (c=?) 177 } 178 do_test indexedby-4.3 { 179 catchsql { 180 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c 181 } 182 } {0 {}} 183 do_test indexedby-4.4 { 184 catchsql { 185 SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c 186 } 187 } {0 {}} 188 189 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block 190 # also tests that nothing bad happens if an index refered to by 191 # a CREATE VIEW statement is dropped and recreated. 192 # 193 do_execsql_test indexedby-5.1 { 194 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 195 EXPLAIN QUERY PLAN SELECT * FROM v2 196 } {/*SEARCH t1 USING INDEX i1 (a>?)*/} 197 do_execsql_test indexedby-5.2 { 198 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 199 } {/*SEARCH t1 USING INDEX i1 (a>?)*/} 200 do_test indexedby-5.3 { 201 execsql { DROP INDEX i1 } 202 catchsql { SELECT * FROM v2 } 203 } {1 {no such index: i1}} 204 do_test indexedby-5.4 { 205 # Recreate index i1 in such a way as it cannot be used by the view query. 206 execsql { CREATE INDEX i1 ON t1(b) } 207 catchsql { SELECT * FROM v2 } 208 } {0 {}} 209 do_test indexedby-5.5 { 210 # Drop and recreate index i1 again. This time, create it so that it can 211 # be used by the query. 212 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) } 213 catchsql { SELECT * FROM v2 } 214 } {0 {}} 215 216 # Test that "NOT INDEXED" may use the rowid index, but not others. 217 # 218 do_eqp_test indexedby-6.1 { 219 SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 220 } {SEARCH t1 USING INDEX i2 (b=?)} 221 do_eqp_test indexedby-6.2 { 222 SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 223 } {SCAN t1} 224 225 # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite 226 # query planner to use a particular named index on a DELETE, SELECT, or 227 # UPDATE statement. 228 # 229 # Test that "INDEXED BY" can be used in a DELETE statement. 230 # 231 do_eqp_test indexedby-7.1 { 232 DELETE FROM t1 WHERE a = 5 233 } {SEARCH t1 USING INDEX i1 (a=?)} 234 do_eqp_test indexedby-7.2 { 235 DELETE FROM t1 NOT INDEXED WHERE a = 5 236 } {SCAN t1} 237 do_eqp_test indexedby-7.3 { 238 DELETE FROM t1 INDEXED BY i1 WHERE a = 5 239 } {SEARCH t1 USING INDEX i1 (a=?)} 240 do_eqp_test indexedby-7.4 { 241 DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 242 } {SEARCH t1 USING INDEX i1 (a=?)} 243 do_eqp_test indexedby-7.5 { 244 DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 245 } {SEARCH t1 USING INDEX i2 (b=?)} 246 do_test indexedby-7.6 { 247 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} 248 } {0 {}} 249 250 # Test that "INDEXED BY" can be used in an UPDATE statement. 251 # 252 do_eqp_test indexedby-8.1 { 253 UPDATE t1 SET rowid=rowid+1 WHERE a = 5 254 } {SEARCH t1 USING COVERING INDEX i1 (a=?)} 255 do_eqp_test indexedby-8.2 { 256 UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 257 } {SCAN t1} 258 do_eqp_test indexedby-8.3 { 259 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 260 } {SEARCH t1 USING COVERING INDEX i1 (a=?)} 261 do_eqp_test indexedby-8.4 { 262 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 263 } {SEARCH t1 USING INDEX i1 (a=?)} 264 do_eqp_test indexedby-8.5 { 265 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 266 } {SEARCH t1 USING INDEX i2 (b=?)} 267 do_test indexedby-8.6 { 268 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} 269 } {0 {}} 270 271 # Test that bug #3560 is fixed. 272 # 273 do_test indexedby-9.1 { 274 execsql { 275 CREATE TABLE maintable( id integer); 276 CREATE TABLE joinme(id_int integer, id_text text); 277 CREATE INDEX joinme_id_text_idx on joinme(id_text); 278 CREATE INDEX joinme_id_int_idx on joinme(id_int); 279 } 280 } {} 281 do_test indexedby-9.2 { 282 catchsql { 283 select * from maintable as m inner join 284 joinme as j indexed by joinme_id_text_idx 285 on ( m.id = j.id_int) 286 } 287 } {0 {}} 288 do_test indexedby-9.3 { 289 catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx } 290 } {0 {}} 291 292 # Make sure we can still create tables, indices, and columns whose name 293 # is "indexed". 294 # 295 do_test indexedby-10.1 { 296 execsql { 297 CREATE TABLE indexed(x,y); 298 INSERT INTO indexed VALUES(1,2); 299 SELECT * FROM indexed; 300 } 301 } {1 2} 302 do_test indexedby-10.2 { 303 execsql { 304 CREATE INDEX i10 ON indexed(x); 305 SELECT * FROM indexed indexed by i10 where x>0; 306 } 307 } {1 2} 308 do_test indexedby-10.3 { 309 execsql { 310 DROP TABLE indexed; 311 CREATE TABLE t10(indexed INTEGER); 312 INSERT INTO t10 VALUES(1); 313 CREATE INDEX indexed ON t10(indexed); 314 SELECT * FROM t10 indexed by indexed WHERE indexed>0 315 } 316 } {1} 317 318 #------------------------------------------------------------------------- 319 # Ensure that the rowid at the end of each index entry may be used 320 # for equality constraints in the same way as other indexed fields. 321 # 322 do_execsql_test 11.1 { 323 CREATE TABLE x1(a, b TEXT); 324 CREATE INDEX x1i ON x1(a, b); 325 INSERT INTO x1 VALUES(1, 1); 326 INSERT INTO x1 VALUES(1, 1); 327 INSERT INTO x1 VALUES(1, 1); 328 INSERT INTO x1 VALUES(1, 1); 329 } 330 do_execsql_test 11.2 { 331 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid=3; 332 } {1 1 3} 333 do_execsql_test 11.3 { 334 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3'; 335 } {1 1 3} 336 do_execsql_test 11.4 { 337 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0'; 338 } {1 1 3} 339 do_eqp_test 11.5 { 340 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0'; 341 } {SEARCH x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)} 342 343 do_execsql_test 11.6 { 344 CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT); 345 CREATE INDEX x2i ON x2(a, b); 346 INSERT INTO x2 VALUES(1, 1, 1); 347 INSERT INTO x2 VALUES(2, 1, 1); 348 INSERT INTO x2 VALUES(3, 1, 1); 349 INSERT INTO x2 VALUES(4, 1, 1); 350 } 351 do_execsql_test 11.7 { 352 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c=3; 353 } {1 1 3} 354 do_execsql_test 11.8 { 355 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3'; 356 } {1 1 3} 357 do_execsql_test 11.9 { 358 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0'; 359 } {1 1 3} 360 do_eqp_test 11.10 { 361 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0'; 362 } {SEARCH x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)} 363 364 #------------------------------------------------------------------------- 365 # Check INDEXED BY works (throws an exception) with partial indexes that 366 # cannot be used. 367 do_execsql_test 12.1 { 368 CREATE TABLE o1(x INTEGER PRIMARY KEY, y, z); 369 CREATE INDEX p1 ON o1(z); 370 CREATE INDEX p2 ON o1(y) WHERE z=1; 371 } 372 do_catchsql_test 12.2 { 373 SELECT * FROM o1 INDEXED BY p2 ORDER BY 1; 374 } {1 {no query solution}} 375 do_execsql_test 12.3 { 376 DROP INDEX p1; 377 DROP INDEX p2; 378 CREATE INDEX p2 ON o1(y) WHERE z=1; 379 CREATE INDEX p1 ON o1(z); 380 } 381 do_catchsql_test 12.4 { 382 SELECT * FROM o1 INDEXED BY p2 ORDER BY 1; 383 } {1 {no query solution}} 384 385 finish_test