third_party/sqlite/src/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 {
 262   execsql {
 263     SELECT a FROM ta WHERE a IN ();
 264   }
 265 } {}
 266 do_test in-7.7 {
 267   execsql {
 268     SELECT a FROM ta WHERE a NOT IN ();
 269   }
 270 } {1 2 3 4 6 8 10}
 271
 272 do_test in-8.1 {
 273   execsql {
 274     SELECT b FROM t1 WHERE a IN ('hello','there')
 275   }
 276 } {world}
 277 do_test in-8.2 {
 278   execsql {
 279     SELECT b FROM t1 WHERE a IN ("hello",'there')
 280   }
 281 } {world}
 282
 283 # Test constructs of the form:  expr IN tablename
 284 #
 285 do_test in-9.1 {
 286   execsql {
 287     CREATE TABLE t4 AS SELECT a FROM tb;
 288     SELECT * FROM t4;
 289   }
 290 } {1 2 3 5 7 9 11}
 291 do_test in-9.2 {
 292   execsql {
 293     SELECT b FROM t1 WHERE a IN t4;
 294   }
 295 } {32 128}
 296 do_test in-9.3 {
 297   execsql {
 298     SELECT b FROM t1 WHERE a NOT IN t4;
 299   }
 300 } {64 256 world}
 301 do_test in-9.4 {
 302   catchsql {
 303     SELECT b FROM t1 WHERE a NOT IN tb;
 304   }
 305 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 306
 307 # IN clauses in CHECK constraints.  Ticket #1645
 308 #
 309 do_test in-10.1 {
 310   execsql {
 311     CREATE TABLE t5(
 312       a INTEGER,
 313       CHECK( a IN (111,222,333) )
 314     );
 315     INSERT INTO t5 VALUES(111);
 316     SELECT * FROM t5;
 317   }
 318 } {111}
 319 do_test in-10.2 {
 320   catchsql {
 321     INSERT INTO t5 VALUES(4);
 322   }
 323 } {1 {constraint failed}}
 324
 325 # Ticket #1821
 326 #
 327 # Type affinity applied to the right-hand side of an IN operator.
 328 #
 329 do_test in-11.1 {
 330   execsql {
 331     CREATE TABLE t6(a,b NUMERIC);
 332     INSERT INTO t6 VALUES(1,2);
 333     INSERT INTO t6 VALUES(2,3);
 334     SELECT * FROM t6 WHERE b IN (2);
 335   }
 336 } {1 2}
 337 do_test in-11.2 {
 338   # The '2' should be coerced into 2 because t6.b is NUMERIC
 339   execsql {
 340     SELECT * FROM t6 WHERE b IN ('2');
 341   }
 342 } {1 2}
 343 do_test in-11.3 {
 344   # No coercion should occur here because of the unary + before b.
 345   execsql {
 346     SELECT * FROM t6 WHERE +b IN ('2');
 347   }
 348 } {}
 349 do_test in-11.4 {
 350   # No coercion because column a as affinity NONE
 351   execsql {
 352     SELECT * FROM t6 WHERE a IN ('2');
 353   }
 354 } {}
 355 do_test in-11.5 {
 356   execsql {
 357     SELECT * FROM t6 WHERE a IN (2);
 358   }
 359 } {2 3}
 360 do_test in-11.6 {
 361   # No coercion because column a as affinity NONE
 362   execsql {
 363     SELECT * FROM t6 WHERE +a IN ('2');
 364   }
 365 } {}
 366
 367 # Test error conditions with expressions of the form IN(<compound select>).
 368 #
 369 ifcapable compound {
 370 do_test in-12.1 {
 371   execsql {
 372     CREATE TABLE t2(a, b, c);
 373     CREATE TABLE t3(a, b, c);
 374   }
 375 } {}
 376 do_test in-12.2 {
 377   catchsql {
 378     SELECT * FROM t2 WHERE a IN (
 379       SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
 380     );
 381   }
 382 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 383 do_test in-12.3 {
 384   catchsql {
 385     SELECT * FROM t2 WHERE a IN (
 386       SELECT a, b FROM t3 UNION SELECT a, b FROM t2
 387     );
 388   }
 389 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 390 do_test in-12.4 {
 391   catchsql {
 392     SELECT * FROM t2 WHERE a IN (
 393       SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2
 394     );
 395   }
 396 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 397 do_test in-12.5 {
 398   catchsql {
 399     SELECT * FROM t2 WHERE a IN (
 400       SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
 401     );
 402   }
 403 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 404 do_test in-12.6 {
 405   catchsql {
 406     SELECT * FROM t2 WHERE a IN (
 407       SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
 408     );
 409   }
 410 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
 411 do_test in-12.7 {
 412   catchsql {
 413     SELECT * FROM t2 WHERE a IN (
 414       SELECT a, b FROM t3 UNION SELECT a FROM t2
 415     );
 416   }
 417 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
 418 do_test in-12.8 {
 419   catchsql {
 420     SELECT * FROM t2 WHERE a IN (
 421       SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
 422     );
 423   }
 424 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
 425 do_test in-12.9 {
 426   catchsql {
 427     SELECT * FROM t2 WHERE a IN (
 428       SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
 429     );
 430   }
 431 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
 432 }
 433
 434 do_test in-12.10 {
 435   catchsql {
 436     SELECT * FROM t2 WHERE a IN (
 437       SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
 438     );
 439   }
 440 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 441 do_test in-12.11 {
 442   catchsql {
 443     SELECT * FROM t2 WHERE a IN (
 444       SELECT a FROM t3 UNION SELECT a, b FROM t2
 445     );
 446   }
 447 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 448 do_test in-12.12 {
 449   catchsql {
 450     SELECT * FROM t2 WHERE a IN (
 451       SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
 452     );
 453   }
 454 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 455 do_test in-12.13 {
 456   catchsql {
 457     SELECT * FROM t2 WHERE a IN (
 458       SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
 459     );
 460   }
 461 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 462
 463
 464 #------------------------------------------------------------------------
 465 # The following tests check that NULL is handled correctly when it
 466 # appears as part of a set of values on the right-hand side of an
 467 # IN or NOT IN operator.
 468 #
 469 # When it appears in such a set, NULL is handled as an "unknown value".
 470 # If, because of the unknown value in the set, the result of the expression
 471 # cannot be determined, then it itself evaluates to NULL.
 472 #
 473
 474 # Warm body test to demonstrate the principles being tested:
 475 #
 476 do_test in-13.1 {
 477   db nullvalue "null"
 478   execsql { SELECT
 479     1 IN (NULL, 1, 2),     -- The value 1 is a member of the set, return true.
 480     3 IN (NULL, 1, 2),     -- Ambiguous, return NULL.
 481     1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
 482     3 NOT IN (NULL, 1, 2)  -- Ambiguous, return NULL.
 483   }
 484 } {1 null 0 null}
 485
 486 do_test in-13.2 {
 487   execsql {
 488     CREATE TABLE t7(a, b, c NOT NULL);
 489     INSERT INTO t7 VALUES(1,    1, 1);
 490     INSERT INTO t7 VALUES(2,    2, 2);
 491     INSERT INTO t7 VALUES(3,    3, 3);
 492     INSERT INTO t7 VALUES(NULL, 4, 4);
 493     INSERT INTO t7 VALUES(NULL, 5, 5);
 494   }
 495 } {}
 496
 497 do_test in-13.3 {
 498   execsql { SELECT 2 IN (SELECT a FROM t7) }
 499 } {1}
 500 do_test in-13.4 {
 501   execsql { SELECT 6 IN (SELECT a FROM t7) }
 502 } {null}
 503
 504 do_test in-13.5 {
 505   execsql { SELECT 2 IN (SELECT b FROM t7) }
 506 } {1}
 507 do_test in-13.6 {
 508   execsql { SELECT 6 IN (SELECT b FROM t7) }
 509 } {0}
 510
 511 do_test in-13.7 {
 512   execsql { SELECT 2 IN (SELECT c FROM t7) }
 513 } {1}
 514 do_test in-13.8 {
 515   execsql { SELECT 6 IN (SELECT c FROM t7) }
 516 } {0}
 517
 518 do_test in-13.9 {
 519   execsql {
 520     SELECT
 521       2 NOT IN (SELECT a FROM t7),
 522       6 NOT IN (SELECT a FROM t7),
 523       2 NOT IN (SELECT b FROM t7),
 524       6 NOT IN (SELECT b FROM t7),
 525       2 NOT IN (SELECT c FROM t7),
 526       6 NOT IN (SELECT c FROM t7)
 527   }
 528 } {0 null 0 1 0 1}
 529
 530 do_test in-13.10 {
 531   execsql {
 532     SELECT b IN (
 533       SELECT inside.a
 534       FROM t7 AS inside
 535       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
 536     )
 537     FROM t7 AS outside ORDER BY b;
 538   }
 539 } {0 null null null 0}
 540
 541 do_test in-13.11 {
 542   execsql {
 543     SELECT b NOT IN (
 544       SELECT inside.a
 545       FROM t7 AS inside
 546       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
 547     )
 548     FROM t7 AS outside ORDER BY b;
 549   }
 550 } {1 null null null 1}
 551
 552 do_test in-13.12 {
 553   execsql {
 554     CREATE INDEX i1 ON t7(a);
 555     CREATE INDEX i2 ON t7(b);
 556     CREATE INDEX i3 ON t7(c);
 557   }
 558   execsql {
 559     SELECT
 560       2 IN (SELECT a FROM t7),
 561       6 IN (SELECT a FROM t7),
 562       2 IN (SELECT b FROM t7),
 563       6 IN (SELECT b FROM t7),
 564       2 IN (SELECT c FROM t7),
 565       6 IN (SELECT c FROM t7)
 566   }
 567 } {1 null 1 0 1 0}
 568
 569 do_test in-13.13 {
 570   execsql {
 571     SELECT
 572       2 NOT IN (SELECT a FROM t7),
 573       6 NOT IN (SELECT a FROM t7),
 574       2 NOT IN (SELECT b FROM t7),
 575       6 NOT IN (SELECT b FROM t7),
 576       2 NOT IN (SELECT c FROM t7),
 577       6 NOT IN (SELECT c FROM t7)
 578   }
 579 } {0 null 0 1 0 1}
 580
 581 do_test in-13.14 {
 582   execsql {
 583     BEGIN TRANSACTION;
 584     CREATE TABLE a(id INTEGER);
 585     INSERT INTO a VALUES(1);
 586     INSERT INTO a VALUES(2);
 587     INSERT INTO a VALUES(3);
 588     CREATE TABLE b(id INTEGER);
 589     INSERT INTO b VALUES(NULL);
 590     INSERT INTO b VALUES(3);
 591     INSERT INTO b VALUES(4);
 592     INSERT INTO b VALUES(5);
 593     COMMIT;
 594     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
 595   }
 596 } {}
 597 do_test in-13.14 {
 598   execsql {
 599     CREATE INDEX i5 ON b(id);
 600     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
 601   }
 602 } {}
 603
 604
 605 do_test in-13.X {
 606   db nullvalue ""
 607 } {}
 608
 609 finish_test