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 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} {/*SEARCH t1 USING INDEX t1a (a=?)*/} 41do_test analyze7-1.1 { 42 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 43} {/*SEARCH t1 USING INDEX t1b (b=?)*/} 44do_test analyze7-1.2 { 45 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 46} {/*SEARCH 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# 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} {/*SEARCH t1 USING INDEX t1a (a=?)*/} 57do_test analyze7-2.1 { 58 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 59} {/*SEARCH t1 USING INDEX t1b (b=?)*/} 60do_test analyze7-2.2 { 61 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} 62} {/*SEARCH 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# 67do_test analyze7-2.3 { 68 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} 69} {/*SEARCH 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# 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} {/*SEARCH t1 USING INDEX t1a (a=?)*/} 79do_test analyze7-3.1 { 80 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} 81} {/*SEARCH t1 USING INDEX t1b (b=?)*/} 82do_test analyze7-3.2.1 { 83 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} 84} {/*SEARCH t1 USING INDEX t1cd (c=?)*/} 85ifcapable stat4 { 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 } {/*SEARCH 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 } {/*SEARCH t1 USING INDEX t1cd (c=?)*/} 97} 98do_test analyze7-3.3 { 99 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} 100} {/*SEARCH t1 USING INDEX t1a (a=?)*/} 101 102ifcapable {!stat4} { 103 do_test analyze7-3.4 { 104 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123} 105 } {/*SEARCH 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 } {/*SEARCH t1 USING INDEX t1a (a=?)*/} 109} 110do_test analyze7-3.6 { 111 execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123} 112} {/*SEARCH t1 USING INDEX t1cd (c=? AND d=?)*/} 113 114finish_test 115