AryaWu/sqlite

1# 2011 April 12#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# This file implements regression tests for SQLite library.12# This file implements tests for the ANALYZE command when an idnex13# name is given as the argument.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# There is nothing to test if ANALYZE is disable for this build.20#21ifcapable {!analyze||!vtab} {22  finish_test23  return24}25 26# Generate some test data27#28do_test analyze7-1.0 {29  load_static_extension db wholenumber30  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 nums37                    WHERE value BETWEEN 1 AND 256;38    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;39  }40} {/*SEARCH t1 USING INDEX t1a (a=?)*/}41do_test analyze7-1.1 {42  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}43} {/*SEARCH t1 USING INDEX t1b (b=?)*/}44do_test analyze7-1.2 {45  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}46} {/*SEARCH t1 USING INDEX t1cd (c=?)*/}47 48# Run an analyze on one of the three indices.  Verify that this49# effects the row-count estimate on the one query that uses that50# one index.51#52do_test analyze7-2.0 {53  execsql {ANALYZE t1a;}54  db cache flush55  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}56} {/*SEARCH t1 USING INDEX t1a (a=?)*/}57do_test analyze7-2.1 {58  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}59} {/*SEARCH t1 USING INDEX t1b (b=?)*/}60do_test analyze7-2.2 {61  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}62} {/*SEARCH t1 USING INDEX t1cd (c=?)*/}63 64# Verify that since the query planner now things that t1a is more65# selective than t1b, it prefers to use t1a.66#67do_test analyze7-2.3 {68  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}69} {/*SEARCH t1 USING INDEX t1a (a=?)*/}70 71# Run an analysis on another of the three indices.  Verify  that this72# new analysis works and does not disrupt the previous analysis.73#74do_test analyze7-3.0 {75  execsql {ANALYZE t1cd;}76  db cache flush;77  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}78} {/*SEARCH t1 USING INDEX t1a (a=?)*/}79do_test analyze7-3.1 {80  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}81} {/*SEARCH t1 USING INDEX t1b (b=?)*/}82do_test analyze7-3.2.1 {83  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}84} {/*SEARCH t1 USING INDEX t1cd (c=?)*/}85ifcapable stat4 {86  # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated87  # 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  } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}91} else {92  # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the93  # same as that for (c=?).94  do_test analyze7-3.2.3 {95    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}96  } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}97}98do_test analyze7-3.3 {99  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}100} {/*SEARCH t1 USING INDEX t1a (a=?)*/}101 102ifcapable {!stat4} {103  do_test analyze7-3.4 {104    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}105  } {/*SEARCH 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  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}109}110do_test analyze7-3.6 {111  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}112} {/*SEARCH t1 USING INDEX t1cd (c=? AND d=?)*/}113 114finish_test115