OLD | NEW |
| (Empty) |
1 # 2014-03-31 | |
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 # Test cases for query planning decisions where one candidate index | |
13 # covers a proper superset of the WHERE clause terms of another | |
14 # candidate index. | |
15 # | |
16 | |
17 set testdir [file dirname $argv0] | |
18 source $testdir/tester.tcl | |
19 | |
20 do_execsql_test whereH-1.1 { | |
21 CREATE TABLE t1(a,b,c,d); | |
22 CREATE INDEX t1abc ON t1(a,b,c); | |
23 CREATE INDEX t1bc ON t1(b,c); | |
24 | |
25 EXPLAIN QUERY PLAN | |
26 SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c; | |
27 } {/INDEX t1abc /} | |
28 do_execsql_test whereH-1.2 { | |
29 EXPLAIN QUERY PLAN | |
30 SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c; | |
31 } {~/TEMP B-TREE FOR ORDER BY/} | |
32 | |
33 do_execsql_test whereH-2.1 { | |
34 DROP TABLE t1; | |
35 CREATE TABLE t1(a,b,c,d); | |
36 CREATE INDEX t1bc ON t1(b,c); | |
37 CREATE INDEX t1abc ON t1(a,b,c); | |
38 | |
39 EXPLAIN QUERY PLAN | |
40 SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c; | |
41 } {/INDEX t1abc /} | |
42 do_execsql_test whereH-2.2 { | |
43 EXPLAIN QUERY PLAN | |
44 SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c; | |
45 } {~/TEMP B-TREE FOR ORDER BY/} | |
46 | |
47 do_execsql_test whereH-3.1 { | |
48 DROP TABLE t1; | |
49 CREATE TABLE t1(a,b,c,d,e); | |
50 CREATE INDEX t1cd ON t1(c,d); | |
51 CREATE INDEX t1bcd ON t1(b,c,d); | |
52 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
53 | |
54 EXPLAIN QUERY PLAN | |
55 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
56 } {/INDEX t1abcd /} | |
57 do_execsql_test whereH-3.2 { | |
58 EXPLAIN QUERY PLAN | |
59 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
60 } {~/TEMP B-TREE FOR ORDER BY/} | |
61 | |
62 do_execsql_test whereH-4.1 { | |
63 DROP TABLE t1; | |
64 CREATE TABLE t1(a,b,c,d,e); | |
65 CREATE INDEX t1cd ON t1(c,d); | |
66 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
67 CREATE INDEX t1bcd ON t1(b,c,d); | |
68 | |
69 EXPLAIN QUERY PLAN | |
70 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
71 } {/INDEX t1abcd /} | |
72 do_execsql_test whereH-4.2 { | |
73 EXPLAIN QUERY PLAN | |
74 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
75 } {~/TEMP B-TREE FOR ORDER BY/} | |
76 | |
77 do_execsql_test whereH-5.1 { | |
78 DROP TABLE t1; | |
79 CREATE TABLE t1(a,b,c,d,e); | |
80 CREATE INDEX t1bcd ON t1(b,c,d); | |
81 CREATE INDEX t1cd ON t1(c,d); | |
82 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
83 | |
84 EXPLAIN QUERY PLAN | |
85 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
86 } {/INDEX t1abcd /} | |
87 do_execsql_test whereH-5.2 { | |
88 EXPLAIN QUERY PLAN | |
89 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
90 } {~/TEMP B-TREE FOR ORDER BY/} | |
91 | |
92 do_execsql_test whereH-6.1 { | |
93 DROP TABLE t1; | |
94 CREATE TABLE t1(a,b,c,d,e); | |
95 CREATE INDEX t1bcd ON t1(b,c,d); | |
96 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
97 CREATE INDEX t1cd ON t1(c,d); | |
98 | |
99 EXPLAIN QUERY PLAN | |
100 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
101 } {/INDEX t1abcd /} | |
102 do_execsql_test whereH-6.2 { | |
103 EXPLAIN QUERY PLAN | |
104 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
105 } {~/TEMP B-TREE FOR ORDER BY/} | |
106 | |
107 do_execsql_test whereH-7.1 { | |
108 DROP TABLE t1; | |
109 CREATE TABLE t1(a,b,c,d,e); | |
110 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
111 CREATE INDEX t1bcd ON t1(b,c,d); | |
112 CREATE INDEX t1cd ON t1(c,d); | |
113 | |
114 EXPLAIN QUERY PLAN | |
115 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
116 } {/INDEX t1abcd /} | |
117 do_execsql_test whereH-7.2 { | |
118 EXPLAIN QUERY PLAN | |
119 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
120 } {~/TEMP B-TREE FOR ORDER BY/} | |
121 | |
122 do_execsql_test whereH-8.1 { | |
123 DROP TABLE t1; | |
124 CREATE TABLE t1(a,b,c,d,e); | |
125 CREATE INDEX t1abcd ON t1(a,b,c,d); | |
126 CREATE INDEX t1cd ON t1(c,d); | |
127 CREATE INDEX t1bcd ON t1(b,c,d); | |
128 | |
129 EXPLAIN QUERY PLAN | |
130 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
131 } {/INDEX t1abcd /} | |
132 do_execsql_test whereH-8.2 { | |
133 EXPLAIN QUERY PLAN | |
134 SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d; | |
135 } {~/TEMP B-TREE FOR ORDER BY/} | |
136 | |
137 | |
138 | |
139 finish_test | |
OLD | NEW |