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 { 262 execsql { 263 SELECT a FROM ta WHERE a IN (); 264 } 265} {} 266do_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 272do_test in-8.1 { 273 execsql { 274 SELECT b FROM t1 WHERE a IN ('hello','there') 275 } 276} {world} 277do_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# 285do_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} 291do_test in-9.2 { 292 execsql { 293 SELECT b FROM t1 WHERE a IN t4; 294 } 295} {32 128} 296do_test in-9.3 { 297 execsql { 298 SELECT b FROM t1 WHERE a NOT IN t4; 299 } 300} {64 256 world} 301do_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# 309do_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} 319do_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# 329do_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} 337do_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} 343do_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} {} 349do_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} {} 355do_test in-11.5 { 356 execsql { 357 SELECT * FROM t6 WHERE a IN (2); 358 } 359} {2 3} 360do_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# 369ifcapable compound { 370do_test in-12.1 { 371 execsql { 372 CREATE TABLE t2(a, b, c); 373 CREATE TABLE t3(a, b, c); 374 } 375} {} 376do_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}} 383do_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}} 390do_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}} 397do_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}} 404do_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}} 411do_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}} 418do_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}} 425do_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 434do_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}} 441do_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}} 448do_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}} 455do_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# 476do_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 486do_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 497do_test in-13.3 { 498 execsql { SELECT 2 IN (SELECT a FROM t7) } 499} {1} 500do_test in-13.4 { 501 execsql { SELECT 6 IN (SELECT a FROM t7) } 502} {null} 503 504do_test in-13.5 { 505 execsql { SELECT 2 IN (SELECT b FROM t7) } 506} {1} 507do_test in-13.6 { 508 execsql { SELECT 6 IN (SELECT b FROM t7) } 509} {0} 510 511do_test in-13.7 { 512 execsql { SELECT 2 IN (SELECT c FROM t7) } 513} {1} 514do_test in-13.8 { 515 execsql { SELECT 6 IN (SELECT c FROM t7) } 516} {0} 517 518do_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 530do_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 541do_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 552do_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 569do_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 581do_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} {} 597do_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 605do_test in-13.X { 606 db nullvalue "" 607} {} 608 609finish_test 610