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