SQLite

Check-in [3c0540e6fb]
Login

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

Overview
Comment::-) (CVS 93)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3c0540e6fb103402c3f37fd2df17f9786b363db0
User & Date: drh 2000-06-12 12:20:49.000
Context
2000-06-12
12:29
:-) (CVS 94) (check-in: 84b506b06c user: drh tags: trunk)
12:20
:-) (CVS 93) (check-in: 3c0540e6fb user: drh tags: trunk)
2000-06-11
23:50
added the fcnt() function for testing (CVS 92) (check-in: 0f93c27cdd user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Added test/where.test.
































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# 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.1 2000/06/12 12:20:49 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);
  }
} {}

# Verify that queries use an index.  We are using the special "fcnt(*)"
# function to verify the results.  fcnt(*) returns the number of Fetch
# operations that have occurred up to the point where fcnt(*) is invoked.
# By verifing that fcnt(*) returns a small number we know that an index
# was used instead of an exhaustive search.
#
do_test where-1.1 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
} {3 121 2}
do_test where-1.2 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
} {3 144 2}
do_test where-1.3 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
} {3 144 2}
do_test where-1.4 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
} {3 144 2}
do_test where-1.5 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 2}
do_test where-1.6 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 2}
do_test where-1.7 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 2}
do_test where-1.8 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 2}
do_test where-1.9 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 2}
do_test where-1.10 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 2}
do_test where-1.11 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 2}

# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 4}
do_test where-2.2 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 4}
do_test where-2.3 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 4}
do_test where-2.4 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 4}
do_test where-2.5 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 4}
do_test where-2.6 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 4}
do_test where-2.7 {
  execsql {
    SELECT w, p, fcnt(*) FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 4}

# Lets do a 3-way join.
#
do_test where-3.1 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) 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 6}
do_test where-3.2 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) 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 6}
do_test where-3.3 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) 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 6}

finish_test
Changes to www/changes.tcl.
12
13
14
15
16
17
18






19
20
21
22
23
24
25
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}







chng {2000 June 8} {
<li>Added lots of new test cases</li>
<li>Fix a few bugs discovered while adding test cases</li>
<li>Begin adding lots of new documentation</li>
}








>
>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2000 June 11} {
<li>Added a <b>fcnt()</b> function which is designed to help write better.
    test scripts.  This function is not useful for anything other than
    testing and debugging, as far as I know.</li>
}

chng {2000 June 8} {
<li>Added lots of new test cases</li>
<li>Fix a few bugs discovered while adding test cases</li>
<li>Begin adding lots of new documentation</li>
}