xref: /sqlite-3.40.0/test/analyzeE.test (revision 8210233c) 
1# 2014-10-08
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# This file implements tests for using STAT4 information
12# on a descending index in a range query.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set ::testprefix analyzeE
18
19ifcapable {!stat4} {
20  finish_test
21  return
22}
23
24# Verify that range queries on an ASCENDING index will use the
25# index only if the range covers only a small fraction of the
26# entries.
27#
28do_execsql_test analyzeE-1.0 {
29  CREATE TABLE t1(a,b);
30  WITH RECURSIVE
31    cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000)
32  INSERT INTO t1(a,b) SELECT x, x FROM cnt;
33  CREATE INDEX t1a ON t1(a);
34  ANALYZE;
35} {}
36do_execsql_test analyzeE-1.1 {
37  EXPLAIN QUERY PLAN
38  SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500;
39} {/SCAN t1/}
40do_execsql_test analyzeE-1.2 {
41  EXPLAIN QUERY PLAN
42  SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000;
43} {/SEARCH t1 USING INDEX t1a/}
44do_execsql_test analyzeE-1.3 {
45  EXPLAIN QUERY PLAN
46  SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750;
47} {/SEARCH t1 USING INDEX t1a/}
48do_execsql_test analyzeE-1.4 {
49  EXPLAIN QUERY PLAN
50  SELECT * FROM t1 WHERE a BETWEEN 1 AND 500
51} {/SEARCH t1 USING INDEX t1a/}
52do_execsql_test analyzeE-1.5 {
53  EXPLAIN QUERY PLAN
54  SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000
55} {/SEARCH t1 USING INDEX t1a/}
56do_execsql_test analyzeE-1.6 {
57  EXPLAIN QUERY PLAN
58  SELECT * FROM t1 WHERE a<500
59} {/SEARCH t1 USING INDEX t1a/}
60do_execsql_test analyzeE-1.7 {
61  EXPLAIN QUERY PLAN
62  SELECT * FROM t1 WHERE a>2500
63} {/SEARCH t1 USING INDEX t1a/}
64do_execsql_test analyzeE-1.8 {
65  EXPLAIN QUERY PLAN
66  SELECT * FROM t1 WHERE a>1900
67} {/SEARCH t1 USING INDEX t1a/}
68do_execsql_test analyzeE-1.9 {
69  EXPLAIN QUERY PLAN
70  SELECT * FROM t1 WHERE a>1100
71} {/SCAN t1/}
72do_execsql_test analyzeE-1.10 {
73  EXPLAIN QUERY PLAN
74  SELECT * FROM t1 WHERE a<1100
75} {/SEARCH t1 USING INDEX t1a/}
76do_execsql_test analyzeE-1.11 {
77  EXPLAIN QUERY PLAN
78  SELECT * FROM t1 WHERE a<1900
79} {/SCAN t1/}
80
81# Verify that everything works the same on a DESCENDING index.
82#
83do_execsql_test analyzeE-2.0 {
84  DROP INDEX t1a;
85  CREATE INDEX t1a ON t1(a DESC);
86  ANALYZE;
87} {}
88do_execsql_test analyzeE-2.1 {
89  EXPLAIN QUERY PLAN
90  SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500;
91} {/SCAN t1/}
92do_execsql_test analyzeE-2.2 {
93  EXPLAIN QUERY PLAN
94  SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000;
95} {/SEARCH t1 USING INDEX t1a/}
96do_execsql_test analyzeE-2.3 {
97  EXPLAIN QUERY PLAN
98  SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750;
99} {/SEARCH t1 USING INDEX t1a/}
100do_execsql_test analyzeE-2.4 {
101  EXPLAIN QUERY PLAN
102  SELECT * FROM t1 WHERE a BETWEEN 1 AND 500
103} {/SEARCH t1 USING INDEX t1a/}
104do_execsql_test analyzeE-2.5 {
105  EXPLAIN QUERY PLAN
106  SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000
107} {/SEARCH t1 USING INDEX t1a/}
108do_execsql_test analyzeE-2.6 {
109  EXPLAIN QUERY PLAN
110  SELECT * FROM t1 WHERE a<500
111} {/SEARCH t1 USING INDEX t1a/}
112do_execsql_test analyzeE-2.7 {
113  EXPLAIN QUERY PLAN
114  SELECT * FROM t1 WHERE a>2500
115} {/SEARCH t1 USING INDEX t1a/}
116do_execsql_test analyzeE-2.8 {
117  EXPLAIN QUERY PLAN
118  SELECT * FROM t1 WHERE a>1900
119} {/SEARCH t1 USING INDEX t1a/}
120do_execsql_test analyzeE-2.9 {
121  EXPLAIN QUERY PLAN
122  SELECT * FROM t1 WHERE a>1100
123} {/SCAN t1/}
124do_execsql_test analyzeE-2.10 {
125  EXPLAIN QUERY PLAN
126  SELECT * FROM t1 WHERE a<1100
127} {/SEARCH t1 USING INDEX t1a/}
128do_execsql_test analyzeE-2.11 {
129  EXPLAIN QUERY PLAN
130  SELECT * FROM t1 WHERE a<1900
131} {/SCAN t1/}
132
133# Now do a range query on the second term of an ASCENDING index
134# where the first term is constrained by equality.
135#
136do_execsql_test analyzeE-3.0 {
137  DROP TABLE t1;
138  CREATE TABLE t1(a,b,c);
139  WITH RECURSIVE
140    cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000)
141  INSERT INTO t1(a,b,c) SELECT x, x, 123 FROM cnt;
142  CREATE INDEX t1ca ON t1(c,a);
143  ANALYZE;
144} {}
145do_execsql_test analyzeE-3.1 {
146  EXPLAIN QUERY PLAN
147  SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123;
148} {/SCAN t1/}
149do_execsql_test analyzeE-3.2 {
150  EXPLAIN QUERY PLAN
151  SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123;
152} {/SEARCH t1 USING INDEX t1ca/}
153do_execsql_test analyzeE-3.3 {
154  EXPLAIN QUERY PLAN
155  SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123;
156} {/SEARCH t1 USING INDEX t1ca/}
157do_execsql_test analyzeE-3.4 {
158  EXPLAIN QUERY PLAN
159  SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123
160} {/SEARCH t1 USING INDEX t1ca/}
161do_execsql_test analyzeE-3.5 {
162  EXPLAIN QUERY PLAN
163  SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123
164} {/SEARCH t1 USING INDEX t1ca/}
165do_execsql_test analyzeE-3.6 {
166  EXPLAIN QUERY PLAN
167  SELECT * FROM t1 WHERE a<500 AND c=123
168} {/SEARCH t1 USING INDEX t1ca/}
169do_execsql_test analyzeE-3.7 {
170  EXPLAIN QUERY PLAN
171  SELECT * FROM t1 WHERE a>2500 AND c=123
172} {/SEARCH t1 USING INDEX t1ca/}
173do_execsql_test analyzeE-3.8 {
174  EXPLAIN QUERY PLAN
175  SELECT * FROM t1 WHERE a>1900 AND c=123
176} {/SEARCH t1 USING INDEX t1ca/}
177do_execsql_test analyzeE-3.9 {
178  EXPLAIN QUERY PLAN
179  SELECT * FROM t1 WHERE a>1100 AND c=123
180} {/SCAN t1/}
181do_execsql_test analyzeE-3.10 {
182  EXPLAIN QUERY PLAN
183  SELECT * FROM t1 WHERE a<1100 AND c=123
184} {/SEARCH t1 USING INDEX t1ca/}
185do_execsql_test analyzeE-3.11 {
186  EXPLAIN QUERY PLAN
187  SELECT * FROM t1 WHERE a<1900 AND c=123
188} {/SCAN t1/}
189
190# Repeat the 3.x tests using a DESCENDING index
191#
192do_execsql_test analyzeE-4.0 {
193  DROP INDEX t1ca;
194  CREATE INDEX t1ca ON t1(c ASC,a DESC);
195  ANALYZE;
196} {}
197do_execsql_test analyzeE-4.1 {
198  EXPLAIN QUERY PLAN
199  SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123;
200} {/SCAN t1/}
201do_execsql_test analyzeE-4.2 {
202  EXPLAIN QUERY PLAN
203  SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123;
204} {/SEARCH t1 USING INDEX t1ca/}
205do_execsql_test analyzeE-4.3 {
206  EXPLAIN QUERY PLAN
207  SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123;
208} {/SEARCH t1 USING INDEX t1ca/}
209do_execsql_test analyzeE-4.4 {
210  EXPLAIN QUERY PLAN
211  SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123
212} {/SEARCH t1 USING INDEX t1ca/}
213do_execsql_test analyzeE-4.5 {
214  EXPLAIN QUERY PLAN
215  SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123
216} {/SEARCH t1 USING INDEX t1ca/}
217do_execsql_test analyzeE-4.6 {
218  EXPLAIN QUERY PLAN
219  SELECT * FROM t1 WHERE a<500 AND c=123
220} {/SEARCH t1 USING INDEX t1ca/}
221do_execsql_test analyzeE-4.7 {
222  EXPLAIN QUERY PLAN
223  SELECT * FROM t1 WHERE a>2500 AND c=123
224} {/SEARCH t1 USING INDEX t1ca/}
225do_execsql_test analyzeE-4.8 {
226  EXPLAIN QUERY PLAN
227  SELECT * FROM t1 WHERE a>1900 AND c=123
228} {/SEARCH t1 USING INDEX t1ca/}
229do_execsql_test analyzeE-4.9 {
230  EXPLAIN QUERY PLAN
231  SELECT * FROM t1 WHERE a>1100 AND c=123
232} {/SCAN t1/}
233do_execsql_test analyzeE-4.10 {
234  EXPLAIN QUERY PLAN
235  SELECT * FROM t1 WHERE a<1100 AND c=123
236} {/SEARCH t1 USING INDEX t1ca/}
237do_execsql_test analyzeE-4.11 {
238  EXPLAIN QUERY PLAN
239  SELECT * FROM t1 WHERE a<1900 AND c=123
240} {/SCAN t1/}
241
242finish_test
243