/ Check-in [3c0540e6]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment::-) (CVS 93)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3c0540e6fb103402c3f37fd2df17f9786b363db0
User & Date: drh 2000-06-12 12:20:49
Context
2000-06-12
12:29
:-) (CVS 94) check-in: 84b506b0 user: drh tags: trunk
12:20
:-) (CVS 93) check-in: 3c0540e6 user: drh tags: trunk
2000-06-11
23:50
added the fcnt() function for testing (CVS 92) check-in: 0f93c27c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/where.test.

            1  +# Copyright (c) 1999, 2000 D. Richard Hipp
            2  +#
            3  +# This program is free software; you can redistribute it and/or
            4  +# modify it under the terms of the GNU General Public
            5  +# License as published by the Free Software Foundation; either
            6  +# version 2 of the License, or (at your option) any later version.
            7  +#
            8  +# This program is distributed in the hope that it will be useful,
            9  +# but WITHOUT ANY WARRANTY; without even the implied warranty of
           10  +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
           11  +# General Public License for more details.
           12  +# 
           13  +# You should have received a copy of the GNU General Public
           14  +# License along with this library; if not, write to the
           15  +# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
           16  +# Boston, MA  02111-1307, USA.
           17  +#
           18  +# Author contact information:
           19  +#   drh@hwaci.com
           20  +#   http://www.hwaci.com/drh/
           21  +#
           22  +#***********************************************************************
           23  +# This file implements regression tests for SQLite library.  The
           24  +# focus of this file is testing the use of indices in WHERE clases.
           25  +#
           26  +# $Id: where.test,v 1.1 2000/06/12 12:20:49 drh Exp $
           27  +
           28  +set testdir [file dirname $argv0]
           29  +source $testdir/tester.tcl
           30  +
           31  +# Build some test data
           32  +#
           33  +do_test where-1.0 {
           34  +  execsql {
           35  +    CREATE TABLE t1(w int, x int, y int);
           36  +    CREATE TABLE t2(p int, q int, r int, s int);
           37  +  }
           38  +  for {set i 1} {$i<=100} {incr i} {
           39  +    set w $i
           40  +    set x [expr {int(log($i)/log(2))}]
           41  +    set y [expr {$i*$i + 2*$i + 1}]
           42  +    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
           43  +  }
           44  +  execsql {
           45  +    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
           46  +    CREATE INDEX i1w ON t1(w);
           47  +    CREATE INDEX i1xy ON t1(x,y);
           48  +    CREATE INDEX i2p ON t2(p);
           49  +    CREATE INDEX i2r ON t2(r);
           50  +    CREATE INDEX i2qs ON t2(q, s);
           51  +  }
           52  +} {}
           53  +
           54  +# Verify that queries use an index.  We are using the special "fcnt(*)"
           55  +# function to verify the results.  fcnt(*) returns the number of Fetch
           56  +# operations that have occurred up to the point where fcnt(*) is invoked.
           57  +# By verifing that fcnt(*) returns a small number we know that an index
           58  +# was used instead of an exhaustive search.
           59  +#
           60  +do_test where-1.1 {
           61  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
           62  +} {3 121 2}
           63  +do_test where-1.2 {
           64  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
           65  +} {3 144 2}
           66  +do_test where-1.3 {
           67  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
           68  +} {3 144 2}
           69  +do_test where-1.4 {
           70  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
           71  +} {3 144 2}
           72  +do_test where-1.5 {
           73  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
           74  +} {3 144 2}
           75  +do_test where-1.6 {
           76  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
           77  +} {3 144 2}
           78  +do_test where-1.7 {
           79  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
           80  +} {3 144 2}
           81  +do_test where-1.8 {
           82  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
           83  +} {3 144 2}
           84  +do_test where-1.9 {
           85  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
           86  +} {3 144 2}
           87  +do_test where-1.10 {
           88  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
           89  +} {3 121 2}
           90  +do_test where-1.11 {
           91  +  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
           92  +} {3 100 2}
           93  +
           94  +# Do the same kind of thing except use a join as the data source.
           95  +#
           96  +do_test where-2.1 {
           97  +  execsql {
           98  +    SELECT w, p, fcnt(*) FROM t2, t1
           99  +    WHERE x=q AND y=s AND r=8977
          100  +  }
          101  +} {34 67 4}
          102  +do_test where-2.2 {
          103  +  execsql {
          104  +    SELECT w, p, fcnt(*) FROM t2, t1
          105  +    WHERE x=q AND s=y AND r=8977
          106  +  }
          107  +} {34 67 4}
          108  +do_test where-2.3 {
          109  +  execsql {
          110  +    SELECT w, p, fcnt(*) FROM t2, t1
          111  +    WHERE x=q AND s=y AND r=8977 AND w>10
          112  +  }
          113  +} {34 67 4}
          114  +do_test where-2.4 {
          115  +  execsql {
          116  +    SELECT w, p, fcnt(*) FROM t2, t1
          117  +    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
          118  +  }
          119  +} {34 67 4}
          120  +do_test where-2.5 {
          121  +  execsql {
          122  +    SELECT w, p, fcnt(*) FROM t2, t1
          123  +    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
          124  +  }
          125  +} {34 67 4}
          126  +do_test where-2.6 {
          127  +  execsql {
          128  +    SELECT w, p, fcnt(*) FROM t2, t1
          129  +    WHERE x=q AND p=77 AND s=y AND w>5
          130  +  }
          131  +} {24 77 4}
          132  +do_test where-2.7 {
          133  +  execsql {
          134  +    SELECT w, p, fcnt(*) FROM t1, t2
          135  +    WHERE x=q AND p>77 AND s=y AND w=5
          136  +  }
          137  +} {5 96 4}
          138  +
          139  +# Lets do a 3-way join.
          140  +#
          141  +do_test where-3.1 {
          142  +  execsql {
          143  +    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
          144  +    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
          145  +  }
          146  +} {11 90 11 6}
          147  +do_test where-3.2 {
          148  +  execsql {
          149  +    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
          150  +    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
          151  +  }
          152  +} {12 89 12 6}
          153  +do_test where-3.3 {
          154  +  execsql {
          155  +    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
          156  +    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
          157  +  }
          158  +} {15 86 86 6}
          159  +
          160  +finish_test

Changes to www/changes.tcl.

    12     12   }
    13     13   
    14     14   
    15     15   proc chng {date desc} {
    16     16     puts "<DT><B>$date</B></DT>"
    17     17     puts "<DD><P><UL>$desc</UL></P></DD>"
    18     18   }
           19  +
           20  +chng {2000 June 11} {
           21  +<li>Added a <b>fcnt()</b> function which is designed to help write better.
           22  +    test scripts.  This function is not useful for anything other than
           23  +    testing and debugging, as far as I know.</li>
           24  +}
    19     25   
    20     26   chng {2000 June 8} {
    21     27   <li>Added lots of new test cases</li>
    22     28   <li>Fix a few bugs discovered while adding test cases</li>
    23     29   <li>Begin adding lots of new documentation</li>
    24     30   }
    25     31