wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |