OLD | NEW |
1 # 2013-11-13 | 1 # 2013-11-13 |
2 # | 2 # |
3 # The author disclaims copyright to this source code. In place of | 3 # The author disclaims copyright to this source code. In place of |
4 # a legal notice, here is a blessing: | 4 # a legal notice, here is a blessing: |
5 # | 5 # |
6 # May you do good and not evil. | 6 # May you do good and not evil. |
7 # May you find forgiveness for yourself and forgive others. | 7 # May you find forgiveness for yourself and forgive others. |
8 # May you share freely, never taking more than you give. | 8 # May you share freely, never taking more than you give. |
9 # | 9 # |
10 #*********************************************************************** | 10 #*********************************************************************** |
(...skipping 25 matching lines...) Expand all Loading... |
36 } {} | 36 } {} |
37 | 37 |
38 # Simple queries that leave the first one or two columns of the | 38 # Simple queries that leave the first one or two columns of the |
39 # index unconstrainted. | 39 # index unconstrainted. |
40 # | 40 # |
41 do_execsql_test skipscan1-1.2 { | 41 do_execsql_test skipscan1-1.2 { |
42 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; | 42 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; |
43 } {abc 345 7 8 | def 345 9 10 |} | 43 } {abc 345 7 8 | def 345 9 10 |} |
44 do_execsql_test skipscan1-1.2eqp { | 44 do_execsql_test skipscan1-1.2eqp { |
45 EXPLAIN QUERY PLAN | 45 EXPLAIN QUERY PLAN |
46 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; | 46 SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a; |
47 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/} | 47 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/} |
48 do_execsql_test skipscan1-1.2sort { | 48 do_execsql_test skipscan1-1.2sort { |
49 EXPLAIN QUERY PLAN | 49 EXPLAIN QUERY PLAN |
50 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; | 50 SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a; |
51 } {~/*ORDER BY*/} | 51 } {~/*ORDER BY*/} |
52 | 52 |
53 do_execsql_test skipscan1-1.3 { | 53 do_execsql_test skipscan1-1.3 { |
54 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a DESC; | 54 SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC; |
55 } {def 345 9 10 | abc 345 7 8 |} | 55 } {def 345 9 10 | abc 345 7 8 |} |
56 do_execsql_test skipscan1-1.3eqp { | 56 do_execsql_test skipscan1-1.3eqp { |
57 EXPLAIN QUERY PLAN | 57 EXPLAIN QUERY PLAN |
58 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; | 58 SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC; |
59 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/} | 59 } {/* USING INDEX t1abc (ANY(a) AND b=?)*/} |
60 do_execsql_test skipscan1-1.3sort { | 60 do_execsql_test skipscan1-1.3sort { |
61 EXPLAIN QUERY PLAN | 61 EXPLAIN QUERY PLAN |
62 SELECT a,b,c,d,'|' FROM t1 WHERE b=345 ORDER BY a; | 62 SELECT a,b,c,d,'|' FROM t1 WHERE d<>99 AND b=345 ORDER BY a DESC; |
63 } {~/*ORDER BY*/} | 63 } {~/*ORDER BY*/} |
64 | 64 |
65 do_execsql_test skipscan1-1.4 { | 65 do_execsql_test skipscan1-1.4 { |
66 SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; | 66 SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; |
67 } {abc 234 6 7 | bcd 100 6 11 |} | 67 } {abc 234 6 7 | bcd 100 6 11 |} |
68 do_execsql_test skipscan1-1.4eqp { | 68 do_execsql_test skipscan1-1.4eqp { |
69 EXPLAIN QUERY PLAN | 69 EXPLAIN QUERY PLAN |
70 SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; | 70 SELECT a,b,c,d,'|' FROM t1 WHERE c=6 ORDER BY a, b, c; |
71 } {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} | 71 } {/* USING INDEX t1abc (ANY(a) AND ANY(b) AND c=?)*/} |
72 do_execsql_test skipscan1-1.4sort { | 72 do_execsql_test skipscan1-1.4sort { |
(...skipping 70 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
143 | 143 |
144 /* Fake the sqlite_stat1 table so that the query planner believes | 144 /* Fake the sqlite_stat1 table so that the query planner believes |
145 ** the table contains thousands of rows and that the first few | 145 ** the table contains thousands of rows and that the first few |
146 ** columns are not selective. */ | 146 ** columns are not selective. */ |
147 ANALYZE; | 147 ANALYZE; |
148 UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL; | 148 UPDATE sqlite_stat1 SET stat='10000 5000 2000 10' WHERE idx NOT NULL; |
149 ANALYZE sqlite_master; | 149 ANALYZE sqlite_master; |
150 } {} | 150 } {} |
151 | 151 |
152 do_execsql_test skipscan1-2.2 { | 152 do_execsql_test skipscan1-2.2 { |
153 SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; | 153 SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a; |
154 } {abc 345 7 8 | def 345 9 10 |} | 154 } {abc 345 7 8 | def 345 9 10 |} |
155 do_execsql_test skipscan1-2.2eqp { | 155 do_execsql_test skipscan1-2.2eqp { |
156 EXPLAIN QUERY PLAN | 156 EXPLAIN QUERY PLAN |
157 SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; | 157 SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a; |
158 } {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/} | 158 } {/* USING INDEX sqlite_autoindex_t2_1 (ANY(a) AND b=?)*/} |
159 do_execsql_test skipscan1-2.2sort { | 159 do_execsql_test skipscan1-2.2sort { |
160 EXPLAIN QUERY PLAN | 160 EXPLAIN QUERY PLAN |
161 SELECT a,b,c,d,'|' FROM t2 WHERE b=345 ORDER BY a; | 161 SELECT a,b,c,d,'|' FROM t2 WHERE d<>99 AND b=345 ORDER BY a; |
162 } {~/*ORDER BY*/} | 162 } {~/*ORDER BY*/} |
163 | 163 |
164 | 164 |
165 do_execsql_test skipscan1-3.1 { | 165 do_execsql_test skipscan1-3.1 { |
166 CREATE TABLE t3(a TEXT, b INT, c INT, d INT, | 166 CREATE TABLE t3(a TEXT, b INT, c INT, d INT, |
167 PRIMARY KEY(a,b,c)) WITHOUT ROWID; | 167 PRIMARY KEY(a,b,c)) WITHOUT ROWID; |
168 INSERT INTO t3 SELECT * FROM t1; | 168 INSERT INTO t3 SELECT * FROM t1; |
169 | 169 |
170 /* Fake the sqlite_stat1 table so that the query planner believes | 170 /* Fake the sqlite_stat1 table so that the query planner believes |
171 ** the table contains thousands of rows and that the first few | 171 ** the table contains thousands of rows and that the first few |
(...skipping 143 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
315 SELECT * FROM t1 | 315 SELECT * FROM t1 |
316 WHERE (y = 'AB' AND x <= 4) | 316 WHERE (y = 'AB' AND x <= 4) |
317 OR (y = 'EF' AND x = 5); | 317 OR (y = 'EF' AND x = 5); |
318 } {/ANY/} | 318 } {/ANY/} |
319 do_execsql_test skipscan1-8.2 { | 319 do_execsql_test skipscan1-8.2 { |
320 SELECT * FROM t1 | 320 SELECT * FROM t1 |
321 WHERE y = 'AB' OR (y = 'CD' AND x = 2) | 321 WHERE y = 'AB' OR (y = 'CD' AND x = 2) |
322 ORDER BY +x; | 322 ORDER BY +x; |
323 } {1 AB 2 CD} | 323 } {1 AB 2 CD} |
324 | 324 |
| 325 # Segfault reported on the mailing list by Keith Medcalf on 2016-09-18. |
| 326 # A skip-scan with a "column IN (SELECT ...)" on the second term of the |
| 327 # index. |
| 328 # |
| 329 do_execsql_test skipscan1-9.2 { |
| 330 CREATE TABLE t9a(a,b,c); |
| 331 CREATE INDEX t9a_ab ON t9a(a,b); |
| 332 CREATE TABLE t9b(x,y); |
| 333 ANALYZE sqlite_master; |
| 334 INSERT INTO sqlite_stat1 VALUES('t9a','t9a_ab','1000000 250000 1'); |
| 335 ANALYZE sqlite_master; |
| 336 EXPLAIN QUERY PLAN |
| 337 SELECT * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5); |
| 338 } {/USING INDEX t9a_ab .ANY.a. AND b=./} |
| 339 |
325 finish_test | 340 finish_test |
OLD | NEW |