test/indexedby.test

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