AryaWu/sqlite

1# 2014-07-222#3# The author disclaims copyright to this source code.  In place of4# 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 terms13# at the end of sqlite_stat1.stat are processed correctly.14#15#  (1) "unordered" means that the index cannot be used for ORDER BY16#      or for range queries17#18#  (2) "sz=NNN" sets the relative size of the index entries19#20#  (3) All other fields are silently ignored21#22 23set testdir [file dirname $argv0]24source $testdir/tester.tcl25set testprefix analyzeC26 27# Baseline case.  Range queries work OK.  Indexes can be used for28# 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 PLAN45  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 PLAN52  SELECT c FROM t1 ORDER BY a;53} {/.*SCAN t1 USING INDEX t1a.*/}54do_execsql_test 1.3x {55  EXPLAIN QUERY PLAN56  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 no60# 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 PLAN69  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 PLAN76  SELECT c FROM t1 ORDER BY a;77} {~/.*USING INDEX.*/}78do_execsql_test 2.3x {79  EXPLAIN QUERY PLAN80  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 PLAN93  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 PLAN100  SELECT c FROM t1 ORDER BY a;101} {~/.*USING INDEX.*/}102do_execsql_test 3.3x {103  EXPLAIN QUERY PLAN104  SELECT c FROM t1 ORDER BY a;105} {/.*B-TREE FOR ORDER BY.*/}106 107# The sz=NNN parameter determines which index to scan108#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 PLAN121  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 PLAN132  SELECT count(a) FROM t1;133} {/.*INDEX t1ca.*/}134 135# 2019-08-15.136# Ticket https://sqlite.org/src/tktview/e4598ecbdd18bd82945f602901137# The sz=N parameter in the sqlite_stat1 table needs to have a value of138# 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 text151# 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 PLAN163  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 PLAN175  SELECT count(a) FROM t1;176} {/.*INDEX t1ca.*/}177 178 179 180 181finish_test182