# 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.17 2003/06/15 23:42:25 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<y}} {15 3}do_test where-1.20 {  count {SELECT w FROM t1 WHERE x=3 AND y>=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 121<y AND 196>y}} {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 0}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 10}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 1}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 199}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 7}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 199}do_test where-5.9 {  count {    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;  }} {2 1 9 3 1 16 6}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 6}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 6}# 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 SortCallback $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.#