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.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 {only a single result allowed for a SELECT that is part of an expression}}
 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: t5}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 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 {only a single result allowed for a SELECT that is part of an expression}}
 475 }; #ifcapable compound
 476
 477
 478 #------------------------------------------------------------------------
 479 # The following tests check that NULL is handled correctly when it
 480 # appears as part of a set of values on the right-hand side of an
 481 # IN or NOT IN operator.
 482 #
 483 # When it appears in such a set, NULL is handled as an "unknown value".
 484 # If, because of the unknown value in the set, the result of the expression
 485 # cannot be determined, then it itself evaluates to NULL.
 486 #
 487
 488 # Warm body test to demonstrate the principles being tested:
 489 #
 490 do_test in-13.1 {
 491   db nullvalue "null"
 492   execsql { SELECT
 493     1 IN (NULL, 1, 2),     -- The value 1 is a member of the set, return true.
 494     3 IN (NULL, 1, 2),     -- Ambiguous, return NULL.
 495     1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
 496     3 NOT IN (NULL, 1, 2)  -- Ambiguous, return NULL.
 497   }
 498 } {1 null 0 null}
 499
 500 do_test in-13.2 {
 501   execsql {
 502     CREATE TABLE t7(a, b, c NOT NULL);
 503     INSERT INTO t7 VALUES(1,    1, 1);
 504     INSERT INTO t7 VALUES(2,    2, 2);
 505     INSERT INTO t7 VALUES(3,    3, 3);
 506     INSERT INTO t7 VALUES(NULL, 4, 4);
 507     INSERT INTO t7 VALUES(NULL, 5, 5);
 508   }
 509 } {}
 510
 511 do_test in-13.3 {
 512   execsql { SELECT 2 IN (SELECT a FROM t7) }
 513 } {1}
 514 do_test in-13.4 {
 515   execsql { SELECT 6 IN (SELECT a FROM t7) }
 516 } {null}
 517
 518 do_test in-13.5 {
 519   execsql { SELECT 2 IN (SELECT b FROM t7) }
 520 } {1}
 521 do_test in-13.6 {
 522   execsql { SELECT 6 IN (SELECT b FROM t7) }
 523 } {0}
 524
 525 do_test in-13.7 {
 526   execsql { SELECT 2 IN (SELECT c FROM t7) }
 527 } {1}
 528 do_test in-13.8 {
 529   execsql { SELECT 6 IN (SELECT c FROM t7) }
 530 } {0}
 531
 532 do_test in-13.9 {
 533   execsql {
 534     SELECT
 535       2 NOT IN (SELECT a FROM t7),
 536       6 NOT IN (SELECT a FROM t7),
 537       2 NOT IN (SELECT b FROM t7),
 538       6 NOT IN (SELECT b FROM t7),
 539       2 NOT IN (SELECT c FROM t7),
 540       6 NOT IN (SELECT c FROM t7)
 541   }
 542 } {0 null 0 1 0 1}
 543
 544 do_test in-13.10 {
 545   execsql {
 546     SELECT b IN (
 547       SELECT inside.a
 548       FROM t7 AS inside
 549       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
 550     )
 551     FROM t7 AS outside ORDER BY b;
 552   }
 553 } {0 null null null 0}
 554
 555 do_test in-13.11 {
 556   execsql {
 557     SELECT b NOT IN (
 558       SELECT inside.a
 559       FROM t7 AS inside
 560       WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
 561     )
 562     FROM t7 AS outside ORDER BY b;
 563   }
 564 } {1 null null null 1}
 565
 566 do_test in-13.12 {
 567   execsql {
 568     CREATE INDEX i1 ON t7(a);
 569     CREATE INDEX i2 ON t7(b);
 570     CREATE INDEX i3 ON t7(c);
 571   }
 572   execsql {
 573     SELECT
 574       2 IN (SELECT a FROM t7),
 575       6 IN (SELECT a FROM t7),
 576       2 IN (SELECT b FROM t7),
 577       6 IN (SELECT b FROM t7),
 578       2 IN (SELECT c FROM t7),
 579       6 IN (SELECT c FROM t7)
 580   }
 581 } {1 null 1 0 1 0}
 582
 583 do_test in-13.13 {
 584   execsql {
 585     SELECT
 586       2 NOT IN (SELECT a FROM t7),
 587       6 NOT IN (SELECT a FROM t7),
 588       2 NOT IN (SELECT b FROM t7),
 589       6 NOT IN (SELECT b FROM t7),
 590       2 NOT IN (SELECT c FROM t7),
 591       6 NOT IN (SELECT c FROM t7)
 592   }
 593 } {0 null 0 1 0 1}
 594
 595 do_test in-13.14 {
 596   execsql {
 597     BEGIN TRANSACTION;
 598     CREATE TABLE a(id INTEGER);
 599     INSERT INTO a VALUES(1);
 600     INSERT INTO a VALUES(2);
 601     INSERT INTO a VALUES(3);
 602     CREATE TABLE b(id INTEGER);
 603     INSERT INTO b VALUES(NULL);
 604     INSERT INTO b VALUES(3);
 605     INSERT INTO b VALUES(4);
 606     INSERT INTO b VALUES(5);
 607     COMMIT;
 608     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
 609   }
 610 } {}
 611 do_test in-13.14 {
 612   execsql {
 613     CREATE INDEX i5 ON b(id);
 614     SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
 615   }
 616 } {}
 617
 618
 619 do_test in-13.X {
 620   db nullvalue ""
 621 } {}
 622
 623 finish_test