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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# There is nothing to test if ANALYZE is disable for this build.
20#
21ifcapable {!analyze||!vtab} {
22  finish_test
23  return
24}
25
26# Generate some test data
27#
28do_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)}}
41do_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)}}
44do_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#
52do_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)}}
57do_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)}}
60do_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#
67do_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#
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} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
79do_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)}}
82do_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)}}
85ifcapable stat2 {
86  # If ENABLE_STAT2 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=?) (~51 rows)}}
91} else {
92  # If ENABLE_STAT2 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}
98do_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)}}
101do_test analyze7-3.4 {
102  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
103} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
104do_test analyze7-3.5 {
105  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
106} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
107do_test analyze7-3.6 {
108  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
109} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}
110
111finish_test
112