OLD | NEW |
| (Empty) |
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 | |
OLD | NEW |