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.14 2005/07/08 18:25:26 drh 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 {int(pow(2,$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 307finish_test 308