gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/where8.test (about) 1 # 2008 December 23 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 focus 12 # is testing of where.c. More specifically, the focus is the optimization 13 # of WHERE clauses that feature the OR operator. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 if {[permutation]=="sorterref"} { 20 finish_test 21 return 22 } 23 24 # Test organization: 25 # 26 # where8-1.*: Tests to demonstrate simple cases work with a single table 27 # in the FROM clause. 28 # 29 # where8-2.*: Tests surrounding virtual tables and the OR optimization. 30 # 31 # where8-3.*: Tests with more than one table in the FROM clause. 32 # 33 34 proc execsql_status {sql {db db}} { 35 set result [uplevel $db eval [list $sql]] 36 concat $result [db status step] [db status sort] 37 } 38 39 proc execsql_status2 {sql {db db}} { 40 set ::sqlite_search_count 0 41 set result [uplevel [list execsql_status $sql $db]] 42 concat $result $::sqlite_search_count 43 } 44 45 do_test where8-1.1 { 46 execsql { 47 CREATE TABLE t1(a, b TEXT, c); 48 CREATE INDEX i1 ON t1(a); 49 CREATE INDEX i2 ON t1(b); 50 51 INSERT INTO t1 VALUES(1, 'one', 'I'); 52 INSERT INTO t1 VALUES(2, 'two', 'II'); 53 INSERT INTO t1 VALUES(3, 'three', 'III'); 54 INSERT INTO t1 VALUES(4, 'four', 'IV'); 55 INSERT INTO t1 VALUES(5, 'five', 'V'); 56 INSERT INTO t1 VALUES(6, 'six', 'VI'); 57 INSERT INTO t1 VALUES(7, 'seven', 'VII'); 58 INSERT INTO t1 VALUES(8, 'eight', 'VIII'); 59 INSERT INTO t1 VALUES(9, 'nine', 'IX'); 60 INSERT INTO t1 VALUES(10, 'ten', 'X'); 61 } 62 } {} 63 64 do_test where8-1.2 { 65 execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' } 66 } {I IX 0 0 6} 67 68 do_test where8-1.3 { 69 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' } 70 } {IX X II 0 0 6} 71 72 ifcapable like_match_blobs { 73 do_test where8-1.4a { 74 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } 75 } {IX X III II 0 0 10} 76 do_test where8-1.5a { 77 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } 78 } {IX X V IV 0 0 10} 79 } else { 80 do_test where8-1.4b { 81 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } 82 } {IX X III II 0 0 9} 83 do_test where8-1.5 { 84 execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } 85 } {IX X V IV 0 0 9} 86 } 87 88 do_test where8-1.6 { 89 execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid } 90 } {I III 0 1} 91 92 do_test where8-1.7 { 93 execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a } 94 } {I III 0 1} 95 96 do_test where8-1.8 { 97 # 18 searches. 9 on the index cursor and 9 on the table cursor. 98 execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' } 99 } {II III IV IX 0 0 18} 100 101 do_test where8-1.9 { 102 execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' } 103 } {IX X VIII 0 0 7} 104 105 do_test where8-1.10 { 106 execsql_status2 { 107 SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight' 108 } 109 } {IX VIII 0 0 7} 110 111 do_test where8-1.11 { 112 execsql_status2 { 113 SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine' 114 } 115 } {IV V VI IX 0 0 10} 116 117 do_test where8-1.12.1 { 118 execsql_status2 { 119 SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5 120 } 121 } {I II III V 0 0 14} 122 123 do_test where8-1.12.2 { 124 execsql_status2 { 125 SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5 126 } 127 } {I II III V 9 0 9} 128 129 do_test where8-1.13 { 130 execsql_status2 { 131 SELECT c FROM t1 132 WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 133 ORDER BY rowid 134 } 135 } {II III IV V VI 0 1 18} 136 do_test where8-1.14 { 137 execsql_status2 { 138 SELECT c FROM t1 139 WHERE 140 a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR 141 b = 'seven' OR a = 8 OR b = 'nine' OR a = 10 142 ORDER BY rowid 143 } 144 } {II III IV V VI VII VIII IX X 0 1 33} 145 146 do_test where8-1.15 { 147 execsql_status2 { 148 SELECT c FROM t1 WHERE 149 a BETWEEN 2 AND 4 OR b = 'nine' 150 ORDER BY rowid 151 } 152 } {II III IV IX 0 1 12} 153 154 155 #-------------------------------------------------------------------------- 156 # Tests where8-2.*: Virtual tables 157 # 158 159 if 0 { 160 ifcapable vtab { 161 # Register the 'echo' module used for testing virtual tables. 162 # 163 register_echo_module [sqlite3_connection_pointer db] 164 165 do_test where8-2.1 { 166 execsql { 167 CREATE VIRTUAL TABLE e1 USING echo(t1); 168 SELECT b FROM e1; 169 } 170 } {one two three four five six seven eight nine ten} 171 172 do_test where8-2.2.1 { 173 set echo_module "" 174 execsql { 175 SELECT c FROM e1 WHERE a=1 OR b='three'; 176 } 177 } {I III} 178 do_test where8-2.2.2 { 179 set echo_module 180 } {TODO: What should this be?} 181 } 182 } 183 184 #-------------------------------------------------------------------------- 185 # Tests where8-3.*: Cases with multiple tables in the FROM clause. 186 # 187 do_test where8-3.1 { 188 execsql { 189 CREATE TABLE t2(d, e, f); 190 CREATE INDEX i3 ON t2(d); 191 CREATE INDEX i4 ON t2(e); 192 193 INSERT INTO t2 VALUES(1, NULL, 'I'); 194 INSERT INTO t2 VALUES(2, 'four', 'IV'); 195 INSERT INTO t2 VALUES(3, NULL, 'IX'); 196 INSERT INTO t2 VALUES(4, 'sixteen', 'XVI'); 197 INSERT INTO t2 VALUES(5, NULL, 'XXV'); 198 INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI'); 199 INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX'); 200 INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV'); 201 INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX'); 202 INSERT INTO t2 VALUES(10, NULL, 'C'); 203 } 204 } {} 205 206 do_test where8-3.2 { 207 execsql_status { 208 SELECT a, d FROM t1, t2 WHERE b=e 209 } 210 } {4 2 9 0} 211 212 do_test where8-3.3 { 213 execsql_status { 214 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6 215 } 216 } {2 6 3 6 0 0} 217 218 do_test where8-3.4 { 219 execsql_status { 220 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a 221 } 222 } {2 2 3 3 0 0} 223 224 do_test where8-3.5 { 225 execsql_status { 226 SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen') 227 ORDER BY +a, +d; 228 } 229 } {2 2 2 4 3 3 3 4 0 1} 230 231 do_test where8-3.6 { 232 # The first part of the WHERE clause in this query, (a=2 OR a=3) is 233 # transformed into "a IN (2, 3)". This is why the sort is required. 234 # 235 execsql_status { 236 SELECT a, d 237 FROM t1, t2 238 WHERE (a = 2 OR a = 3) AND (d = +a OR e = 'sixteen') 239 ORDER BY t1.rowid 240 } 241 } {2 2 2 4 3 3 3 4 0 1} 242 do_test where8-3.7 { 243 execsql_status { 244 SELECT a, d 245 FROM t1, t2 246 WHERE a = 2 AND (d = a OR e = 'sixteen') 247 ORDER BY t1.rowid 248 } 249 } {/2 2 2 4 0 [01]/} 250 do_test where8-3.8 { 251 execsql_status { 252 SELECT a, d 253 FROM t1, t2 254 WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen') 255 ORDER BY t1.rowid 256 } 257 } {2 2 2 4 3 3 3 4 0 1} 258 259 do_test where8-3.9 { 260 # The "OR c = 'IX'" term forces a linear scan. 261 execsql_status { 262 SELECT a, d 263 FROM t1, t2 264 WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen') 265 ORDER BY t1.rowid 266 } 267 } {2 2 2 4 3 3 3 4 9 9 9 4 9 0} 268 269 do_test where8-3.10 { 270 execsql_status { 271 SELECT d FROM t2 WHERE e IS NULL OR e = 'four' 272 } 273 } {1 3 5 10 2 0 0} 274 275 do_test where8-3.11 { 276 execsql_status { 277 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a 278 } 279 } {1 1 2 2 3 3 4 2 4 4 0 0} 280 do_test where8-3.12 { 281 execsql_status { 282 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a 283 } 284 } {1 1 2 2 3 3 4 2 4 4 9 0} 285 do_test where8-3.13 { 286 execsql_status { 287 SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 288 } 289 } {1 1 2 2 3 3 4 2 4 4 9 0} 290 291 do_test where8-3.14 { 292 execsql_status { 293 SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5 294 } 295 } {IV V 9 0} 296 297 do_test where8-3.15 { 298 execsql_status { 299 SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = ( 300 SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d 301 ) 302 ORDER BY c 303 } 304 } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1} 305 306 307 do_test where8-3.21 { 308 execsql_status { 309 SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a 310 } 311 } {1 1 2 2 3 3 4 2 4 4 0 0} 312 do_test where8-3.21.1 { 313 execsql_status { 314 SELECT a, d FROM t1, ((t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a 315 } 316 } {1 1 2 2 3 3 4 2 4 4 0 0} 317 if {[permutation] != "no_optimization"} { 318 do_test where8-3.21.2 { 319 execsql_status { 320 SELECT a, d FROM t1, ((SELECT * FROM t2)) AS t3 WHERE (a=d OR b=e) AND a<5 ORDER BY a 321 } 322 } {1 1 2 2 3 3 4 2 4 4 0 0} 323 } 324 do_test where8-3.22 { 325 execsql_status { 326 SELECT a, d FROM ((((((t1))), (((t2)))))) 327 WHERE (a=d OR b=e) AND a<5 ORDER BY a 328 } 329 } {1 1 2 2 3 3 4 2 4 4 0 0} 330 if {[permutation] != "no_optimization"} { 331 do_test where8-3.23 { 332 execsql_status { 333 SELECT * FROM ((SELECT * FROM t2)) AS t3; 334 } 335 } {1 {} I 2 four IV 3 {} IX 4 sixteen XVI 5 {} XXV 6 thirtysix XXXVI 7 fortynine XLIX 8 sixtyeight LXIV 9 eightyone LXXXIX 10 {} C 9 0} 336 } 337 338 #----------------------------------------------------------------------- 339 # The following tests - where8-4.* - verify that adding or removing 340 # indexes does not change the results returned by various queries. 341 # 342 do_test where8-4.1 { 343 execsql { 344 BEGIN; 345 CREATE TABLE t3(a INTEGER, b REAL, c TEXT); 346 CREATE TABLE t4(f INTEGER, g REAL, h TEXT); 347 INSERT INTO t3 VALUES('hills', NULL, 1415926535); 348 INSERT INTO t3 VALUES('and', 'of', NULL); 349 INSERT INTO t3 VALUES('have', 'towering', 53594.08128); 350 INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not'); 351 INSERT INTO t3 VALUES('same', 5028841971, NULL); 352 INSERT INTO t3 VALUES('onlookers', 'in', 8214808651); 353 INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL); 354 INSERT INTO t3 VALUES(1415926535, 'of', 'are'); 355 INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots'); 356 INSERT INTO t3 VALUES('over', 'the', 8628034825); 357 INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823); 358 INSERT INTO t3 VALUES('onlookers', 'same', 'and'); 359 INSERT INTO t3 VALUES(NULL, 'light', 6939937510); 360 INSERT INTO t3 VALUES('from', 'their', 'viewed'); 361 INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots'); 362 INSERT INTO t3 VALUES('from', 'sometimes', 'unalike'); 363 INSERT INTO t3 VALUES(1339.360726, 'light', 'have'); 364 INSERT INTO t3 VALUES(6939937510, 3282306647, 'other'); 365 INSERT INTO t3 VALUES('paintings', 8628034825, 'all'); 366 INSERT INTO t3 VALUES('paintings', NULL, 'same'); 367 INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike'); 368 INSERT INTO t3 VALUES('Alpine', NULL, 'same'); 369 INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over'); 370 INSERT INTO t3 VALUES('villages', 'their', 'have'); 371 INSERT INTO t3 VALUES('unalike', 'remarkably', 'in'); 372 INSERT INTO t3 VALUES('and', 8979323846, 'and'); 373 INSERT INTO t3 VALUES(NULL, 1415926535, 'an'); 374 INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502); 375 INSERT INTO t3 VALUES('all', 3421170679, 'the'); 376 INSERT INTO t3 VALUES('Not', 'and', 1415926535); 377 INSERT INTO t3 VALUES('of', 'other', 'light'); 378 INSERT INTO t3 VALUES(NULL, 'towering', 'Not'); 379 INSERT INTO t3 VALUES(346.0348610, NULL, 'other'); 380 INSERT INTO t3 VALUES('Not', 378678316.5, NULL); 381 INSERT INTO t3 VALUES('snapshots', 8628034825, 'of'); 382 INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and'); 383 INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971); 384 INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots'); 385 INSERT INTO t3 VALUES('writings', 8979323846, 8979323846); 386 INSERT INTO t3 VALUES('onlookers', 'his', 'in'); 387 INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726); 388 INSERT INTO t3 VALUES('of', 'Alpine', 'and'); 389 INSERT INTO t3 VALUES('onlookers', NULL, 'from'); 390 INSERT INTO t3 VALUES('writings', 'it', 1339.360726); 391 INSERT INTO t3 VALUES('it', 'and', 'villages'); 392 INSERT INTO t3 VALUES('an', 'the', 'villages'); 393 INSERT INTO t3 VALUES(8214808651, 8214808651, 'same'); 394 INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535); 395 INSERT INTO t3 VALUES(NULL, 8979323846, 'and'); 396 INSERT INTO t3 VALUES(NULL, 'same', 1339.360726); 397 INSERT INTO t4 VALUES('his', 'from', 'an'); 398 INSERT INTO t4 VALUES('snapshots', 'or', NULL); 399 INSERT INTO t4 VALUES('Alpine', 'have', 'it'); 400 INSERT INTO t4 VALUES('have', 'peak', 'remarkably'); 401 INSERT INTO t4 VALUES('hills', NULL, 'Not'); 402 INSERT INTO t4 VALUES('same', 'from', 2643383279); 403 INSERT INTO t4 VALUES('have', 'angle', 8628034825); 404 INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823); 405 INSERT INTO t4 VALUES(0938446095, 'peak', 'of'); 406 INSERT INTO t4 VALUES(8628034825, 'and', 'same'); 407 INSERT INTO t4 VALUES('and', 271.2019091, 'their'); 408 INSERT INTO t4 VALUES('the', 'of', 'remarkably'); 409 INSERT INTO t4 VALUES('and', 3421170679, 1415926535); 410 INSERT INTO t4 VALUES('and', 'in', 'all'); 411 INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots'); 412 INSERT INTO t4 VALUES('it', 'are', 'have'); 413 INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5); 414 INSERT INTO t4 VALUES('from', 1415926535, 8628034825); 415 INSERT INTO t4 VALUES('snapshots', 'angle', 'have'); 416 INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not'); 417 INSERT INTO t4 VALUES('peak', NULL, 0.4811174502); 418 INSERT INTO t4 VALUES('same', 'have', 'Alpine'); 419 INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095); 420 INSERT INTO t4 VALUES(8979323846, 'his', 'an'); 421 INSERT INTO t4 VALUES(NULL, 'and', 3282306647); 422 INSERT INTO t4 VALUES('remarkably', NULL, 'Not'); 423 INSERT INTO t4 VALUES('villages', 4543.266482, 'his'); 424 INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers'); 425 INSERT INTO t4 VALUES(1339.360726, 'of', 'the'); 426 INSERT INTO t4 VALUES('peak', 'other', 'peak'); 427 INSERT INTO t4 VALUES('it', 'or', 8979323846); 428 INSERT INTO t4 VALUES('onlookers', 'Not', 'towering'); 429 INSERT INTO t4 VALUES(NULL, 'peak', 'Not'); 430 INSERT INTO t4 VALUES('of', 'have', 6939937510); 431 INSERT INTO t4 VALUES('light', 'hills', 0.4811174502); 432 INSERT INTO t4 VALUES(5028841971, 'Not', 'it'); 433 INSERT INTO t4 VALUES('and', 'Not', NULL); 434 INSERT INTO t4 VALUES(346.0348610, 'villages', NULL); 435 INSERT INTO t4 VALUES(8979323846, NULL, 6939937510); 436 INSERT INTO t4 VALUES('an', 'light', 'peak'); 437 INSERT INTO t4 VALUES(5028841971, 6939937510, 'light'); 438 INSERT INTO t4 VALUES('sometimes', 'peak', 'peak'); 439 INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an'); 440 INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine'); 441 INSERT INTO t4 VALUES('from', 'of', 'all'); 442 INSERT INTO t4 VALUES(0938446095, 'same', NULL); 443 INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL); 444 INSERT INTO t4 VALUES('his', 'of', 378678316.5); 445 INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647); 446 INSERT INTO t4 VALUES('hills', 'all', 'peak'); 447 CREATE TABLE t5(s); 448 INSERT INTO t5 VALUES('tab-t5'); 449 CREATE TABLE t6(t); 450 INSERT INTO t6 VALUES(123456); 451 COMMIT; 452 } 453 } {} 454 455 catch {unset results} 456 catch {unset A} 457 catch {unset B} 458 459 set A 2 460 foreach idxsql { 461 { 462 /* No indexes */ 463 } { 464 CREATE INDEX i5 ON t3(a); 465 } { 466 CREATE INDEX i5 ON t3(a, b); 467 CREATE INDEX i6 ON t4(f); 468 } { 469 CREATE UNIQUE INDEX i5 ON t3(a, b); 470 CREATE INDEX i7 ON t3(c); 471 CREATE INDEX i6 ON t4(f); 472 CREATE INDEX i8 ON t4(h); 473 } { 474 CREATE INDEX i5 ON t3(a, b, c); 475 CREATE INDEX i6 ON t4(f, g, h); 476 CREATE INDEX i7 ON t3(c, b, a); 477 CREATE INDEX i8 ON t4(h, g, f); 478 } 479 } { 480 481 execsql { 482 DROP INDEX IF EXISTS i5; 483 DROP INDEX IF EXISTS i6; 484 DROP INDEX IF EXISTS i7; 485 DROP INDEX IF EXISTS i8; 486 } 487 execsql $idxsql 488 489 foreach {B sql} { 490 1 { SELECT * FROM t3 WHERE c LIKE b } 491 2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' } 492 3 { SELECT * FROM t3 WHERE rowid LIKE '12%' } 493 4 { SELECT * FROM t3 WHERE +c LIKE 'the%' } 494 5 { SELECT * FROM t3 WHERE c LIKE 'the%' } 495 6 { SELECT * FROM t3 WHERE c GLOB '*llo' } 496 497 7 { SELECT * FROM t3 WHERE a = 'angle' } 498 8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 } 499 9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f } 500 10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h } 501 11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL } 502 12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 } 503 504 13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 } 505 14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL } 506 15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' } 507 16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a } 508 17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' } 509 18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 } 510 19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 } 511 20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' } 512 21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' } 513 22 { SELECT * FROM t3 WHERE b = b AND a > 'are' } 514 23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' } 515 24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' } 516 25 { SELECT * FROM t3 WHERE b = 'from' } 517 26 { SELECT * FROM t3 WHERE b = 4543.266482 } 518 27 { SELECT * FROM t3 WHERE c < 3282306647 } 519 28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c } 520 29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a } 521 30 { SELECT * FROM t3 WHERE c = 'peak' } 522 31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b } 523 32 { SELECT * FROM t3 WHERE 'writings' <= b } 524 33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 } 525 34 { SELECT * FROM t3 WHERE a IS NULL } 526 35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 } 527 36 { SELECT * FROM t3 WHERE 'and' >= c } 528 37 { SELECT * FROM t3 WHERE c < 'from' } 529 38 { SELECT * FROM t3 WHERE 'his' < c OR b < b } 530 39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b } 531 40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b } 532 41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a } 533 42 { SELECT * FROM t3 WHERE b = c } 534 43 { SELECT * FROM t3 WHERE c > a AND b < 'all' } 535 44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a } 536 45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' } 537 46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL } 538 47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b } 539 48 { SELECT * FROM t3 WHERE a IS NULL } 540 49 { SELECT * FROM t3 WHERE a > 'other' } 541 50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c } 542 51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b } 543 52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b } 544 53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 } 545 54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b } 546 55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c } 547 56 { SELECT * FROM t3 WHERE b <= b AND c > b } 548 57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c } 549 58 { SELECT * FROM t3 WHERE b < c OR b = c } 550 59 { SELECT * FROM t3 WHERE c < b AND b >= 'it' } 551 60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a } 552 61 { SELECT * FROM t3 WHERE b = c } 553 62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b } 554 63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' } 555 64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a } 556 65 { SELECT * FROM t3 WHERE a > c } 557 66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b } 558 67 { SELECT * FROM t3 WHERE c = a OR b < c } 559 68 { SELECT * FROM t3 WHERE b > a } 560 69 { SELECT * FROM t3 WHERE a < b OR a > 4543.266482 OR 'same' = b } 561 70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b } 562 71 { SELECT * FROM t3 WHERE c > a } 563 72 { SELECT * FROM t3 WHERE c > b } 564 73 { SELECT * FROM t3 WHERE b <= a } 565 74 { SELECT * FROM t3 WHERE 3282306647 < b AND a >= 'or' OR a >= 378678316.5 } 566 75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b } 567 76 { SELECT * FROM t3 WHERE 'and' < b OR b < c OR c > 1339.360726 } 568 77 { SELECT * FROM t3 WHERE b <= c } 569 78 { SELECT * FROM t3 WHERE 'in' <= c } 570 79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b } 571 80 { SELECT * FROM t3 WHERE 'over' < b } 572 81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b } 573 82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b } 574 83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' } 575 84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL } 576 85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 } 577 86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL } 578 87 { SELECT * FROM t3 WHERE b > 'the' } 579 88 { SELECT * FROM t3 WHERE 'and' = a } 580 89 { SELECT * FROM t3 WHERE b >= b } 581 90 { SELECT * FROM t3 WHERE b >= 8979323846 } 582 91 { SELECT * FROM t3 WHERE c <= a } 583 92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 } 584 93 { SELECT * FROM t3 WHERE c > 3282306647 } 585 94 { SELECT * FROM t3 WHERE b > c } 586 95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c } 587 96 { SELECT * FROM t3 WHERE 'angle' > a } 588 97 { SELECT * FROM t3 WHERE 'paintings' >= a } 589 98 { SELECT * FROM t3 WHERE 'or' >= c } 590 99 { SELECT * FROM t3 WHERE c < b } 591 592 593 101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 } 594 102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h } 595 103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' } 596 104 { SELECT * FROM t3, t4 WHERE c = a } 597 105 { SELECT * FROM t3, t4 WHERE 'of' >= h } 598 106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' } 599 107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b } 600 108 { SELECT * FROM t3, t4 WHERE a < 'the' } 601 109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' } 602 110 { SELECT * FROM t3, t4 WHERE c IS NULL } 603 111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 } 604 112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h } 605 113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a } 606 114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a } 607 115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b } 608 116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' } 609 117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c } 610 118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f } 611 119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' } 612 120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 } 613 121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c } 614 122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h } 615 123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 } 616 124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a } 617 125 { SELECT * FROM t3, t4 WHERE h = 2643383279 } 618 126 { SELECT * FROM t3, t4 WHERE a <= 'the' } 619 127 { SELECT * FROM t3, t4 WHERE h <= c } 620 128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f } 621 129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' < b OR b > g } 622 130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' } 623 131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 } 624 132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h } 625 133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c } 626 134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 } 627 135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b } 628 136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f } 629 137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f } 630 138 { SELECT * FROM t3, t4 WHERE 'other' >= g } 631 139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 } 632 140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' } 633 141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 } 634 142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f } 635 143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 } 636 144 { SELECT * FROM t3, t4 WHERE 5028841971 = h } 637 145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g } 638 146 { SELECT * FROM t3, t4 WHERE c < 5028841971 } 639 147 { SELECT * FROM t3, t4 WHERE a > a } 640 148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' } 641 149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c } 642 150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c } 643 151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' } 644 152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 } 645 153 { SELECT * FROM t3, t4 WHERE 'it' = b } 646 154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 } 647 155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 } 648 156 { SELECT * FROM t3, t4 WHERE 'and' <= b } 649 157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a } 650 158 { SELECT * FROM t3, t4 WHERE a < 'an' } 651 159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f } 652 160 { SELECT * FROM t3, t4 WHERE b = 'peak' } 653 161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h } 654 162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c } 655 163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL } 656 164 { SELECT * FROM t3, t4 WHERE a >= g } 657 165 { SELECT * FROM t3, t4 WHERE c IS NULL } 658 166 { SELECT * FROM t3, t4 WHERE h >= h } 659 167 { SELECT * FROM t3, t4 WHERE 'over' <= h } 660 168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b } 661 169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 } 662 170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g } 663 171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g } 664 172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f } 665 173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c } 666 174 { SELECT * FROM t3, t4 WHERE 'of' <= a } 667 175 { SELECT * FROM t3, t4 WHERE f > 0938446095 } 668 176 { SELECT * FROM t3, t4 WHERE a = g } 669 177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' } 670 178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f } 671 179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f } 672 180 { SELECT * FROM t3, t4 WHERE f < 'and' } 673 181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h } 674 182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' } 675 183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c } 676 184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' } 677 185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h } 678 186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 } 679 187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f } 680 188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' } 681 189 { SELECT * FROM t3, t4 WHERE b < a } 682 190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' } 683 191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g } 684 192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' } 685 193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' } 686 194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g } 687 195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f } 688 196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b } 689 197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g } 690 198 { SELECT * FROM t3, t4 WHERE g < 8979323846 } 691 199 { SELECT * FROM t3, t4 WHERE 'are' <= b } 692 200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825) 693 OR (a=6939937510 AND f=2643383279) } 694 201 { SELECT * FROM t3, t4, t5, t6 695 WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5) 696 OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) } 697 202 { SELECT * FROM t3, t4, t5, t6 698 WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5) 699 OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) } 700 701 } { 702 do_test where8-4.$A.$B.1 { 703 unset -nocomplain R 704 set R [execsql $sql] 705 if {![info exists results($B)]} { 706 set results($B) $R 707 } 708 list 709 } {} 710 711 do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)] 712 } 713 incr A 714 } 715 catch {unset results} 716 catch {unset A} 717 catch {unset B} 718 719 # At one point the following tests provoked an invalid write error (writing 720 # to memory that had already been freed). It was not possible to demonstrate 721 # that this bug could cause a query to return bad data. 722 # 723 do_test where8-5.1 { 724 db close 725 sqlite3 db test.db 726 sqlite3_db_config_lookaside db 0 0 0 727 execsql { 728 CREATE TABLE tA( 729 a, b, c, d, e, f, g, h, 730 i, j, k, l, m, n, o, p 731 ); 732 } 733 execsql { 734 SELECT * FROM tA WHERE 735 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND 736 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND 737 (p = 1 OR p = 2 OR p = 3) 738 } 739 } {} 740 do_test where8-5.2 { 741 execsql { 742 SELECT * FROM tA WHERE 743 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND 744 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND 745 (p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7 746 } 747 } {} 748 do_test where8-5.3 { 749 execsql { 750 INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8); 751 CREATE UNIQUE INDEX tAI ON tA(p); 752 CREATE TABLE tB(x); 753 INSERT INTO tB VALUES('x'); 754 } 755 execsql { 756 SELECT a, x FROM tA LEFT JOIN tB ON ( 757 a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND 758 i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND 759 (p = 1 OR p = 2 OR p = 3) 760 ) 761 } 762 } {1 {}} 763 764 # The OR optimization and WITHOUT ROWID 765 # 766 do_execsql_test where8-6.1 { 767 CREATE TABLE t600(a PRIMARY KEY, b) WITHOUT rowid; 768 CREATE INDEX t600b ON t600(b); 769 INSERT INTO t600 VALUES('state','screen'),('exact','dolphin'),('green','mercury'); 770 SELECT a, b, '|' FROM t600 WHERE a=='state' OR b='mercury' ORDER BY +a; 771 } {green mercury | state screen |} 772 773 finish_test