Return to analyze7.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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:   register_wholenumber_module db
   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=?) (~10 rows)}}
   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=?) (~10 rows)}}
   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=?) (~10 rows)}}
   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=?) (~1 rows)}}
   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=?) (~10 rows)}}
   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=?) (~10 rows)}}
   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=?) (~1 rows)}}
   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=?) (~1 rows)}}
   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=?) (~10 rows)}}
   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=?) (~86 rows)}}
   85: ifcapable stat3 {
   86:   # If ENABLE_STAT3 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=?) (~57 rows)}}
   91: } else {
   92:   # If ENABLE_STAT3 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=?) (~86 rows)}}
   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=?) (~1 rows)}}
  101: ifcapable {!stat3} {
  102:   do_test analyze7-3.4 {
  103:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
  104:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
  105:   do_test analyze7-3.5 {
  106:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
  107:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
  108: }
  109: do_test analyze7-3.6 {
  110:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
  111: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}
  112: 
  113: finish_test