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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Generate the test data we will need for the first squences of tests. 20# 21do_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# 37do_test in-1.1 { 38 execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a} 39} {4 5} 40do_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} 43do_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} 46do_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} 49do_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} 52do_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# 60ifcapable !subquery { 61 finish_test 62 return 63} 64 65# Testing of the IN operator using static lists on the right-hand side. 66# 67do_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} 70do_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} 73do_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} 76do_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} 79do_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 83do_test in-2.6 { 84 execsql {SELECT a FROM t1 WHERE b IN (b+8,64)} 85} {6} 86do_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} 89do_test in-2.8 { 90 execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b} 91} {4 5} 92do_test in-2.9 { 93 execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)} 94} {} 95do_test in-2.10 { 96 execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))} 97} {} 98do_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# 105do_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} 112do_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} 119do_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# 127do_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} 134do_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} 140do_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# 151do_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# 165do_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} 179do_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} 193do_test in-6.3 { 194 execsql { 195 SELECT a FROM ta WHERE b IN (SELECT a FROM tb); 196 } 197} {1 2 3} 198do_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} 203do_test in-6.5 { 204 execsql { 205 SELECT a FROM ta WHERE b IN (SELECT b FROM tb); 206 } 207} {1 2 3 10} 208do_test in-6.6 { 209 execsql { 210 SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb); 211 } 212} {4 6 8} 213do_test in-6.7 { 214 execsql { 215 SELECT a FROM ta WHERE a IN (SELECT a FROM tb); 216 } 217} {1 2 3} 218do_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} 223do_test in-6.9 { 224 execsql { 225 SELECT a FROM ta WHERE a IN (SELECT b FROM tb); 226 } 227} {1 2 3} 228do_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# 236do_test in-7.1 { 237 execsql { 238 SELECT a FROM t1 WHERE a IN (); 239 } 240} {} 241do_test in-7.2 { 242 execsql { 243 SELECT a FROM t1 WHERE a IN (5); 244 } 245} {5} 246do_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} 251do_test in-7.4 { 252 execsql { 253 SELECT a FROM t1 WHERE a IN (5) AND b IN (); 254 } 255} {} 256do_test in-7.5 { 257 execsql { 258 SELECT a FROM t1 WHERE a IN (5) AND b NOT IN (); 259 } 260} {5} 261do_test in-7.6.1 { 262 execsql { 263 SELECT a FROM ta WHERE a IN (); 264 } 265} {} 266do_test in-7.6.2 { 267 db status step 268} {0} 269do_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 275do_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} {} 280do_test in-7.8.2 { 281 db status step 282} {0} 283 284do_test in-8.1 { 285 execsql { 286 SELECT b FROM t1 WHERE a IN ('hello','there') 287 } 288} {world} 289do_test in-8.2 { 290 execsql { 291 SELECT b FROM t1 WHERE a IN ("hello",'there') 292 } 293} {world} 294 295# Test constructs of the form: expr IN tablename 296# 297do_test in-9.1 { 298 execsql { 299 CREATE TABLE t4 AS SELECT a FROM tb; 300 SELECT * FROM t4; 301 } 302} {1 2 3 5 7 9 11} 303do_test in-9.2 { 304 execsql { 305 SELECT b FROM t1 WHERE a IN t4; 306 } 307} {32 128} 308do_test in-9.3 { 309 execsql { 310 SELECT b FROM t1 WHERE a NOT IN t4; 311 } 312} {64 256 world} 313do_test in-9.4 { 314 catchsql { 315 SELECT b FROM t1 WHERE a NOT IN tb; 316 } 317} {1 {sub-select returns 2 columns - expected 1}} 318 319# IN clauses in CHECK constraints. Ticket #1645 320# 321do_test in-10.1 { 322 execsql { 323 CREATE TABLE t5( 324 a INTEGER, 325 CHECK( a IN (111,222,333) ) 326 ); 327 INSERT INTO t5 VALUES(111); 328 SELECT * FROM t5; 329 } 330} {111} 331do_test in-10.2 { 332 catchsql { 333 INSERT INTO t5 VALUES(4); 334 } 335} {1 {CHECK constraint failed: a IN (111,222,333)}} 336 337# Ticket #1821 338# 339# Type affinity applied to the right-hand side of an IN operator. 340# 341do_test in-11.1 { 342 execsql { 343 CREATE TABLE t6(a,b NUMERIC); 344 INSERT INTO t6 VALUES(1,2); 345 INSERT INTO t6 VALUES(2,3); 346 SELECT * FROM t6 WHERE b IN (2); 347 } 348} {1 2} 349do_test in-11.2 { 350 # The '2' should be coerced into 2 because t6.b is NUMERIC 351 execsql { 352 SELECT * FROM t6 WHERE b IN ('2'); 353 } 354} {1 2} 355do_test in-11.3 { 356 # No coercion should occur here because of the unary + before b. 357 execsql { 358 SELECT * FROM t6 WHERE +b IN ('2'); 359 } 360} {} 361do_test in-11.4 { 362 # No coercion because column a as affinity NONE 363 execsql { 364 SELECT * FROM t6 WHERE a IN ('2'); 365 } 366} {} 367do_test in-11.5 { 368 execsql { 369 SELECT * FROM t6 WHERE a IN (2); 370 } 371} {2 3} 372do_test in-11.6 { 373 # No coercion because column a as affinity NONE 374 execsql { 375 SELECT * FROM t6 WHERE +a IN ('2'); 376 } 377} {} 378 379# Test error conditions with expressions of the form IN(<compound select>). 380# 381ifcapable compound { 382do_test in-12.1 { 383 execsql { 384 CREATE TABLE t2(a, b, c); 385 CREATE TABLE t3(a, b, c); 386 } 387} {} 388do_test in-12.2 { 389 catchsql { 390 SELECT * FROM t2 WHERE a IN ( 391 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2 392 ); 393 } 394} {1 {sub-select returns 2 columns - expected 1}} 395do_test in-12.3 { 396 catchsql { 397 SELECT * FROM t2 WHERE a IN ( 398 SELECT a, b FROM t3 UNION SELECT a, b FROM t2 399 ); 400 } 401} {1 {sub-select returns 2 columns - expected 1}} 402do_test in-12.4 { 403 catchsql { 404 SELECT * FROM t2 WHERE a IN ( 405 SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2 406 ); 407 } 408} {1 {sub-select returns 2 columns - expected 1}} 409do_test in-12.5 { 410 catchsql { 411 SELECT * FROM t2 WHERE a IN ( 412 SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2 413 ); 414 } 415} {1 {sub-select returns 2 columns - expected 1}} 416do_test in-12.6 { 417 catchsql { 418 SELECT * FROM t2 WHERE a IN ( 419 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 420 ); 421 } 422} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} 423do_test in-12.7 { 424 catchsql { 425 SELECT * FROM t2 WHERE a IN ( 426 SELECT a, b FROM t3 UNION SELECT a FROM t2 427 ); 428 } 429} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 430do_test in-12.8 { 431 catchsql { 432 SELECT * FROM t2 WHERE a IN ( 433 SELECT a, b FROM t3 EXCEPT SELECT a FROM t2 434 ); 435 } 436} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} 437do_test in-12.9 { 438 catchsql { 439 SELECT * FROM t2 WHERE a IN ( 440 SELECT a, b FROM t3 INTERSECT SELECT a FROM t2 441 ); 442 } 443} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} 444} 445 446ifcapable compound { 447do_test in-12.10 { 448 catchsql { 449 SELECT * FROM t2 WHERE a IN ( 450 SELECT a FROM t3 UNION ALL SELECT a, b FROM t2 451 ); 452 } 453} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} 454do_test in-12.11 { 455 catchsql { 456 SELECT * FROM t2 WHERE a IN ( 457 SELECT a FROM t3 UNION SELECT a, b FROM t2 458 ); 459 } 460} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} 461do_test in-12.12 { 462 catchsql { 463 SELECT * FROM t2 WHERE a IN ( 464 SELECT a FROM t3 EXCEPT SELECT a, b FROM t2 465 ); 466 } 467} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} 468do_test in-12.13 { 469 catchsql { 470 SELECT * FROM t2 WHERE a IN ( 471 SELECT a FROM t3 INTERSECT SELECT a, b FROM t2 472 ); 473 } 474} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} 475do_test in-12.14 { 476 catchsql { 477 SELECT * FROM t2 WHERE a IN ( 478 SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2 479 ); 480 } 481} {1 {sub-select returns 2 columns - expected 1}} 482do_test in-12.15 { 483 catchsql { 484 SELECT * FROM t2 WHERE a IN ( 485 SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 486 ); 487 } 488} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} 489}; #ifcapable compound 490 491 492#------------------------------------------------------------------------ 493# The following tests check that NULL is handled correctly when it 494# appears as part of a set of values on the right-hand side of an 495# IN or NOT IN operator. 496# 497# When it appears in such a set, NULL is handled as an "unknown value". 498# If, because of the unknown value in the set, the result of the expression 499# cannot be determined, then it itself evaluates to NULL. 500# 501 502# Warm body test to demonstrate the principles being tested: 503# 504do_test in-13.1 { 505 db nullvalue "null" 506 execsql { SELECT 507 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true. 508 3 IN (NULL, 1, 2), -- Ambiguous, return NULL. 509 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false. 510 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL. 511 } 512} {1 null 0 null} 513 514do_test in-13.2 { 515 execsql { 516 CREATE TABLE t7(a, b, c NOT NULL); 517 INSERT INTO t7 VALUES(1, 1, 1); 518 INSERT INTO t7 VALUES(2, 2, 2); 519 INSERT INTO t7 VALUES(3, 3, 3); 520 INSERT INTO t7 VALUES(NULL, 4, 4); 521 INSERT INTO t7 VALUES(NULL, 5, 5); 522 } 523} {} 524 525do_test in-13.3 { 526 execsql { SELECT 2 IN (SELECT a FROM t7) } 527} {1} 528do_test in-13.4 { 529 execsql { SELECT 6 IN (SELECT a FROM t7) } 530} {null} 531 532do_test in-13.5 { 533 execsql { SELECT 2 IN (SELECT b FROM t7) } 534} {1} 535do_test in-13.6 { 536 execsql { SELECT 6 IN (SELECT b FROM t7) } 537} {0} 538 539do_test in-13.7 { 540 execsql { SELECT 2 IN (SELECT c FROM t7) } 541} {1} 542do_test in-13.8 { 543 execsql { SELECT 6 IN (SELECT c FROM t7) } 544} {0} 545 546do_test in-13.9 { 547 execsql { 548 SELECT 549 2 NOT IN (SELECT a FROM t7), 550 6 NOT IN (SELECT a FROM t7), 551 2 NOT IN (SELECT b FROM t7), 552 6 NOT IN (SELECT b FROM t7), 553 2 NOT IN (SELECT c FROM t7), 554 6 NOT IN (SELECT c FROM t7) 555 } 556} {0 null 0 1 0 1} 557 558do_test in-13.10 { 559 execsql { 560 SELECT b IN ( 561 SELECT inside.a 562 FROM t7 AS inside 563 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2 564 ) 565 FROM t7 AS outside ORDER BY b; 566 } 567} {0 null null null 0} 568 569do_test in-13.11 { 570 execsql { 571 SELECT b NOT IN ( 572 SELECT inside.a 573 FROM t7 AS inside 574 WHERE inside.b BETWEEN outside.b+1 AND outside.b+2 575 ) 576 FROM t7 AS outside ORDER BY b; 577 } 578} {1 null null null 1} 579 580do_test in-13.12 { 581 execsql { 582 CREATE INDEX i1 ON t7(a); 583 CREATE INDEX i2 ON t7(b); 584 CREATE INDEX i3 ON t7(c); 585 } 586 execsql { 587 SELECT 588 2 IN (SELECT a FROM t7), 589 6 IN (SELECT a FROM t7), 590 2 IN (SELECT b FROM t7), 591 6 IN (SELECT b FROM t7), 592 2 IN (SELECT c FROM t7), 593 6 IN (SELECT c FROM t7) 594 } 595} {1 null 1 0 1 0} 596 597do_test in-13.13 { 598 execsql { 599 SELECT 600 2 NOT IN (SELECT a FROM t7), 601 6 NOT IN (SELECT a FROM t7), 602 2 NOT IN (SELECT b FROM t7), 603 6 NOT IN (SELECT b FROM t7), 604 2 NOT IN (SELECT c FROM t7), 605 6 NOT IN (SELECT c FROM t7) 606 } 607} {0 null 0 1 0 1} 608 609do_test in-13.14 { 610 execsql { 611 BEGIN TRANSACTION; 612 CREATE TABLE a(id INTEGER); 613 INSERT INTO a VALUES(1); 614 INSERT INTO a VALUES(2); 615 INSERT INTO a VALUES(3); 616 CREATE TABLE b(id INTEGER); 617 INSERT INTO b VALUES(NULL); 618 INSERT INTO b VALUES(3); 619 INSERT INTO b VALUES(4); 620 INSERT INTO b VALUES(5); 621 COMMIT; 622 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); 623 } 624} {} 625do_test in-13.14 { 626 execsql { 627 CREATE INDEX i5 ON b(id); 628 SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); 629 } 630} {} 631 632do_test in-13.15 { 633 catchsql { 634 SELECT 0 WHERE (SELECT 0,0) OR (0 IN (1,2)); 635 } 636} {1 {sub-select returns 2 columns - expected 1}} 637 638 639do_test in-13.X { 640 db nullvalue "" 641} {} 642 643# At one point the following was causing valgrind to report a "jump 644# depends on unitialized location" problem. 645# 646do_execsql_test in-14.0 { 647 CREATE TABLE c1(a); 648 INSERT INTO c1 VALUES(1), (2), (4), (3); 649} 650do_execsql_test in-14.1 { 651 SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1 652} {1 2 3 4} 653 654# 2019-02-20 Ticket https://www.sqlite.org/src/tktview/df46dfb631f75694fbb97033b69 655# 656do_execsql_test in-15.0 { 657 DROP TABLE IF EXISTS t1; 658 CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY); 659 INSERT INTO t1 VALUES(1); 660 SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3); 661} {1} 662do_execsql_test in-15.1 { 663 DROP TABLE IF EXISTS t2; 664 CREATE TABLE t2(a INTEGER PRIMARY KEY,b); 665 INSERT INTO t2 VALUES(1,11); 666 INSERT INTO t2 VALUES(2,22); 667 INSERT INTO t2 VALUES(3,33); 668 SELECT b, a IN (3,4,5) FROM t2 ORDER BY b; 669} {11 0 22 0 33 1} 670do_execsql_test in-15.2 { 671 DROP TABLE IF EXISTS t3; 672 CREATE TABLE t3(x INTEGER PRIMARY KEY); 673 INSERT INTO t3 VALUES(8); 674 SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3; 675 SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3; 676} {yes no} 677do_execsql_test in-15.3 { 678 SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3; 679 SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3; 680} {yes no} 681do_execsql_test in-15.4 { 682 DROP TABLE IF EXISTS t4; 683 CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT); 684 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) 685 INSERT INTO t4(a,b) SELECT x, x+100 FROM c; 686 SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b; 687} {103 108} 688do_execsql_test in-15.5 { 689 SELECT b FROM t4 WHERE a NOT IN (3,null,8); 690} {} 691do_execsql_test in-15.6 { 692 DROP TABLE IF EXISTS t5; 693 DROP TABLE IF EXISTS t6; 694 CREATE TABLE t5(id INTEGER PRIMARY KEY, name TEXT); 695 CREATE TABLE t6(id INTEGER PRIMARY KEY, name TEXT, t5_id INT); 696 INSERT INTO t5 VALUES(1,'Alice'),(2,'Emma'); 697 INSERT INTO t6 VALUES(1,'Bob',1),(2,'Cindy',1),(3,'Dave',2); 698 SELECT a.* 699 FROM t5 AS 'a' JOIN t5 AS 'b' ON b.id=a.id 700 WHERE b.id IN ( 701 SELECT t6.t5_id 702 FROM t6 703 WHERE name='Bob' 704 AND t6.t5_id IS NOT NULL 705 AND t6.id IN ( 706 SELECT id 707 FROM (SELECT t6.id, count(*) AS x 708 FROM t6 709 WHERE name='Bob' 710 ) AS 't' 711 WHERE x=1 712 ) 713 AND t6.id IN (1,id) 714 ); 715} {1 Alice} 716 717#------------------------------------------------------------------------- 718reset_db 719do_execsql_test in-16.0 { 720 CREATE TABLE x1(a, b); 721 INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6); 722 CREATE INDEX x1i ON x1(a, b); 723} 724 725do_execsql_test in-16.1 { 726 SELECT * FROM x1 727 WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0) 728 ORDER BY a DESC, b; 729} {6 {} 4 {} 2 {}} 730 731do_execsql_test in-16.2 { 732 SELECT * FROM x1 733 WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0) 734 ORDER BY a DESC, b; 735} {} 736 737# 2019-06-11 738# https://www.sqlite.org/src/info/57353f8243c637c0 739# 740do_execsql_test in-17.1 { 741 SELECT 1 IN ('1'); 742} 0 743do_execsql_test in-17.2 { 744 SELECT 1 IN ('1' COLLATE nocase); 745} 0 746do_execsql_test in-17.3 { 747 SELECT 1 IN (CAST('1' AS text)); 748} 0 749do_execsql_test in-17.4 { 750 SELECT 1 IN (CAST('1' AS text) COLLATE nocase); 751} 0 752 753# 2019-08-27 ticket https://sqlite.org/src/info/dbaf8a6820be1ece 754# 755do_execsql_test in-18.1 { 756 DROP TABLE IF EXISTS t0; 757 CREATE TABLE t0(c0 INT UNIQUE); 758 INSERT INTO t0(c0) VALUES (1); 759 SELECT * FROM t0 WHERE '1' IN (t0.c0); 760} {} 761 762# 2019-09-02 ticket https://www.sqlite.org/src/info/2841e99d104c6436 763# For the IN_INDEX_NOOP optimization, apply REAL affinity to the LHS 764# values prior to comparison if the RHS has REAL affinity. 765# 766# Also ticket https://sqlite.org/src/info/29f635e0af71234b 767# 768do_execsql_test in-19.10 { 769 DROP TABLE IF EXISTS t0; 770 CREATE TABLE t0(c0 REAL UNIQUE); 771 INSERT INTO t0(c0) VALUES(2.0625E00); 772 SELECT 1 FROM t0 WHERE c0 IN ('2.0625'); 773} {1} 774do_execsql_test in-19.20 { 775 SELECT c0 IN ('2.0625') FROM t0; 776} {1} 777do_execsql_test in-19.21 { 778 SELECT c0 = ('2.0625') FROM t0; 779} {1} 780do_execsql_test in-19.22 { 781 SELECT c0 = ('0.20625e+01') FROM t0; 782} {1} 783do_execsql_test in-19.30 { 784 SELECT c0 IN ('2.0625',2,3) FROM t0; 785} {1} 786do_execsql_test in-19.40 { 787 DROP TABLE t0; 788 CREATE TABLE t0(c0 TEXT, c1 REAL, c2, PRIMARY KEY(c2, c0, c1)); 789 CREATE INDEX i0 ON t0(c1 IN (c0)); 790 INSERT INTO t0(c0, c2) VALUES (0, NULL) ON CONFLICT(c2, c1, c0) DO NOTHING; 791 PRAGMA integrity_check; 792} {ok} 793 794# Ticket f3ff1472887 795# 796do_execsql_test in-20.1 { 797 SELECT (1 IN (2 IS TRUE)); 798} {1} 799 800finish_test 801