# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the IN and BETWEEN operator. # # $Id: in.test,v 1.16 2006/05/23 23:22:29 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Generate the test data we will need for the first squences of tests. # do_test in-1.0 { execsql { BEGIN; CREATE TABLE t1(a int, b int); } for {set i 1} {$i<=10} {incr i} { execsql "INSERT INTO t1 VALUES($i,[expr {int(pow(2,$i))}])" } execsql { COMMIT; SELECT count(*) FROM t1; } } {10} # Do basic testing of BETWEEN. # do_test in-1.1 { execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a} } {4 5} do_test in-1.2 { execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a} } {1 2 3 6 7 8 9 10} do_test in-1.3 { execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a} } {1 2 3 4} do_test in-1.4 { execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a} } {5 6 7 8 9 10} do_test in-1.6 { execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a} } {1 2 3 4 9} do_test in-1.7 { execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b} } {101 102 103 4 5 6 7 8 9 10} # The rest of this file concentrates on testing the IN operator. # Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY # (because the IN operator is unavailable). # ifcapable !subquery { finish_test return } # Testing of the IN operator using static lists on the right-hand side. # do_test in-2.1 { execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a} } {3 4 5} do_test in-2.2 { execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a} } {1 2 6 7 8 9 10} do_test in-2.3 { execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a} } {3 4 5 9} do_test in-2.4 { execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a} } {1 2 6 7 8 9 10} do_test in-2.5 { execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b} } {1 2 103 104 5 6 7 8 9 10} do_test in-2.6 { execsql {SELECT a FROM t1 WHERE b IN (b+8,64)} } {6} do_test in-2.7 { execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)} } {4 5 6 7 8 9 10} do_test in-2.8 { execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b} } {4 5} do_test in-2.9 { execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)} } {} do_test in-2.10 { execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))} } {} do_test in-2.11 { set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg] lappend v $msg } {1 {no such column: c}} # Testing the IN operator where the right-hand side is a SELECT # do_test in-3.1 { execsql { SELECT a FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a<5) ORDER BY a } } {1 2 3 4} do_test in-3.2 { execsql { SELECT a FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512 ORDER BY a } } {1 2 3 4 9} do_test in-3.3 { execsql { SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b } } {101 102 103 104 5 6 7 8 9 10} # Make sure the UPDATE and DELETE commands work with IN-SELECT # do_test in-4.1 { execsql { UPDATE t1 SET b=b*2 WHERE b IN (SELECT b FROM t1 WHERE a>8) } execsql {SELECT b FROM t1 ORDER BY b} } {2 4 8 16 32 64 128 256 1024 2048} do_test in-4.2 { execsql { DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8) } execsql {SELECT a FROM t1 ORDER BY a} } {1 2 3 4 5 6 7 8} do_test in-4.3 { execsql { DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4) } execsql {SELECT a FROM t1 ORDER BY a} } {5 6 7 8} # Do an IN with a constant RHS but where the RHS has many, many # elements. We need to test that collisions in the hash table # are resolved properly. # do_test in-5.1 { execsql { INSERT INTO t1 VALUES('hello', 'world'); SELECT * FROM t1 WHERE a IN ( 'Do','an','IN','with','a','constant','RHS','but','where','the', 'has','many','elements','We','need','to','test','that', 'collisions','hash','table','are','resolved','properly', 'This','in-set','contains','thirty','one','entries','hello'); } } {hello world} # Make sure the IN operator works with INTEGER PRIMARY KEY fields. # do_test in-6.1 { execsql { CREATE TABLE ta(a INTEGER PRIMARY KEY, b); INSERT INTO ta VALUES(1,1); INSERT INTO ta VALUES(2,2); INSERT INTO ta VALUES(3,3); INSERT INTO ta VALUES(4,4); INSERT INTO ta VALUES(6,6); INSERT INTO ta VALUES(8,8); INSERT INTO ta VALUES(10, 'This is a key that is long enough to require a malloc in the VDBE'); SELECT * FROM ta WHERE a<10; } } {1 1 2 2 3 3 4 4 6 6 8 8} do_test in-6.2 { execsql { CREATE TABLE tb(a INTEGER PRIMARY KEY, b); INSERT INTO tb VALUES(1,1); INSERT INTO tb VALUES(2,2); INSERT INTO tb VALUES(3,3); INSERT INTO tb VALUES(5,5); INSERT INTO tb VALUES(7,7); INSERT INTO tb VALUES(9,9); INSERT INTO tb VALUES(11, 'This is a key that is long enough to require a malloc in the VDBE'); SELECT * FROM tb WHERE a<10; } } {1 1 2 2 3 3 5 5 7 7 9 9} do_test in-6.3 { execsql { SELECT a FROM ta WHERE b IN (SELECT a FROM tb); } } {1 2 3} do_test in-6.4 { execsql { SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb); } } {4 6 8 10} do_test in-6.5 { execsql { SELECT a FROM ta WHERE b IN (SELECT b FROM tb); } } {1 2 3 10} do_test in-6.6 { execsql { SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb); } } {4 6 8} do_test in-6.7 { execsql { SELECT a FROM ta WHERE a IN (SELECT a FROM tb); } } {1 2 3} do_test in-6.8 { execsql { SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb); } } {4 6 8 10} do_test in-6.9 { execsql { SELECT a FROM ta WHERE a IN (SELECT b FROM tb); } } {1 2 3} do_test in-6.10 { execsql { SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb); } } {4 6 8 10} # Tests of IN operator against empty sets. (Ticket #185) # do_test in-7.1 { execsql { SELECT a FROM t1 WHERE a IN (); } } {} do_test in-7.2 { execsql { SELECT a FROM t1 WHERE a IN (5); } } {5} do_test in-7.3 { execsql { SELECT a FROM t1 WHERE a NOT IN () ORDER BY a; } } {5 6 7 8 hello} do_test in-7.4 { execsql { SELECT a FROM t1 WHERE a IN (5) AND b IN (); } } {} do_test in-7.5 { execsql { SELECT a FROM t1 WHERE a IN (5) AND b NOT IN (); } } {5} do_test in-7.6 { execsql { SELECT a FROM ta WHERE a IN (); } } {} do_test in-7.7 { execsql { SELECT a FROM ta WHERE a NOT IN (); } } {1 2 3 4 6 8 10} do_test in-8.1 { execsql { SELECT b FROM t1 WHERE a IN ('hello','there') } } {world} do_test in-8.2 { execsql { SELECT b FROM t1 WHERE a IN ("hello",'there') } } {world} # Test constructs of the form: expr IN tablename # do_test in-9.1 { execsql { CREATE TABLE t4 AS SELECT a FROM tb; SELECT * FROM t4; } } {1 2 3 5 7 9 11} do_test in-9.2 { execsql { SELECT b FROM t1 WHERE a IN t4; } } {32 128} do_test in-9.3 { execsql { SELECT b FROM t1 WHERE a NOT IN t4; } } {64 256 world} do_test in-9.4 { catchsql { SELECT b FROM t1 WHERE a NOT IN tb; } } {1 {only a single result allowed for a SELECT that is part of an expression}} # IN clauses in CHECK constraints. Ticket #1645 # do_test in-10.1 { execsql { CREATE TABLE t5( a INTEGER, CHECK( a IN (111,222,333) ) ); INSERT INTO t5 VALUES(111); SELECT * FROM t5; } } {111} do_test in-10.2 { catchsql { INSERT INTO t5 VALUES(4); } } {1 {constraint failed}} # Ticket #1821 # # Type affinity applied to the right-hand side of an IN operator. # do_test in-11.1 { execsql { CREATE TABLE t6(a,b NUMERIC); INSERT INTO t6 VALUES(1,2); INSERT INTO t6 VALUES(2,3); SELECT * FROM t6 WHERE b IN (2); } } {1 2} do_test in-11.2 { # The '2' should be coerced into 2 because t6.b is NUMERIC execsql { SELECT * FROM t6 WHERE b IN ('2'); } } {1 2} do_test in-11.3 { # No coercion should occur here because of the unary + before b. execsql { SELECT * FROM t6 WHERE +b IN ('2'); } } {} finish_test