test/analyzeC.test

   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
  23 set testdir [file dirname $argv0]
  24 source $testdir/tester.tcl
  25 set testprefix analyzeC
  26
  27 # Baseline case.  Range queries work OK.  Indexes can be used for
  28 # ORDER BY.
  29 #
  30 do_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 #}
  43 do_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<...*/}
  47 do_execsql_test 1.2 {
  48   SELECT c FROM t1 ORDER BY a;
  49 } {3 111 6 12 9 12}
  50 do_execsql_test 1.3 {
  51   EXPLAIN QUERY PLAN
  52   SELECT c FROM t1 ORDER BY a;
  53 } {/.*SCAN t1 USING INDEX t1a.*/}
  54 do_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 #
  62 do_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 #}
  67 do_execsql_test 2.1 {
  68   EXPLAIN QUERY PLAN
  69   SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
  70 } {~/.*USING INDEX.*/}
  71 do_execsql_test 2.2 {
  72   SELECT c FROM t1 ORDER BY a;
  73 } {3 111 6 12 9 12}
  74 do_execsql_test 2.3 {
  75   EXPLAIN QUERY PLAN
  76   SELECT c FROM t1 ORDER BY a;
  77 } {~/.*USING INDEX.*/}
  78 do_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 #
  85 do_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 #}
  91 do_execsql_test 3.1 {
  92   EXPLAIN QUERY PLAN
  93   SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
  94 } {~/.*USING INDEX.*/}
  95 do_execsql_test 3.2 {
  96   SELECT c FROM t1 ORDER BY a;
  97 } {3 111 6 12 9 12}
  98 do_execsql_test 3.3 {
  99   EXPLAIN QUERY PLAN
 100   SELECT c FROM t1 ORDER BY a;
 101 } {~/.*USING INDEX.*/}
 102 do_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 #
 109 do_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}
 119 do_execsql_test 4.1 {
 120   EXPLAIN QUERY PLAN
 121   SELECT count(a) FROM t1;
 122 } {/.*INDEX t1ab.*/}
 123 do_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}
 130 do_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 #
 140 do_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 #
 153 do_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}
 161 do_execsql_test 5.1 {
 162   EXPLAIN QUERY PLAN
 163   SELECT count(a) FROM t1;
 164 } {/.*INDEX t1ab.*/}
 165 do_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}
 173 do_execsql_test 5.3 {
 174   EXPLAIN QUERY PLAN
 175   SELECT count(a) FROM t1;
 176 } {/.*INDEX t1ca.*/}
 177
 178
 179
 180
 181 finish_test