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 112
