xref: /titanic_44/usr/src/lib/libsqlite/test/in.test (revision 56b2bdd1f04d465cfe4a95b88ae5cba5884154e4)
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
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 $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Generate the test data we will need for the first squences of tests.
23#
24do_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}
37
38# Do basic testing of BETWEEN.
39#
40do_test in-1.1 {
41  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
42} {4 5}
43do_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}
46do_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}
49do_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}
52do_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}
55do_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}
58
59
60# Testing of the IN operator using static lists on the right-hand side.
61#
62do_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}
65do_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}
68do_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}
71do_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}
74do_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}
77
78do_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}}
82do_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}}
86do_test in-2.8 {
87  execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
88} {4 5}
89do_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}}
93do_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}}
97do_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}}
101
102# Testing the IN operator where the right-hand side is a SELECT
103#
104do_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}
111do_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}
118do_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}
123
124# Make sure the UPDATE and DELETE commands work with IN-SELECT
125#
126do_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}
133do_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}
139do_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}
145
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#
150do_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}
161
162# Make sure the IN operator works with INTEGER PRIMARY KEY fields.
163#
164do_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}
178do_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}
192do_test in-6.3 {
193  execsql {
194    SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
195  }
196} {1 2 3}
197do_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}
202do_test in-6.5 {
203  execsql {
204    SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
205  }
206} {1 2 3 10}
207do_test in-6.6 {
208  execsql {
209    SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
210  }
211} {4 6 8}
212do_test in-6.7 {
213  execsql {
214    SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
215  }
216} {1 2 3}
217do_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}
222do_test in-6.9 {
223  execsql {
224    SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
225  }
226} {1 2 3}
227do_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}
232
233# Tests of IN operator against empty sets.  (Ticket #185)
234#
235do_test in-7.1 {
236  execsql {
237    SELECT a FROM t1 WHERE a IN ();
238  }
239} {}
240do_test in-7.2 {
241  execsql {
242    SELECT a FROM t1 WHERE a IN (5);
243  }
244} {5}
245do_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}
250do_test in-7.4 {
251  execsql {
252    SELECT a FROM t1 WHERE a IN (5) AND b IN ();
253  }
254} {}
255do_test in-7.5 {
256  execsql {
257    SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
258  }
259} {5}
260do_test in-7.6 {
261  execsql {
262    SELECT a FROM ta WHERE a IN ();
263  }
264} {}
265do_test in-7.7 {
266  execsql {
267    SELECT a FROM ta WHERE a NOT IN ();
268  }
269} {1 2 3 4 6 8 10}
270
271do_test in-8.1 {
272  execsql {
273    SELECT b FROM t1 WHERE a IN ('hello','there')
274  }
275} {world}
276do_test in-8.2 {
277  execsql {
278    SELECT b FROM t1 WHERE a IN ("hello",'there')
279  }
280} {world}
281
282# Test constructs of the form:  expr IN tablename
283#
284do_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}
290do_test in-9.2 {
291  execsql {
292    SELECT b FROM t1 WHERE a IN t4;
293  }
294} {32 128}
295do_test in-9.3 {
296  execsql {
297    SELECT b FROM t1 WHERE a NOT IN t4;
298  }
299} {64 256 world}
300do_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}}
305
306finish_test
307