test/autoindex3.test

   1 # 2014-06-17
   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 # This file implements regression tests for SQLite library.  The
  13 # focus of this script is testing automatic index creation logic,
  14 # and specifically that an automatic index will not be created that
  15 # shadows a declared index.
  16 #
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20 set testprefix autoindex3
  21
  22 # The t1b and t2d indexes are not very selective.  It used to be that
  23 # the autoindex mechanism would create automatic indexes on t1(b) or
  24 # t2(d), make assumptions that they were reasonably selective, and use
  25 # them instead of t1b or t2d.  But that would be cheating, because the
  26 # automatic index cannot be any more selective than the real index.
  27 #
  28 # This test verifies that the cheat is no longer allowed.
  29 #
  30 do_execsql_test autoindex3-100 {
  31   CREATE TABLE t1(a,b,x);
  32   CREATE TABLE t2(c,d,y);
  33   CREATE INDEX t1b ON t1(b);
  34   CREATE INDEX t2d ON t2(d);
  35   ANALYZE sqlite_master;
  36   INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500');
  37   INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500');
  38   ANALYZE sqlite_master;
  39   EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b;
  40 } {~/AUTO/}
  41
  42 # Automatic indexes can still be used if existing indexes do not
  43 # participate in == constraints.
  44 #
  45 do_execsql_test autoindex3-110 {
  46   EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y;
  47 } {/AUTO/}
  48 do_execsql_test autoindex3-120 {
  49   EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y;
  50 } {/AUTO/}
  51 do_execsql_test autoindex3-130 {
  52   EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y;
  53 } {/AUTO/}
  54 do_execsql_test autoindex3-140 {
  55   EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y;
  56 } {/AUTO/}
  57
  58 reset_db
  59 do_execsql_test 210 {
  60   CREATE TABLE v(b, d, e);
  61   CREATE TABLE u(a, b, c);
  62   ANALYZE sqlite_master;
  63   INSERT INTO "sqlite_stat1" VALUES('u','uab','40000 400 1');
  64   INSERT INTO "sqlite_stat1" VALUES('v','vbde','40000 400 1 1');
  65   INSERT INTO "sqlite_stat1" VALUES('v','ve','40000 21');
  66
  67   CREATE INDEX uab on u(a, b);
  68   CREATE INDEX ve on v(e);
  69   CREATE INDEX vbde on v(b,d,e);
  70
  71   DROP TABLE IF EXISTS sqlite_stat4;
  72   ANALYZE sqlite_master;
  73 }
  74
  75 # At one point, SQLite was using the inferior plan:
  76 #
  77 #   0|0|1|SEARCH v USING INDEX ve (e>?)
  78 #   0|1|0|SEARCH u USING COVERING INDEX uab (ANY(a) AND b=?)
  79 #
  80 # on the basis that the real index "uab" must be better than the automatic
  81 # index. This is not right - a skip-scan is not necessarily better than an
  82 # automatic index scan.
  83 #
  84 do_eqp_test 220 {
  85   select count(*) from u, v where u.b = v.b and v.e > 34;
  86 } {
  87   QUERY PLAN
  88   |--SEARCH v USING INDEX ve (e>?)
  89   `--SEARCH u USING AUTOMATIC COVERING INDEX (b=?)
  90 }
  91
  92
  93 finish_test