| # 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<?)}} |
| |
| do_eqp_test 1.$tn.3.6 { |
| SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125 |
| } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} |
| |
| do_eqp_test 1.$tn.3.7 { |
| 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<?)}} |
| |
| do_eqp_test 1.$tn.3.8 { |
| SELECT * FROM t1 |
| WHERE b BETWEEN cast('0' AS int) AND cast('50.0' AS real) |
| AND c BETWEEN cast('0' AS numeric) AND cast('50.0' AS real) |
| } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} |
| |
| do_eqp_test 1.$tn.3.9 { |
| SELECT * FROM t1 WHERE b BETWEEN +75 AND +125 AND c BETWEEN +75 AND +125 |
| } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} |
| |
| do_eqp_test 1.$tn.3.10 { |
| SELECT * FROM t1 |
| WHERE b BETWEEN cast('75' AS int) AND cast('125.0' AS real) |
| AND c BETWEEN cast('75' AS numeric) AND cast('125.0' AS real) |
| } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} |
| } |
| |
| finish_test |