wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 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 | } |
||
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 | do_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)}} |
||
104 | do_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)}} |
||
107 | do_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 | |||
111 | finish_test |