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