Debian Codegraph @

test/analyzeC.test

raw
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://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