wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.22 2008/08/04 03:51:24 danielk1977 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