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.11 2004/01/15 03:30:25 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 set fd [open data1.txt w] 23 for {set i 1} {$i<=10} {incr i} { 24 puts $fd "$i\t[expr {int(pow(2,$i))}]" 25 } 26 close $fd 27 execsql { 28 CREATE TABLE t1(a int, b int); 29 COPY t1 FROM 'data1.txt'; 30 } 31 file delete -force data1.txt 32 execsql {SELECT count(*) FROM t1} 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 57# Testing of the IN operator using static lists on the right-hand side. 58# 59do_test in-2.1 { 60 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a} 61} {3 4 5} 62do_test in-2.2 { 63 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a} 64} {1 2 6 7 8 9 10} 65do_test in-2.3 { 66 execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a} 67} {3 4 5 9} 68do_test in-2.4 { 69 execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a} 70} {1 2 6 7 8 9 10} 71do_test in-2.5 { 72 execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b} 73} {1 2 103 104 5 6 7 8 9 10} 74 75do_test in-2.6 { 76 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (b+10,20)}} msg] 77 lappend v $msg 78} {1 {right-hand side of IN operator must be constant}} 79do_test in-2.7 { 80 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}} msg] 81 lappend v $msg 82} {1 {right-hand side of IN operator must be constant}} 83do_test in-2.8 { 84 execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b} 85} {4 5} 86do_test in-2.9 { 87 set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}} msg] 88 lappend v $msg 89} {1 {right-hand side of IN operator must be constant}} 90do_test in-2.10 { 91 set v [catch {execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} msg] 92 lappend v $msg 93} {1 {right-hand side of IN operator must be constant}} 94do_test in-2.11 { 95 set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg] 96 lappend v $msg 97} {1 {no such column: c}} 98 99# Testing the IN operator where the right-hand side is a SELECT 100# 101do_test in-3.1 { 102 execsql { 103 SELECT a FROM t1 104 WHERE b IN (SELECT b FROM t1 WHERE a<5) 105 ORDER BY a 106 } 107} {1 2 3 4} 108do_test in-3.2 { 109 execsql { 110 SELECT a FROM t1 111 WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512 112 ORDER BY a 113 } 114} {1 2 3 4 9} 115do_test in-3.3 { 116 execsql { 117 SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b 118 } 119} {101 102 103 104 5 6 7 8 9 10} 120 121# Make sure the UPDATE and DELETE commands work with IN-SELECT 122# 123do_test in-4.1 { 124 execsql { 125 UPDATE t1 SET b=b*2 126 WHERE b IN (SELECT b FROM t1 WHERE a>8) 127 } 128 execsql {SELECT b FROM t1 ORDER BY b} 129} {2 4 8 16 32 64 128 256 1024 2048} 130do_test in-4.2 { 131 execsql { 132 DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8) 133 } 134 execsql {SELECT a FROM t1 ORDER BY a} 135} {1 2 3 4 5 6 7 8} 136do_test in-4.3 { 137 execsql { 138 DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4) 139 } 140 execsql {SELECT a FROM t1 ORDER BY a} 141} {5 6 7 8} 142 143# Do an IN with a constant RHS but where the RHS has many, many 144# elements. We need to test that collisions in the hash table 145# are resolved properly. 146# 147do_test in-5.1 { 148 execsql { 149 INSERT INTO t1 VALUES('hello', 'world'); 150 SELECT * FROM t1 151 WHERE a IN ( 152 'Do','an','IN','with','a','constant','RHS','but','where','the', 153 'has','many','elements','We','need','to','test','that', 154 'collisions','hash','table','are','resolved','properly', 155 'This','in-set','contains','thirty','one','entries','hello'); 156 } 157} {hello world} 158 159# Make sure the IN operator works with INTEGER PRIMARY KEY fields. 160# 161do_test in-6.1 { 162 execsql { 163 CREATE TABLE ta(a INTEGER PRIMARY KEY, b); 164 INSERT INTO ta VALUES(1,1); 165 INSERT INTO ta VALUES(2,2); 166 INSERT INTO ta VALUES(3,3); 167 INSERT INTO ta VALUES(4,4); 168 INSERT INTO ta VALUES(6,6); 169 INSERT INTO ta VALUES(8,8); 170 INSERT INTO ta VALUES(10, 171 'This is a key that is long enough to require a malloc in the VDBE'); 172 SELECT * FROM ta WHERE a<10; 173 } 174} {1 1 2 2 3 3 4 4 6 6 8 8} 175do_test in-6.2 { 176 execsql { 177 CREATE TABLE tb(a INTEGER PRIMARY KEY, b); 178 INSERT INTO tb VALUES(1,1); 179 INSERT INTO tb VALUES(2,2); 180 INSERT INTO tb VALUES(3,3); 181 INSERT INTO tb VALUES(5,5); 182 INSERT INTO tb VALUES(7,7); 183 INSERT INTO tb VALUES(9,9); 184 INSERT INTO tb VALUES(11, 185 'This is a key that is long enough to require a malloc in the VDBE'); 186 SELECT * FROM tb WHERE a<10; 187 } 188} {1 1 2 2 3 3 5 5 7 7 9 9} 189do_test in-6.3 { 190 execsql { 191 SELECT a FROM ta WHERE b IN (SELECT a FROM tb); 192 } 193} {1 2 3} 194do_test in-6.4 { 195 execsql { 196 SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb); 197 } 198} {4 6 8 10} 199do_test in-6.5 { 200 execsql { 201 SELECT a FROM ta WHERE b IN (SELECT b FROM tb); 202 } 203} {1 2 3 10} 204do_test in-6.6 { 205 execsql { 206 SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb); 207 } 208} {4 6 8} 209do_test in-6.7 { 210 execsql { 211 SELECT a FROM ta WHERE a IN (SELECT a FROM tb); 212 } 213} {1 2 3} 214do_test in-6.8 { 215 execsql { 216 SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb); 217 } 218} {4 6 8 10} 219do_test in-6.9 { 220 execsql { 221 SELECT a FROM ta WHERE a IN (SELECT b FROM tb); 222 } 223} {1 2 3} 224do_test in-6.10 { 225 execsql { 226 SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb); 227 } 228} {4 6 8 10} 229 230# Tests of IN operator against empty sets. (Ticket #185) 231# 232do_test in-7.1 { 233 execsql { 234 SELECT a FROM t1 WHERE a IN (); 235 } 236} {} 237do_test in-7.2 { 238 execsql { 239 SELECT a FROM t1 WHERE a IN (5); 240 } 241} {5} 242do_test in-7.3 { 243 execsql { 244 SELECT a FROM t1 WHERE a NOT IN () ORDER BY a; 245 } 246} {5 6 7 8 hello} 247do_test in-7.4 { 248 execsql { 249 SELECT a FROM t1 WHERE a IN (5) AND b IN (); 250 } 251} {} 252do_test in-7.5 { 253 execsql { 254 SELECT a FROM t1 WHERE a IN (5) AND b NOT IN (); 255 } 256} {5} 257do_test in-7.6 { 258 execsql { 259 SELECT a FROM ta WHERE a IN (); 260 } 261} {} 262do_test in-7.7 { 263 execsql { 264 SELECT a FROM ta WHERE a NOT IN (); 265 } 266} {1 2 3 4 6 8 10} 267 268do_test in-8.1 { 269 execsql { 270 SELECT b FROM t1 WHERE a IN ('hello','there') 271 } 272} {world} 273do_test in-8.2 { 274 execsql { 275 SELECT b FROM t1 WHERE a IN ("hello",'there') 276 } 277} {world} 278 279# Test constructs of the form: expr IN tablename 280# 281do_test in-9.1 { 282 execsql { 283 CREATE TABLE t4 AS SELECT a FROM tb; 284 SELECT * FROM t4; 285 } 286} {1 2 3 5 7 9 11} 287do_test in-9.2 { 288 execsql { 289 SELECT b FROM t1 WHERE a IN t4; 290 } 291} {32 128} 292do_test in-9.3 { 293 execsql { 294 SELECT b FROM t1 WHERE a NOT IN t4; 295 } 296} {64 256 world} 297do_test in-9.4 { 298 catchsql { 299 SELECT b FROM t1 WHERE a NOT IN tb; 300 } 301} {1 {only a single result allowed for a SELECT that is part of an expression}} 302 303finish_test 304