4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this file is testing the use of indices in WHERE clases.
15 # $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Build some test data
24 CREATE TABLE t1(w int, x int, y int);
25 CREATE TABLE t2(p int, q int, r int, s int);
27 for {set i 1} {$i<=100} {incr i} {
29 set x [expr {int(log($i)/log(2))}]
30 set y [expr {$i*$i + 2*$i + 1}]
31 execsql "INSERT INTO t1 VALUES($w,$x,$y)"
34 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
35 CREATE INDEX i1w ON t1(w);
36 CREATE INDEX i1xy ON t1(x,y);
37 CREATE INDEX i2p ON t2(p);
38 CREATE INDEX i2r ON t2(r);
39 CREATE INDEX i2qs ON t2(q, s);
43 # Do an SQL statement. Append the search count to the end of the result.
46 set ::sqlite_search_count 0
47 return [concat [execsql $sql] $::sqlite_search_count]
50 # Verify that queries use an index. We are using the special variable
51 # "sqlite_search_count" which tallys the number of executions of MoveTo
52 # and Next operators in the VDBE. By verifing that the search count is
53 # small we can be assured that indices are being used properly.
56 count {SELECT x, y FROM t1 WHERE w=10}
59 count {SELECT x, y FROM t1 WHERE w=11}
62 count {SELECT x, y FROM t1 WHERE 11=w}
65 count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
68 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
71 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
74 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
77 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
80 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
83 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
86 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
89 # New for SQLite version 2.1: Verify that that inequality constraints
93 count {SELECT w FROM t1 WHERE x=3 AND y<100}
96 count {SELECT w FROM t1 WHERE x=3 AND 100>y}
99 count {SELECT w FROM t1 WHERE 3=x AND y<100}
102 count {SELECT w FROM t1 WHERE 3=x AND 100>y}
105 count {SELECT w FROM t1 WHERE x=3 AND y<=100}
108 count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
111 count {SELECT w FROM t1 WHERE x=3 AND y>225}
114 count {SELECT w FROM t1 WHERE x=3 AND 225<y}
117 count {SELECT w FROM t1 WHERE x=3 AND y>=225}
120 count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
123 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
126 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
129 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
132 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
135 # Need to work on optimizing the BETWEEN operator.
137 # do_test where-1.26 {
138 # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
142 count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
145 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
148 count {SELECT w FROM t1 WHERE y==121}
153 count {SELECT w FROM t1 WHERE w>97}
156 count {SELECT w FROM t1 WHERE w>=97}
159 count {SELECT w FROM t1 WHERE w==97}
162 count {SELECT w FROM t1 WHERE w+1==98}
165 count {SELECT w FROM t1 WHERE w<3}
168 count {SELECT w FROM t1 WHERE w<=3}
171 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
175 count {SELECT (w) FROM t1 WHERE (w)>(97)}
178 count {SELECT (w) FROM t1 WHERE (w)>=(97)}
181 count {SELECT (w) FROM t1 WHERE (w)==(97)}
184 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
188 # Do the same kind of thing except use a join as the data source.
192 SELECT w, p FROM t2, t1
193 WHERE x=q AND y=s AND r=8977
198 SELECT w, p FROM t2, t1
199 WHERE x=q AND s=y AND r=8977
204 SELECT w, p FROM t2, t1
205 WHERE x=q AND s=y AND r=8977 AND w>10
210 SELECT w, p FROM t2, t1
211 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
216 SELECT w, p FROM t2, t1
217 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
222 SELECT w, p FROM t2, t1
223 WHERE x=q AND p=77 AND s=y AND w>5
228 SELECT w, p FROM t1, t2
229 WHERE x=q AND p>77 AND s=y AND w=5
233 # Lets do a 3-way join.
237 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
238 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
243 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
244 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
249 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
250 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
254 # Test to see that the special case of a constant WHERE clause is
259 SELECT * FROM t1 WHERE 0
264 SELECT * FROM t1 WHERE 1 LIMIT 1
278 # Verify that IN operators in a WHERE clause are handled correctly.
282 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
284 } {1 0 4 2 1 9 3 1 16 0}
287 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
289 } {1 0 4 2 1 9 3 1 16 199}
292 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
294 } {1 0 4 2 1 9 3 1 16 10}
297 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
299 } {1 0 4 2 1 9 3 1 16 199}
302 SELECT * FROM t1 WHERE rowid IN
303 (select rowid from t1 where rowid IN (-1,2,4))
309 SELECT * FROM t1 WHERE rowid+0 IN
310 (select rowid from t1 where rowid IN (-1,2,4))
316 SELECT * FROM t1 WHERE w IN
317 (select rowid from t1 where rowid IN (-1,2,4))
323 SELECT * FROM t1 WHERE w+0 IN
324 (select rowid from t1 where rowid IN (-1,2,4))
330 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
335 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
340 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
342 } {79 6 6400 89 6 8100 199}
345 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
347 } {79 6 6400 89 6 8100 74}
350 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
355 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
359 # This procedure executes the SQL. Then it checks the generated program
360 # for the SQL and appends a "nosort" to the result if the program contains the
361 # SortCallback opcode. If the program does not contain the SortCallback
362 # opcode it appends "sort"
365 set data [execsql $sql]
366 set prog [execsql "EXPLAIN $sql"]
367 if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
371 # Check out the logic that attempts to implement the ORDER BY clause
372 # using an index rather than by sorting.
376 CREATE TABLE t3(a,b,c);
377 CREATE INDEX t3a ON t3(a);
378 CREATE INDEX t3bc ON t3(b,c);
379 CREATE INDEX t3acb ON t3(a,c,b);
380 INSERT INTO t3 SELECT w, 101-w, y FROM t1;
381 SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
383 } {100 5050 5050 348550}
386 SELECT * FROM t3 ORDER BY a LIMIT 3
388 } {1 100 4 2 99 9 3 98 16 nosort}
391 SELECT * FROM t3 ORDER BY a+1 LIMIT 3
393 } {1 100 4 2 99 9 3 98 16 sort}
396 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
398 } {1 100 4 2 99 9 3 98 16 nosort}
401 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
403 } {1 100 4 2 99 9 3 98 16 nosort}
406 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
408 } {1 100 4 2 99 9 3 98 16 nosort}
411 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
413 } {1 100 4 2 99 9 3 98 16 sort}
416 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
418 } {1 100 4 2 99 9 3 98 16 sort}
419 do_test where-6.9.1 {
421 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
424 do_test where-6.9.2 {
426 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
429 do_test where-6.9.3 {
431 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
434 do_test where-6.9.4 {
436 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
439 do_test where-6.9.5 {
441 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
444 do_test where-6.9.6 {
446 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
449 do_test where-6.9.7 {
451 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
454 do_test where-6.9.8 {
456 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
459 do_test where-6.9.9 {
461 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
466 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
471 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
476 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
481 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
483 } {100 1 10201 99 2 10000 98 3 9801 nosort}
484 do_test where-6.13.1 {
486 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
488 } {100 1 10201 99 2 10000 98 3 9801 sort}
491 SELECT * FROM t3 ORDER BY b LIMIT 3
493 } {100 1 10201 99 2 10000 98 3 9801 nosort}
496 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
498 } {1 0 2 1 3 1 nosort}
501 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
506 SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
511 SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
516 SELECT y FROM t1 ORDER BY w LIMIT 3;
520 # Tests for reverse-order sorting.
524 SELECT w FROM t1 WHERE x=3 ORDER BY y;
526 } {8 9 10 11 12 13 14 15 nosort}
529 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
531 } {15 14 13 12 11 10 9 8 nosort}
534 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
539 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
544 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
546 } {15 14 13 12 11 nosort}
549 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
551 } {15 14 13 12 11 10 nosort}
554 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
559 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
561 } {13 12 11 10 nosort}
564 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
569 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
574 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
579 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
581 } {10 11 12 13 nosort}
584 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
589 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
594 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
599 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
604 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
609 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
614 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
619 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
624 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
629 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
634 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
639 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
644 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
649 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
654 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
659 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
664 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
669 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
675 CREATE TABLE t4 AS SELECT * FROM t1;
676 CREATE INDEX i4xy ON t4(x,y);
679 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
687 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
691 # Make sure searches with an index work with an empty table.
695 CREATE TABLE t5(x PRIMARY KEY);
696 SELECT * FROM t5 WHERE x<10;
701 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
706 SELECT * FROM t5 WHERE x=10;
712 SELECT 1 WHERE abs(random())<0
716 proc tclvar_func {vname} {return [set ::$vname]}
717 db function tclvar tclvar_func
720 SELECT count(*) FROM t1 WHERE tclvar('v1');
726 SELECT count(*) FROM t1 WHERE tclvar('v1');
731 proc tclvar_func {vname} {
737 SELECT count(*) FROM t1 WHERE tclvar('v1');
741 integrity_check {where-99.0}