gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/where4.test (about) 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 20 set testdir [file dirname $argv0] 21 source $testdir/tester.tcl 22 set testprefix where4 23 24 ifcapable !tclvar||!bloblit { 25 finish_test 26 return 27 } 28 29 # Build some test data 30 # 31 do_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 # 48 proc 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 # 58 do_test where4-1.1 { 59 count {SELECT rowid FROM t1 WHERE w IS NULL} 60 } {7 2} 61 do_test where4-1.1b { 62 unset -nocomplain null 63 count {SELECT rowid FROM t1 WHERE w IS $null} 64 } {7 2} 65 do_test where4-1.2 { 66 count {SELECT rowid FROM t1 WHERE +w IS NULL} 67 } {7 6} 68 do_test where4-1.3 { 69 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL} 70 } {2 2} 71 do_test where4-1.4 { 72 count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL} 73 } {2 3} 74 do_test where4-1.5 { 75 count {SELECT rowid FROM t1 WHERE w=1 AND x>0} 76 } {1 2} 77 do_test where4-1.6 { 78 count {SELECT rowid FROM t1 WHERE w=1 AND x<9} 79 } {1 2} 80 do_test where4-1.7 { 81 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3} 82 } {2 2} 83 do_test where4-1.8 { 84 count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2} 85 } {2 2} 86 do_test where4-1.9 { 87 count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'} 88 } {4 2} 89 do_test where4-1.10 { 90 count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL} 91 } {6 2} 92 do_test where4-1.11 { 93 count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123} 94 } {0} 95 do_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} 98 do_test where4-1.13 { 99 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL} 100 } {7 2} 101 do_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} 104 do_test where4-1.15 { 105 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y<0} 106 } {1} 107 do_test where4-1.16 { 108 count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y>=0} 109 } {1} 110 111 do_test where4-2.1 { 112 execsql {SELECT rowid FROM t1 ORDER BY w, x, y} 113 } {7 2 1 4 3 6 5} 114 do_test where4-2.2 { 115 execsql {SELECT rowid FROM t1 ORDER BY w DESC, x, y} 116 } {6 5 4 3 2 1 7} 117 do_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 # 133 do_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 {} {}} 146 do_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 {} {}} 151 do_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 {} {}} 156 do_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 # 165 do_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 } {} 179 do_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 } {} 185 do_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 } {} 191 do_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 # 200 ifcapable subquery { 201 do_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 {} {} {} {} {} {}} 212 do_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} 221 do_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} 226 do_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} 239 do_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} 245 do_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 254 integrity_check {where4-99.0} 255 256 do_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 # 280 do_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 294 unset -nocomplain null 295 296 do_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 } 301 do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2} 302 do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2} 303 304 305 306 307 finish_test