1# 2006 October 27 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 use of indices in WHERE clauses. 13# This file was created when support for optimizing IS NULL phrases 14# was added. And so the principle purpose of this file is to test 15# that IS NULL phrases are correctly optimized. But you can never 16# have too many tests, so some other tests are thrown in as well. 17# 18# $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $ 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22set testprefix where4 23 24ifcapable !tclvar||!bloblit { 25 finish_test 26 return 27} 28 29# Build some test data 30# 31do_test where4-1.0 { 32 execsql { 33 CREATE TABLE t1(w, x, y); 34 CREATE INDEX i1wxy ON t1(w,x,y); 35 INSERT INTO t1 VALUES(1,2,3); 36 INSERT INTO t1 VALUES(1,NULL,3); 37 INSERT INTO t1 VALUES('a','b','c'); 38 INSERT INTO t1 VALUES('a',NULL,'c'); 39 INSERT INTO t1 VALUES(X'78',x'79',x'7a'); 40 INSERT INTO t1 VALUES(X'78',NULL,X'7A'); 41 INSERT INTO t1 VALUES(NULL,NULL,NULL); 42 SELECT count(*) FROM t1; 43 } 44} {7} 45 46# Do an SQL statement. Append the search count to the end of the result. 47# 48proc count sql { 49 set ::sqlite_search_count 0 50 return [concat [execsql $sql] $::sqlite_search_count] 51} 52 53# Verify that queries use an index. We are using the special variable 54# "sqlite_search_count" which tallys the number of executions of MoveTo 55# and Next operators in the VDBE. By verifing that the search count is 56# small we can be assured that indices are being used properly. 57# 58do_test where4-1.1 { 59 count {SELECT rowid FROM t1 WHERE w IS NULL} 60} {7 2} 61do_test where4-1.1b { 62 unset -nocomplain null 63 count {SELECT rowid FROM t1 WHERE w IS $null} 64} {7 2} 65do_test where4-1.2 { 66 count {SELECT rowid FROM t1 WHERE +w IS NULL} 67} {7 6} 68do_test where4-1.3 { 69 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL} 70} {2 2} 71do_test where4-1.4 { 72 count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL} 73} {2 3} 74do_test where4-1.5 { 75 count {SELECT rowid FROM t1 WHERE w=1 AND x>0} 76} {1 2} 77do_test where4-1.6 { 78 count {SELECT rowid FROM t1 WHERE w=1 AND x<9} 79} {1 2} 80do_test where4-1.7 { 81 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3} 82} {2 2} 83do_test where4-1.8 { 84 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2} 85} {2 2} 86do_test where4-1.9 { 87 count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'} 88} {4 2} 89do_test where4-1.10 { 90 count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL} 91} {6 2} 92do_test where4-1.11 { 93 count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123} 94} {0} 95do_test where4-1.12 { 96 count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'} 97} {6 2} 98do_test where4-1.13 { 99 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL} 100} {7 2} 101do_test where4-1.14 { 102 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y IS NULL} 103} {7 2} 104do_test where4-1.15 { 105 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y<0} 106} {1} 107do_test where4-1.16 { 108 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y>=0} 109} {1} 110 111do_test where4-2.1 { 112 execsql {SELECT rowid FROM t1 ORDER BY w, x, y} 113} {7 2 1 4 3 6 5} 114do_test where4-2.2 { 115 execsql {SELECT rowid FROM t1 ORDER BY w DESC, x, y} 116} {6 5 4 3 2 1 7} 117do_test where4-2.3 { 118 execsql {SELECT rowid FROM t1 ORDER BY w, x DESC, y} 119} {7 1 2 3 4 5 6} 120 121 122# Ticket #2177 123# 124# Suppose you have a left join where the right table of the left 125# join (the one that can be NULL) has an index on two columns. 126# The first indexed column is used in the ON clause of the join. 127# The second indexed column is used in the WHERE clause with an IS NULL 128# constraint. It is not allowed to use the IS NULL optimization to 129# optimize the query because the second column might be NULL because 130# the right table did not match - something the index does not know 131# about. 132# 133do_test where4-3.1 { 134 execsql { 135 CREATE TABLE t2(a); 136 INSERT INTO t2 VALUES(1); 137 INSERT INTO t2 VALUES(2); 138 INSERT INTO t2 VALUES(3); 139 CREATE TABLE t3(x,y,UNIQUE("x",'y' ASC)); -- Goofy syntax allowed 140 INSERT INTO t3 VALUES(1,11); 141 INSERT INTO t3 VALUES(2,NULL); 142 143 SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL; 144 } 145} {2 2 {} 3 {} {}} 146do_test where4-3.2 { 147 execsql { 148 SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL; 149 } 150} {2 2 {} 3 {} {}} 151do_test where4-3.3 { 152 execsql { 153 SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y; 154 } 155} {2 2 {} 3 {} {}} 156do_test where4-3.4 { 157 unset -nocomplain null 158 execsql { 159 SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null; 160 } 161} {2 2 {} 3 {} {}} 162 163# Ticket #2189. Probably the same bug as #2177. 164# 165do_test where4-4.1 { 166 execsql { 167 CREATE TABLE test(col1 TEXT PRIMARY KEY); 168 INSERT INTO test(col1) values('a'); 169 INSERT INTO test(col1) values('b'); 170 INSERT INTO test(col1) values('c'); 171 CREATE TABLE test2(col1 TEXT PRIMARY KEY); 172 INSERT INTO test2(col1) values('a'); 173 INSERT INTO test2(col1) values('b'); 174 INSERT INTO test2(col1) values('c'); 175 SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 176 WHERE +t2.col1 IS NULL; 177 } 178} {} 179do_test where4-4.2 { 180 execsql { 181 SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 182 WHERE t2.col1 IS NULL; 183 } 184} {} 185do_test where4-4.3 { 186 execsql { 187 SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 188 WHERE +t1.col1 IS NULL; 189 } 190} {} 191do_test where4-4.4 { 192 execsql { 193 SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 194 WHERE t1.col1 IS NULL; 195 } 196} {} 197 198# Ticket #2273. Problems with IN operators and NULLs. 199# 200ifcapable subquery { 201do_test where4-5.1 { 202 execsql { 203 -- Allow the 'x' syntax for backwards compatibility 204 CREATE TABLE t4(x,y,z,PRIMARY KEY('x' ASC, "y" ASC)); 205 } 206 execsql { 207 SELECT * 208 FROM t2 LEFT JOIN t4 b1 209 LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y); 210 } 211} {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}} 212do_test where4-5.2 { 213 execsql { 214 INSERT INTO t4 VALUES(1,1,11); 215 INSERT INTO t4 VALUES(1,2,12); 216 INSERT INTO t4 VALUES(1,3,13); 217 INSERT INTO t4 VALUES(2,2,22); 218 SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13; 219 } 220} {1 2 4} 221do_test where4-5.3 { 222 execsql { 223 SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13; 224 } 225} {1 2 4} 226do_test where4-6.1 { 227 execsql { 228 CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f)); 229 INSERT INTO t5 VALUES(1,1,1,1,1,11111); 230 INSERT INTO t5 VALUES(2,2,2,2,2,22222); 231 INSERT INTO t5 VALUES(1,2,3,4,5,12345); 232 INSERT INTO t5 VALUES(2,3,4,5,6,23456); 233 } 234 execsql { 235 SELECT rowid FROM t5 236 WHERE a IN (1,9,2) AND b=2 AND c IN (1,2,3,4) AND d>0 237 } 238} {3 2} 239do_test where4-6.2 { 240 execsql { 241 SELECT rowid FROM t5 242 WHERE a IN (1,NULL,2) AND b=2 AND c IN (1,2,3,4) AND d>0 243 } 244} {3 2} 245do_test where4-7.1 { 246 execsql { 247 CREATE TABLE t6(y,z,PRIMARY KEY(y,z)); 248 } 249 execsql { 250 SELECT * FROM t6 WHERE y=NULL AND z IN ('hello'); 251 } 252} {} 253 254integrity_check {where4-99.0} 255 256do_test where4-7.1 { 257 execsql { 258 BEGIN; 259 CREATE TABLE t8(a, b, c, d); 260 CREATE INDEX t8_i ON t8(a, b, c); 261 CREATE TABLE t7(i); 262 263 INSERT INTO t7 VALUES(1); 264 INSERT INTO t7 SELECT i*2 FROM t7; 265 INSERT INTO t7 SELECT i*2 FROM t7; 266 INSERT INTO t7 SELECT i*2 FROM t7; 267 INSERT INTO t7 SELECT i*2 FROM t7; 268 INSERT INTO t7 SELECT i*2 FROM t7; 269 INSERT INTO t7 SELECT i*2 FROM t7; 270 271 COMMIT; 272 } 273} {} 274 275# At one point the sub-select inside the aggregate sum() function in the 276# following query was leaking a couple of stack entries. This query 277# runs the SELECT in a loop enough times that an assert() fails. Or rather, 278# did fail before the bug was fixed. 279# 280do_test where4-7.2 { 281 execsql { 282 SELECT sum(( 283 SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL 284 )) FROM t7; 285 } 286} {{}} 287 288}; #ifcapable subquery 289 290#------------------------------------------------------------------------- 291# Verify that "IS ?" with a NULL bound to the variable also functions 292# correctly. 293 294unset -nocomplain null 295 296do_execsql_test 8.1 { 297 CREATE TABLE u9(a UNIQUE, b); 298 INSERT INTO u9 VALUES(NULL, 1); 299 INSERT INTO u9 VALUES(NULL, 2); 300} 301do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2} 302do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2} 303 304 305 306 307finish_test 308