| blob | 5671de9b7da8e88a1472ec5a84262ac88bbcf8d9 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the use of indices in WHERE clases.
16 #
17 # $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Build some test data
23 #
24 do_test where-1.0 {
25 execsql {
26 CREATE TABLE t1(w int, x int, y int);
27 CREATE TABLE t2(p int, q int, r int, s int);
28 }
29 for {set i 1} {$i<=100} {incr i} {
30 set w $i
31 set x [expr {int(log($i)/log(2))}]
32 set y [expr {$i*$i + 2*$i + 1}]
33 execsql "INSERT INTO t1 VALUES($w,$x,$y)"
34 }
35 execsql {
36 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
37 CREATE INDEX i1w ON t1(w);
38 CREATE INDEX i1xy ON t1(x,y);
39 CREATE INDEX i2p ON t2(p);
40 CREATE INDEX i2r ON t2(r);
41 CREATE INDEX i2qs ON t2(q, s);
42 }
43 } {}
45 # Do an SQL statement. Append the search count to the end of the result.
46 #
47 proc count sql {
48 set ::sqlite_search_count 0
49 return [concat [execsql $sql] $::sqlite_search_count]
50 }
52 # Verify that queries use an index. We are using the special variable
53 # "sqlite_search_count" which tallys the number of executions of MoveTo
54 # and Next operators in the VDBE. By verifing that the search count is
55 # small we can be assured that indices are being used properly.
56 #
57 do_test where-1.1 {
58 count {SELECT x, y FROM t1 WHERE w=10}
59 } {3 121 3}
60 do_test where-1.2 {
61 count {SELECT x, y FROM t1 WHERE w=11}
62 } {3 144 3}
63 do_test where-1.3 {
64 count {SELECT x, y FROM t1 WHERE 11=w}
65 } {3 144 3}
66 do_test where-1.4 {
67 count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
68 } {3 144 3}
69 do_test where-1.5 {
70 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
71 } {3 144 3}
72 do_test where-1.6 {
73 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
74 } {3 144 3}
75 do_test where-1.7 {
76 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
77 } {3 144 3}
78 do_test where-1.8 {
79 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
80 } {3 144 3}
81 do_test where-1.9 {
82 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
83 } {3 144 3}
84 do_test where-1.10 {
85 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
86 } {3 121 3}
87 do_test where-1.11 {
88 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
89 } {3 100 3}
91 # New for SQLite version 2.1: Verify that that inequality constraints
92 # are used correctly.
93 #
94 do_test where-1.12 {
95 count {SELECT w FROM t1 WHERE x=3 AND y<100}
96 } {8 3}
97 do_test where-1.13 {
98 count {SELECT w FROM t1 WHERE x=3 AND 100>y}
99 } {8 3}
100 do_test where-1.14 {
101 count {SELECT w FROM t1 WHERE 3=x AND y<100}
102 } {8 3}
103 do_test where-1.15 {
104 count {SELECT w FROM t1 WHERE 3=x AND 100>y}
105 } {8 3}
106 do_test where-1.16 {
107 count {SELECT w FROM t1 WHERE x=3 AND y<=100}
108 } {8 9 5}
109 do_test where-1.17 {
110 count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
111 } {8 9 5}
112 do_test where-1.18 {
113 count {SELECT w FROM t1 WHERE x=3 AND y>225}
114 } {15 3}
115 do_test where-1.19 {
116 count {SELECT w FROM t1 WHERE x=3 AND 225<y}
117 } {15 3}
118 do_test where-1.20 {
119 count {SELECT w FROM t1 WHERE x=3 AND y>=225}
120 } {14 15 5}
121 do_test where-1.21 {
122 count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
123 } {14 15 5}
124 do_test where-1.22 {
125 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
126 } {11 12 5}
127 do_test where-1.23 {
128 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
129 } {10 11 12 13 9}
130 do_test where-1.24 {
131 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
132 } {11 12 5}
133 do_test where-1.25 {
134 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
135 } {10 11 12 13 9}
137 # Need to work on optimizing the BETWEEN operator.
138 #
139 # do_test where-1.26 {
140 # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
141 # } {10 11 12 13 9}
143 do_test where-1.27 {
144 count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
145 } {10 17}
146 do_test where-1.28 {
147 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
148 } {10 99}
149 do_test where-1.29 {
150 count {SELECT w FROM t1 WHERE y==121}
151 } {10 99}
154 do_test where-1.30 {
155 count {SELECT w FROM t1 WHERE w>97}
156 } {98 99 100 6}
157 do_test where-1.31 {
158 count {SELECT w FROM t1 WHERE w>=97}
159 } {97 98 99 100 8}
160 do_test where-1.33 {
161 count {SELECT w FROM t1 WHERE w==97}
162 } {97 3}
163 do_test where-1.34 {
164 count {SELECT w FROM t1 WHERE w+1==98}
165 } {97 99}
166 do_test where-1.35 {
167 count {SELECT w FROM t1 WHERE w<3}
168 } {1 2 4}
169 do_test where-1.36 {
170 count {SELECT w FROM t1 WHERE w<=3}
171 } {1 2 3 6}
172 do_test where-1.37 {
173 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
174 } {1 2 3 199}
176 do_test where-1.38 {
177 count {SELECT (w) FROM t1 WHERE (w)>(97)}
178 } {98 99 100 6}
179 do_test where-1.39 {
180 count {SELECT (w) FROM t1 WHERE (w)>=(97)}
181 } {97 98 99 100 8}
182 do_test where-1.40 {
183 count {SELECT (w) FROM t1 WHERE (w)==(97)}
184 } {97 3}
185 do_test where-1.41 {
186 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
187 } {97 99}
190 # Do the same kind of thing except use a join as the data source.
191 #
192 do_test where-2.1 {
193 count {
194 SELECT w, p FROM t2, t1
195 WHERE x=q AND y=s AND r=8977
196 }
197 } {34 67 6}
198 do_test where-2.2 {
199 count {
200 SELECT w, p FROM t2, t1
201 WHERE x=q AND s=y AND r=8977
202 }
203 } {34 67 6}
204 do_test where-2.3 {
205 count {
206 SELECT w, p FROM t2, t1
207 WHERE x=q AND s=y AND r=8977 AND w>10
208 }
209 } {34 67 6}
210 do_test where-2.4 {
211 count {
212 SELECT w, p FROM t2, t1
213 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
214 }
215 } {34 67 6}
216 do_test where-2.5 {
217 count {
218 SELECT w, p FROM t2, t1
219 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
220 }
221 } {34 67 6}
222 do_test where-2.6 {
223 count {
224 SELECT w, p FROM t2, t1
225 WHERE x=q AND p=77 AND s=y AND w>5
226 }
227 } {24 77 6}
228 do_test where-2.7 {
229 count {
230 SELECT w, p FROM t1, t2
231 WHERE x=q AND p>77 AND s=y AND w=5
232 }
233 } {5 96 6}
235 # Lets do a 3-way join.
236 #
237 do_test where-3.1 {
238 count {
239 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
240 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
241 }
242 } {11 90 11 9}
243 do_test where-3.2 {
244 count {
245 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
246 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
247 }
248 } {12 89 12 9}
249 do_test where-3.3 {
250 count {
251 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
252 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
253 }
254 } {15 86 86 9}
256 # Test to see that the special case of a constant WHERE clause is
257 # handled.
258 #
259 do_test where-4.1 {
260 count {
261 SELECT * FROM t1 WHERE 0
262 }
263 } {0}
264 do_test where-4.2 {
265 count {
266 SELECT * FROM t1 WHERE 1 LIMIT 1
267 }
268 } {1 0 4 1}
269 do_test where-4.3 {
270 execsql {
271 SELECT 99 WHERE 0
272 }
273 } {}
274 do_test where-4.4 {
275 execsql {
276 SELECT 99 WHERE 1
277 }
278 } {99}
280 # Verify that IN operators in a WHERE clause are handled correctly.
281 #
282 do_test where-5.1 {
283 count {
284 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
285 }
286 } {1 0 4 2 1 9 3 1 16 0}
287 do_test where-5.2 {
288 count {
289 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
290 }
291 } {1 0 4 2 1 9 3 1 16 199}
292 do_test where-5.3 {
293 count {
294 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
295 }
296 } {1 0 4 2 1 9 3 1 16 10}
297 do_test where-5.4 {
298 count {
299 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
300 }
301 } {1 0 4 2 1 9 3 1 16 199}
302 do_test where-5.5 {
303 count {
304 SELECT * FROM t1 WHERE rowid IN
305 (select rowid from t1 where rowid IN (-1,2,4))
306 ORDER BY 1;
307 }
308 } {2 1 9 4 2 25 1}
309 do_test where-5.6 {
310 count {
311 SELECT * FROM t1 WHERE rowid+0 IN
312 (select rowid from t1 where rowid IN (-1,2,4))
313 ORDER BY 1;
314 }
315 } {2 1 9 4 2 25 199}
316 do_test where-5.7 {
317 count {
318 SELECT * FROM t1 WHERE w IN
319 (select rowid from t1 where rowid IN (-1,2,4))
320 ORDER BY 1;
321 }
322 } {2 1 9 4 2 25 7}
323 do_test where-5.8 {
324 count {
325 SELECT * FROM t1 WHERE w+0 IN
326 (select rowid from t1 where rowid IN (-1,2,4))
327 ORDER BY 1;
328 }
329 } {2 1 9 4 2 25 199}
330 do_test where-5.9 {
331 count {
332 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
333 }
334 } {2 1 9 3 1 16 6}
335 do_test where-5.10 {
336 count {
337 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
338 }
339 } {2 1 9 3 1 16 199}
340 do_test where-5.11 {
341 count {
342 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
343 }
344 } {79 6 6400 89 6 8100 199}
345 do_test where-5.12 {
346 count {
347 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
348 }
349 } {79 6 6400 89 6 8100 74}
350 do_test where-5.13 {
351 count {
352 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
353 }
354 } {2 1 9 3 1 16 6}
355 do_test where-5.14 {
356 count {
357 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
358 }
359 } {2 1 9 6}
361 # This procedure executes the SQL. Then it checks the generated program
362 # for the SQL and appends a "nosort" to the result if the program contains the
363 # SortCallback opcode. If the program does not contain the SortCallback
364 # opcode it appends "sort"
365 #
366 proc cksort {sql} {
367 set data [execsql $sql]
368 set prog [execsql "EXPLAIN $sql"]
369 if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
370 lappend data $x
371 return $data
372 }
373 # Check out the logic that attempts to implement the ORDER BY clause
374 # using an index rather than by sorting.
375 #
376 do_test where-6.1 {
377 execsql {
378 CREATE TABLE t3(a,b,c);
379 CREATE INDEX t3a ON t3(a);
380 CREATE INDEX t3bc ON t3(b,c);
381 CREATE INDEX t3acb ON t3(a,c,b);
382 INSERT INTO t3 SELECT w, 101-w, y FROM t1;
383 SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
384 }
385 } {100 5050 5050 348550}
386 do_test where-6.2 {
387 cksort {
388 SELECT * FROM t3 ORDER BY a LIMIT 3
389 }
390 } {1 100 4 2 99 9 3 98 16 nosort}
391 do_test where-6.3 {
392 cksort {
393 SELECT * FROM t3 ORDER BY a+1 LIMIT 3
394 }
395 } {1 100 4 2 99 9 3 98 16 sort}
396 do_test where-6.4 {
397 cksort {
398 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
399 }
400 } {1 100 4 2 99 9 3 98 16 nosort}
401 do_test where-6.5 {
402 cksort {
403 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
404 }
405 } {1 100 4 2 99 9 3 98 16 nosort}
406 do_test where-6.6 {
407 cksort {
408 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
409 }
410 } {1 100 4 2 99 9 3 98 16 nosort}
411 do_test where-6.7 {
412 cksort {
413 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
414 }
415 } {1 100 4 2 99 9 3 98 16 sort}
416 do_test where-6.8 {
417 cksort {
418 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
419 }
420 } {1 100 4 2 99 9 3 98 16 sort}
421 do_test where-6.9.1 {
422 cksort {
423 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
424 }
425 } {1 100 4 nosort}
426 do_test where-6.9.2 {
427 cksort {
428 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
429 }
430 } {1 100 4 nosort}
431 do_test where-6.9.3 {
432 cksort {
433 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
434 }
435 } {1 100 4 nosort}
436 do_test where-6.9.4 {
437 cksort {
438 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
439 }
440 } {1 100 4 nosort}
441 do_test where-6.9.5 {
442 cksort {
443 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
444 }
445 } {1 100 4 nosort}
446 do_test where-6.9.6 {
447 cksort {
448 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
449 }
450 } {1 100 4 nosort}
451 do_test where-6.9.7 {
452 cksort {
453 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
454 }
455 } {1 100 4 sort}
456 do_test where-6.9.8 {
457 cksort {
458 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
459 }
460 } {1 100 4 sort}
461 do_test where-6.9.9 {
462 cksort {
463 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
464 }
465 } {1 100 4 sort}
466 do_test where-6.10 {
467 cksort {
468 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
469 }
470 } {1 100 4 nosort}
471 do_test where-6.11 {
472 cksort {
473 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
474 }
475 } {1 100 4 nosort}
476 do_test where-6.12 {
477 cksort {
478 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
479 }
480 } {1 100 4 nosort}
481 do_test where-6.13 {
482 cksort {
483 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
484 }
485 } {100 1 10201 99 2 10000 98 3 9801 nosort}
486 do_test where-6.13.1 {
487 cksort {
488 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
489 }
490 } {100 1 10201 99 2 10000 98 3 9801 sort}
491 do_test where-6.14 {
492 cksort {
493 SELECT * FROM t3 ORDER BY b LIMIT 3
494 }
495 } {100 1 10201 99 2 10000 98 3 9801 nosort}
496 do_test where-6.15 {
497 cksort {
498 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
499 }
500 } {1 0 2 1 3 1 nosort}
501 do_test where-6.16 {
502 cksort {
503 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
504 }
505 } {1 0 2 1 3 1 sort}
506 do_test where-6.17 {
507 cksort {
508 SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
509 }
510 } {4 121 10201 sort}
511 do_test where-6.18 {
512 cksort {
513 SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
514 }
515 } {4 9 16 sort}
516 do_test where-6.19 {
517 cksort {
518 SELECT y FROM t1 ORDER BY w LIMIT 3;
519 }
520 } {4 9 16 nosort}
522 # Tests for reverse-order sorting.
523 #
524 do_test where-7.1 {
525 cksort {
526 SELECT w FROM t1 WHERE x=3 ORDER BY y;
527 }
528 } {8 9 10 11 12 13 14 15 nosort}
529 do_test where-7.2 {
530 cksort {
531 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
532 }
533 } {15 14 13 12 11 10 9 8 nosort}
534 do_test where-7.3 {
535 cksort {
536 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
537 }
538 } {10 11 12 nosort}
539 do_test where-7.4 {
540 cksort {
541 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
542 }
543 } {15 14 13 nosort}
544 do_test where-7.5 {
545 cksort {
546 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
547 }
548 } {15 14 13 12 11 nosort}
549 do_test where-7.6 {
550 cksort {
551 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
552 }
553 } {15 14 13 12 11 10 nosort}
554 do_test where-7.7 {
555 cksort {
556 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
557 }
558 } {12 11 10 nosort}
559 do_test where-7.8 {
560 cksort {
561 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
562 }
563 } {13 12 11 10 nosort}
564 do_test where-7.9 {
565 cksort {
566 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
567 }
568 } {13 12 11 nosort}
569 do_test where-7.10 {
570 cksort {
571 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
572 }
573 } {12 11 10 nosort}
574 do_test where-7.11 {
575 cksort {
576 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
577 }
578 } {10 11 12 nosort}
579 do_test where-7.12 {
580 cksort {
581 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
582 }
583 } {10 11 12 13 nosort}
584 do_test where-7.13 {
585 cksort {
586 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
587 }
588 } {11 12 13 nosort}
589 do_test where-7.14 {
590 cksort {
591 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
592 }
593 } {10 11 12 nosort}
594 do_test where-7.15 {
595 cksort {
596 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
597 }
598 } {nosort}
599 do_test where-7.16 {
600 cksort {
601 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
602 }
603 } {8 nosort}
604 do_test where-7.17 {
605 cksort {
606 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
607 }
608 } {nosort}
609 do_test where-7.18 {
610 cksort {
611 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
612 }
613 } {15 nosort}
614 do_test where-7.19 {
615 cksort {
616 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
617 }
618 } {nosort}
619 do_test where-7.20 {
620 cksort {
621 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
622 }
623 } {8 nosort}
624 do_test where-7.21 {
625 cksort {
626 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
627 }
628 } {nosort}
629 do_test where-7.22 {
630 cksort {
631 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
632 }
633 } {15 nosort}
634 do_test where-7.23 {
635 cksort {
636 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
637 }
638 } {nosort}
639 do_test where-7.24 {
640 cksort {
641 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
642 }
643 } {1 nosort}
644 do_test where-7.25 {
645 cksort {
646 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
647 }
648 } {nosort}
649 do_test where-7.26 {
650 cksort {
651 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
652 }
653 } {100 nosort}
654 do_test where-7.27 {
655 cksort {
656 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
657 }
658 } {nosort}
659 do_test where-7.28 {
660 cksort {
661 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
662 }
663 } {1 nosort}
664 do_test where-7.29 {
665 cksort {
666 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
667 }
668 } {nosort}
669 do_test where-7.30 {
670 cksort {
671 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
672 }
673 } {100 nosort}
675 do_test where-8.1 {
676 execsql {
677 CREATE TABLE t4 AS SELECT * FROM t1;
678 CREATE INDEX i4xy ON t4(x,y);
679 }
680 cksort {
681 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
682 }
683 } {30 29 28 nosort}
684 do_test where-8.2 {
685 execsql {
686 DELETE FROM t4;
687 }
688 cksort {
689 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
690 }
691 } {nosort}
693 # Make sure searches with an index work with an empty table.
694 #
695 do_test where-9.1 {
696 execsql {
697 CREATE TABLE t5(x PRIMARY KEY);
698 SELECT * FROM t5 WHERE x<10;
699 }
700 } {}
701 do_test where-9.2 {
702 execsql {
703 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
704 }
705 } {}
706 do_test where-9.3 {
707 execsql {
708 SELECT * FROM t5 WHERE x=10;
709 }
710 } {}
712 do_test where-10.1 {
713 execsql {
714 SELECT 1 WHERE abs(random())<0
715 }
716 } {}
717 do_test where-10.2 {
718 proc tclvar_func {vname} {return [set ::$vname]}
719 db function tclvar tclvar_func
720 set ::v1 0
721 execsql {
722 SELECT count(*) FROM t1 WHERE tclvar('v1');
723 }
724 } {0}
725 do_test where-10.3 {
726 set ::v1 1
727 execsql {
728 SELECT count(*) FROM t1 WHERE tclvar('v1');
729 }
730 } {100}
731 do_test where-10.4 {
732 set ::v1 1
733 proc tclvar_func {vname} {
734 upvar #0 $vname v
735 set v [expr {!$v}]
736 return $v
737 }
738 execsql {
739 SELECT count(*) FROM t1 WHERE tclvar('v1');
740 }
741 } {50}
743 integrity_check {where-99.0}
745 finish_test