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