1: # 2001 September 15
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the IN and BETWEEN operator.
13: #
14: # $Id: in.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: # Generate the test data we will need for the first squences of tests.
20: #
21: do_test in-1.0 {
22: execsql {
23: BEGIN;
24: CREATE TABLE t1(a int, b int);
25: }
26: for {set i 1} {$i<=10} {incr i} {
27: execsql "INSERT INTO t1 VALUES($i,[expr {1<<$i}])"
28: }
29: execsql {
30: COMMIT;
31: SELECT count(*) FROM t1;
32: }
33: } {10}
34:
35: # Do basic testing of BETWEEN.
36: #
37: do_test in-1.1 {
38: execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
39: } {4 5}
40: do_test in-1.2 {
41: execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}
42: } {1 2 3 6 7 8 9 10}
43: do_test in-1.3 {
44: execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}
45: } {1 2 3 4}
46: do_test in-1.4 {
47: execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}
48: } {5 6 7 8 9 10}
49: do_test in-1.6 {
50: execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
51: } {1 2 3 4 9}
52: do_test in-1.7 {
53: execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
54: } {101 102 103 4 5 6 7 8 9 10}
55:
56: # The rest of this file concentrates on testing the IN operator.
57: # Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY
58: # (because the IN operator is unavailable).
59: #
60: ifcapable !subquery {
61: finish_test
62: return
63: }
64:
65: # Testing of the IN operator using static lists on the right-hand side.
66: #
67: do_test in-2.1 {
68: execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
69: } {3 4 5}
70: do_test in-2.2 {
71: execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}
72: } {1 2 6 7 8 9 10}
73: do_test in-2.3 {
74: execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}
75: } {3 4 5 9}
76: do_test in-2.4 {
77: execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
78: } {1 2 6 7 8 9 10}
79: do_test in-2.5 {
80: execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
81: } {1 2 103 104 5 6 7 8 9 10}
82:
83: do_test in-2.6 {
84: execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}
85: } {6}
86: do_test in-2.7 {
87: execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}
88: } {4 5 6 7 8 9 10}
89: do_test in-2.8 {
90: execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
91: } {4 5}
92: do_test in-2.9 {
93: execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}
94: } {}
95: do_test in-2.10 {
96: execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}
97: } {}
98: do_test in-2.11 {
99: set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
100: lappend v $msg
101: } {1 {no such column: c}}
102:
103: # Testing the IN operator where the right-hand side is a SELECT
104: #
105: do_test in-3.1 {
106: execsql {
107: SELECT a FROM t1
108: WHERE b IN (SELECT b FROM t1 WHERE a<5)
109: ORDER BY a
110: }
111: } {1 2 3 4}
112: do_test in-3.2 {
113: execsql {
114: SELECT a FROM t1
115: WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512
116: ORDER BY a
117: }
118: } {1 2 3 4 9}
119: do_test in-3.3 {
120: execsql {
121: SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b
122: }
123: } {101 102 103 104 5 6 7 8 9 10}
124:
125: # Make sure the UPDATE and DELETE commands work with IN-SELECT
126: #
127: do_test in-4.1 {
128: execsql {
129: UPDATE t1 SET b=b*2
130: WHERE b IN (SELECT b FROM t1 WHERE a>8)
131: }
132: execsql {SELECT b FROM t1 ORDER BY b}
133: } {2 4 8 16 32 64 128 256 1024 2048}
134: do_test in-4.2 {
135: execsql {
136: DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
137: }
138: execsql {SELECT a FROM t1 ORDER BY a}
139: } {1 2 3 4 5 6 7 8}
140: do_test in-4.3 {
141: execsql {
142: DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
143: }
144: execsql {SELECT a FROM t1 ORDER BY a}
145: } {5 6 7 8}
146:
147: # Do an IN with a constant RHS but where the RHS has many, many
148: # elements. We need to test that collisions in the hash table
149: # are resolved properly.
150: #
151: do_test in-5.1 {
152: execsql {
153: INSERT INTO t1 VALUES('hello', 'world');
154: SELECT * FROM t1
155: WHERE a IN (
156: 'Do','an','IN','with','a','constant','RHS','but','where','the',
157: 'has','many','elements','We','need','to','test','that',
158: 'collisions','hash','table','are','resolved','properly',
159: 'This','in-set','contains','thirty','one','entries','hello');
160: }
161: } {hello world}
162:
163: # Make sure the IN operator works with INTEGER PRIMARY KEY fields.
164: #
165: do_test in-6.1 {
166: execsql {
167: CREATE TABLE ta(a INTEGER PRIMARY KEY, b);
168: INSERT INTO ta VALUES(1,1);
169: INSERT INTO ta VALUES(2,2);
170: INSERT INTO ta VALUES(3,3);
171: INSERT INTO ta VALUES(4,4);
172: INSERT INTO ta VALUES(6,6);
173: INSERT INTO ta VALUES(8,8);
174: INSERT INTO ta VALUES(10,
175: 'This is a key that is long enough to require a malloc in the VDBE');
176: SELECT * FROM ta WHERE a<10;
177: }
178: } {1 1 2 2 3 3 4 4 6 6 8 8}
179: do_test in-6.2 {
180: execsql {
181: CREATE TABLE tb(a INTEGER PRIMARY KEY, b);
182: INSERT INTO tb VALUES(1,1);
183: INSERT INTO tb VALUES(2,2);
184: INSERT INTO tb VALUES(3,3);
185: INSERT INTO tb VALUES(5,5);
186: INSERT INTO tb VALUES(7,7);
187: INSERT INTO tb VALUES(9,9);
188: INSERT INTO tb VALUES(11,
189: 'This is a key that is long enough to require a malloc in the VDBE');
190: SELECT * FROM tb WHERE a<10;
191: }
192: } {1 1 2 2 3 3 5 5 7 7 9 9}
193: do_test in-6.3 {
194: execsql {
195: SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
196: }
197: } {1 2 3}
198: do_test in-6.4 {
199: execsql {
200: SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);
201: }
202: } {4 6 8 10}
203: do_test in-6.5 {
204: execsql {
205: SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
206: }
207: } {1 2 3 10}
208: do_test in-6.6 {
209: execsql {
210: SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
211: }
212: } {4 6 8}
213: do_test in-6.7 {
214: execsql {
215: SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
216: }
217: } {1 2 3}
218: do_test in-6.8 {
219: execsql {
220: SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);
221: }
222: } {4 6 8 10}
223: do_test in-6.9 {
224: execsql {
225: SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
226: }
227: } {1 2 3}
228: do_test in-6.10 {
229: execsql {
230: SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);
231: }
232: } {4 6 8 10}
233:
234: # Tests of IN operator against empty sets. (Ticket #185)
235: #
236: do_test in-7.1 {
237: execsql {
238: SELECT a FROM t1 WHERE a IN ();
239: }
240: } {}
241: do_test in-7.2 {
242: execsql {
243: SELECT a FROM t1 WHERE a IN (5);
244: }
245: } {5}
246: do_test in-7.3 {
247: execsql {
248: SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;
249: }
250: } {5 6 7 8 hello}
251: do_test in-7.4 {
252: execsql {
253: SELECT a FROM t1 WHERE a IN (5) AND b IN ();
254: }
255: } {}
256: do_test in-7.5 {
257: execsql {
258: SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
259: }
260: } {5}
261: do_test in-7.6 {
262: execsql {
263: SELECT a FROM ta WHERE a IN ();
264: }
265: } {}
266: do_test in-7.7 {
267: execsql {
268: SELECT a FROM ta WHERE a NOT IN ();
269: }
270: } {1 2 3 4 6 8 10}
271:
272: do_test in-8.1 {
273: execsql {
274: SELECT b FROM t1 WHERE a IN ('hello','there')
275: }
276: } {world}
277: do_test in-8.2 {
278: execsql {
279: SELECT b FROM t1 WHERE a IN ("hello",'there')
280: }
281: } {world}
282:
283: # Test constructs of the form: expr IN tablename
284: #
285: do_test in-9.1 {
286: execsql {
287: CREATE TABLE t4 AS SELECT a FROM tb;
288: SELECT * FROM t4;
289: }
290: } {1 2 3 5 7 9 11}
291: do_test in-9.2 {
292: execsql {
293: SELECT b FROM t1 WHERE a IN t4;
294: }
295: } {32 128}
296: do_test in-9.3 {
297: execsql {
298: SELECT b FROM t1 WHERE a NOT IN t4;
299: }
300: } {64 256 world}
301: do_test in-9.4 {
302: catchsql {
303: SELECT b FROM t1 WHERE a NOT IN tb;
304: }
305: } {1 {only a single result allowed for a SELECT that is part of an expression}}
306:
307: # IN clauses in CHECK constraints. Ticket #1645
308: #
309: do_test in-10.1 {
310: execsql {
311: CREATE TABLE t5(
312: a INTEGER,
313: CHECK( a IN (111,222,333) )
314: );
315: INSERT INTO t5 VALUES(111);
316: SELECT * FROM t5;
317: }
318: } {111}
319: do_test in-10.2 {
320: catchsql {
321: INSERT INTO t5 VALUES(4);
322: }
323: } {1 {constraint failed}}
324:
325: # Ticket #1821
326: #
327: # Type affinity applied to the right-hand side of an IN operator.
328: #
329: do_test in-11.1 {
330: execsql {
331: CREATE TABLE t6(a,b NUMERIC);
332: INSERT INTO t6 VALUES(1,2);
333: INSERT INTO t6 VALUES(2,3);
334: SELECT * FROM t6 WHERE b IN (2);
335: }
336: } {1 2}
337: do_test in-11.2 {
338: # The '2' should be coerced into 2 because t6.b is NUMERIC
339: execsql {
340: SELECT * FROM t6 WHERE b IN ('2');
341: }
342: } {1 2}
343: do_test in-11.3 {
344: # No coercion should occur here because of the unary + before b.
345: execsql {
346: SELECT * FROM t6 WHERE +b IN ('2');
347: }
348: } {}
349: do_test in-11.4 {
350: # No coercion because column a as affinity NONE
351: execsql {
352: SELECT * FROM t6 WHERE a IN ('2');
353: }
354: } {}
355: do_test in-11.5 {
356: execsql {
357: SELECT * FROM t6 WHERE a IN (2);
358: }
359: } {2 3}
360: do_test in-11.6 {
361: # No coercion because column a as affinity NONE
362: execsql {
363: SELECT * FROM t6 WHERE +a IN ('2');
364: }
365: } {}
366:
367: # Test error conditions with expressions of the form IN(<compound select>).
368: #
369: ifcapable compound {
370: do_test in-12.1 {
371: execsql {
372: CREATE TABLE t2(a, b, c);
373: CREATE TABLE t3(a, b, c);
374: }
375: } {}
376: do_test in-12.2 {
377: catchsql {
378: SELECT * FROM t2 WHERE a IN (
379: SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
380: );
381: }
382: } {1 {only a single result allowed for a SELECT that is part of an expression}}
383: do_test in-12.3 {
384: catchsql {
385: SELECT * FROM t2 WHERE a IN (
386: SELECT a, b FROM t3 UNION SELECT a, b FROM t2
387: );
388: }
389: } {1 {only a single result allowed for a SELECT that is part of an expression}}
390: do_test in-12.4 {
391: catchsql {
392: SELECT * FROM t2 WHERE a IN (
393: SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2
394: );
395: }
396: } {1 {only a single result allowed for a SELECT that is part of an expression}}
397: do_test in-12.5 {
398: catchsql {
399: SELECT * FROM t2 WHERE a IN (
400: SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
401: );
402: }
403: } {1 {only a single result allowed for a SELECT that is part of an expression}}
404: do_test in-12.6 {
405: catchsql {
406: SELECT * FROM t2 WHERE a IN (
407: SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
408: );
409: }
410: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
411: do_test in-12.7 {
412: catchsql {
413: SELECT * FROM t2 WHERE a IN (
414: SELECT a, b FROM t3 UNION SELECT a FROM t2
415: );
416: }
417: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
418: do_test in-12.8 {
419: catchsql {
420: SELECT * FROM t2 WHERE a IN (
421: SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
422: );
423: }
424: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
425: do_test in-12.9 {
426: catchsql {
427: SELECT * FROM t2 WHERE a IN (
428: SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
429: );
430: }
431: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
432: }
433:
434: do_test in-12.10 {
435: catchsql {
436: SELECT * FROM t2 WHERE a IN (
437: SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
438: );
439: }
440: } {1 {only a single result allowed for a SELECT that is part of an expression}}
441: do_test in-12.11 {
442: catchsql {
443: SELECT * FROM t2 WHERE a IN (
444: SELECT a FROM t3 UNION SELECT a, b FROM t2
445: );
446: }
447: } {1 {only a single result allowed for a SELECT that is part of an expression}}
448: do_test in-12.12 {
449: catchsql {
450: SELECT * FROM t2 WHERE a IN (
451: SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
452: );
453: }
454: } {1 {only a single result allowed for a SELECT that is part of an expression}}
455: do_test in-12.13 {
456: catchsql {
457: SELECT * FROM t2 WHERE a IN (
458: SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
459: );
460: }
461: } {1 {only a single result allowed for a SELECT that is part of an expression}}
462:
463:
464: #------------------------------------------------------------------------
465: # The following tests check that NULL is handled correctly when it
466: # appears as part of a set of values on the right-hand side of an
467: # IN or NOT IN operator.
468: #
469: # When it appears in such a set, NULL is handled as an "unknown value".
470: # If, because of the unknown value in the set, the result of the expression
471: # cannot be determined, then it itself evaluates to NULL.
472: #
473:
474: # Warm body test to demonstrate the principles being tested:
475: #
476: do_test in-13.1 {
477: db nullvalue "null"
478: execsql { SELECT
479: 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true.
480: 3 IN (NULL, 1, 2), -- Ambiguous, return NULL.
481: 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
482: 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL.
483: }
484: } {1 null 0 null}
485:
486: do_test in-13.2 {
487: execsql {
488: CREATE TABLE t7(a, b, c NOT NULL);
489: INSERT INTO t7 VALUES(1, 1, 1);
490: INSERT INTO t7 VALUES(2, 2, 2);
491: INSERT INTO t7 VALUES(3, 3, 3);
492: INSERT INTO t7 VALUES(NULL, 4, 4);
493: INSERT INTO t7 VALUES(NULL, 5, 5);
494: }
495: } {}
496:
497: do_test in-13.3 {
498: execsql { SELECT 2 IN (SELECT a FROM t7) }
499: } {1}
500: do_test in-13.4 {
501: execsql { SELECT 6 IN (SELECT a FROM t7) }
502: } {null}
503:
504: do_test in-13.5 {
505: execsql { SELECT 2 IN (SELECT b FROM t7) }
506: } {1}
507: do_test in-13.6 {
508: execsql { SELECT 6 IN (SELECT b FROM t7) }
509: } {0}
510:
511: do_test in-13.7 {
512: execsql { SELECT 2 IN (SELECT c FROM t7) }
513: } {1}
514: do_test in-13.8 {
515: execsql { SELECT 6 IN (SELECT c FROM t7) }
516: } {0}
517:
518: do_test in-13.9 {
519: execsql {
520: SELECT
521: 2 NOT IN (SELECT a FROM t7),
522: 6 NOT IN (SELECT a FROM t7),
523: 2 NOT IN (SELECT b FROM t7),
524: 6 NOT IN (SELECT b FROM t7),
525: 2 NOT IN (SELECT c FROM t7),
526: 6 NOT IN (SELECT c FROM t7)
527: }
528: } {0 null 0 1 0 1}
529:
530: do_test in-13.10 {
531: execsql {
532: SELECT b IN (
533: SELECT inside.a
534: FROM t7 AS inside
535: WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
536: )
537: FROM t7 AS outside ORDER BY b;
538: }
539: } {0 null null null 0}
540:
541: do_test in-13.11 {
542: execsql {
543: SELECT b NOT IN (
544: SELECT inside.a
545: FROM t7 AS inside
546: WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
547: )
548: FROM t7 AS outside ORDER BY b;
549: }
550: } {1 null null null 1}
551:
552: do_test in-13.12 {
553: execsql {
554: CREATE INDEX i1 ON t7(a);
555: CREATE INDEX i2 ON t7(b);
556: CREATE INDEX i3 ON t7(c);
557: }
558: execsql {
559: SELECT
560: 2 IN (SELECT a FROM t7),
561: 6 IN (SELECT a FROM t7),
562: 2 IN (SELECT b FROM t7),
563: 6 IN (SELECT b FROM t7),
564: 2 IN (SELECT c FROM t7),
565: 6 IN (SELECT c FROM t7)
566: }
567: } {1 null 1 0 1 0}
568:
569: do_test in-13.13 {
570: execsql {
571: SELECT
572: 2 NOT IN (SELECT a FROM t7),
573: 6 NOT IN (SELECT a FROM t7),
574: 2 NOT IN (SELECT b FROM t7),
575: 6 NOT IN (SELECT b FROM t7),
576: 2 NOT IN (SELECT c FROM t7),
577: 6 NOT IN (SELECT c FROM t7)
578: }
579: } {0 null 0 1 0 1}
580:
581: do_test in-13.14 {
582: execsql {
583: BEGIN TRANSACTION;
584: CREATE TABLE a(id INTEGER);
585: INSERT INTO a VALUES(1);
586: INSERT INTO a VALUES(2);
587: INSERT INTO a VALUES(3);
588: CREATE TABLE b(id INTEGER);
589: INSERT INTO b VALUES(NULL);
590: INSERT INTO b VALUES(3);
591: INSERT INTO b VALUES(4);
592: INSERT INTO b VALUES(5);
593: COMMIT;
594: SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
595: }
596: } {}
597: do_test in-13.14 {
598: execsql {
599: CREATE INDEX i5 ON b(id);
600: SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
601: }
602: } {}
603:
604:
605: do_test in-13.X {
606: db nullvalue ""
607: } {}
608:
609: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to in.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test