1# 2014-07-22 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# 12# This file contains automated tests used to verify that the text terms 13# at the end of sqlite_stat1.stat are processed correctly. 14# 15# (1) "unordered" means that the index cannot be used for ORDER BY 16# or for range queries 17# 18# (2) "sz=NNN" sets the relative size of the index entries 19# 20# (3) All other fields are silently ignored 21# 22 23set testdir [file dirname $argv0] 24source $testdir/tester.tcl 25set testprefix analyzeC 26 27# Baseline case. Range queries work OK. Indexes can be used for 28# ORDER BY. 29# 30do_execsql_test 1.0 { 31 CREATE TABLE t1(a,b,c); 32 INSERT INTO t1(a,b,c) 33 VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111); 34 CREATE INDEX t1a ON t1(a); 35 CREATE INDEX t1b ON t1(b); 36 ANALYZE; 37 DELETE FROM sqlite_stat1; 38 INSERT INTO sqlite_stat1(tbl,idx,stat) 39 VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4'); 40 ANALYZE sqlite_master; 41 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 42} {4 5 6 # 7 8 9 # 4 8 12 #} 43do_execsql_test 1.1 { 44 EXPLAIN QUERY PLAN 45 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 46} {/.* USING INDEX t1a .a>. AND a<...*/} 47do_execsql_test 1.2 { 48 SELECT c FROM t1 ORDER BY a; 49} {3 111 6 12 9 12} 50do_execsql_test 1.3 { 51 EXPLAIN QUERY PLAN 52 SELECT c FROM t1 ORDER BY a; 53} {/.*SCAN t1 USING INDEX t1a.*/} 54do_execsql_test 1.3x { 55 EXPLAIN QUERY PLAN 56 SELECT c FROM t1 ORDER BY a; 57} {~/.*B-TREE FOR ORDER BY.*/} 58 59# Now mark the t1a index as "unordered". Range queries and ORDER BY no 60# longer use the index, but equality queries do. 61# 62do_execsql_test 2.0 { 63 UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a'; 64 ANALYZE sqlite_master; 65 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 66} {4 5 6 # 7 8 9 # 4 8 12 #} 67do_execsql_test 2.1 { 68 EXPLAIN QUERY PLAN 69 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 70} {~/.*USING INDEX.*/} 71do_execsql_test 2.2 { 72 SELECT c FROM t1 ORDER BY a; 73} {3 111 6 12 9 12} 74do_execsql_test 2.3 { 75 EXPLAIN QUERY PLAN 76 SELECT c FROM t1 ORDER BY a; 77} {~/.*USING INDEX.*/} 78do_execsql_test 2.3x { 79 EXPLAIN QUERY PLAN 80 SELECT c FROM t1 ORDER BY a; 81} {/.*B-TREE FOR ORDER BY.*/} 82 83# Ignore extraneous text parameters in the sqlite_stat1.stat field. 84# 85do_execsql_test 3.0 { 86 UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11' 87 WHERE idx='t1a'; 88 ANALYZE sqlite_master; 89 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 90} {4 5 6 # 7 8 9 # 4 8 12 #} 91do_execsql_test 3.1 { 92 EXPLAIN QUERY PLAN 93 SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; 94} {~/.*USING INDEX.*/} 95do_execsql_test 3.2 { 96 SELECT c FROM t1 ORDER BY a; 97} {3 111 6 12 9 12} 98do_execsql_test 3.3 { 99 EXPLAIN QUERY PLAN 100 SELECT c FROM t1 ORDER BY a; 101} {~/.*USING INDEX.*/} 102do_execsql_test 3.3x { 103 EXPLAIN QUERY PLAN 104 SELECT c FROM t1 ORDER BY a; 105} {/.*B-TREE FOR ORDER BY.*/} 106 107# The sz=NNN parameter determines which index to scan 108# 109do_execsql_test 4.0 { 110 DROP INDEX t1a; 111 CREATE INDEX t1ab ON t1(a,b); 112 CREATE INDEX t1ca ON t1(c,a); 113 DELETE FROM sqlite_stat1; 114 INSERT INTO sqlite_stat1(tbl,idx,stat) 115 VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20'); 116 ANALYZE sqlite_master; 117 SELECT count(a) FROM t1; 118} {6} 119do_execsql_test 4.1 { 120 EXPLAIN QUERY PLAN 121 SELECT count(a) FROM t1; 122} {/.*INDEX t1ab.*/} 123do_execsql_test 4.2 { 124 DELETE FROM sqlite_stat1; 125 INSERT INTO sqlite_stat1(tbl,idx,stat) 126 VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10'); 127 ANALYZE sqlite_master; 128 SELECT count(a) FROM t1; 129} {6} 130do_execsql_test 4.3 { 131 EXPLAIN QUERY PLAN 132 SELECT count(a) FROM t1; 133} {/.*INDEX t1ca.*/} 134 135# 2019-08-15. 136# Ticket https://www.sqlite.org/src/tktview/e4598ecbdd18bd82945f602901 137# The sz=N parameter in the sqlite_stat1 table needs to have a value of 138# 2 or more to avoid a division by zero in the query planner. 139# 140do_execsql_test 4.4 { 141 DROP TABLE IF EXISTS t44; 142 CREATE TABLE t44(a PRIMARY KEY); 143 INSERT INTO sqlite_stat1 VALUES('t44',null,'sz=0'); 144 ANALYZE sqlite_master; 145 SELECT 0 FROM t44 WHERE a IN(1,2,3); 146} {} 147 148 149 150# The sz=NNN parameter works even if there is other extraneous text 151# in the sqlite_stat1.stat column. 152# 153do_execsql_test 5.0 { 154 DELETE FROM sqlite_stat1; 155 INSERT INTO sqlite_stat1(tbl,idx,stat) 156 VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'), 157 ('t1','t1ca','12345 3 2 whatever sz=20 junk'); 158 ANALYZE sqlite_master; 159 SELECT count(a) FROM t1; 160} {6} 161do_execsql_test 5.1 { 162 EXPLAIN QUERY PLAN 163 SELECT count(a) FROM t1; 164} {/.*INDEX t1ab.*/} 165do_execsql_test 5.2 { 166 DELETE FROM sqlite_stat1; 167 INSERT INTO sqlite_stat1(tbl,idx,stat) 168 VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'), 169 ('t1','t1ab','12345 3 2 whatever sz=20 junk'); 170 ANALYZE sqlite_master; 171 SELECT count(a) FROM t1; 172} {6} 173do_execsql_test 5.3 { 174 EXPLAIN QUERY PLAN 175 SELECT count(a) FROM t1; 176} {/.*INDEX t1ca.*/} 177 178 179 180 181finish_test 182