# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clases. # # $Id: where.test,v 1.21 2004/05/21 03:01:59 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test where-1.0 { execsql { CREATE TABLE t1(w int, x int, y int); CREATE TABLE t2(p int, q int, r int, s int); } for {set i 1} {$i<=100} {incr i} { set w $i set x [expr {int(log($i)/log(2))}] set y [expr {$i*$i + 2*$i + 1}] execsql "INSERT INTO t1 VALUES($w,$x,$y)" } execsql { INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; CREATE INDEX i1w ON t1(w); CREATE INDEX i1xy ON t1(x,y); CREATE INDEX i2p ON t2(p); CREATE INDEX i2r ON t2(r); CREATE INDEX i2qs ON t2(q, s); } } {} # Do an SQL statement. Append the search count to the end of the result. # proc count sql { set ::sqlite_search_count 0 return [concat [execsql $sql] $::sqlite_search_count] } # Verify that queries use an index. We are using the special variable # "sqlite_search_count" which tallys the number of executions of MoveTo # and Next operators in the VDBE. By verifing that the search count is # small we can be assured that indices are being used properly. # do_test where-1.1 { count {SELECT x, y FROM t1 WHERE w=10} } {3 121 3} do_test where-1.2 { count {SELECT x, y FROM t1 WHERE w=11} } {3 144 3} do_test where-1.3 { count {SELECT x, y FROM t1 WHERE 11=w} } {3 144 3} do_test where-1.4 { count {SELECT x, y FROM t1 WHERE 11=w AND x>2} } {3 144 3} do_test where-1.5 { count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} } {3 144 3} do_test where-1.6 { count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} } {3 144 3} do_test where-1.7 { count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} } {3 144 3} do_test where-1.8 { count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} } {3 144 3} do_test where-1.9 { count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} } {3 144 3} do_test where-1.10 { count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} } {3 121 3} do_test where-1.11 { count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} } {3 100 3} # New for SQLite version 2.1: Verify that that inequality constraints # are used correctly. # do_test where-1.12 { count {SELECT w FROM t1 WHERE x=3 AND y<100} } {8 3} do_test where-1.13 { count {SELECT w FROM t1 WHERE x=3 AND 100>y} } {8 3} do_test where-1.14 { count {SELECT w FROM t1 WHERE 3=x AND y<100} } {8 3} do_test where-1.15 { count {SELECT w FROM t1 WHERE 3=x AND 100>y} } {8 3} do_test where-1.16 { count {SELECT w FROM t1 WHERE x=3 AND y<=100} } {8 9 5} do_test where-1.17 { count {SELECT w FROM t1 WHERE x=3 AND 100>=y} } {8 9 5} do_test where-1.18 { count {SELECT w FROM t1 WHERE x=3 AND y>225} } {15 3} do_test where-1.19 { count {SELECT w FROM t1 WHERE x=3 AND 225=225} } {14 15 5} do_test where-1.21 { count {SELECT w FROM t1 WHERE x=3 AND 225<=y} } {14 15 5} do_test where-1.22 { count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} } {11 12 5} do_test where-1.23 { count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} } {10 11 12 13 9} do_test where-1.24 { count {SELECT w FROM t1 WHERE x=3 AND 121y} } {11 12 5} do_test where-1.25 { count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} } {10 11 12 13 9} # Need to work on optimizing the BETWEEN operator. # # do_test where-1.26 { # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} # } {10 11 12 13 9} do_test where-1.27 { count {SELECT w FROM t1 WHERE x=3 AND y+1==122} } {10 17} do_test where-1.28 { count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} } {10 99} do_test where-1.29 { count {SELECT w FROM t1 WHERE y==121} } {10 99} do_test where-1.30 { count {SELECT w FROM t1 WHERE w>97} } {98 99 100 6} do_test where-1.31 { count {SELECT w FROM t1 WHERE w>=97} } {97 98 99 100 8} do_test where-1.33 { count {SELECT w FROM t1 WHERE w==97} } {97 3} do_test where-1.34 { count {SELECT w FROM t1 WHERE w+1==98} } {97 99} do_test where-1.35 { count {SELECT w FROM t1 WHERE w<3} } {1 2 4} do_test where-1.36 { count {SELECT w FROM t1 WHERE w<=3} } {1 2 3 6} do_test where-1.37 { count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} } {1 2 3 199} do_test where-1.38 { count {SELECT (w) FROM t1 WHERE (w)>(97)} } {98 99 100 6} do_test where-1.39 { count {SELECT (w) FROM t1 WHERE (w)>=(97)} } {97 98 99 100 8} do_test where-1.40 { count {SELECT (w) FROM t1 WHERE (w)==(97)} } {97 3} do_test where-1.41 { count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)} } {97 99} # Do the same kind of thing except use a join as the data source. # do_test where-2.1 { count { SELECT w, p FROM t2, t1 WHERE x=q AND y=s AND r=8977 } } {34 67 6} do_test where-2.2 { count { SELECT w, p FROM t2, t1 WHERE x=q AND s=y AND r=8977 } } {34 67 6} do_test where-2.3 { count { SELECT w, p FROM t2, t1 WHERE x=q AND s=y AND r=8977 AND w>10 } } {34 67 6} do_test where-2.4 { count { SELECT w, p FROM t2, t1 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 } } {34 67 6} do_test where-2.5 { count { SELECT w, p FROM t2, t1 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 } } {34 67 6} do_test where-2.6 { count { SELECT w, p FROM t2, t1 WHERE x=q AND p=77 AND s=y AND w>5 } } {24 77 6} do_test where-2.7 { count { SELECT w, p FROM t1, t2 WHERE x=q AND p>77 AND s=y AND w=5 } } {5 96 6} # Lets do a 3-way join. # do_test where-3.1 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 } } {11 90 11 9} do_test where-3.2 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 } } {12 89 12 9} do_test where-3.3 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y } } {15 86 86 9} # Test to see that the special case of a constant WHERE clause is # handled. # do_test where-4.1 { count { SELECT * FROM t1 WHERE 0 } } {0} do_test where-4.2 { count { SELECT * FROM t1 WHERE 1 LIMIT 1 } } {1 0 4 1} do_test where-4.3 { execsql { SELECT 99 WHERE 0 } } {} do_test where-4.4 { execsql { SELECT 99 WHERE 1 } } {99} # Verify that IN operators in a WHERE clause are handled correctly. # do_test where-5.1 { count { SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 3} do_test where-5.2 { count { SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 199} do_test where-5.3 { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 13} do_test where-5.4 { count { SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 199} do_test where-5.5 { count { SELECT * FROM t1 WHERE rowid IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 3} do_test where-5.6 { count { SELECT * FROM t1 WHERE rowid+0 IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 201} do_test where-5.7 { count { SELECT * FROM t1 WHERE w IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 9} do_test where-5.8 { count { SELECT * FROM t1 WHERE w+0 IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 201} do_test where-5.9 { count { SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 7} do_test where-5.10 { count { SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 199} do_test where-5.11 { count { SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 199} do_test where-5.12 { count { SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 74} do_test where-5.13 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; } } {2 1 9 3 1 16 7} do_test where-5.14 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; } } {2 1 9 7} # This procedure executes the SQL. Then it checks the generated program # for the SQL and appends a "nosort" to the result if the program contains the # SortCallback opcode. If the program does not contain the SortCallback # opcode it appends "sort" # proc cksort {sql} { set data [execsql $sql] set prog [execsql "EXPLAIN $sql"] if {[regexp Sort $prog]} {set x sort} {set x nosort} lappend data $x return $data } # Check out the logic that attempts to implement the ORDER BY clause # using an index rather than by sorting. # do_test where-6.1 { execsql { CREATE TABLE t3(a,b,c); CREATE INDEX t3a ON t3(a); CREATE INDEX t3bc ON t3(b,c); CREATE INDEX t3acb ON t3(a,c,b); INSERT INTO t3 SELECT w, 101-w, y FROM t1; SELECT count(*), sum(a), sum(b), sum(c) FROM t3; } } {100 5050 5050 348550} do_test where-6.2 { cksort { SELECT * FROM t3 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test where-6.3 { cksort { SELECT * FROM t3 ORDER BY a+1 LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test where-6.4 { cksort { SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test where-6.5 { cksort { SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test where-6.6 { cksort { SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test where-6.7 { cksort { SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test where-6.8 { cksort { SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test where-6.9.1 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.2 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.3 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.4 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.5 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.6 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3 } } {1 100 4 nosort} do_test where-6.9.7 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3 } } {1 100 4 sort} do_test where-6.9.8 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3 } } {1 100 4 sort} do_test where-6.9.9 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3 } } {1 100 4 sort} do_test where-6.10 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 } } {1 100 4 nosort} do_test where-6.11 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 } } {1 100 4 nosort} do_test where-6.12 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 } } {1 100 4 nosort} do_test where-6.13 { cksort { SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 } } {100 1 10201 99 2 10000 98 3 9801 nosort} do_test where-6.13.1 { cksort { SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3 } } {100 1 10201 99 2 10000 98 3 9801 sort} do_test where-6.14 { cksort { SELECT * FROM t3 ORDER BY b LIMIT 3 } } {100 1 10201 99 2 10000 98 3 9801 nosort} do_test where-6.15 { cksort { SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 } } {1 0 2 1 3 1 nosort} do_test where-6.16 { cksort { SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 } } {1 0 2 1 3 1 sort} #### Version 3 does not work this way: #do_test where-6.17 { # cksort { # SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; # } #} {4 121 10201 sort} #do_test where-6.18 { # cksort { # SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; # } #} {4 9 16 sort} do_test where-6.19 { cksort { SELECT y FROM t1 ORDER BY w LIMIT 3; } } {4 9 16 nosort} # Tests for reverse-order sorting. # do_test where-7.1 { cksort { SELECT w FROM t1 WHERE x=3 ORDER BY y; } } {8 9 10 11 12 13 14 15 nosort} do_test where-7.2 { cksort { SELECT w FROM t1 WHERE x=3 ORDER BY y DESC; } } {15 14 13 12 11 10 9 8 nosort} do_test where-7.3 { cksort { SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3; } } {10 11 12 nosort} do_test where-7.4 { cksort { SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3; } } {15 14 13 nosort} do_test where-7.5 { cksort { SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC; } } {15 14 13 12 11 nosort} do_test where-7.6 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC; } } {15 14 13 12 11 10 nosort} do_test where-7.7 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC; } } {12 11 10 nosort} do_test where-7.8 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC; } } {13 12 11 10 nosort} do_test where-7.9 { cksort { SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC; } } {13 12 11 nosort} do_test where-7.10 { cksort { SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC; } } {12 11 10 nosort} do_test where-7.11 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y; } } {10 11 12 nosort} do_test where-7.12 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y; } } {10 11 12 13 nosort} do_test where-7.13 { cksort { SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y; } } {11 12 13 nosort} do_test where-7.14 { cksort { SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y; } } {10 11 12 nosort} do_test where-7.15 { cksort { SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y; } } {nosort} do_test where-7.16 { cksort { SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y; } } {8 nosort} do_test where-7.17 { cksort { SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y; } } {nosort} do_test where-7.18 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y; } } {15 nosort} do_test where-7.19 { cksort { SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC; } } {nosort} do_test where-7.20 { cksort { SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC; } } {8 nosort} do_test where-7.21 { cksort { SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC; } } {nosort} do_test where-7.22 { cksort { SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC; } } {15 nosort} do_test where-7.23 { cksort { SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y; } } {nosort} do_test where-7.24 { cksort { SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y; } } {1 nosort} do_test where-7.25 { cksort { SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y; } } {nosort} do_test where-7.26 { cksort { SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y; } } {100 nosort} do_test where-7.27 { cksort { SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC; } } {nosort} do_test where-7.28 { cksort { SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC; } } {1 nosort} do_test where-7.29 { cksort { SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC; } } {nosort} do_test where-7.30 { cksort { SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC; } } {100 nosort} do_test where-8.1 { execsql { CREATE TABLE t4 AS SELECT * FROM t1; CREATE INDEX i4xy ON t4(x,y); } cksort { SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; } } {30 29 28 nosort} do_test where-8.2 { execsql { DELETE FROM t4; } cksort { SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; } } {nosort} # Make sure searches with an index work with an empty table. # do_test where-9.1 { execsql { CREATE TABLE t5(x PRIMARY KEY); SELECT * FROM t5 WHERE x<10; } } {} do_test where-9.2 { execsql { SELECT * FROM t5 WHERE x<10 ORDER BY x DESC; } } {} do_test where-9.3 { execsql { SELECT * FROM t5 WHERE x=10; } } {} do_test where-10.1 { execsql { SELECT 1 WHERE abs(random())<0 } } {} do_test where-10.2 { proc tclvar_func {vname} {return [set ::$vname]} db function tclvar tclvar_func set ::v1 0 execsql { SELECT count(*) FROM t1 WHERE tclvar('v1'); } } {0} do_test where-10.3 { set ::v1 1 execsql { SELECT count(*) FROM t1 WHERE tclvar('v1'); } } {100} do_test where-10.4 { set ::v1 1 proc tclvar_func {vname} { upvar #0 $vname v set v [expr {!$v}] return $v } execsql { SELECT count(*) FROM t1 WHERE tclvar('v1'); } } {50} integrity_check {where-99.0} finish_test