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.1 {
 285   execsql {
 286     SELECT b FROM t1 WHERE a IN ('hello','there')
 287   }
 288 } {world}
 289 do_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 #
 297 do_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}
 303 do_test in-9.2 {
 304   execsql {
 305     SELECT b FROM t1 WHERE a IN t4;
 306   }
 307 } {32 128}
 308 do_test in-9.3 {
 309   execsql {
 310     SELECT b FROM t1 WHERE a NOT IN t4;
 311   }
 312 } {64 256 world}
 313 do_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 #
 321 do_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}
 331 do_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 #
 341 do_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}
 349 do_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}
 355 do_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 } {}
 361 do_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 } {}
 367 do_test in-11.5 {
 368   execsql {
 369     SELECT * FROM t6 WHERE a IN (2);
 370   }
 371 } {2 3}
 372 do_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 #
 381 ifcapable compound {
 382 do_test in-12.1 {
 383   execsql {
 384     CREATE TABLE t2(a, b, c);
 385     CREATE TABLE t3(a, b, c);
 386   }
 387 } {}
 388 do_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}}
 395 do_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}}
 402 do_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}}
 409 do_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}}
 416 do_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}}
 423 do_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}}
 430 do_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}}
 437 do_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
 446 ifcapable compound {
 447 do_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}}
 454 do_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}}
 461 do_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}}
 468 do_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}}
 475 do_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}}
 482 do_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 #
 504 do_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
 514 do_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
 525 do_test in-13.3 {
 526   execsql { SELECT 2 IN (SELECT a FROM t7) }
 527 } {1}
 528 do_test in-13.4 {
 529   execsql { SELECT 6 IN (SELECT a FROM t7) }
 530 } {null}
 531
 532 do_test in-13.5 {
 533   execsql { SELECT 2 IN (SELECT b FROM t7) }
 534 } {1}
 535 do_test in-13.6 {
 536   execsql { SELECT 6 IN (SELECT b FROM t7) }
 537 } {0}
 538
 539 do_test in-13.7 {
 540   execsql { SELECT 2 IN (SELECT c FROM t7) }
 541 } {1}
 542 do_test in-13.8 {
 543   execsql { SELECT 6 IN (SELECT c FROM t7) }
 544 } {0}
 545
 546 do_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
 558 do_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
 569 do_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
 580 do_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
 597 do_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
 609 do_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 } {}
 625 do_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
 632 do_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
 639 do_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 #
 646 do_execsql_test in-14.0 {
 647   CREATE TABLE c1(a);
 648   INSERT INTO c1 VALUES(1), (2), (4), (3);
 649 }
 650 do_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 #
 656 do_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}
 662 do_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}
 670 do_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}
 677 do_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}
 681 do_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}
 688 do_execsql_test in-15.5 {
 689   SELECT b FROM t4 WHERE a NOT IN (3,null,8);
 690 } {}
 691 do_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 #-------------------------------------------------------------------------
 718 reset_db
 719 do_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
 725 do_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
 731 do_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 #
 740 do_execsql_test in-17.1 {
 741   SELECT 1 IN ('1');
 742 } 0
 743 do_execsql_test in-17.2 {
 744   SELECT 1 IN ('1' COLLATE nocase);
 745 } 0
 746 do_execsql_test in-17.3 {
 747   SELECT 1 IN (CAST('1' AS text));
 748 } 0
 749 do_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 #
 755 do_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 #
 768 do_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}
 774 do_execsql_test in-19.20 {
 775   SELECT c0 IN ('2.0625') FROM t0;
 776 } {1}
 777 do_execsql_test in-19.21 {
 778   SELECT c0 = ('2.0625') FROM t0;
 779 } {1}
 780 do_execsql_test in-19.22 {
 781   SELECT c0 = ('0.20625e+01') FROM t0;
 782 } {1}
 783 do_execsql_test in-19.30 {
 784   SELECT c0 IN ('2.0625',2,3) FROM t0;
 785 } {1}
 786 do_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 #
 796 do_execsql_test in-20.1 {
 797   SELECT (1 IN (2 IS TRUE));
 798 } {1}
 799
 800 finish_test