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} {1} 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)); 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 CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y)); 204 } 205 execsql { 206 SELECT * 207 FROM t2 LEFT JOIN t4 b1 208 LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y); 209 } 210} {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}} 211do_test where4-5.2 { 212 execsql { 213 INSERT INTO t4 VALUES(1,1,11); 214 INSERT INTO t4 VALUES(1,2,12); 215 INSERT INTO t4 VALUES(1,3,13); 216 INSERT INTO t4 VALUES(2,2,22); 217 SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13; 218 } 219} {1 2 4} 220do_test where4-5.3 { 221 execsql { 222 SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13; 223 } 224} {1 2 4} 225do_test where4-6.1 { 226 execsql { 227 CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f)); 228 INSERT INTO t5 VALUES(1,1,1,1,1,11111); 229 INSERT INTO t5 VALUES(2,2,2,2,2,22222); 230 INSERT INTO t5 VALUES(1,2,3,4,5,12345); 231 INSERT INTO t5 VALUES(2,3,4,5,6,23456); 232 } 233 execsql { 234 SELECT rowid FROM t5 235 WHERE a IN (1,9,2) AND b=2 AND c IN (1,2,3,4) AND d>0 236 } 237} {3 2} 238do_test where4-6.2 { 239 execsql { 240 SELECT rowid FROM t5 241 WHERE a IN (1,NULL,2) AND b=2 AND c IN (1,2,3,4) AND d>0 242 } 243} {3 2} 244do_test where4-7.1 { 245 execsql { 246 CREATE TABLE t6(y,z,PRIMARY KEY(y,z)); 247 } 248 execsql { 249 SELECT * FROM t6 WHERE y=NULL AND z IN ('hello'); 250 } 251} {} 252 253integrity_check {where4-99.0} 254 255do_test where4-7.1 { 256 execsql { 257 BEGIN; 258 CREATE TABLE t8(a, b, c, d); 259 CREATE INDEX t8_i ON t8(a, b, c); 260 CREATE TABLE t7(i); 261 262 INSERT INTO t7 VALUES(1); 263 INSERT INTO t7 SELECT i*2 FROM t7; 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 270 COMMIT; 271 } 272} {} 273 274# At one point the sub-select inside the aggregate sum() function in the 275# following query was leaking a couple of stack entries. This query 276# runs the SELECT in a loop enough times that an assert() fails. Or rather, 277# did fail before the bug was fixed. 278# 279do_test where4-7.2 { 280 execsql { 281 SELECT sum(( 282 SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL 283 )) FROM t7; 284 } 285} {{}} 286 287}; #ifcapable subquery 288 289#------------------------------------------------------------------------- 290# Verify that "IS ?" with a NULL bound to the variable also functions 291# correctly. 292 293unset -nocomplain null 294 295do_execsql_test 8.1 { 296 CREATE TABLE u9(a UNIQUE, b); 297 INSERT INTO u9 VALUES(NULL, 1); 298 INSERT INTO u9 VALUES(NULL, 2); 299} 300do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2} 301do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2} 302 303 304 305 306finish_test 307 308