Debian Codegraph @

test/in.test

raw
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.1 {
262 execsql {
263 SELECT a FROM ta WHERE a IN ();
264 }
265 } {}
266 do_test in-7.6.2 {
267 db status step
268 } {0}
269 do_test in-7.7 {
270 execsql {
271 SELECT a FROM ta WHERE a NOT IN ();
272 }
273 } {1 2 3 4 6 8 10}
274
275 do_test in-7.8.1 {
276 execsql {
277 SELECT * FROM ta LEFT JOIN tb ON (ta.b=tb.b) WHERE ta.a IN ();
278 }
279 } {}
280 do_test in-7.8.2 {
281 db status step
282 } {0}
283
284 do_test in-8.3 {
285 execsql {
286 SELECT b FROM t1 WHERE a IN ('hello','there')
287 }
288 } {world}
289 do_test in-8.4 {
290 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
291 execsql {
292 SELECT b FROM t1 WHERE a IN ("hello",'there')
293 }
294 } {world}
295
296 # Test constructs of the form: expr IN tablename
297 #
298 do_test in-9.1 {
299 execsql {
300 CREATE TABLE t4 AS SELECT a FROM tb;
301 SELECT * FROM t4;
302 }
303 } {1 2 3 5 7 9 11}
304 do_test in-9.2 {
305 execsql {
306 SELECT b FROM t1 WHERE a IN t4;
307 }
308 } {32 128}
309 do_test in-9.3 {
310 execsql {
311 SELECT b FROM t1 WHERE a NOT IN t4;
312 }
313 } {64 256 world}
314 do_test in-9.4 {
315 catchsql {
316 SELECT b FROM t1 WHERE a NOT IN tb;
317 }
318 } {1 {sub-select returns 2 columns - expected 1}}
319
320 # IN clauses in CHECK constraints. Ticket #1645
321 #
322 do_test in-10.1 {
323 execsql {
324 CREATE TABLE t5(
325 a INTEGER,
326 CHECK( a IN (111,222,333) )
327 );
328 INSERT INTO t5 VALUES(111);
329 SELECT * FROM t5;
330 }
331 } {111}
332 do_test in-10.2 {
333 catchsql {
334 INSERT INTO t5 VALUES(4);
335 }
336 } {1 {CHECK constraint failed: a IN (111,222,333)}}
337
338 # Ticket #1821
339 #
340 # Type affinity applied to the right-hand side of an IN operator.
341 #
342 do_test in-11.1 {
343 execsql {
344 CREATE TABLE t6(a,b NUMERIC);
345 INSERT INTO t6 VALUES(1,2);
346 INSERT INTO t6 VALUES(2,3);
347 SELECT * FROM t6 WHERE b IN (2);
348 }
349 } {1 2}
350 do_test in-11.2 {
351 # The '2' should be coerced into 2 because t6.b is NUMERIC
352 execsql {
353 SELECT * FROM t6 WHERE b IN ('2');
354 }
355 } {1 2}
356 do_test in-11.3 {
357 # No coercion should occur here because of the unary + before b.
358 execsql {
359 SELECT * FROM t6 WHERE +b IN ('2');
360 }
361 } {}
362 do_test in-11.4 {
363 # No coercion because column a as affinity NONE
364 execsql {
365 SELECT * FROM t6 WHERE a IN ('2');
366 }
367 } {}
368 do_test in-11.5 {
369 execsql {
370 SELECT * FROM t6 WHERE a IN (2);
371 }
372 } {2 3}
373 do_test in-11.6 {
374 # No coercion because column a as affinity NONE
375 execsql {
376 SELECT * FROM t6 WHERE +a IN ('2');
377 }
378 } {}
379
380 # Test error conditions with expressions of the form IN(<compound select>).
381 #
382 ifcapable compound {
383 do_test in-12.1 {
384 execsql {
385 CREATE TABLE t2(a, b, c);
386 CREATE TABLE t3(a, b, c);
387 }
388 } {}
389 do_test in-12.2 {
390 catchsql {
391 SELECT * FROM t2 WHERE a IN (
392 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
393 );
394 }
395 } {1 {sub-select returns 2 columns - expected 1}}
396 do_test in-12.3 {
397 catchsql {
398 SELECT * FROM t2 WHERE a IN (
399 SELECT a, b FROM t3 UNION SELECT a, b FROM t2
400 );
401 }
402 } {1 {sub-select returns 2 columns - expected 1}}
403 do_test in-12.4 {
404 catchsql {
405 SELECT * FROM t2 WHERE a IN (
406 SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2
407 );
408 }
409 } {1 {sub-select returns 2 columns - expected 1}}
410 do_test in-12.5 {
411 catchsql {
412 SELECT * FROM t2 WHERE a IN (
413 SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
414 );
415 }
416 } {1 {sub-select returns 2 columns - expected 1}}
417 do_test in-12.6 {
418 catchsql {
419 SELECT * FROM t2 WHERE a IN (
420 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
421 );
422 }
423 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
424 do_test in-12.7 {
425 catchsql {
426 SELECT * FROM t2 WHERE a IN (
427 SELECT a, b FROM t3 UNION SELECT a FROM t2
428 );
429 }
430 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
431 do_test in-12.8 {
432 catchsql {
433 SELECT * FROM t2 WHERE a IN (
434 SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
435 );
436 }
437 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
438 do_test in-12.9 {
439 catchsql {
440 SELECT * FROM t2 WHERE a IN (
441 SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
442 );
443 }
444 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
445 }
446
447 ifcapable compound {
448 do_test in-12.10 {
449 catchsql {
450 SELECT * FROM t2 WHERE a IN (
451 SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
452 );
453 }
454 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
455 do_test in-12.11 {
456 catchsql {
457 SELECT * FROM t2 WHERE a IN (
458 SELECT a FROM t3 UNION SELECT a, b FROM t2
459 );
460 }
461 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
462 do_test in-12.12 {
463 catchsql {
464 SELECT * FROM t2 WHERE a IN (
465 SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
466 );
467 }
468 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
469 do_test in-12.13 {
470 catchsql {
471 SELECT * FROM t2 WHERE a IN (
472 SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
473 );
474 }
475 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
476 do_test in-12.14 {
477 catchsql {
478 SELECT * FROM t2 WHERE a IN (
479 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
480 );
481 }
482 } {1 {sub-select returns 2 columns - expected 1}}
483 do_test in-12.15 {
484 catchsql {
485 SELECT * FROM t2 WHERE a IN (
486 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
487 );
488 }
489 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
490 }; #ifcapable compound
491
492
493 #------------------------------------------------------------------------
494 # The following tests check that NULL is handled correctly when it
495 # appears as part of a set of values on the right-hand side of an
496 # IN or NOT IN operator.
497 #
498 # When it appears in such a set, NULL is handled as an "unknown value".
499 # If, because of the unknown value in the set, the result of the expression
500 # cannot be determined, then it itself evaluates to NULL.
501 #
502
503 # Warm body test to demonstrate the principles being tested:
504 #
505 do_test in-13.1 {
506 db nullvalue "null"
507 execsql { SELECT
508 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true.
509 3 IN (NULL, 1, 2), -- Ambiguous, return NULL.
510 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
511 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL.
512 }
513 } {1 null 0 null}
514
515 do_test in-13.2 {
516 execsql {
517 CREATE TABLE t7(a, b, c NOT NULL);
518 INSERT INTO t7 VALUES(1, 1, 1);
519 INSERT INTO t7 VALUES(2, 2, 2);
520 INSERT INTO t7 VALUES(3, 3, 3);
521 INSERT INTO t7 VALUES(NULL, 4, 4);
522 INSERT INTO t7 VALUES(NULL, 5, 5);
523 }
524 } {}
525
526 do_test in-13.3 {
527 execsql { SELECT 2 IN (SELECT a FROM t7) }
528 } {1}
529 do_test in-13.4 {
530 execsql { SELECT 6 IN (SELECT a FROM t7) }
531 } {null}
532
533 do_test in-13.5 {
534 execsql { SELECT 2 IN (SELECT b FROM t7) }
535 } {1}
536 do_test in-13.6 {
537 execsql { SELECT 6 IN (SELECT b FROM t7) }
538 } {0}
539
540 do_test in-13.7 {
541 execsql { SELECT 2 IN (SELECT c FROM t7) }
542 } {1}
543 do_test in-13.8 {
544 execsql { SELECT 6 IN (SELECT c FROM t7) }
545 } {0}
546
547 do_test in-13.9 {
548 execsql {
549 SELECT
550 2 NOT IN (SELECT a FROM t7),
551 6 NOT IN (SELECT a FROM t7),
552 2 NOT IN (SELECT b FROM t7),
553 6 NOT IN (SELECT b FROM t7),
554 2 NOT IN (SELECT c FROM t7),
555 6 NOT IN (SELECT c FROM t7)
556 }
557 } {0 null 0 1 0 1}
558
559 do_test in-13.10 {
560 execsql {
561 SELECT b IN (
562 SELECT inside.a
563 FROM t7 AS inside
564 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
565 )
566 FROM t7 AS outside ORDER BY b;
567 }
568 } {0 null null null 0}
569
570 do_test in-13.11 {
571 execsql {
572 SELECT b NOT IN (
573 SELECT inside.a
574 FROM t7 AS inside
575 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
576 )
577 FROM t7 AS outside ORDER BY b;
578 }
579 } {1 null null null 1}
580
581 do_test in-13.12 {
582 execsql {
583 CREATE INDEX i1 ON t7(a);
584 CREATE INDEX i2 ON t7(b);
585 CREATE INDEX i3 ON t7(c);
586 }
587 execsql {
588 SELECT
589 2 IN (SELECT a FROM t7),
590 6 IN (SELECT a FROM t7),
591 2 IN (SELECT b FROM t7),
592 6 IN (SELECT b FROM t7),
593 2 IN (SELECT c FROM t7),
594 6 IN (SELECT c FROM t7)
595 }
596 } {1 null 1 0 1 0}
597
598 do_test in-13.13 {
599 execsql {
600 SELECT
601 2 NOT IN (SELECT a FROM t7),
602 6 NOT IN (SELECT a FROM t7),
603 2 NOT IN (SELECT b FROM t7),
604 6 NOT IN (SELECT b FROM t7),
605 2 NOT IN (SELECT c FROM t7),
606 6 NOT IN (SELECT c FROM t7)
607 }
608 } {0 null 0 1 0 1}
609
610 do_test in-13.14 {
611 execsql {
612 BEGIN TRANSACTION;
613 CREATE TABLE a(id INTEGER);
614 INSERT INTO a VALUES(1);
615 INSERT INTO a VALUES(2);
616 INSERT INTO a VALUES(3);
617 CREATE TABLE b(id INTEGER);
618 INSERT INTO b VALUES(NULL);
619 INSERT INTO b VALUES(3);
620 INSERT INTO b VALUES(4);
621 INSERT INTO b VALUES(5);
622 COMMIT;
623 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
624 }
625 } {}
626 do_test in-13.14 {
627 execsql {
628 CREATE INDEX i5 ON b(id);
629 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
630 }
631 } {}
632
633 do_test in-13.15 {
634 catchsql {
635 SELECT 0 WHERE (SELECT 0,0) OR (0 IN (1,2));
636 }
637 } {1 {sub-select returns 2 columns - expected 1}}
638
639
640 do_test in-13.X {
641 db nullvalue ""
642 } {}
643
644 # At one point the following was causing valgrind to report a "jump
645 # depends on unitialized location" problem.
646 #
647 do_execsql_test in-14.0 {
648 CREATE TABLE c1(a);
649 INSERT INTO c1 VALUES(1), (2), (4), (3);
650 }
651 do_execsql_test in-14.1 {
652 SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
653 } {1 2 3 4}
654
655 # 2019-02-20 Ticket https://sqlite.org/src/tktview/df46dfb631f75694fbb97033b69
656 #
657 do_execsql_test in-15.0 {
658 DROP TABLE IF EXISTS t1;
659 CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);
660 INSERT INTO t1 VALUES(1);
661 SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
662 } {1}
663 do_execsql_test in-15.1 {
664 DROP TABLE IF EXISTS t2;
665 CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
666 INSERT INTO t2 VALUES(1,11);
667 INSERT INTO t2 VALUES(2,22);
668 INSERT INTO t2 VALUES(3,33);
669 SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
670 } {11 0 22 0 33 1}
671 do_execsql_test in-15.2 {
672 DROP TABLE IF EXISTS t3;
673 CREATE TABLE t3(x INTEGER PRIMARY KEY);
674 INSERT INTO t3 VALUES(8);
675 SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;
676 SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;
677 } {yes no}
678 do_execsql_test in-15.3 {
679 SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
680 SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
681 } {yes no}
682 do_execsql_test in-15.4 {
683 DROP TABLE IF EXISTS t4;
684 CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
685 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
686 INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
687 SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
688 } {103 108}
689 do_execsql_test in-15.5 {
690 SELECT b FROM t4 WHERE a NOT IN (3,null,8);
691 } {}
692 do_execsql_test in-15.6 {
693 DROP TABLE IF EXISTS t5;
694 DROP TABLE IF EXISTS t6;
695 CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT);
696 CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT);
697 INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma');
698 INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2);
699 SELECT a.*
700 FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id
701 WHERE b.id IN (
702 SELECT t6.t5_id
703 FROM t6
704 WHERE name='Bob'
705 AND t6.t5_id IS NOT NULL
706 AND t6.id IN (
707 SELECT id
708 FROM (SELECT t6.id, count(*) AS x
709 FROM t6
710 WHERE name='Bob'
711 ) AS 't'
712 WHERE x=1
713 )
714 AND t6.id IN (1,id)
715 );
716 } {1 Alice}
717
718 #-------------------------------------------------------------------------
719 reset_db
720 do_execsql_test in-16.0 {
721 CREATE TABLE x1(a, b);
722 INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);
723 CREATE INDEX x1i ON x1(a, b);
724 }
725
726 do_execsql_test in-16.1 {
727 SELECT * FROM x1
728 WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0)
729 ORDER BY a DESC, b;
730 } {6 {} 4 {} 2 {}}
731
732 do_execsql_test in-16.2 {
733 SELECT * FROM x1
734 WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0)
735 ORDER BY a DESC, b;
736 } {}
737
738 # 2019-06-11
739 # https://sqlite.org/src/info/57353f8243c637c0
740 #
741 do_execsql_test in-17.1 {
742 SELECT 1 IN ('1');
743 } 0
744 do_execsql_test in-17.2 {
745 SELECT 1 IN ('1' COLLATE nocase);
746 } 0
747 do_execsql_test in-17.3 {
748 SELECT 1 IN (CAST('1' AS text));
749 } 0
750 do_execsql_test in-17.4 {
751 SELECT 1 IN (CAST('1' AS text) COLLATE nocase);
752 } 0
753
754 # 2019-08-27 ticket https://sqlite.org/src/info/dbaf8a6820be1ece
755 #
756 do_execsql_test in-18.1 {
757 DROP TABLE IF EXISTS t0;
758 CREATE TABLE t0(c0 INT UNIQUE);
759 INSERT INTO t0(c0) VALUES (1);
760 SELECT * FROM t0 WHERE '1' IN (t0.c0);
761 } {}
762
763 # 2019-09-02 ticket https://sqlite.org/src/info/2841e99d104c6436
764 # For the IN_INDEX_NOOP optimization, apply REAL affinity to the LHS
765 # values prior to comparison if the RHS has REAL affinity.
766 #
767 # Also ticket https://sqlite.org/src/info/29f635e0af71234b
768 #
769 do_execsql_test in-19.10 {
770 DROP TABLE IF EXISTS t0;
771 CREATE TABLE t0(c0 REAL UNIQUE);
772 INSERT INTO t0(c0) VALUES(2.0625E00);
773 SELECT 1 FROM t0 WHERE c0 IN ('2.0625');
774 } {1}
775 do_execsql_test in-19.20 {
776 SELECT c0 IN ('2.0625') FROM t0;
777 } {1}
778 do_execsql_test in-19.21 {
779 SELECT c0 = ('2.0625') FROM t0;
780 } {1}
781 do_execsql_test in-19.22 {
782 SELECT c0 = ('0.20625e+01') FROM t0;
783 } {1}
784 do_execsql_test in-19.30 {
785 SELECT c0 IN ('2.0625',2,3) FROM t0;
786 } {1}
787 do_execsql_test in-19.40 {
788 DROP TABLE t0;
789 CREATE TABLE t0(c0 TEXT, c1 REAL, c2, PRIMARY KEY(c2, c0, c1));
790 CREATE INDEX i0 ON t0(c1 IN (c0));
791 INSERT INTO t0(c0, c2) VALUES (0, NULL) ON CONFLICT(c2, c1, c0) DO NOTHING;
792 PRAGMA integrity_check;
793 } {ok}
794
795 # Ticket f3ff1472887
796 #
797 do_execsql_test in-20.1 {
798 SELECT (1 IN (2 IS TRUE));
799 } {1}
800
801 # Forum post: https://sqlite.org/forum/forumpost/5782619992.
802 #
803 reset_db
804 do_execsql_test in-21.1 {
805 CREATE TABLE t0(c0);
806 SELECT COUNT(*) FROM t0 ORDER BY (t0.c0 IN ());
807 } {0}
808
809 # Ignore extra parentheses around a subquery on the RHS of an IN operator,
810 # because that is what PostgreSQL does.
811 #
812 do_execsql_test in-22.1 {
813 DROP TABLE IF EXISTS t1;
814 CREATE TABLE t1(x INT PRIMARY KEY, y INT);
815 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<8)
816 INSERT INTO t1(x,y) SELECT x, x*100 FROM c;
817 DROP TABLE IF EXISTS t2;
818 CREATE TABLE t2(a INT);
819 INSERT INTO t2 VALUES(2),(4),(6);
820 SELECT * FROM t1 WHERE x IN (SELECT a FROM t2);
821 } {2 200 4 400 6 600}
822 do_execsql_test in-22.2 {
823 SELECT * FROM t1 WHERE x IN ((SELECT a FROM t2));
824 } {2 200 4 400 6 600}
825 do_execsql_test in-22.3 {
826 SELECT * FROM t1 WHERE x IN (((SELECT a FROM t2)));
827 } {2 200 4 400 6 600}
828 do_execsql_test in-22.4 {
829 SELECT * FROM t1 WHERE x IN ((((((SELECT a FROM t2))))));
830 } {2 200 4 400 6 600}
831
832 # 2023-04-04 https://sqlite.org/forum/forumpost/dc16ec63d3
833 # Faulty assert() statement in the IN optimization.
834 #
835 do_execsql_test in-23.0 {
836 DROP TABLE IF EXISTS t4;
837 CREATE TABLE t4(a TEXT, b INT);
838 INSERT INTO t4(a,b) VALUES('abc',0),('ABC',1),('def',2);
839 CREATE INDEX t4x ON t4(a, +a COLLATE NOCASE);
840 SELECT a0.a, group_concat(a1.a) AS b
841 FROM t4 AS a0 JOIN t4 AS a1
842 GROUP BY a0.a
843 HAVING (SELECT sum( (a1.a == +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
844 } {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}
845 do_execsql_test in-23.0-b {
846 SELECT a0.a, group_concat(a1.a) AS b
847 FROM t4 AS a0 JOIN t4 AS a1
848 GROUP BY a0.a
849 HAVING (SELECT sum( (a1.a GLOB +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
850 } {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}
851 #
852 # Follow-up forum/forumpost/0713a16a44
853 #
854 do_execsql_test in-23.1 {
855 CREATE VIEW t5 AS
856 SELECT 1 AS b
857 WHERE (SELECT count(0=NOT+a COLLATE NOCASE IN (SELECT 0))
858 FROM t4
859 GROUP BY a);
860 SELECT * FROM t5;
861 } 1
862
863 finish_test