wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2005 July 28 |
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 regression tests for SQLite library. The |
||
12 | # focus of this file is testing the use of indices in WHERE clauses |
||
13 | # based on recent changes to the optimizer. |
||
14 | # |
||
15 | # $Id: where2.test,v 1.15 2009/02/02 01:50:40 drh Exp $ |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # Build some test data |
||
21 | # |
||
22 | do_test where2-1.0 { |
||
23 | execsql { |
||
24 | BEGIN; |
||
25 | CREATE TABLE t1(w int, x int, y int, z int); |
||
26 | } |
||
27 | for {set i 1} {$i<=100} {incr i} { |
||
28 | set w $i |
||
29 | set x [expr {int(log($i)/log(2))}] |
||
30 | set y [expr {$i*$i + 2*$i + 1}] |
||
31 | set z [expr {$x+$y}] |
||
32 | ifcapable tclvar { |
||
33 | execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)} |
||
34 | } else { |
||
35 | execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)} |
||
36 | } |
||
37 | } |
||
38 | execsql { |
||
39 | CREATE UNIQUE INDEX i1w ON t1(w); |
||
40 | CREATE INDEX i1xy ON t1(x,y); |
||
41 | CREATE INDEX i1zyx ON t1(z,y,x); |
||
42 | COMMIT; |
||
43 | } |
||
44 | } {} |
||
45 | |||
46 | # Do an SQL statement. Append the search count to the end of the result. |
||
47 | # |
||
48 | proc count sql { |
||
49 | set ::sqlite_search_count 0 |
||
50 | return [concat [execsql $sql] $::sqlite_search_count] |
||
51 | } |
||
52 | |||
53 | # This procedure executes the SQL. Then it checks to see if the OP_Sort |
||
54 | # opcode was executed. If an OP_Sort did occur, then "sort" is appended |
||
55 | # to the result. If no OP_Sort happened, then "nosort" is appended. |
||
56 | # |
||
57 | # This procedure is used to check to make sure sorting is or is not |
||
58 | # occurring as expected. |
||
59 | # |
||
60 | proc cksort {sql} { |
||
61 | set data [execsql $sql] |
||
62 | if {[db status sort]} {set x sort} {set x nosort} |
||
63 | lappend data $x |
||
64 | return $data |
||
65 | } |
||
66 | |||
67 | # This procedure executes the SQL. Then it appends to the result the |
||
68 | # "sort" or "nosort" keyword (as in the cksort procedure above) then |
||
69 | # it appends the ::sqlite_query_plan variable. |
||
70 | # |
||
71 | proc queryplan {sql} { |
||
72 | set ::sqlite_sort_count 0 |
||
73 | set data [execsql $sql] |
||
74 | if {$::sqlite_sort_count} {set x sort} {set x nosort} |
||
75 | lappend data $x |
||
76 | return [concat $data $::sqlite_query_plan] |
||
77 | } |
||
78 | |||
79 | |||
80 | # Prefer a UNIQUE index over another index. |
||
81 | # |
||
82 | do_test where2-1.1 { |
||
83 | queryplan { |
||
84 | SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 |
||
85 | } |
||
86 | } {85 6 7396 7402 nosort t1 i1w} |
||
87 | |||
88 | # Always prefer a rowid== constraint over any other index. |
||
89 | # |
||
90 | do_test where2-1.3 { |
||
91 | queryplan { |
||
92 | SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85 |
||
93 | } |
||
94 | } {85 6 7396 7402 nosort t1 *} |
||
95 | |||
96 | # When constrained by a UNIQUE index, the ORDER BY clause is always ignored. |
||
97 | # |
||
98 | do_test where2-2.1 { |
||
99 | queryplan { |
||
100 | SELECT * FROM t1 WHERE w=85 ORDER BY random(); |
||
101 | } |
||
102 | } {85 6 7396 7402 nosort t1 i1w} |
||
103 | do_test where2-2.2 { |
||
104 | queryplan { |
||
105 | SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random(); |
||
106 | } |
||
107 | } {85 6 7396 7402 sort t1 i1xy} |
||
108 | do_test where2-2.3 { |
||
109 | queryplan { |
||
110 | SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random(); |
||
111 | } |
||
112 | } {85 6 7396 7402 nosort t1 *} |
||
113 | |||
114 | |||
115 | # Efficient handling of forward and reverse table scans. |
||
116 | # |
||
117 | do_test where2-3.1 { |
||
118 | queryplan { |
||
119 | SELECT * FROM t1 ORDER BY rowid LIMIT 2 |
||
120 | } |
||
121 | } {1 0 4 4 2 1 9 10 nosort t1 *} |
||
122 | do_test where2-3.2 { |
||
123 | queryplan { |
||
124 | SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2 |
||
125 | } |
||
126 | } {100 6 10201 10207 99 6 10000 10006 nosort t1 *} |
||
127 | |||
128 | # The IN operator can be used by indices at multiple layers |
||
129 | # |
||
130 | ifcapable subquery { |
||
131 | do_test where2-4.1 { |
||
132 | queryplan { |
||
133 | SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201) |
||
134 | AND x>0 AND x<10 |
||
135 | ORDER BY w |
||
136 | } |
||
137 | } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx} |
||
138 | do_test where2-4.2 { |
||
139 | queryplan { |
||
140 | SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000 |
||
141 | AND x>0 AND x<10 |
||
142 | ORDER BY w |
||
143 | } |
||
144 | } {99 6 10000 10006 sort t1 i1zyx} |
||
145 | do_test where2-4.3 { |
||
146 | queryplan { |
||
147 | SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201) |
||
148 | AND x>0 AND x<10 |
||
149 | ORDER BY w |
||
150 | } |
||
151 | } {99 6 10000 10006 sort t1 i1zyx} |
||
152 | ifcapable compound { |
||
153 | do_test where2-4.4 { |
||
154 | queryplan { |
||
155 | SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006) |
||
156 | AND y IN (10000,10201) |
||
157 | AND x>0 AND x<10 |
||
158 | ORDER BY w |
||
159 | } |
||
160 | } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx} |
||
161 | do_test where2-4.5 { |
||
162 | queryplan { |
||
163 | SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006) |
||
164 | AND y IN (SELECT 10000 UNION SELECT 10201) |
||
165 | AND x>0 AND x<10 |
||
166 | ORDER BY w |
||
167 | } |
||
168 | } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx} |
||
169 | } |
||
170 | do_test where2-4.6 { |
||
171 | queryplan { |
||
172 | SELECT * FROM t1 |
||
173 | WHERE x IN (1,2,3,4,5,6,7,8) |
||
174 | AND y IN (10000,10001,10002,10003,10004,10005) |
||
175 | ORDER BY 2 |
||
176 | } |
||
177 | } {99 6 10000 10006 sort t1 i1xy} |
||
178 | |||
179 | # Duplicate entires on the RHS of an IN operator do not cause duplicate |
||
180 | # output rows. |
||
181 | # |
||
182 | do_test where2-4.6 { |
||
183 | queryplan { |
||
184 | SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207) |
||
185 | ORDER BY w |
||
186 | } |
||
187 | } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx} |
||
188 | ifcapable compound { |
||
189 | do_test where2-4.7 { |
||
190 | queryplan { |
||
191 | SELECT * FROM t1 WHERE z IN ( |
||
192 | SELECT 10207 UNION ALL SELECT 10006 |
||
193 | UNION ALL SELECT 10006 UNION ALL SELECT 10207) |
||
194 | ORDER BY w |
||
195 | } |
||
196 | } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx} |
||
197 | } |
||
198 | |||
199 | } ;# ifcapable subquery |
||
200 | |||
201 | # The use of an IN operator disables the index as a sorter. |
||
202 | # |
||
203 | do_test where2-5.1 { |
||
204 | queryplan { |
||
205 | SELECT * FROM t1 WHERE w=99 ORDER BY w |
||
206 | } |
||
207 | } {99 6 10000 10006 nosort t1 i1w} |
||
208 | |||
209 | ifcapable subquery { |
||
210 | do_test where2-5.2 { |
||
211 | queryplan { |
||
212 | SELECT * FROM t1 WHERE w IN (99) ORDER BY w |
||
213 | } |
||
214 | } {99 6 10000 10006 sort t1 i1w} |
||
215 | } |
||
216 | |||
217 | # Verify that OR clauses get translated into IN operators. |
||
218 | # |
||
219 | set ::idx {} |
||
220 | ifcapable subquery {set ::idx i1w} |
||
221 | do_test where2-6.1.1 { |
||
222 | queryplan { |
||
223 | SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w |
||
224 | } |
||
225 | } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx] |
||
226 | do_test where2-6.1.2 { |
||
227 | queryplan { |
||
228 | SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w |
||
229 | } |
||
230 | } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx] |
||
231 | do_test where2-6.2 { |
||
232 | queryplan { |
||
233 | SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w |
||
234 | } |
||
235 | } [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx] |
||
236 | |||
237 | do_test where2-6.3 { |
||
238 | queryplan { |
||
239 | SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w |
||
240 | } |
||
241 | } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} |
||
242 | do_test where2-6.4 { |
||
243 | queryplan { |
||
244 | SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w |
||
245 | } |
||
246 | } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}} |
||
247 | |||
248 | set ::idx {} |
||
249 | ifcapable subquery {set ::idx i1zyx} |
||
250 | do_test where2-6.5 { |
||
251 | queryplan { |
||
252 | SELECT b.* FROM t1 a, t1 b |
||
253 | WHERE a.w=1 AND (a.y=b.z OR b.z=10) |
||
254 | ORDER BY +b.w |
||
255 | } |
||
256 | } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx] |
||
257 | do_test where2-6.6 { |
||
258 | queryplan { |
||
259 | SELECT b.* FROM t1 a, t1 b |
||
260 | WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10) |
||
261 | ORDER BY +b.w |
||
262 | } |
||
263 | } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx] |
||
264 | |||
265 | # Ticket #2249. Make sure the OR optimization is not attempted if |
||
266 | # comparisons between columns of different affinities are needed. |
||
267 | # |
||
268 | do_test where2-6.7 { |
||
269 | execsql { |
||
270 | CREATE TABLE t2249a(a TEXT UNIQUE); |
||
271 | CREATE TABLE t2249b(b INTEGER); |
||
272 | INSERT INTO t2249a VALUES('0123'); |
||
273 | INSERT INTO t2249b VALUES(123); |
||
274 | } |
||
275 | queryplan { |
||
276 | -- Because a is type TEXT and b is type INTEGER, both a and b |
||
277 | -- will attempt to convert to NUMERIC before the comparison. |
||
278 | -- They will thus compare equal. |
||
279 | -- |
||
280 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b; |
||
281 | } |
||
282 | } {123 0123 nosort t2249b {} t2249a {}} |
||
283 | do_test where2-6.9 { |
||
284 | queryplan { |
||
285 | -- The + operator removes affinity from the rhs. No conversions |
||
286 | -- occur and the comparison is false. The result is an empty set. |
||
287 | -- |
||
288 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b; |
||
289 | } |
||
290 | } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} |
||
291 | do_test where2-6.9.2 { |
||
292 | # The same thing but with the expression flipped around. |
||
293 | queryplan { |
||
294 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a |
||
295 | } |
||
296 | } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} |
||
297 | do_test where2-6.10 { |
||
298 | queryplan { |
||
299 | -- Use + on both sides of the comparison to disable indices |
||
300 | -- completely. Make sure we get the same result. |
||
301 | -- |
||
302 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b; |
||
303 | } |
||
304 | } {nosort t2249b {} t2249a {}} |
||
305 | do_test where2-6.11 { |
||
306 | # This will not attempt the OR optimization because of the a=b |
||
307 | # comparison. |
||
308 | queryplan { |
||
309 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; |
||
310 | } |
||
311 | } {123 0123 nosort t2249b {} t2249a {}} |
||
312 | do_test where2-6.11.2 { |
||
313 | # Permutations of the expression terms. |
||
314 | queryplan { |
||
315 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; |
||
316 | } |
||
317 | } {123 0123 nosort t2249b {} t2249a {}} |
||
318 | do_test where2-6.11.3 { |
||
319 | # Permutations of the expression terms. |
||
320 | queryplan { |
||
321 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; |
||
322 | } |
||
323 | } {123 0123 nosort t2249b {} t2249a {}} |
||
324 | do_test where2-6.11.4 { |
||
325 | # Permutations of the expression terms. |
||
326 | queryplan { |
||
327 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; |
||
328 | } |
||
329 | } {123 0123 nosort t2249b {} t2249a {}} |
||
330 | ifcapable explain&&subquery { |
||
331 | # These tests are not run if subquery support is not included in the |
||
332 | # build. This is because these tests test the "a = 1 OR a = 2" to |
||
333 | # "a IN (1, 2)" optimisation transformation, which is not enabled if |
||
334 | # subqueries and the IN operator is not available. |
||
335 | # |
||
336 | do_test where2-6.12 { |
||
337 | # In this case, the +b disables the affinity conflict and allows |
||
338 | # the OR optimization to be used again. The result is now an empty |
||
339 | # set, the same as in where2-6.9. |
||
340 | queryplan { |
||
341 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; |
||
342 | } |
||
343 | } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} |
||
344 | do_test where2-6.12.2 { |
||
345 | # In this case, the +b disables the affinity conflict and allows |
||
346 | # the OR optimization to be used again. The result is now an empty |
||
347 | # set, the same as in where2-6.9. |
||
348 | queryplan { |
||
349 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; |
||
350 | } |
||
351 | } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} |
||
352 | do_test where2-6.12.3 { |
||
353 | # In this case, the +b disables the affinity conflict and allows |
||
354 | # the OR optimization to be used again. The result is now an empty |
||
355 | # set, the same as in where2-6.9. |
||
356 | queryplan { |
||
357 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; |
||
358 | } |
||
359 | } {nosort t2249b {} {} sqlite_autoindex_t2249a_1} |
||
360 | do_test where2-6.13 { |
||
361 | # The addition of +a on the second term disabled the OR optimization. |
||
362 | # But we should still get the same empty-set result as in where2-6.9. |
||
363 | queryplan { |
||
364 | SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; |
||
365 | } |
||
366 | } {nosort t2249b {} t2249a {}} |
||
367 | } |
||
368 | |||
369 | # Variations on the order of terms in a WHERE clause in order |
||
370 | # to make sure the OR optimizer can recognize them all. |
||
371 | do_test where2-6.20 { |
||
372 | queryplan { |
||
373 | SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a |
||
374 | } |
||
375 | } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} |
||
376 | ifcapable explain&&subquery { |
||
377 | # These tests are not run if subquery support is not included in the |
||
378 | # build. This is because these tests test the "a = 1 OR a = 2" to |
||
379 | # "a IN (1, 2)" optimisation transformation, which is not enabled if |
||
380 | # subqueries and the IN operator is not available. |
||
381 | # |
||
382 | do_test where2-6.21 { |
||
383 | queryplan { |
||
384 | SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello' |
||
385 | } |
||
386 | } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} |
||
387 | do_test where2-6.22 { |
||
388 | queryplan { |
||
389 | SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello' |
||
390 | } |
||
391 | } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} |
||
392 | do_test where2-6.23 { |
||
393 | queryplan { |
||
394 | SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a |
||
395 | } |
||
396 | } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1} |
||
397 | } |
||
398 | |||
399 | # Unique queries (queries that are guaranteed to return only a single |
||
400 | # row of result) do not call the sorter. But all tables must give |
||
401 | # a unique result. If any one table in the join does not give a unique |
||
402 | # result then sorting is necessary. |
||
403 | # |
||
404 | do_test where2-7.1 { |
||
405 | cksort { |
||
406 | create table t8(a unique, b, c); |
||
407 | insert into t8 values(1,2,3); |
||
408 | insert into t8 values(2,3,4); |
||
409 | create table t9(x,y); |
||
410 | insert into t9 values(2,4); |
||
411 | insert into t9 values(2,3); |
||
412 | select y from t8, t9 where a=1 order by a, y; |
||
413 | } |
||
414 | } {3 4 sort} |
||
415 | do_test where2-7.2 { |
||
416 | cksort { |
||
417 | select * from t8 where a=1 order by b, c |
||
418 | } |
||
419 | } {1 2 3 nosort} |
||
420 | do_test where2-7.3 { |
||
421 | cksort { |
||
422 | select * from t8, t9 where a=1 and y=3 order by b, x |
||
423 | } |
||
424 | } {1 2 3 2 3 sort} |
||
425 | do_test where2-7.4 { |
||
426 | cksort { |
||
427 | create unique index i9y on t9(y); |
||
428 | select * from t8, t9 where a=1 and y=3 order by b, x |
||
429 | } |
||
430 | } {1 2 3 2 3 nosort} |
||
431 | |||
432 | # Ticket #1807. Using IN constrains on multiple columns of |
||
433 | # a multi-column index. |
||
434 | # |
||
435 | ifcapable subquery { |
||
436 | do_test where2-8.1 { |
||
437 | execsql { |
||
438 | SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2) |
||
439 | } |
||
440 | } {} |
||
441 | do_test where2-8.2 { |
||
442 | execsql { |
||
443 | SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6) |
||
444 | } |
||
445 | } {} |
||
446 | execsql {CREATE TABLE tx AS SELECT * FROM t1} |
||
447 | do_test where2-8.3 { |
||
448 | execsql { |
||
449 | SELECT w FROM t1 |
||
450 | WHERE x IN (SELECT x FROM tx WHERE rowid<0) |
||
451 | AND +y IN (SELECT y FROM tx WHERE rowid=1) |
||
452 | } |
||
453 | } {} |
||
454 | do_test where2-8.4 { |
||
455 | execsql { |
||
456 | SELECT w FROM t1 |
||
457 | WHERE x IN (SELECT x FROM tx WHERE rowid=1) |
||
458 | AND y IN (SELECT y FROM tx WHERE rowid<0) |
||
459 | } |
||
460 | } {} |
||
461 | #set sqlite_where_trace 1 |
||
462 | do_test where2-8.5 { |
||
463 | execsql { |
||
464 | CREATE INDEX tx_xyz ON tx(x, y, z, w); |
||
465 | SELECT w FROM tx |
||
466 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
467 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
468 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14) |
||
469 | } |
||
470 | } {12 13 14} |
||
471 | do_test where2-8.6 { |
||
472 | execsql { |
||
473 | SELECT w FROM tx |
||
474 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
475 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14) |
||
476 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
477 | } |
||
478 | } {12 13 14} |
||
479 | do_test where2-8.7 { |
||
480 | execsql { |
||
481 | SELECT w FROM tx |
||
482 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14) |
||
483 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
484 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
485 | } |
||
486 | } {10 11 12 13 14 15} |
||
487 | do_test where2-8.8 { |
||
488 | execsql { |
||
489 | SELECT w FROM tx |
||
490 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
491 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
492 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
493 | } |
||
494 | } {10 11 12 13 14 15 16 17 18 19 20} |
||
495 | do_test where2-8.9 { |
||
496 | execsql { |
||
497 | SELECT w FROM tx |
||
498 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
499 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
500 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4) |
||
501 | } |
||
502 | } {} |
||
503 | do_test where2-8.10 { |
||
504 | execsql { |
||
505 | SELECT w FROM tx |
||
506 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
507 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4) |
||
508 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
509 | } |
||
510 | } {} |
||
511 | do_test where2-8.11 { |
||
512 | execsql { |
||
513 | SELECT w FROM tx |
||
514 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4) |
||
515 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
516 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
517 | } |
||
518 | } {} |
||
519 | do_test where2-8.12 { |
||
520 | execsql { |
||
521 | SELECT w FROM tx |
||
522 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
523 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
524 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2) |
||
525 | } |
||
526 | } {} |
||
527 | do_test where2-8.13 { |
||
528 | execsql { |
||
529 | SELECT w FROM tx |
||
530 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
531 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2) |
||
532 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
533 | } |
||
534 | } {} |
||
535 | do_test where2-8.14 { |
||
536 | execsql { |
||
537 | SELECT w FROM tx |
||
538 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2) |
||
539 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
540 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
541 | } |
||
542 | } {} |
||
543 | do_test where2-8.15 { |
||
544 | execsql { |
||
545 | SELECT w FROM tx |
||
546 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
547 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
548 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300) |
||
549 | } |
||
550 | } {} |
||
551 | do_test where2-8.16 { |
||
552 | execsql { |
||
553 | SELECT w FROM tx |
||
554 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20) |
||
555 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300) |
||
556 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
557 | } |
||
558 | } {} |
||
559 | do_test where2-8.17 { |
||
560 | execsql { |
||
561 | SELECT w FROM tx |
||
562 | WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300) |
||
563 | AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20) |
||
564 | AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20) |
||
565 | } |
||
566 | } {} |
||
567 | do_test where2-8.18 { |
||
568 | execsql { |
||
569 | SELECT w FROM tx |
||
570 | WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
571 | AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
572 | AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300) |
||
573 | } |
||
574 | } {} |
||
575 | do_test where2-8.19 { |
||
576 | execsql { |
||
577 | SELECT w FROM tx |
||
578 | WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
579 | AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300) |
||
580 | AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
581 | } |
||
582 | } {} |
||
583 | do_test where2-8.20 { |
||
584 | execsql { |
||
585 | SELECT w FROM tx |
||
586 | WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300) |
||
587 | AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
588 | AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20) |
||
589 | } |
||
590 | } {} |
||
591 | } |
||
592 | |||
593 | # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized |
||
594 | # when we have an index on A and B. |
||
595 | # |
||
596 | ifcapable or_opt&&tclvar { |
||
597 | do_test where2-9.1 { |
||
598 | execsql { |
||
599 | BEGIN; |
||
600 | CREATE TABLE t10(a,b,c); |
||
601 | INSERT INTO t10 VALUES(1,1,1); |
||
602 | INSERT INTO t10 VALUES(1,2,2); |
||
603 | INSERT INTO t10 VALUES(1,3,3); |
||
604 | } |
||
605 | for {set i 4} {$i<=1000} {incr i} { |
||
606 | execsql {INSERT INTO t10 VALUES(1,$i,$i)} |
||
607 | } |
||
608 | execsql { |
||
609 | CREATE INDEX i10 ON t10(a,b); |
||
610 | COMMIT; |
||
611 | SELECT count(*) FROM t10; |
||
612 | } |
||
613 | } 1000 |
||
614 | ifcapable subquery { |
||
615 | do_test where2-9.2 { |
||
616 | count { |
||
617 | SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3) |
||
618 | } |
||
619 | } {1 2 2 1 3 3 7} |
||
620 | } |
||
621 | } |
||
622 | |||
623 | # Indices with redundant columns |
||
624 | # |
||
625 | do_test where2-11.1 { |
||
626 | execsql { |
||
627 | CREATE TABLE t11(a,b,c,d); |
||
628 | CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice. |
||
629 | INSERT INTO t11 VALUES(1,2,3,4); |
||
630 | INSERT INTO t11 VALUES(5,6,7,8); |
||
631 | INSERT INTO t11 VALUES(1,2,9,10); |
||
632 | INSERT INTO t11 VALUES(5,11,12,13); |
||
633 | SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c; |
||
634 | } |
||
635 | } {3 9} |
||
636 | do_test where2-11.2 { |
||
637 | execsql { |
||
638 | CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column |
||
639 | SELECT d FROM t11 WHERE c=9; |
||
640 | } |
||
641 | } {10} |
||
642 | do_test where2-11.3 { |
||
643 | execsql { |
||
644 | SELECT d FROM t11 WHERE c IN (1,2,3,4,5); |
||
645 | } |
||
646 | } {4} |
||
647 | do_test where2-11.4 { |
||
648 | execsql { |
||
649 | SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d; |
||
650 | } |
||
651 | } {4 8 10} |
||
652 | |||
653 | |||
654 | finish_test |