gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/whereD.test (about) 1 # 2012 August 24 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 that an index may be used as a covering 13 # index when there are OR expressions in the WHERE clause. 14 # 15 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set ::testprefix whereD 20 21 do_execsql_test 1.1 { 22 CREATE TABLE t(i,j,k,m,n); 23 CREATE INDEX ijk ON t(i,j,k); 24 CREATE INDEX jmn ON t(j,m,n); 25 26 INSERT INTO t VALUES(3, 3, 'three', 3, 'tres'); 27 INSERT INTO t VALUES(2, 2, 'two', 2, 'dos'); 28 INSERT INTO t VALUES(1, 1, 'one', 1, 'uno'); 29 INSERT INTO t VALUES(4, 4, 'four', 4, 'cuatro'); 30 } 31 32 do_execsql_test 1.2 { 33 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 34 } {one two} 35 do_execsql_test 1.3 { 36 SELECT k FROM t WHERE (i=1 AND j=1) OR (+i=2 AND j=2); 37 } {one two} 38 do_execsql_test 1.4 { 39 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 40 } {uno dos} 41 do_execsql_test 1.5 { 42 SELECT k, n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 43 } {one uno two dos} 44 do_execsql_test 1.6 { 45 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 46 } {one two three} 47 do_execsql_test 1.7 { 48 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 49 } {uno dos tres} 50 do_execsql_test 1.8 { 51 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2); 52 } {one two} 53 do_execsql_test 1.9 { 54 SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 55 } {one two three} 56 do_execsql_test 1.10 { 57 SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 58 } {uno dos tres} 59 do_execsql_test 1.11 { 60 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 61 } {one two three} 62 do_execsql_test 1.12 { 63 SELECT n FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 64 } {uno dos tres} 65 do_execsql_test 1.13 { 66 SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 67 } {one two three} 68 do_execsql_test 1.14 { 69 SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3); 70 } {one two three} 71 do_execsql_test 1.15 { 72 SELECT k FROM t WHERE (i=1 AND j=2) OR (i=2 AND j=1) OR (i=3 AND j=4); 73 } {} 74 do_execsql_test 1.16 { 75 SELECT k FROM t WHERE (i=1 AND (j=1 or j=2)) OR (i=3 AND j=3); 76 } {one three} 77 78 do_execsql_test 2.0 { 79 CREATE TABLE t1(a,b,c,d); 80 CREATE INDEX t1b ON t1(b); 81 CREATE INDEX t1c ON t1(c); 82 CREATE INDEX t1d ON t1(d); 83 CREATE TABLE t2(x,y); 84 CREATE INDEX t2y ON t2(y); 85 86 INSERT INTO t1 VALUES(1,2,3,4); 87 INSERT INTO t1 VALUES(5,6,7,8); 88 INSERT INTO t2 VALUES(1,2); 89 INSERT INTO t2 VALUES(2,7); 90 INSERT INTO t2 VALUES(3,4); 91 } {} 92 do_execsql_test 2.1 { 93 SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x; 94 } {1 3} 95 do_execsql_test 2.2 { 96 SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x; 97 } {1 3} 98 99 100 # Similar to [do_execsql_test], except that two elements are appended 101 # to the result - the string "search" and the number of times test variable 102 # sqlite3_search_count is incremented by running the supplied SQL. e.g. 103 # 104 # do_searchcount_test 1.0 { SELECT * FROM t1 } {x y search 2} 105 # 106 proc do_searchcount_test {tn sql res} { 107 uplevel [subst -nocommands { 108 do_test $tn { 109 set ::sqlite_search_count 0 110 concat [db eval {$sql}] search [set ::sqlite_search_count] 111 } [list $res] 112 }] 113 } 114 115 do_execsql_test 3.0 { 116 CREATE TABLE t3(a, b, c); 117 CREATE UNIQUE INDEX i3 ON t3(a, b); 118 INSERT INTO t3 VALUES(1, 'one', 'i'); 119 INSERT INTO t3 VALUES(3, 'three', 'iii'); 120 INSERT INTO t3 VALUES(6, 'six', 'vi'); 121 INSERT INTO t3 VALUES(2, 'two', 'ii'); 122 INSERT INTO t3 VALUES(4, 'four', 'iv'); 123 INSERT INTO t3 VALUES(5, 'five', 'v'); 124 125 CREATE TABLE t4(x PRIMARY KEY, y); 126 INSERT INTO t4 VALUES('a', 'one'); 127 INSERT INTO t4 VALUES('b', 'two'); 128 } 129 130 do_searchcount_test 3.1 { 131 SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 132 } {1 one 2 two search 4} 133 134 do_searchcount_test 3.2 { 135 SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 136 } {1 i 2 ii search 6} 137 138 do_searchcount_test 3.4.1 { 139 SELECT y FROM t4 WHERE x='a' 140 } {one search 2} 141 do_searchcount_test 3.4.2 { 142 SELECT a, b FROM t3 WHERE 143 (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 144 OR (a=2 AND b='two') 145 } {1 one 2 two search 6} 146 do_searchcount_test 3.4.3 { 147 SELECT a, b FROM t3 WHERE 148 (a=2 AND b='two') 149 OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 150 } {2 two 1 one search 6} 151 do_searchcount_test 3.4.4 { 152 SELECT a, b FROM t3 WHERE 153 (a=2 AND b=(SELECT y FROM t4 WHERE x='b')) 154 OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 155 } {2 two 1 one search 8} 156 157 do_searchcount_test 3.5.1 { 158 SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4 159 } {1 one 2 two search 2} 160 do_searchcount_test 3.5.2 { 161 SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4 162 } {1 i 2 ii search 3} 163 164 # Ticket [d02e1406a58ea02d] (2012-10-04) 165 # LEFT JOIN with an OR in the ON clause causes segfault 166 # 167 do_test 4.1 { 168 db eval { 169 CREATE TABLE t41(a,b,c); 170 INSERT INTO t41 VALUES(1,2,3), (4,5,6); 171 CREATE TABLE t42(d,e,f); 172 INSERT INTO t42 VALUES(3,6,9), (4,8,12); 173 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); 174 } 175 } {1 2 3 3 6 9 4 5 6 {} {} {}} 176 do_test 4.2 { 177 db eval { 178 CREATE INDEX t42d ON t42(d); 179 CREATE INDEX t42e ON t42(e); 180 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.e=x.b); 181 } 182 } {1 2 3 3 6 9 4 5 6 {} {} {}} 183 do_test 4.3 { 184 db eval { 185 SELECT * FROM t41 AS x LEFT JOIN t42 AS y ON (y.d=x.c) OR (y.d=x.b); 186 } 187 } {1 2 3 3 6 9 4 5 6 {} {} {}} 188 189 # Ticket [bc1aea7b725f276177] 190 # Incorrect result on LEFT JOIN with OR constraints and an ORDER BY clause. 191 # 192 do_execsql_test 4.4 { 193 CREATE TABLE t44(a INTEGER, b INTEGER); 194 INSERT INTO t44 VALUES(1,2); 195 INSERT INTO t44 VALUES(3,4); 196 SELECT * 197 FROM t44 AS x 198 LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c 199 WHERE d=4 OR d IS NULL; 200 } {3 4 3 4} 201 do_execsql_test 4.5 { 202 SELECT * 203 FROM t44 AS x 204 LEFT JOIN (SELECT a AS c, b AS d FROM t44) AS y ON a=c 205 WHERE d=4 OR d IS NULL 206 ORDER BY a; 207 } {3 4 3 4} 208 do_execsql_test 4.6 { 209 CREATE TABLE t46(c INTEGER, d INTEGER); 210 INSERT INTO t46 SELECT a, b FROM t44; 211 SELECT * FROM t44 LEFT JOIN t46 ON a=c 212 WHERE d=4 OR d IS NULL; 213 } {3 4 3 4} 214 do_execsql_test 4.7 { 215 SELECT * FROM t44 LEFT JOIN t46 ON a=c 216 WHERE d=4 OR d IS NULL 217 ORDER BY a; 218 } {3 4 3 4} 219 220 # Verify fix of a bug reported on the mailing list by Peter Reid 221 # 222 do_execsql_test 5.1 { 223 DROP TABLE IF EXISTS t; 224 CREATE TABLE t(c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17); 225 CREATE INDEX tc0 ON t(c0); 226 CREATE INDEX tc1 ON t(c1); 227 CREATE INDEX tc2 ON t(c2); 228 CREATE INDEX tc3 ON t(c3); 229 CREATE INDEX tc4 ON t(c4); 230 CREATE INDEX tc5 ON t(c5); 231 CREATE INDEX tc6 ON t(c6); 232 CREATE INDEX tc7 ON t(c7); 233 CREATE INDEX tc8 ON t(c8); 234 CREATE INDEX tc9 ON t(c9); 235 CREATE INDEX tc10 ON t(c10); 236 CREATE INDEX tc11 ON t(c11); 237 CREATE INDEX tc12 ON t(c12); 238 CREATE INDEX tc13 ON t(c13); 239 CREATE INDEX tc14 ON t(c14); 240 CREATE INDEX tc15 ON t(c15); 241 CREATE INDEX tc16 ON t(c16); 242 CREATE INDEX tc17 ON t(c17); 243 244 INSERT INTO t(c0, c16) VALUES (1,1); 245 246 SELECT * FROM t WHERE 247 c0=1 or c1=1 or c2=1 or c3=1 or 248 c4=1 or c5=1 or c6=1 or c7=1 or 249 c8=1 or c9=1 or c10=1 or c11=1 or 250 c12=1 or c13=1 or c14=1 or c15=1 or 251 c16=1 or c17=1; 252 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {}} 253 do_execsql_test 5.2 { 254 DELETE FROM t; 255 INSERT INTO t(c0,c17) VALUES(1,1); 256 SELECT * FROM t WHERE 257 c0=1 or c1=1 or c2=1 or c3=1 or 258 c4=1 or c5=1 or c6=1 or c7=1 or 259 c8=1 or c9=1 or c10=1 or c11=1 or 260 c12=1 or c13=1 or c14=1 or c15=1 or 261 c16=1 or c17=1; 262 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1} 263 do_execsql_test 5.3 { 264 DELETE FROM t; 265 INSERT INTO t(c0,c15) VALUES(1,1); 266 SELECT * FROM t WHERE 267 c0=1 or c1=1 or c2=1 or c3=1 or 268 c4=1 or c5=1 or c6=1 or c7=1 or 269 c8=1 or c9=1 or c10=1 or c11=1 or 270 c12=1 or c13=1 or c14=1 or c15=1 or 271 c16=1 or c17=1; 272 } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {} {}} 273 274 #------------------------------------------------------------------------- 275 do_execsql_test 6.1 { 276 CREATE TABLE x1(a, b, c, d, e); 277 CREATE INDEX x1a ON x1(a); 278 CREATE INDEX x1bc ON x1(b, c); 279 CREATE INDEX x1cd ON x1(c, d); 280 281 INSERT INTO x1 VALUES(1, 2, 3, 4, 'A'); 282 INSERT INTO x1 VALUES(5, 6, 7, 8, 'B'); 283 INSERT INTO x1 VALUES(9, 10, 11, 12, 'C'); 284 INSERT INTO x1 VALUES(13, 14, 15, 16, 'D'); 285 } 286 287 do_searchcount_test 6.2.1 { 288 SELECT e FROM x1 WHERE b=2 OR c=7; 289 } {A B search 6} 290 do_searchcount_test 6.2.2 { 291 SELECT c FROM x1 WHERE b=2 OR c=7; 292 } {3 7 search 4} 293 294 do_searchcount_test 6.3.1 { 295 SELECT e FROM x1 WHERE a=1 OR b=10; 296 } {A C search 6} 297 do_searchcount_test 6.3.2 { 298 SELECT c FROM x1 WHERE a=1 OR b=10; 299 } {3 11 search 5} 300 do_searchcount_test 6.3.3 { 301 SELECT rowid FROM x1 WHERE a=1 OR b=10; 302 } {1 3 search 4} 303 304 do_searchcount_test 6.4.1 { 305 SELECT a FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 306 } {1 9 search 6} 307 do_searchcount_test 6.4.2 { 308 SELECT b, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 309 } {2 3 10 11 search 5} 310 do_searchcount_test 6.4.3 { 311 SELECT rowid, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12 312 } {1 3 3 11 search 4} 313 314 do_searchcount_test 6.5.1 { 315 SELECT a FROM x1 WHERE rowid = 2 OR c=11 316 } {5 9 search 3} 317 do_searchcount_test 6.5.2 { 318 SELECT d FROM x1 WHERE rowid = 2 OR c=11 319 } {8 12 search 2} 320 do_searchcount_test 6.5.3 { 321 SELECT d FROM x1 WHERE c=11 OR rowid = 2 322 } {12 8 search 2} 323 do_searchcount_test 6.5.4 { 324 SELECT a FROM x1 WHERE c=11 OR rowid = 2 325 } {9 5 search 3} 326 327 do_searchcount_test 6.6.1 { 328 SELECT rowid FROM x1 WHERE a=1 OR b=6 OR c=11 329 } {1 2 3 search 6} 330 do_searchcount_test 6.6.2 { 331 SELECT c FROM x1 WHERE a=1 OR b=6 OR c=11 332 } {3 7 11 search 7} 333 do_searchcount_test 6.6.3 { 334 SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 335 } {11 3 7 search 7} 336 do_searchcount_test 6.6.4 { 337 SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1 338 } {7 11 3 search 7} 339 340 # 2020-02-22 ticket aa4378693018aa99 341 # In the OP_Column opcode, if a cursor is marked with OP_NullRow 342 # (because it is the right table of a LEFT JOIN that does not match) 343 # then do not substitute index cursors, as the index cursors do not 344 # have the VdbeCursor.nullRow flag set. 345 # 346 do_execsql_test 6.7 { 347 DROP TABLE IF EXISTS t1; 348 DROP TABLE IF EXISTS t2; 349 CREATE TABLE t1(a UNIQUE, b UNIQUE); 350 INSERT INTO t1(a,b) VALUES(null,2); 351 CREATE VIEW t2 AS SELECT * FROM t1 WHERE b<10 OR a<7 ORDER BY b; 352 SELECT t1.* FROM t1 LEFT JOIN t2 ON abs(t1.a)=abs(t2.b); 353 } {{} 2} 354 355 356 #------------------------------------------------------------------------- 357 # 358 do_execsql_test 7.0 { 359 CREATE TABLE y1(a, b); 360 CREATE TABLE y2(x, y); 361 CREATE INDEX y2xy ON y2(x, y); 362 INSERT INTO y1 VALUES(1, 1); 363 INSERT INTO y2 VALUES(3, 3); 364 } 365 366 do_execsql_test 7.1 { 367 SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b)) 368 } {1 1 {} {}} 369 370 do_execsql_test 7.3 { 371 CREATE TABLE foo (Id INTEGER PRIMARY KEY, fa INTEGER, fb INTEGER); 372 CREATE TABLE bar (Id INTEGER PRIMARY KEY, ba INTEGER, bb INTEGER); 373 374 INSERT INTO foo VALUES(1, 1, 1); 375 INSERT INTO foo VALUES(2, 1, 2); 376 INSERT INTO foo VALUES(3, 1, 3); 377 INSERT INTO foo VALUES(4, 1, 4); 378 INSERT INTO foo VALUES(5, 1, 5); 379 INSERT INTO foo VALUES(6, 1, 6); 380 INSERT INTO foo VALUES(7, 1, 7); 381 INSERT INTO foo VALUES(8, 1, 8); 382 INSERT INTO foo VALUES(9, 1, 9); 383 384 INSERT INTO bar VALUES(NULL, 1, 1); 385 INSERT INTO bar VALUES(NULL, 2, 2); 386 INSERT INTO bar VALUES(NULL, 3, 3); 387 INSERT INTO bar VALUES(NULL, 1, 4); 388 INSERT INTO bar VALUES(NULL, 2, 5); 389 INSERT INTO bar VALUES(NULL, 3, 6); 390 INSERT INTO bar VALUES(NULL, 1, 7); 391 INSERT INTO bar VALUES(NULL, 2, 8); 392 INSERT INTO bar VALUES(NULL, 3, 9); 393 } 394 395 do_execsql_test 7.4 { 396 SELECT 397 bar.Id, bar.ba, bar.bb, foo.fb 398 FROM foo LEFT JOIN bar 399 ON (bar.ba = 1 AND bar.bb = foo.fb) 400 OR (bar.ba = 5 AND bar.bb = foo.fb); 401 } { 402 1 1 1 1 403 {} {} {} 2 404 {} {} {} 3 405 4 1 4 4 406 {} {} {} 5 407 {} {} {} 6 408 7 1 7 7 409 {} {} {} 8 410 {} {} {} 9 411 } 412 413 do_execsql_test 7.5 { 414 CREATE INDEX idx_bar ON bar(ba, bb); 415 SELECT 416 bar.Id, bar.ba, bar.bb, foo.fb 417 FROM foo LEFT JOIN bar 418 ON (bar.ba = 1 AND bar.bb = foo.fb) 419 OR (bar.ba = 5 AND bar.bb = foo.fb); 420 } { 421 1 1 1 1 422 {} {} {} 2 423 {} {} {} 3 424 4 1 4 4 425 {} {} {} 5 426 {} {} {} 6 427 7 1 7 7 428 {} {} {} 8 429 {} {} {} 9 430 } 431 432 433 finish_test