test/analyze7.test

   1 # 2011 April 1
   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 # This file implements regression tests for SQLite library.
  12 # This file implements tests for the ANALYZE command when an idnex
  13 # name is given as the argument.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # There is nothing to test if ANALYZE is disable for this build.
  20 #
  21 ifcapable {!analyze||!vtab} {
  22   finish_test
  23   return
  24 }
  25
  26 # Generate some test data
  27 #
  28 do_test analyze7-1.0 {
  29   load_static_extension db wholenumber
  30   execsql {
  31     CREATE TABLE t1(a,b,c,d);
  32     CREATE INDEX t1a ON t1(a);
  33     CREATE INDEX t1b ON t1(b);
  34     CREATE INDEX t1cd ON t1(c,d);
  35     CREATE VIRTUAL TABLE nums USING wholenumber;
  36     INSERT INTO t1 SELECT value, value, value/100, value FROM nums
  37                     WHERE value BETWEEN 1 AND 256;
  38     EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
  39   }
  40 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
  41 do_test analyze7-1.1 {
  42   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
  43 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  44 do_test analyze7-1.2 {
  45   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  46 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
  47
  48 # Run an analyze on one of the three indices.  Verify that this
  49 # effects the row-count estimate on the one query that uses that
  50 # one index.
  51 #
  52 do_test analyze7-2.0 {
  53   execsql {ANALYZE t1a;}
  54   db cache flush
  55   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
  56 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
  57 do_test analyze7-2.1 {
  58   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
  59 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  60 do_test analyze7-2.2 {
  61   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  62 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
  63
  64 # Verify that since the query planner now things that t1a is more
  65 # selective than t1b, it prefers to use t1a.
  66 #
  67 do_test analyze7-2.3 {
  68   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
  69 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
  70
  71 # Run an analysis on another of the three indices.  Verify  that this
  72 # new analysis works and does not disrupt the previous analysis.
  73 #
  74 do_test analyze7-3.0 {
  75   execsql {ANALYZE t1cd;}
  76   db cache flush;
  77   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
  78 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
  79 do_test analyze7-3.1 {
  80   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
  81 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  82 do_test analyze7-3.2.1 {
  83   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
  84 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
  85 ifcapable stat4||stat3 {
  86   # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated
  87   # row count for (c=2) than it does for (c=?).
  88   do_test analyze7-3.2.2 {
  89     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  90   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
  91 } else {
  92   # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the
  93   # same as that for (c=?).
  94   do_test analyze7-3.2.3 {
  95     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  96   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
  97 }
  98 do_test analyze7-3.3 {
  99   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
 100 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
 101
 102 ifcapable {!stat4 && !stat3} {
 103   do_test analyze7-3.4 {
 104     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
 105   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
 106   do_test analyze7-3.5 {
 107     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
 108   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
 109 }
 110 do_test analyze7-3.6 {
 111   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
 112 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}}
 113
 114 finish_test