# 2013 August 3 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file contains automated tests used to verify that the current build # (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3 # and stat4 data. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyzeA ifcapable !stat4&&!stat3 { finish_test return } # Populate the stat3 table according to the current contents of the db # proc populate_stat3 {{bDropTable 1}} { # Open a second connection on database "test.db" and run ANALYZE. If this # is an ENABLE_STAT3 build, this is all that is required to create and # populate the sqlite_stat3 table. # sqlite3 db2 test.db execsql { ANALYZE } # Now, if this is an ENABLE_STAT4 build, create and populate the # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE # above. Then drop the sqlite_stat4 table. # ifcapable stat4 { db2 func lindex lindex execsql { PRAGMA writable_schema = on; CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample); INSERT INTO sqlite_stat3 SELECT DISTINCT tbl, idx, lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0) FROM sqlite_stat4; } db2 if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 } execsql { PRAGMA writable_schema = off } } # Modify the database schema cookie to ensure that the other connection # reloads the schema. # execsql { CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } # Populate the stat4 table according to the current contents of the db # proc populate_stat4 {{bDropTable 1}} { sqlite3 db2 test.db execsql { ANALYZE } ifcapable stat3 { execsql { PRAGMA writable_schema = on; CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); INSERT INTO sqlite_stat4 SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) FROM sqlite_stat3; } db2 if {$bDropTable} { execsql {DROP TABLE sqlite_stat3} db2 } execsql { PRAGMA writable_schema = off } } # Modify the database schema cookie to ensure that the other connection # reloads the schema. # execsql { CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } # Populate the stat4 table according to the current contents of the db. # Leave deceptive data in the stat3 table. This data should be ignored # in favour of that from the stat4 table. # proc populate_both {} { ifcapable stat4 { populate_stat3 0 } ifcapable stat3 { populate_stat4 0 } sqlite3 db2 test.db execsql { PRAGMA writable_schema = on; UPDATE sqlite_stat3 SET idx = CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b' END; PRAGMA writable_schema = off; CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } foreach {tn analyze_cmd} { 1 populate_stat4 2 populate_stat3 3 populate_both } { reset_db do_test 1.$tn.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT) } for {set i 0} {$i < 100} {incr i} { set c [expr int(pow(1.1,$i)/100)] set b [expr 125 - int(pow(1.1,99-$i))/100] execsql {INSERT INTO t1 VALUES($i, $b, $c)} } } {} execsql { CREATE INDEX t1b ON t1(b) } execsql { CREATE INDEX t1c ON t1(c) } $analyze_cmd do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1 do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0 } 49 do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125 } 49 do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16 } 1 do_eqp_test 1.$tn.2.5 { SELECT * FROM t1 WHERE b = 31 AND c = 0; } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_eqp_test 1.$tn.2.6 { SELECT * FROM t1 WHERE b = 125 AND c = 16; } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}} do_execsql_test 1.$tn.3.1 { SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 } {6} do_execsql_test 1.$tn.3.2 { SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 } {90} do_execsql_test 1.$tn.3.3 { SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125 } {90} do_execsql_test 1.$tn.3.4 { SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125 } {6} do_eqp_test 1.$tn.3.5 { SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b? AND c? AND b? AND b? AND c? AND c