1# 2008 September 1 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# 12# $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $ 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix in4 17 18do_test in4-1.1 { 19 execsql { 20 CREATE TABLE t1(a, b); 21 CREATE INDEX i1 ON t1(a); 22 } 23} {} 24do_test in4-1.2 { 25 execsql { 26 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 27 } 28} {} 29do_test in4-1.3 { 30 execsql { 31 INSERT INTO t1 VALUES('aaa', 1); 32 INSERT INTO t1 VALUES('ddd', 2); 33 INSERT INTO t1 VALUES('ccc', 3); 34 INSERT INTO t1 VALUES('eee', 4); 35 SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 36 } 37} {1 3} 38do_test in4-1.4 { 39 execsql { 40 SELECT a FROM t1 WHERE rowid IN (1, 3); 41 } 42} {aaa ccc} 43do_test in4-1.5 { 44 execsql { 45 SELECT a FROM t1 WHERE rowid IN (); 46 } 47} {} 48do_test in4-1.6 { 49 execsql { 50 SELECT a FROM t1 WHERE a IN ('ddd'); 51 } 52} {ddd} 53 54do_test in4-2.1 { 55 execsql { 56 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 57 INSERT INTO t2 VALUES(-1, '-one'); 58 INSERT INTO t2 VALUES(0, 'zero'); 59 INSERT INTO t2 VALUES(1, 'one'); 60 INSERT INTO t2 VALUES(2, 'two'); 61 INSERT INTO t2 VALUES(3, 'three'); 62 } 63} {} 64 65do_test in4-2.2 { 66 execsql { SELECT b FROM t2 WHERE a IN (0, 2) } 67} {zero two} 68 69do_test in4-2.3 { 70 execsql { SELECT b FROM t2 WHERE a IN (2, 0) } 71} {zero two} 72 73do_test in4-2.4 { 74 execsql { SELECT b FROM t2 WHERE a IN (2, -1) } 75} {-one two} 76 77do_test in4-2.5 { 78 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } 79} {three} 80 81do_test in4-2.6 { 82 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } 83} {one} 84 85do_test in4-2.7 { 86 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } 87} {one two} 88 89do_test in4-2.8 { 90 execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') } 91} {two} 92 93# The following block of tests test expressions of the form: 94# 95# <expr> IN () 96# 97# i.e. IN expressions with a literal empty set. 98# 99# This has led to crashes on more than one occasion. Test case in4-3.2 100# was added in reponse to a bug reported on the mailing list on 11/7/2008. 101# See also tickets #3602 and #185. 102# 103do_test in4-3.1 { 104 execsql { 105 DROP TABLE IF EXISTS t1; 106 DROP TABLE IF EXISTS t2; 107 CREATE TABLE t1(x, id); 108 CREATE TABLE t2(x, id); 109 INSERT INTO t1 VALUES(NULL, NULL); 110 INSERT INTO t1 VALUES(0, NULL); 111 INSERT INTO t1 VALUES(1, 3); 112 INSERT INTO t1 VALUES(2, 4); 113 INSERT INTO t1 VALUES(3, 5); 114 INSERT INTO t1 VALUES(4, 6); 115 INSERT INTO t2 VALUES(0, NULL); 116 INSERT INTO t2 VALUES(4, 1); 117 INSERT INTO t2 VALUES(NULL, 1); 118 INSERT INTO t2 VALUES(NULL, NULL); 119 } 120} {} 121do_test in4-3.2 { 122 execsql { 123 SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1) 124 } 125} {} 126do_test in4-3.3 { 127 execsql { 128 CREATE TABLE t3(x, y, z); 129 CREATE INDEX t3i1 ON t3(x, y); 130 INSERT INTO t3 VALUES(1, 1, 1); 131 INSERT INTO t3 VALUES(10, 10, 10); 132 } 133 execsql { SELECT * FROM t3 WHERE x IN () } 134} {} 135do_test in4-3.4 { 136 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } 137} {} 138do_test in4-3.5 { 139 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } 140} {} 141do_test in4-3.6 { 142 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } 143} {10 10 10} 144do_test in4-3.7 { 145 execsql { SELECT * FROM t3 WHERE y IN () } 146} {} 147do_test in4-3.8 { 148 execsql { SELECT x IN() AS a FROM t3 WHERE a } 149} {} 150do_test in4-3.9 { 151 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } 152} {0 0} 153do_test in4-3.10 { 154 execsql { SELECT * FROM t3 WHERE oid IN () } 155} {} 156do_test in4-3.11 { 157 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} 158} {1 1 1} 159do_test in4-3.12 { 160 execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} 161} {} 162 163# Tests for "... IN (?)" and "... NOT IN (?)". In other words, tests 164# for when the RHS of IN is a single expression. This should work the 165# same as the == and <> operators. 166# 167do_execsql_test in4-3.21 { 168 SELECT * FROM t3 WHERE x=10 AND y IN (10); 169} {10 10 10} 170do_execsql_test in4-3.22 { 171 SELECT * FROM t3 WHERE x IN (10) AND y=10; 172} {10 10 10} 173do_execsql_test in4-3.23 { 174 SELECT * FROM t3 WHERE x IN (10) AND y IN (10); 175} {10 10 10} 176do_execsql_test in4-3.24 { 177 SELECT * FROM t3 WHERE x=1 AND y NOT IN (10); 178} {1 1 1} 179do_execsql_test in4-3.25 { 180 SELECT * FROM t3 WHERE x NOT IN (10) AND y=1; 181} {1 1 1} 182do_execsql_test in4-3.26 { 183 SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10); 184} {1 1 1} 185 186# The query planner recognizes that "x IN (?)" only generates a 187# single match and can use this information to optimize-out ORDER BY 188# clauses. 189# 190do_execsql_test in4-3.31 { 191 DROP INDEX t3i1; 192 CREATE UNIQUE INDEX t3xy ON t3(x,y); 193 194 SELECT *, '|' FROM t3 A, t3 B 195 WHERE A.x=10 AND A.y IN (10) 196 AND B.x=1 AND B.y IN (1); 197} {10 10 10 1 1 1 |} 198do_execsql_test in4-3.32 { 199 EXPLAIN QUERY PLAN 200 SELECT *, '|' FROM t3 A, t3 B 201 WHERE A.x=10 AND A.y IN (10) 202 AND B.x=1 AND B.y IN (1); 203} {~/B-TREE/} ;# No separate sorting pass 204do_execsql_test in4-3.33 { 205 SELECT *, '|' FROM t3 A, t3 B 206 WHERE A.x IN (10) AND A.y=10 207 AND B.x IN (1) AND B.y=1; 208} {10 10 10 1 1 1 |} 209do_execsql_test in4-3.34 { 210 EXPLAIN QUERY PLAN 211 SELECT *, '|' FROM t3 A, t3 B 212 WHERE A.x IN (10) AND A.y=10 213 AND B.x IN (1) AND B.y=1; 214} {~/B-TREE/} ;# No separate sorting pass 215 216# An expression of the form "x IN (?,?)" creates an ephemeral table to 217# hold the list of values on the RHS. But "x IN (?)" does not create 218# an ephemeral table. 219# 220do_execsql_test in4-3.41 { 221 SELECT * FROM t3 WHERE x IN (10,11); 222} {10 10 10} 223do_execsql_test in4-3.42 { 224 EXPLAIN 225 SELECT * FROM t3 WHERE x IN (10,11); 226} {/OpenEphemeral/} 227do_execsql_test in4-3.43 { 228 SELECT * FROM t3 WHERE x IN (10); 229} {10 10 10} 230 231# This test would verify that the "X IN (Y)" -> "X==Y" optimization 232# was working. But we have now taken that optimization out. 233#do_execsql_test in4-3.44 { 234# EXPLAIN 235# SELECT * FROM t3 WHERE x IN (10); 236#} {~/OpenEphemeral/} 237do_execsql_test in4-3.45 { 238 SELECT * FROM t3 WHERE x NOT IN (10,11,99999); 239} {1 1 1} 240do_execsql_test in4-3.46 { 241 EXPLAIN 242 SELECT * FROM t3 WHERE x NOT IN (10,11,99999); 243} {/OpenEphemeral/} 244do_execsql_test in4-3.47 { 245 SELECT * FROM t3 WHERE x NOT IN (10); 246} {1 1 1} 247do_execsql_test in4-3.48 { 248 EXPLAIN 249 SELECT * FROM t3 WHERE x NOT IN (10); 250} {~/OpenEphemeral/} 251 252# Make sure that when "x IN (?)" is converted into "x==?" that collating 253# sequence and affinity computations do not get messed up. 254# 255do_execsql_test in4-4.1 { 256 CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c); 257 INSERT INTO t4a VALUES('ABC','abc',1); 258 INSERT INTO t4a VALUES('def','xyz',2); 259 INSERT INTO t4a VALUES('ghi','ghi',3); 260 SELECT c FROM t4a WHERE a=b ORDER BY c; 261} {3} 262do_execsql_test in4-4.2 { 263 SELECT c FROM t4a WHERE b=a ORDER BY c; 264} {1 3} 265do_execsql_test in4-4.3 { 266 SELECT c FROM t4a WHERE (a||'')=b ORDER BY c; 267} {1 3} 268do_execsql_test in4-4.4 { 269 SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c; 270} {3} 271do_execsql_test in4-4.5 { 272 SELECT c FROM t4a WHERE a IN (b) ORDER BY c; 273} {3} 274do_execsql_test in4-4.6 { 275 SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c; 276} {3} 277 278 279do_execsql_test in4-4.11 { 280 CREATE TABLE t4b(a TEXT, b NUMERIC, c); 281 INSERT INTO t4b VALUES('1.0',1,4); 282 SELECT c FROM t4b WHERE a=b; 283} {4} 284do_execsql_test in4-4.12 { 285 SELECT c FROM t4b WHERE b=a; 286} {4} 287do_execsql_test in4-4.13 { 288 SELECT c FROM t4b WHERE +a=b; 289} {4} 290do_execsql_test in4-4.14 { 291 SELECT c FROM t4b WHERE a=+b; 292} {} 293do_execsql_test in4-4.15 { 294 SELECT c FROM t4b WHERE +b=a; 295} {} 296do_execsql_test in4-4.16 { 297 SELECT c FROM t4b WHERE b=+a; 298} {4} 299do_execsql_test in4-4.17 { 300 SELECT c FROM t4b WHERE a IN (b); 301} {} 302do_execsql_test in4-4.18 { 303 SELECT c FROM t4b WHERE b IN (a); 304} {4} 305do_execsql_test in4-4.19 { 306 SELECT c FROM t4b WHERE +b IN (a); 307} {} 308 309do_execsql_test in4-5.1 { 310 CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase); 311 INSERT INTO t5 VALUES(17, 'fuzz'); 312 SELECT 1 FROM t5 WHERE 'fuzz' IN (d); -- match 313 SELECT 2 FROM t5 WHERE 'FUZZ' IN (d); -- no match 314 SELECT 3 FROM t5 WHERE d IN ('fuzz'); -- match 315 SELECT 4 FROM t5 WHERE d IN ('FUZZ'); -- match 316} {1 3 4} 317 318# An expression of the form "x IN (y)" can be used as "x=y" by the 319# query planner when computing transitive constraints or to run the 320# query using an index on y. 321# 322do_execsql_test in4-6.1 { 323 CREATE TABLE t6a(a INTEGER PRIMARY KEY, b); 324 INSERT INTO t6a VALUES(1,2),(3,4),(5,6); 325 CREATE TABLE t6b(c INTEGER PRIMARY KEY, d); 326 INSERT INTO t6b VALUES(4,44),(5,55),(6,66); 327 328 SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); 329} {3 4 4 44} 330do_execsql_test in4-6.1-eqp { 331 EXPLAIN QUERY PLAN 332 SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); 333} {~/SCAN TABLE t6a/} 334do_execsql_test in4-6.2 { 335 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); 336} {3 4 4 44} 337do_execsql_test in4-6.2-eqp { 338 EXPLAIN QUERY PLAN 339 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); 340} {~/SCAN/} 341 342reset_db 343do_execsql_test 7.0 { 344 CREATE TABLE t1(a, b, c); 345 CREATE TABLE t2(d, e); 346 CREATE INDEX t1bc ON t1(c, b); 347 INSERT INTO t2(e) VALUES(1); 348 INSERT INTO t1 VALUES(NULL, NULL, NULL); 349} 350 351do_execsql_test 7.1 { 352 SELECT * FROM t2 LEFT JOIN t1 ON c = d AND b IN (10,10,10); 353} {{} 1 {} {} {}} 354 355ifcapable rtree { 356 reset_db 357 do_execsql_test 7.2 { 358 CREATE VIRTUAL TABLE t1 USING rtree(a, b, c); 359 CREATE TABLE t2(d INTEGER, e INT); 360 INSERT INTO t2(e) VALUES(1); 361 } 362 363 do_execsql_test 7.3 { 364 SELECT * FROM t2 LEFT JOIN t1 ON c IN (d) AND b IN (10,10,10); 365 } {{} 1 {} {} {}} 366} 367 368#------------------------------------------------------------------------- 369reset_db 370do_execsql_test 8.0 { 371 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 372 CREATE UNIQUE INDEX t1y ON t1(y); 373 INSERT INTO t1 VALUES(111, 'AAA'),(222, 'BBB'),(333, 'CCC'); 374 CREATE TABLE t2(z); 375 INSERT INTO t2 VALUES('BBB'),('AAA'); 376 ANALYZE sqlite_schema; 377 INSERT INTO sqlite_stat1 VALUES('t1', 't1y','100 1'); 378} 379 380db close 381sqlite3 db test.db 382 383do_execsql_test 8.1 { 384 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y; 385} {222 111} 386 387do_execsql_test 8.2 { 388 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND +t1.x IN (111, 222); 389} {222 111} 390 391do_execsql_test 8.3 { 392 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND t1.x IN (111, 222); 393} {222 111} 394 395 396finish_test 397