| blob | 612dc1909c7ba640179e906c27fd2218e664aca7 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the IN and BETWEEN operator.
16 #
17 # $Id: in.test,v 1.11 2004/01/15 03:30:25 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Generate the test data we will need for the first squences of tests.
23 #
24 do_test in-1.0 {
25 set fd [open data1.txt w]
26 for {set i 1} {$i<=10} {incr i} {
27 puts $fd "$i\t[expr {int(pow(2,$i))}]"
28 }
29 close $fd
30 execsql {
31 CREATE TABLE t1(a int, b int);
32 COPY t1 FROM 'data1.txt';
33 }
34 file delete -force data1.txt
35 execsql {SELECT count(*) FROM t1}
36 } {10}
38 # Do basic testing of BETWEEN.
39 #
40 do_test in-1.1 {
41 execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
42 } {4 5}
43 do_test in-1.2 {
44 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}
45 } {1 2 3 6 7 8 9 10}
46 do_test in-1.3 {
47 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}
48 } {1 2 3 4}
49 do_test in-1.4 {
50 execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}
51 } {5 6 7 8 9 10}
52 do_test in-1.6 {
53 execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
54 } {1 2 3 4 9}
55 do_test in-1.7 {
56 execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
57 } {101 102 103 4 5 6 7 8 9 10}
60 # Testing of the IN operator using static lists on the right-hand side.
61 #
62 do_test in-2.1 {
63 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
64 } {3 4 5}
65 do_test in-2.2 {
66 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}
67 } {1 2 6 7 8 9 10}
68 do_test in-2.3 {
69 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}
70 } {3 4 5 9}
71 do_test in-2.4 {
72 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
73 } {1 2 6 7 8 9 10}
74 do_test in-2.5 {
75 execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
76 } {1 2 103 104 5 6 7 8 9 10}
78 do_test in-2.6 {
79 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (b+10,20)}} msg]
80 lappend v $msg
81 } {1 {right-hand side of IN operator must be constant}}
82 do_test in-2.7 {
83 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}} msg]
84 lappend v $msg
85 } {1 {right-hand side of IN operator must be constant}}
86 do_test in-2.8 {
87 execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
88 } {4 5}
89 do_test in-2.9 {
90 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}} msg]
91 lappend v $msg
92 } {1 {right-hand side of IN operator must be constant}}
93 do_test in-2.10 {
94 set v [catch {execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} msg]
95 lappend v $msg
96 } {1 {right-hand side of IN operator must be constant}}
97 do_test in-2.11 {
98 set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
99 lappend v $msg
100 } {1 {no such column: c}}
102 # Testing the IN operator where the right-hand side is a SELECT
103 #
104 do_test in-3.1 {
105 execsql {
106 SELECT a FROM t1
107 WHERE b IN (SELECT b FROM t1 WHERE a<5)
108 ORDER BY a
109 }
110 } {1 2 3 4}
111 do_test in-3.2 {
112 execsql {
113 SELECT a FROM t1
114 WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512
115 ORDER BY a
116 }
117 } {1 2 3 4 9}
118 do_test in-3.3 {
119 execsql {
120 SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b
121 }
122 } {101 102 103 104 5 6 7 8 9 10}
124 # Make sure the UPDATE and DELETE commands work with IN-SELECT
125 #
126 do_test in-4.1 {
127 execsql {
128 UPDATE t1 SET b=b*2
129 WHERE b IN (SELECT b FROM t1 WHERE a>8)
130 }
131 execsql {SELECT b FROM t1 ORDER BY b}
132 } {2 4 8 16 32 64 128 256 1024 2048}
133 do_test in-4.2 {
134 execsql {
135 DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
136 }
137 execsql {SELECT a FROM t1 ORDER BY a}
138 } {1 2 3 4 5 6 7 8}
139 do_test in-4.3 {
140 execsql {
141 DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
142 }
143 execsql {SELECT a FROM t1 ORDER BY a}
144 } {5 6 7 8}
146 # Do an IN with a constant RHS but where the RHS has many, many
147 # elements. We need to test that collisions in the hash table
148 # are resolved properly.
149 #
150 do_test in-5.1 {
151 execsql {
152 INSERT INTO t1 VALUES('hello', 'world');
153 SELECT * FROM t1
154 WHERE a IN (
155 'Do','an','IN','with','a','constant','RHS','but','where','the',
156 'has','many','elements','We','need','to','test','that',
157 'collisions','hash','table','are','resolved','properly',
158 'This','in-set','contains','thirty','one','entries','hello');
159 }
160 } {hello world}
162 # Make sure the IN operator works with INTEGER PRIMARY KEY fields.
163 #
164 do_test in-6.1 {
165 execsql {
166 CREATE TABLE ta(a INTEGER PRIMARY KEY, b);
167 INSERT INTO ta VALUES(1,1);
168 INSERT INTO ta VALUES(2,2);
169 INSERT INTO ta VALUES(3,3);
170 INSERT INTO ta VALUES(4,4);
171 INSERT INTO ta VALUES(6,6);
172 INSERT INTO ta VALUES(8,8);
173 INSERT INTO ta VALUES(10,
174 'This is a key that is long enough to require a malloc in the VDBE');
175 SELECT * FROM ta WHERE a<10;
176 }
177 } {1 1 2 2 3 3 4 4 6 6 8 8}
178 do_test in-6.2 {
179 execsql {
180 CREATE TABLE tb(a INTEGER PRIMARY KEY, b);
181 INSERT INTO tb VALUES(1,1);
182 INSERT INTO tb VALUES(2,2);
183 INSERT INTO tb VALUES(3,3);
184 INSERT INTO tb VALUES(5,5);
185 INSERT INTO tb VALUES(7,7);
186 INSERT INTO tb VALUES(9,9);
187 INSERT INTO tb VALUES(11,
188 'This is a key that is long enough to require a malloc in the VDBE');
189 SELECT * FROM tb WHERE a<10;
190 }
191 } {1 1 2 2 3 3 5 5 7 7 9 9}
192 do_test in-6.3 {
193 execsql {
194 SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
195 }
196 } {1 2 3}
197 do_test in-6.4 {
198 execsql {
199 SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);
200 }
201 } {4 6 8 10}
202 do_test in-6.5 {
203 execsql {
204 SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
205 }
206 } {1 2 3 10}
207 do_test in-6.6 {
208 execsql {
209 SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
210 }
211 } {4 6 8}
212 do_test in-6.7 {
213 execsql {
214 SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
215 }
216 } {1 2 3}
217 do_test in-6.8 {
218 execsql {
219 SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);
220 }
221 } {4 6 8 10}
222 do_test in-6.9 {
223 execsql {
224 SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
225 }
226 } {1 2 3}
227 do_test in-6.10 {
228 execsql {
229 SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);
230 }
231 } {4 6 8 10}
233 # Tests of IN operator against empty sets. (Ticket #185)
234 #
235 do_test in-7.1 {
236 execsql {
237 SELECT a FROM t1 WHERE a IN ();
238 }
239 } {}
240 do_test in-7.2 {
241 execsql {
242 SELECT a FROM t1 WHERE a IN (5);
243 }
244 } {5}
245 do_test in-7.3 {
246 execsql {
247 SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;
248 }
249 } {5 6 7 8 hello}
250 do_test in-7.4 {
251 execsql {
252 SELECT a FROM t1 WHERE a IN (5) AND b IN ();
253 }
254 } {}
255 do_test in-7.5 {
256 execsql {
257 SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
258 }
259 } {5}
260 do_test in-7.6 {
261 execsql {
262 SELECT a FROM ta WHERE a IN ();
263 }
264 } {}
265 do_test in-7.7 {
266 execsql {
267 SELECT a FROM ta WHERE a NOT IN ();
268 }
269 } {1 2 3 4 6 8 10}
271 do_test in-8.1 {
272 execsql {
273 SELECT b FROM t1 WHERE a IN ('hello','there')
274 }
275 } {world}
276 do_test in-8.2 {
277 execsql {
278 SELECT b FROM t1 WHERE a IN ("hello",'there')
279 }
280 } {world}
282 # Test constructs of the form: expr IN tablename
283 #
284 do_test in-9.1 {
285 execsql {
286 CREATE TABLE t4 AS SELECT a FROM tb;
287 SELECT * FROM t4;
288 }
289 } {1 2 3 5 7 9 11}
290 do_test in-9.2 {
291 execsql {
292 SELECT b FROM t1 WHERE a IN t4;
293 }
294 } {32 128}
295 do_test in-9.3 {
296 execsql {
297 SELECT b FROM t1 WHERE a NOT IN t4;
298 }
299 } {64 256 world}
300 do_test in-9.4 {
301 catchsql {
302 SELECT b FROM t1 WHERE a NOT IN tb;
303 }
304 } {1 {only a single result allowed for a SELECT that is part of an expression}}
306 finish_test