diff options
Diffstat (limited to 'test/analyzeB.test')
| -rw-r--r-- | test/analyzeB.test | 683 |
1 files changed, 683 insertions, 0 deletions
diff --git a/test/analyzeB.test b/test/analyzeB.test new file mode 100644 index 0000000..2a78c18 --- /dev/null +++ b/test/analyzeB.test @@ -0,0 +1,683 @@ +# 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 sqlite_stat3 +# functionality is working. The tests in this file are based on a subset +# of the sqlite_stat4 tests in analyze9.test. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix analyzeB + +ifcapable !stat3 { + finish_test + return +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a TEXT, b TEXT); + INSERT INTO t1 VALUES('(0)', '(0)'); + INSERT INTO t1 VALUES('(1)', '(1)'); + INSERT INTO t1 VALUES('(2)', '(2)'); + INSERT INTO t1 VALUES('(3)', '(3)'); + INSERT INTO t1 VALUES('(4)', '(4)'); + CREATE INDEX i1 ON t1(a, b); +} {} + + +do_execsql_test 1.1 { + ANALYZE; +} {} + +do_execsql_test 1.2 { + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; +} { + t1 i1 1 0 0 '(0)' + t1 i1 1 1 1 '(1)' + t1 i1 1 2 2 '(2)' + t1 i1 1 3 3 '(3)' + t1 i1 1 4 4 '(4)' +} + +if {[permutation] != "utf16"} { + do_execsql_test 1.3 { + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; + } { + t1 i1 1 0 0 '(0)' + t1 i1 1 1 1 '(1)' + t1 i1 1 2 2 '(2)' + t1 i1 1 3 3 '(3)' + t1 i1 1 4 4 '(4)' + } +} + + +#------------------------------------------------------------------------- +# This is really just to test SQL user function "test_decode". +# +reset_db +do_execsql_test 2.1 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1(a) VALUES('some text'); + INSERT INTO t1(a) VALUES(14); + INSERT INTO t1(a) VALUES(NULL); + INSERT INTO t1(a) VALUES(22.0); + INSERT INTO t1(a) VALUES(x'656667'); + CREATE INDEX i1 ON t1(a, b, c); + ANALYZE; + SELECT quote(sample) FROM sqlite_stat3; +} { + NULL 14 22.0 {'some text'} X'656667' +} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t2(a, b); + CREATE INDEX i2 ON t2(a, b); + BEGIN; +} + +do_test 3.2 { + for {set i 0} {$i < 1000} {incr i} { + set a [expr $i / 10] + set b [expr int(rand() * 15.0)] + execsql { INSERT INTO t2 VALUES($a, $b) } + } + execsql COMMIT +} {} + +db func lindex lindex + +# Each value of "a" occurs exactly 10 times in the table. +# +do_execsql_test 3.3.1 { + SELECT count(*) FROM t2 GROUP BY a; +} [lrange [string repeat "10 " 100] 0 99] + +# The first element in the "nEq" list of all samples should therefore be 10. +# +do_execsql_test 3.3.2 { + ANALYZE; + SELECT nEq FROM sqlite_stat3; +} [lrange [string repeat "10 " 100] 0 23] + +#------------------------------------------------------------------------- +# +do_execsql_test 3.4 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(1, 1, 'one-a'); + INSERT INTO t1 VALUES(11, 1, 'one-b'); + INSERT INTO t1 VALUES(21, 1, 'one-c'); + INSERT INTO t1 VALUES(31, 1, 'one-d'); + INSERT INTO t1 VALUES(41, 1, 'one-e'); + INSERT INTO t1 VALUES(51, 1, 'one-f'); + INSERT INTO t1 VALUES(61, 1, 'one-g'); + INSERT INTO t1 VALUES(71, 1, 'one-h'); + INSERT INTO t1 VALUES(81, 1, 'one-i'); + INSERT INTO t1 VALUES(91, 1, 'one-j'); + INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1; + INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + CREATE INDEX t1b ON t1(b); + ANALYZE; + SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60; +} {three-d three-e three-f} + + +#------------------------------------------------------------------------- +# These tests verify that the sample selection for stat3 appears to be +# working as designed. +# + +reset_db +db func lindex lindex +db func lrange lrange + +do_execsql_test 4.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(c, b, a); +} + + +proc insert_filler_rows_n {iStart args} { + set A(-ncopy) 1 + set A(-nval) 1 + + foreach {k v} $args { + if {[info exists A($k)]==0} { error "no such option: $k" } + set A($k) $v + } + if {[llength $args] % 2} { + error "option requires an argument: [lindex $args end]" + } + + for {set i 0} {$i < $A(-nval)} {incr i} { + set iVal [expr $iStart+$i] + for {set j 0} {$j < $A(-ncopy)} {incr j} { + execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) } + } + } +} + +do_test 4.1 { + execsql { BEGIN } + insert_filler_rows_n 0 -ncopy 10 -nval 19 + insert_filler_rows_n 20 -ncopy 1 -nval 100 + + execsql { + INSERT INTO t1(c, b, a) VALUES(200, 1, 'a'); + INSERT INTO t1(c, b, a) VALUES(200, 1, 'b'); + INSERT INTO t1(c, b, a) VALUES(200, 1, 'c'); + + INSERT INTO t1(c, b, a) VALUES(200, 2, 'e'); + INSERT INTO t1(c, b, a) VALUES(200, 2, 'f'); + + INSERT INTO t1(c, b, a) VALUES(201, 3, 'g'); + INSERT INTO t1(c, b, a) VALUES(201, 4, 'h'); + + ANALYZE; + SELECT count(*) FROM sqlite_stat3; + SELECT count(*) FROM t1; + } +} {24 297} + +do_execsql_test 4.2 { + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16; +} { + 10 0 0 0 + 10 10 1 1 + 10 20 2 2 + 10 30 3 3 + 10 40 4 4 + 10 50 5 5 + 10 60 6 6 + 10 70 7 7 + 10 80 8 8 + 10 90 9 9 + 10 100 10 10 + 10 110 11 11 + 10 120 12 12 + 10 130 13 13 + 10 140 14 14 + 10 150 15 15 +} + +do_execsql_test 4.3 { + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 + ORDER BY rowid DESC LIMIT 2; +} { + 2 295 120 201 + 5 290 119 200 +} + +do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 +do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 + +reset_db +do_test 4.7 { + execsql { + BEGIN; + CREATE TABLE t1(o,t INTEGER PRIMARY KEY); + CREATE INDEX i1 ON t1(o); + } + for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} { + execsql { INSERT INTO t1 VALUES('x', $i) } + } + execsql { + COMMIT; + ANALYZE; + SELECT count(*) FROM sqlite_stat3; + } +} {1} +do_execsql_test 4.8 { + SELECT sample FROM sqlite_stat3; +} {x} + + +#------------------------------------------------------------------------- +# The following would cause a crash at one point. +# +reset_db +do_execsql_test 5.1 { + PRAGMA encoding = 'utf-16'; + CREATE TABLE t0(v); + ANALYZE; +} + +#------------------------------------------------------------------------- +# This was also crashing (corrupt sqlite_stat3 table). +# +reset_db +do_execsql_test 6.1 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t1(b); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(2, 2); + INSERT INTO t1 VALUES(3, 3); + INSERT INTO t1 VALUES(4, 4); + INSERT INTO t1 VALUES(5, 5); + ANALYZE; + PRAGMA writable_schema = 1; + CREATE TEMP TABLE x1 AS + SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3 + ORDER BY (rowid%5), rowid; + DELETE FROM sqlite_stat3; + INSERT INTO sqlite_stat3 SELECT * FROM x1; + PRAGMA writable_schema = 0; + ANALYZE sqlite_master; +} +do_execsql_test 6.2 { + SELECT * FROM t1 WHERE a = 'abc'; +} + +#------------------------------------------------------------------------- +# The following tests experiment with adding corrupted records to the +# 'sample' column of the sqlite_stat3 table. +# +reset_db +sqlite3_db_config_lookaside db 0 0 0 + +do_execsql_test 7.1 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a, b); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(2, 2); + INSERT INTO t1 VALUES(3, 3); + INSERT INTO t1 VALUES(4, 4); + INSERT INTO t1 VALUES(5, 5); + ANALYZE; + UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1; + ANALYZE sqlite_master; +} + +do_execsql_test 7.2 { + UPDATE sqlite_stat3 SET sample = X'FFFF'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 1; +} {1 1} + +do_execsql_test 7.3 { + ANALYZE; + UPDATE sqlite_stat3 SET neq = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 1; +} {1 1} + +do_execsql_test 7.4 { + ANALYZE; + UPDATE sqlite_stat3 SET ndlt = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 3; +} {3 3} + +do_execsql_test 7.5 { + ANALYZE; + UPDATE sqlite_stat3 SET nlt = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 5; +} {5 5} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 8.1 { + CREATE TABLE t1(x TEXT); + CREATE INDEX i1 ON t1(x); + INSERT INTO t1 VALUES('1'); + INSERT INTO t1 VALUES('2'); + INSERT INTO t1 VALUES('3'); + INSERT INTO t1 VALUES('4'); + ANALYZE; +} +do_execsql_test 8.2 { + SELECT * FROM t1 WHERE x = 3; +} {3} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 9.1 { + CREATE TABLE t1(a, b, c, d, e); + CREATE INDEX i1 ON t1(a, b, c, d); + CREATE INDEX i2 ON t1(e); +} +do_test 9.2 { + execsql BEGIN; + for {set i 0} {$i < 100} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" + } + for {set i 0} {$i < 20} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" + } + for {set i 102} {$i < 200} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" + } + execsql COMMIT + execsql ANALYZE +} {} + +do_eqp_test 9.3.1 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; +} {/t1 USING INDEX i1/} +do_eqp_test 9.3.2 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; +} {/t1 USING INDEX i1/} + +set value_d [expr 101] +do_eqp_test 9.4.1 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 +} {/t1 USING INDEX i1/} +set value_d [expr 99] +do_eqp_test 9.4.2 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 +} {/t1 USING INDEX i1/} + +#------------------------------------------------------------------------- +# Check that the planner takes stat3 data into account when considering +# "IS NULL" and "IS NOT NULL" constraints. +# +do_execsql_test 10.1.1 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a, b); + CREATE INDEX t3a ON t3(a); + CREATE INDEX t3b ON t3(b); +} +do_test 10.1.2 { + for {set i 1} {$i < 100} {incr i} { + if {$i>90} { set a $i } else { set a NULL } + set b [expr $i % 5] + execsql "INSERT INTO t3 VALUES($a, $b)" + } + execsql ANALYZE +} {} +do_eqp_test 10.1.3 { + SELECT * FROM t3 WHERE a IS NULL AND b = 2 +} {/t3 USING INDEX t3b/} +do_eqp_test 10.1.4 { + SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 +} {/t3 USING INDEX t3a/} + +#------------------------------------------------------------------------- +# Check that stat3 data is used correctly with non-default collation +# sequences. +# +foreach {tn schema} { + 1 { + CREATE TABLE t4(a COLLATE nocase, b); + CREATE INDEX t4a ON t4(a); + CREATE INDEX t4b ON t4(b); + } + 2 { + CREATE TABLE t4(a, b); + CREATE INDEX t4a ON t4(a COLLATE nocase); + CREATE INDEX t4b ON t4(b); + } +} { + drop_all_tables + do_test 11.$tn.1 { execsql $schema } {} + + do_test 11.$tn.2 { + for {set i 0} {$i < 100} {incr i} { + if { ($i % 10)==0 } { set a ABC } else { set a DEF } + set b [expr $i % 5] + execsql { INSERT INTO t4 VALUES($a, $b) } + } + execsql ANALYZE + } {} + + do_eqp_test 11.$tn.3 { + SELECT * FROM t4 WHERE a = 'def' AND b = 3; + } {/t4 USING INDEX t4b/} + + if {$tn==1} { + set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" + do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} + } else { + + set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" + do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} + + set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" + do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} + } +} + +#------------------------------------------------------------------------- +# Test that nothing untoward happens if the stat3 table contains entries +# for indexes that do not exist. Or NULL values in the idx column. +# Or NULL values in any of the other columns. +# +drop_all_tables +do_execsql_test 15.1 { + CREATE TABLE x1(a, b, UNIQUE(a, b)); + INSERT INTO x1 VALUES(1, 2); + INSERT INTO x1 VALUES(3, 4); + INSERT INTO x1 VALUES(5, 6); + ANALYZE; + INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); +} +db close +sqlite3 db test.db +do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} + +do_execsql_test 15.3 { + INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42); +} +db close +sqlite3 db test.db +do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} + +do_execsql_test 15.5 { + UPDATE sqlite_stat1 SET stat = NULL; +} +db close +sqlite3 db test.db +do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} + +do_execsql_test 15.7 { + ANALYZE; + UPDATE sqlite_stat1 SET tbl = 'no such tbl'; +} +db close +sqlite3 db test.db +do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} + +do_execsql_test 15.9 { + ANALYZE; + UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL; +} +db close +sqlite3 db test.db +do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} + +# This is just for coverage.... +do_execsql_test 15.11 { + ANALYZE; + UPDATE sqlite_stat1 SET stat = stat || ' unordered'; +} +db close +sqlite3 db test.db +do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} + +#------------------------------------------------------------------------- +# Test that allocations used for sqlite_stat3 samples are included in +# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. +# +set one [string repeat x 1000] +set two [string repeat x 2000] +do_test 16.1 { + reset_db + execsql { + CREATE TABLE t1(a, UNIQUE(a)); + INSERT INTO t1 VALUES($one); + ANALYZE; + } + set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] + + reset_db + execsql { + CREATE TABLE t1(a, UNIQUE(a)); + INSERT INTO t1 VALUES($two); + ANALYZE; + } + set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] + + expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050} +} {1} + +#------------------------------------------------------------------------- +# Test that stat3 data may be used with partial indexes. +# +do_test 17.1 { + reset_db + execsql { + CREATE TABLE t1(a, b, c, d); + CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; + INSERT INTO t1 VALUES(-1, -1, -1, NULL); + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; + } + + for {set i 0} {$i < 32} {incr i} { + execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } + } + execsql {ANALYZE main.t1} +} {} + +do_catchsql_test 17.1.2 { + ANALYZE temp.t1; +} {1 {no such table: temp.t1}} + +do_eqp_test 17.2 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; +} {/USING INDEX i1/} +do_eqp_test 17.3 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; +} {/USING INDEX i1/} + +do_execsql_test 17.4 { + CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL; + ANALYZE main.i2; +} +do_eqp_test 17.5 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; +} {/USING INDEX i1/} +do_eqp_test 17.6 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; +} {/USING INDEX i2/} + +#------------------------------------------------------------------------- +# +do_test 18.1 { + reset_db + execsql { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a, b); + } + for {set i 0} {$i < 9} {incr i} { + execsql { + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + INSERT INTO t1 VALUES($i, 0); + } + } + execsql ANALYZE + execsql { SELECT count(*) FROM sqlite_stat3 } +} {9} + +#------------------------------------------------------------------------- +# For coverage. +# +ifcapable view { + do_test 19.1 { + reset_db + execsql { + CREATE TABLE t1(x, y); + CREATE INDEX i1 ON t1(x, y); + CREATE VIEW v1 AS SELECT * FROM t1; + ANALYZE; + } + } {} +} +ifcapable auth { + proc authproc {op args} { + if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } + return "SQLITE_OK" + } + do_test 19.2 { + reset_db + db auth authproc + execsql { + CREATE TABLE t1(x, y); + CREATE VIEW v1 AS SELECT * FROM t1; + } + catchsql ANALYZE + } {1 {not authorized}} +} + +#------------------------------------------------------------------------- +# +reset_db +proc r {args} { expr rand() } +db func r r +db func lrange lrange +do_test 20.1 { + execsql { + CREATE TABLE t1(a,b,c,d); + CREATE INDEX i1 ON t1(a,b,c,d); + } + for {set i 0} {$i < 16} {incr i} { + execsql { + INSERT INTO t1 VALUES($i, r(), r(), r()); + INSERT INTO t1 VALUES($i, $i, r(), r()); + INSERT INTO t1 VALUES($i, $i, $i, r()); + INSERT INTO t1 VALUES($i, $i, $i, $i); + INSERT INTO t1 VALUES($i, $i, $i, $i); + INSERT INTO t1 VALUES($i, $i, $i, r()); + INSERT INTO t1 VALUES($i, $i, r(), r()); + INSERT INTO t1 VALUES($i, r(), r(), r()); + } + } +} {} +do_execsql_test 20.2 { ANALYZE } +for {set i 0} {$i<16} {incr i} { + set val $i + do_execsql_test 20.3.$i { + SELECT count(*) FROM sqlite_stat3 WHERE sample=$val + } {1} +} + +finish_test + |