AryaWu/sqlite

1# 2008-10-042#3# The author disclaims copyright to this source code.  In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set ::testprefix indexedby16 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 Plan36#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_eqp_test indexedby-1.2 {44  select * from t1 WHERE a = 10; 45} {SEARCH t1 USING INDEX i1 (a=?)}46do_eqp_test indexedby-1.3 {47  select * from t1 ; 48} {SCAN t1}49do_eqp_test indexedby-1.4 {50  select * from t1, t2 WHERE c = 10; 51} {52  QUERY PLAN53  |--SEARCH t2 USING INDEX i3 (c=?)54  `--SCAN t155}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 or59# SQL view. Also test that specifying an index that does not exist or60# is attached to a different table is detected as an error.61#62# X-EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name63# 64# EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase65# specifies that the named index must be used in order to look up values66# on the preceding table.67#68do_test indexedby-2.1 {69  execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}70} {}71do_test indexedby-2.1b {72  execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}73} {}74do_test indexedby-2.2 {75  execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}76} {}77do_test indexedby-2.2b {78  execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}79} {}80do_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 the84# optimizer hints about which index to use; it gives the optimizer a85# requirement of which index to use.86# EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be87# used for the query, then the preparation of the SQL statement fails.88#89do_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 the94# index specified by the INDEXED BY clause, then the query will fail95# with an error.96do_test indexedby-2.4.1 {97  catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }98} {0 {}}99 100do_test indexedby-2.5 {101  catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}102} {1 {no such index: i5}}103do_test indexedby-2.6 {104  catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}105} {1 {near "WHERE": syntax error}}106do_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 no114# index shall be used when accessing the preceding table, including115# 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#119do_eqp_test indexedby-3.1 {120  SELECT * FROM t1 WHERE a = 'one' AND b = 'two'121} {/SEARCH t1 USING INDEX/}122do_eqp_test indexedby-3.1.1 {123  SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'124} {SCAN t1}125do_eqp_test indexedby-3.1.2 {126  SELECT * FROM t1 NOT INDEXED WHERE rowid=1127} {/SEARCH t1 USING INTEGER PRIMARY KEY .rowid=/}128 129 130do_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=?)}133do_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=?)}136do_test indexedby-3.4 {137  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }138} {0 {}}139do_test indexedby-3.5 {140  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }141} {0 {}}142do_test indexedby-3.6 {143  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }144} {0 {}}145do_test indexedby-3.7 {146  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }147} {0 {}}148 149do_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}152do_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=?)}155do_test indexedby-3.10 {156  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }157} {0 {}}158do_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#164do_eqp_test indexedby-4.1 {165  SELECT * FROM t1, t2 WHERE a = c 166} {167  QUERY PLAN168  |--SCAN t1169  `--SEARCH t2 USING INDEX i3 (c=?)170}171do_eqp_test indexedby-4.2 {172  SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 173} {174  QUERY PLAN175  |--SCAN t1 USING INDEX i1176  `--SEARCH t2 USING INDEX i3 (c=?)177}178do_test indexedby-4.3 {179  catchsql {180    SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c181  }182} {0 {}}183do_test indexedby-4.4 {184  catchsql {185    SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c186  }187} {0 {}}188 189# Test embedding an INDEXED BY in a CREATE VIEW statement. This block190# also tests that nothing bad happens if an index referred to by191# a CREATE VIEW statement is dropped and recreated.192#193do_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>?)*/}197do_execsql_test indexedby-5.2 {198  EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 199} {/*SEARCH t1 USING INDEX i1 (a>?)*/}200do_test indexedby-5.3 {201  execsql { DROP INDEX i1 }202  catchsql { SELECT * FROM v2 }203} {1 {no such index: i1}}204do_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 {}}209do_test indexedby-5.5 {210  # Drop and recreate index i1 again. This time, create it so that it can211  # 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# 218do_eqp_test indexedby-6.1 {219  SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 220} {SEARCH t1 USING INDEX i2 (b=?)}221do_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 SQLite226# query planner to use a particular named index on a DELETE, SELECT, or227# UPDATE statement.228#229# Test that "INDEXED BY" can be used in a DELETE statement.230# 231do_eqp_test indexedby-7.1 {232  DELETE FROM t1 WHERE a = 5 233} {SEARCH t1 USING INDEX i1 (a=?)}234do_eqp_test indexedby-7.2 {235  DELETE FROM t1 NOT INDEXED WHERE a = 5 236} {SCAN t1}237do_eqp_test indexedby-7.3 {238  DELETE FROM t1 INDEXED BY i1 WHERE a = 5 239} {SEARCH t1 USING INDEX i1 (a=?)}240do_eqp_test indexedby-7.4 {241  DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10242} {SEARCH t1 USING INDEX i1 (a=?)}243do_eqp_test indexedby-7.5 {244  DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10245} {SEARCH t1 USING INDEX i2 (b=?)}246do_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# 252do_eqp_test indexedby-8.1 {253  UPDATE t1 SET rowid=rowid+1 WHERE a = 5 254} {SEARCH t1 USING COVERING INDEX i1 (a=?)}255do_eqp_test indexedby-8.2 {256  UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 257} {SCAN t1}258do_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=?)}261do_eqp_test indexedby-8.4 {262  UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10263} {SEARCH t1 USING INDEX i1 (a=?)}264do_eqp_test indexedby-8.5 {265  UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10266} {SEARCH t1 USING INDEX i2 (b=?)}267do_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#273do_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} {}281do_test indexedby-9.2 {282  catchsql {283    select * from maintable as m inner join284    joinme as j indexed by joinme_id_text_idx285    on ( m.id  = j.id_int)286  }287} {0 {}}288do_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 name293# is "indexed".294#295do_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}302do_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}308do_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>0315  }316} {1}317 318#-------------------------------------------------------------------------319# Ensure that the rowid at the end of each index entry may be used320# for equality constraints in the same way as other indexed fields.321#322do_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}330do_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}333do_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}336do_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}339do_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 343do_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}351do_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}354do_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}357do_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}360do_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.367do_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}372do_catchsql_test 12.2 {373  SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;374} {1 {no query solution}}375do_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}381do_catchsql_test 12.4 {382  SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;383} {1 {no query solution}}384 385finish_test386