test/in.test

   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://www.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://www.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://www.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