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