000001 # 2008-10-04
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012
000013 set testdir [file dirname $argv0]
000014 source $testdir/tester.tcl
000015 set ::testprefix indexedby
000016
000017 # Create a schema with some indexes.
000018 #
000019 do_test indexedby-1.1 {
000020 execsql {
000021 CREATE TABLE t1(a, b);
000022 CREATE INDEX i1 ON t1(a);
000023 CREATE INDEX i2 ON t1(b);
000024
000025 CREATE TABLE t2(c, d);
000026 CREATE INDEX i3 ON t2(c);
000027 CREATE INDEX i4 ON t2(d);
000028
000029 CREATE TABLE t3(e PRIMARY KEY, f);
000030
000031 CREATE VIEW v1 AS SELECT * FROM t1;
000032 }
000033 } {}
000034
000035 # Explain Query Plan
000036 #
000037 proc EQP {sql} {
000038 uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
000039 }
000040
000041 # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
000042 #
000043 do_execsql_test indexedby-1.2 {
000044 EXPLAIN QUERY PLAN select * from t1 WHERE a = 10;
000045 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000046 do_execsql_test indexedby-1.3 {
000047 EXPLAIN QUERY PLAN select * from t1 ;
000048 } {0 0 0 {SCAN TABLE t1}}
000049 do_execsql_test indexedby-1.4 {
000050 EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10;
000051 } {
000052 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)}
000053 0 1 0 {SCAN TABLE t1}
000054 }
000055
000056 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be
000057 # attached to a table in the FROM clause, but not to a sub-select or
000058 # SQL view. Also test that specifying an index that does not exist or
000059 # is attached to a different table is detected as an error.
000060 #
000061 # EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
000062 #
000063 # EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase
000064 # specifies that the named index must be used in order to look up values
000065 # on the preceding table.
000066 #
000067 do_test indexedby-2.1 {
000068 execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
000069 } {}
000070 do_test indexedby-2.1b {
000071 execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
000072 } {}
000073 do_test indexedby-2.2 {
000074 execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
000075 } {}
000076 do_test indexedby-2.2b {
000077 execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
000078 } {}
000079 do_test indexedby-2.3 {
000080 execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
000081 } {}
000082 # EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the
000083 # optimizer hints about which index to use; it gives the optimizer a
000084 # requirement of which index to use.
000085 # EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be
000086 # used for the query, then the preparation of the SQL statement fails.
000087 #
000088 do_test indexedby-2.4 {
000089 catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
000090 } {1 {no such index: i3}}
000091
000092 # EVIDENCE-OF: R-62112-42456 If the query optimizer is unable to use the
000093 # index specified by the INDEX BY clause, then the query will fail with
000094 # an error.
000095 do_test indexedby-2.4.1 {
000096 catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }
000097 } {1 {no query solution}}
000098
000099 do_test indexedby-2.5 {
000100 catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
000101 } {1 {no such index: i5}}
000102 do_test indexedby-2.6 {
000103 catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
000104 } {1 {near "WHERE": syntax error}}
000105 do_test indexedby-2.7 {
000106 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
000107 } {1 {no such index: i1}}
000108
000109
000110 # Tests for single table cases.
000111 #
000112 # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
000113 # index shall be used when accessing the preceding table, including
000114 # implied indices create by UNIQUE and PRIMARY KEY constraints. However,
000115 # the rowid can still be used to look up entries even when "NOT INDEXED"
000116 # is specified.
000117 #
000118 do_execsql_test indexedby-3.1 {
000119 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
000120 } {/SEARCH TABLE t1 USING INDEX/}
000121 do_execsql_test indexedby-3.1.1 {
000122 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
000123 } {0 0 0 {SCAN TABLE t1}}
000124 do_execsql_test indexedby-3.1.2 {
000125 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1
000126 } {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/}
000127
000128
000129 do_execsql_test indexedby-3.2 {
000130 EXPLAIN QUERY PLAN
000131 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
000132 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000133 do_execsql_test indexedby-3.3 {
000134 EXPLAIN QUERY PLAN
000135 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
000136 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000137 do_test indexedby-3.4 {
000138 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
000139 } {1 {no query solution}}
000140 do_test indexedby-3.5 {
000141 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
000142 } {1 {no query solution}}
000143 do_test indexedby-3.6 {
000144 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
000145 } {0 {}}
000146 do_test indexedby-3.7 {
000147 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
000148 } {0 {}}
000149
000150 do_execsql_test indexedby-3.8 {
000151 EXPLAIN QUERY PLAN
000152 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e
000153 } {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}}
000154 do_execsql_test indexedby-3.9 {
000155 EXPLAIN QUERY PLAN
000156 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10
000157 } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}}
000158 do_test indexedby-3.10 {
000159 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
000160 } {1 {no query solution}}
000161 do_test indexedby-3.11 {
000162 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
000163 } {1 {no such index: sqlite_autoindex_t3_2}}
000164
000165 # Tests for multiple table cases.
000166 #
000167 do_execsql_test indexedby-4.1 {
000168 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c
000169 } {
000170 0 0 0 {SCAN TABLE t1}
000171 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)}
000172 }
000173 do_execsql_test indexedby-4.2 {
000174 EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c
000175 } {
000176 0 0 1 {SCAN TABLE t2}
000177 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
000178 }
000179 do_test indexedby-4.3 {
000180 catchsql {
000181 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
000182 }
000183 } {1 {no query solution}}
000184 do_test indexedby-4.4 {
000185 catchsql {
000186 SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
000187 }
000188 } {1 {no query solution}}
000189
000190 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
000191 # also tests that nothing bad happens if an index refered to by
000192 # a CREATE VIEW statement is dropped and recreated.
000193 #
000194 do_execsql_test indexedby-5.1 {
000195 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
000196 EXPLAIN QUERY PLAN SELECT * FROM v2
000197 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
000198 do_execsql_test indexedby-5.2 {
000199 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10
000200 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
000201 do_test indexedby-5.3 {
000202 execsql { DROP INDEX i1 }
000203 catchsql { SELECT * FROM v2 }
000204 } {1 {no such index: i1}}
000205 do_test indexedby-5.4 {
000206 # Recreate index i1 in such a way as it cannot be used by the view query.
000207 execsql { CREATE INDEX i1 ON t1(b) }
000208 catchsql { SELECT * FROM v2 }
000209 } {1 {no query solution}}
000210 do_test indexedby-5.5 {
000211 # Drop and recreate index i1 again. This time, create it so that it can
000212 # be used by the query.
000213 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
000214 catchsql { SELECT * FROM v2 }
000215 } {0 {}}
000216
000217 # Test that "NOT INDEXED" may use the rowid index, but not others.
000218 #
000219 do_execsql_test indexedby-6.1 {
000220 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid
000221 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000222 do_execsql_test indexedby-6.2 {
000223 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid
000224 } {0 0 0 {SCAN TABLE t1}}
000225
000226 # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
000227 # query planner to use a particular named index on a DELETE, SELECT, or
000228 # UPDATE statement.
000229 #
000230 # Test that "INDEXED BY" can be used in a DELETE statement.
000231 #
000232 do_execsql_test indexedby-7.1 {
000233 EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5
000234 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000235 do_execsql_test indexedby-7.2 {
000236 EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5
000237 } {0 0 0 {SCAN TABLE t1}}
000238 do_execsql_test indexedby-7.3 {
000239 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5
000240 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000241 do_execsql_test indexedby-7.4 {
000242 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
000243 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000244 do_execsql_test indexedby-7.5 {
000245 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
000246 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000247 do_test indexedby-7.6 {
000248 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
000249 } {1 {no query solution}}
000250
000251 # Test that "INDEXED BY" can be used in an UPDATE statement.
000252 #
000253 do_execsql_test indexedby-8.1 {
000254 EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5
000255 } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
000256 do_execsql_test indexedby-8.2 {
000257 EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5
000258 } {0 0 0 {SCAN TABLE t1}}
000259 do_execsql_test indexedby-8.3 {
000260 EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5
000261 } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
000262 do_execsql_test indexedby-8.4 {
000263 EXPLAIN QUERY PLAN
000264 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
000265 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000266 do_execsql_test indexedby-8.5 {
000267 EXPLAIN QUERY PLAN
000268 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
000269 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000270 do_test indexedby-8.6 {
000271 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
000272 } {1 {no query solution}}
000273
000274 # Test that bug #3560 is fixed.
000275 #
000276 do_test indexedby-9.1 {
000277 execsql {
000278 CREATE TABLE maintable( id integer);
000279 CREATE TABLE joinme(id_int integer, id_text text);
000280 CREATE INDEX joinme_id_text_idx on joinme(id_text);
000281 CREATE INDEX joinme_id_int_idx on joinme(id_int);
000282 }
000283 } {}
000284 do_test indexedby-9.2 {
000285 catchsql {
000286 select * from maintable as m inner join
000287 joinme as j indexed by joinme_id_text_idx
000288 on ( m.id = j.id_int)
000289 }
000290 } {1 {no query solution}}
000291 do_test indexedby-9.3 {
000292 catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
000293 } {1 {no query solution}}
000294
000295 # Make sure we can still create tables, indices, and columns whose name
000296 # is "indexed".
000297 #
000298 do_test indexedby-10.1 {
000299 execsql {
000300 CREATE TABLE indexed(x,y);
000301 INSERT INTO indexed VALUES(1,2);
000302 SELECT * FROM indexed;
000303 }
000304 } {1 2}
000305 do_test indexedby-10.2 {
000306 execsql {
000307 CREATE INDEX i10 ON indexed(x);
000308 SELECT * FROM indexed indexed by i10 where x>0;
000309 }
000310 } {1 2}
000311 do_test indexedby-10.3 {
000312 execsql {
000313 DROP TABLE indexed;
000314 CREATE TABLE t10(indexed INTEGER);
000315 INSERT INTO t10 VALUES(1);
000316 CREATE INDEX indexed ON t10(indexed);
000317 SELECT * FROM t10 indexed by indexed WHERE indexed>0
000318 }
000319 } {1}
000320
000321 #-------------------------------------------------------------------------
000322 # Ensure that the rowid at the end of each index entry may be used
000323 # for equality constraints in the same way as other indexed fields.
000324 #
000325 do_execsql_test 11.1 {
000326 CREATE TABLE x1(a, b TEXT);
000327 CREATE INDEX x1i ON x1(a, b);
000328 INSERT INTO x1 VALUES(1, 1);
000329 INSERT INTO x1 VALUES(1, 1);
000330 INSERT INTO x1 VALUES(1, 1);
000331 INSERT INTO x1 VALUES(1, 1);
000332 }
000333 do_execsql_test 11.2 {
000334 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid=3;
000335 } {1 1 3}
000336 do_execsql_test 11.3 {
000337 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3';
000338 } {1 1 3}
000339 do_execsql_test 11.4 {
000340 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
000341 } {1 1 3}
000342 do_eqp_test 11.5 {
000343 SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
000344 } {0 0 0 {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}}
000345
000346 do_execsql_test 11.6 {
000347 CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT);
000348 CREATE INDEX x2i ON x2(a, b);
000349 INSERT INTO x2 VALUES(1, 1, 1);
000350 INSERT INTO x2 VALUES(2, 1, 1);
000351 INSERT INTO x2 VALUES(3, 1, 1);
000352 INSERT INTO x2 VALUES(4, 1, 1);
000353 }
000354 do_execsql_test 11.7 {
000355 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c=3;
000356 } {1 1 3}
000357 do_execsql_test 11.8 {
000358 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3';
000359 } {1 1 3}
000360 do_execsql_test 11.9 {
000361 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
000362 } {1 1 3}
000363 do_eqp_test 11.10 {
000364 SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
000365 } {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}}
000366
000367 finish_test