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