gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/where.test (about) 1 # 2001 September 15 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 clases. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # Build some test data 19 # 20 do_test where-1.0 { 21 execsql { 22 CREATE TABLE t1(w int, x int, y int); 23 CREATE TABLE t2(p int, q int, r int, s int); 24 } 25 for {set i 1} {$i<=100} {incr i} { 26 set w $i 27 set x [expr {int(log($i)/log(2))}] 28 set y [expr {$i*$i + 2*$i + 1}] 29 execsql "INSERT INTO t1 VALUES($w,$x,$y)" 30 } 31 32 ifcapable subquery { 33 execsql { 34 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; 35 } 36 } else { 37 set maxy [execsql {select max(y) from t1}] 38 execsql " 39 INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1; 40 " 41 } 42 43 execsql { 44 CREATE INDEX i1w ON t1("w"); -- Verify quoted identifier names 45 CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility 46 CREATE INDEX i2p ON t2(p); 47 CREATE INDEX i2r ON t2(r); 48 CREATE INDEX i2qs ON t2(q, s); 49 } 50 } {} 51 52 # Do an SQL statement. Append the search count to the end of the result. 53 # 54 proc count sql { 55 set ::sqlite_search_count 0 56 return [concat [execsql $sql] $::sqlite_search_count] 57 } 58 59 # Verify that queries use an index. We are using the special variable 60 # "sqlite_search_count" which tallys the number of executions of MoveTo 61 # and Next operators in the VDBE. By verifing that the search count is 62 # small we can be assured that indices are being used properly. 63 # 64 do_test where-1.1.1 { 65 count {SELECT x, y, w FROM t1 WHERE w=10} 66 } {3 121 10 3} 67 do_test where-1.1.1b { 68 count {SELECT x, y, w FROM t1 WHERE w IS 10} 69 } {3 121 10 3} 70 do_eqp_test where-1.1.2 { 71 SELECT x, y, w FROM t1 WHERE w=10 72 } {*SEARCH t1 USING INDEX i1w (w=?)*} 73 do_eqp_test where-1.1.2b { 74 SELECT x, y, w FROM t1 WHERE w IS 10 75 } {*SEARCH t1 USING INDEX i1w (w=?)*} 76 do_test where-1.1.3 { 77 db status step 78 } {0} 79 do_test where-1.1.4 { 80 db eval {SELECT x, y, w FROM t1 WHERE +w=10} 81 } {3 121 10} 82 do_test where-1.1.5 { 83 db status step 84 } {99} 85 do_eqp_test where-1.1.6 { 86 SELECT x, y, w FROM t1 WHERE +w=10 87 } {*SCAN t1*} 88 do_test where-1.1.7 { 89 count {SELECT x, y, w AS abc FROM t1 WHERE abc=10} 90 } {3 121 10 3} 91 do_eqp_test where-1.1.8 { 92 SELECT x, y, w AS abc FROM t1 WHERE abc=10 93 } {*SEARCH t1 USING INDEX i1w (w=?)*} 94 do_test where-1.1.9 { 95 db status step 96 } {0} 97 do_test where-1.2.1 { 98 count {SELECT x, y, w FROM t1 WHERE w=11} 99 } {3 144 11 3} 100 do_test where-1.2.2 { 101 count {SELECT x, y, w AS abc FROM t1 WHERE abc=11} 102 } {3 144 11 3} 103 do_test where-1.3.1 { 104 count {SELECT x, y, w AS abc FROM t1 WHERE 11=w} 105 } {3 144 11 3} 106 do_test where-1.3.2 { 107 count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc} 108 } {3 144 11 3} 109 do_test where-1.3.3 { 110 count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc} 111 } {3 144 11 3} 112 do_test where-1.4.1 { 113 count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} 114 } {11 3 144 3} 115 do_test where-1.4.1b { 116 count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2} 117 } {11 3 144 3} 118 do_eqp_test where-1.4.2 { 119 SELECT w, x, y FROM t1 WHERE 11=w AND x>2 120 } {*SEARCH t1 USING INDEX i1w (w=?)*} 121 do_eqp_test where-1.4.2b { 122 SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2 123 } {*SEARCH t1 USING INDEX i1w (w=?)*} 124 do_test where-1.4.3 { 125 count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} 126 } {11 3 144 3} 127 do_eqp_test where-1.4.4 { 128 SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 129 } {*SEARCH t1 USING INDEX i1w (w=?)*} 130 do_test where-1.5 { 131 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} 132 } {3 144 3} 133 do_eqp_test where-1.5.2 { 134 SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2 135 } {*SEARCH t1 USING INDEX i1w (w=?)*} 136 do_test where-1.6 { 137 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} 138 } {3 144 3} 139 do_test where-1.7 { 140 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} 141 } {3 144 3} 142 do_test where-1.8 { 143 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} 144 } {3 144 3} 145 do_eqp_test where-1.8.2 { 146 SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3 147 } {*SEARCH t1 USING INDEX i1xy (x=? AND y=?)*} 148 do_eqp_test where-1.8.3 { 149 SELECT x, y FROM t1 WHERE y=144 AND x=3 150 } {*SEARCH t1 USING COVERING INDEX i1xy (x=? AND y=?)*} 151 do_test where-1.9 { 152 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} 153 } {3 144 3} 154 do_test where-1.10 { 155 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} 156 } {3 121 3} 157 do_test where-1.11 { 158 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} 159 } {3 100 3} 160 do_test where-1.11b { 161 count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10} 162 } {3 100 3} 163 164 # New for SQLite version 2.1: Verify that that inequality constraints 165 # are used correctly. 166 # 167 do_test where-1.12 { 168 count {SELECT w FROM t1 WHERE x=3 AND y<100} 169 } {8 3} 170 do_test where-1.12b { 171 count {SELECT w FROM t1 WHERE x IS 3 AND y<100} 172 } {8 3} 173 do_test where-1.13 { 174 count {SELECT w FROM t1 WHERE x=3 AND 100>y} 175 } {8 3} 176 do_test where-1.14 { 177 count {SELECT w FROM t1 WHERE 3=x AND y<100} 178 } {8 3} 179 do_test where-1.14b { 180 count {SELECT w FROM t1 WHERE 3 IS x AND y<100} 181 } {8 3} 182 do_test where-1.15 { 183 count {SELECT w FROM t1 WHERE 3=x AND 100>y} 184 } {8 3} 185 do_test where-1.16 { 186 count {SELECT w FROM t1 WHERE x=3 AND y<=100} 187 } {8 9 5} 188 do_test where-1.17 { 189 count {SELECT w FROM t1 WHERE x=3 AND 100>=y} 190 } {8 9 5} 191 do_test where-1.18 { 192 count {SELECT w FROM t1 WHERE x=3 AND y>225} 193 } {15 3} 194 do_test where-1.18b { 195 count {SELECT w FROM t1 WHERE x IS 3 AND y>225} 196 } {15 3} 197 do_test where-1.19 { 198 count {SELECT w FROM t1 WHERE x=3 AND 225<y} 199 } {15 3} 200 do_test where-1.20 { 201 count {SELECT w FROM t1 WHERE x=3 AND y>=225} 202 } {14 15 5} 203 do_test where-1.21 { 204 count {SELECT w FROM t1 WHERE x=3 AND 225<=y} 205 } {14 15 5} 206 do_test where-1.22 { 207 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} 208 } {11 12 5} 209 do_test where-1.22b { 210 count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196} 211 } {11 12 5} 212 do_test where-1.23 { 213 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} 214 } {10 11 12 13 9} 215 do_test where-1.24 { 216 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y} 217 } {11 12 5} 218 do_test where-1.25 { 219 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} 220 } {10 11 12 13 9} 221 222 # Need to work on optimizing the BETWEEN operator. 223 # 224 # do_test where-1.26 { 225 # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} 226 # } {10 11 12 13 9} 227 228 do_test where-1.27 { 229 count {SELECT w FROM t1 WHERE x=3 AND y+1==122} 230 } {10 10} 231 232 do_test where-1.28 { 233 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} 234 } {10 99} 235 do_test where-1.29 { 236 count {SELECT w FROM t1 WHERE y==121} 237 } {10 99} 238 239 240 do_test where-1.30 { 241 count {SELECT w FROM t1 WHERE w>97} 242 } {98 99 100 3} 243 do_test where-1.31 { 244 count {SELECT w FROM t1 WHERE w>=97} 245 } {97 98 99 100 4} 246 do_test where-1.33 { 247 count {SELECT w FROM t1 WHERE w==97} 248 } {97 2} 249 do_test where-1.33.1 { 250 count {SELECT w FROM t1 WHERE w<=97 AND w==97} 251 } {97 2} 252 do_test where-1.33.2 { 253 count {SELECT w FROM t1 WHERE w<98 AND w==97} 254 } {97 2} 255 do_test where-1.33.3 { 256 count {SELECT w FROM t1 WHERE w>=97 AND w==97} 257 } {97 2} 258 do_test where-1.33.4 { 259 count {SELECT w FROM t1 WHERE w>96 AND w==97} 260 } {97 2} 261 do_test where-1.33.5 { 262 count {SELECT w FROM t1 WHERE w==97 AND w==97} 263 } {97 2} 264 do_test where-1.34 { 265 count {SELECT w FROM t1 WHERE w+1==98} 266 } {97 99} 267 do_test where-1.35 { 268 count {SELECT w FROM t1 WHERE w<3} 269 } {1 2 3} 270 do_test where-1.36 { 271 count {SELECT w FROM t1 WHERE w<=3} 272 } {1 2 3 4} 273 do_test where-1.37 { 274 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} 275 } {1 2 3 99} 276 277 do_test where-1.38 { 278 count {SELECT (w) FROM t1 WHERE (w)>(97)} 279 } {98 99 100 3} 280 do_test where-1.39 { 281 count {SELECT (w) FROM t1 WHERE (w)>=(97)} 282 } {97 98 99 100 4} 283 do_test where-1.40 { 284 count {SELECT (w) FROM t1 WHERE (w)==(97)} 285 } {97 2} 286 do_test where-1.41 { 287 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)} 288 } {97 99} 289 290 291 # Do the same kind of thing except use a join as the data source. 292 # 293 do_test where-2.1 { 294 count { 295 SELECT w, p FROM t2, t1 296 WHERE x=q AND y=s AND r=8977 297 } 298 } {34 67 6} 299 do_test where-2.2 { 300 count { 301 SELECT w, p FROM t2, t1 302 WHERE x=q AND s=y AND r=8977 303 } 304 } {34 67 6} 305 do_test where-2.3 { 306 count { 307 SELECT w, p FROM t2, t1 308 WHERE x=q AND s=y AND r=8977 AND w>10 309 } 310 } {34 67 6} 311 do_test where-2.4 { 312 count { 313 SELECT w, p FROM t2, t1 314 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 315 } 316 } {34 67 6} 317 do_test where-2.5 { 318 count { 319 SELECT w, p FROM t2, t1 320 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 321 } 322 } {34 67 6} 323 do_test where-2.6 { 324 count { 325 SELECT w, p FROM t2, t1 326 WHERE x=q AND p=77 AND s=y AND w>5 327 } 328 } {24 77 6} 329 do_test where-2.7 { 330 count { 331 SELECT w, p FROM t1, t2 332 WHERE x=q AND p>77 AND s=y AND w=5 333 } 334 } {5 96 6} 335 336 # Lets do a 3-way join. 337 # 338 do_test where-3.1 { 339 count { 340 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 341 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 342 } 343 } {11 90 11 8} 344 do_test where-3.2 { 345 count { 346 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 347 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 348 } 349 } {12 89 12 8} 350 do_test where-3.3 { 351 count { 352 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 353 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y 354 } 355 } {15 86 86 8} 356 357 # Test to see that the special case of a constant WHERE clause is 358 # handled. 359 # 360 do_test where-4.1 { 361 count { 362 SELECT * FROM t1 WHERE 0 363 } 364 } {0} 365 do_test where-4.2 { 366 count { 367 SELECT * FROM t1 WHERE 1 LIMIT 1 368 } 369 } {1 0 4 0} 370 do_test where-4.3 { 371 execsql { 372 SELECT 99 WHERE 0 373 } 374 } {} 375 do_test where-4.4 { 376 execsql { 377 SELECT 99 WHERE 1 378 } 379 } {99} 380 do_test where-4.5 { 381 execsql { 382 SELECT 99 WHERE 0.1 383 } 384 } {99} 385 do_test where-4.6 { 386 execsql { 387 SELECT 99 WHERE 0.0 388 } 389 } {} 390 do_test where-4.7 { 391 execsql { 392 SELECT count(*) FROM t1 WHERE t1.w 393 } 394 } {100} 395 396 # Verify that IN operators in a WHERE clause are handled correctly. 397 # Omit these tests if the build is not capable of sub-queries. 398 # 399 ifcapable subquery { 400 do_test where-5.1 { 401 count { 402 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; 403 } 404 } {1 0 4 2 1 9 3 1 16 4} 405 do_test where-5.2 { 406 count { 407 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; 408 } 409 } {1 0 4 2 1 9 3 1 16 102} 410 do_test where-5.3a { 411 count { 412 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; 413 } 414 } {1 0 4 2 1 9 3 1 16 12} 415 do_test where-5.3b { 416 count { 417 SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1; 418 } 419 } {1 0 4 2 1 9 3 1 16 12} 420 do_test where-5.3c { 421 count { 422 SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1; 423 } 424 } {1 0 4 2 1 9 3 1 16 12} 425 do_test where-5.3d { 426 count { 427 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC; 428 } 429 } {3 1 16 2 1 9 1 0 4 11} 430 do_test where-5.4 { 431 count { 432 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; 433 } 434 } {1 0 4 2 1 9 3 1 16 102} 435 do_test where-5.5 { 436 count { 437 SELECT * FROM t1 WHERE rowid IN 438 (select rowid from t1 where rowid IN (-1,2,4)) 439 ORDER BY 1; 440 } 441 } {2 1 9 4 2 25 3} 442 do_test where-5.6 { 443 count { 444 SELECT * FROM t1 WHERE rowid+0 IN 445 (select rowid from t1 where rowid IN (-1,2,4)) 446 ORDER BY 1; 447 } 448 } {2 1 9 4 2 25 103} 449 do_test where-5.7 { 450 count { 451 SELECT * FROM t1 WHERE w IN 452 (select rowid from t1 where rowid IN (-1,2,4)) 453 ORDER BY 1; 454 } 455 } {2 1 9 4 2 25 9} 456 do_test where-5.8 { 457 count { 458 SELECT * FROM t1 WHERE w+0 IN 459 (select rowid from t1 where rowid IN (-1,2,4)) 460 ORDER BY 1; 461 } 462 } {2 1 9 4 2 25 103} 463 do_test where-5.9 { 464 count { 465 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; 466 } 467 } {2 1 9 3 1 16 6} 468 do_test where-5.10 { 469 count { 470 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; 471 } 472 } {2 1 9 3 1 16 199} 473 do_test where-5.11 { 474 count { 475 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; 476 } 477 } {79 6 6400 89 6 8100 199} 478 do_test where-5.12 { 479 count { 480 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; 481 } 482 } {79 6 6400 89 6 8100 7} 483 do_test where-5.13 { 484 count { 485 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; 486 } 487 } {2 1 9 3 1 16 6} 488 do_test where-5.14 { 489 count { 490 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; 491 } 492 } {2 1 9 5} 493 do_test where-5.15 { 494 count { 495 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1; 496 } 497 } {2 1 9 3 1 16 9} 498 do_test where-5.100 { 499 db eval { 500 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) 501 ORDER BY x, y 502 } 503 } {2 1 9 54 5 3025 62 5 3969} 504 do_test where-5.101 { 505 db eval { 506 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) 507 ORDER BY x DESC, y DESC 508 } 509 } {62 5 3969 54 5 3025 2 1 9} 510 do_test where-5.102 { 511 db eval { 512 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) 513 ORDER BY x DESC, y 514 } 515 } {54 5 3025 62 5 3969 2 1 9} 516 do_test where-5.103 { 517 db eval { 518 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) 519 ORDER BY x, y DESC 520 } 521 } {2 1 9 62 5 3969 54 5 3025} 522 } 523 524 # This procedure executes the SQL. Then it checks to see if the OP_Sort 525 # opcode was executed. If an OP_Sort did occur, then "sort" is appended 526 # to the result. If no OP_Sort happened, then "nosort" is appended. 527 # 528 # This procedure is used to check to make sure sorting is or is not 529 # occurring as expected. 530 # 531 proc cksort {sql} { 532 set data [execsql $sql] 533 if {[db status sort]} {set x sort} {set x nosort} 534 lappend data $x 535 return $data 536 } 537 # Check out the logic that attempts to implement the ORDER BY clause 538 # using an index rather than by sorting. 539 # 540 do_test where-6.1 { 541 execsql { 542 CREATE TABLE t3(a,b,c); 543 CREATE INDEX t3a ON t3(a); 544 CREATE INDEX t3bc ON t3(b,c); 545 CREATE INDEX t3acb ON t3(a,c,b); 546 INSERT INTO t3 SELECT w, 101-w, y FROM t1; 547 SELECT count(*), sum(a), sum(b), sum(c) FROM t3; 548 } 549 } {100 5050 5050 348550} 550 do_test where-6.2 { 551 cksort { 552 SELECT * FROM t3 ORDER BY a LIMIT 3 553 } 554 } {1 100 4 2 99 9 3 98 16 nosort} 555 do_test where-6.3 { 556 cksort { 557 SELECT * FROM t3 ORDER BY a+1 LIMIT 3 558 } 559 } {1 100 4 2 99 9 3 98 16 sort} 560 do_test where-6.4 { 561 cksort { 562 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 563 } 564 } {1 100 4 2 99 9 3 98 16 nosort} 565 do_test where-6.5 { 566 cksort { 567 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 568 } 569 } {1 100 4 2 99 9 3 98 16 nosort} 570 do_test where-6.6 { 571 cksort { 572 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 573 } 574 } {1 100 4 2 99 9 3 98 16 nosort} 575 do_test where-6.7.1 { 576 cksort { 577 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10 578 } 579 } {/1 100 4 2 99 9 3 98 16 .* nosort/} 580 do_test where-6.7.2 { 581 cksort { 582 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1 583 } 584 } {1 100 4 nosort} 585 ifcapable subquery { 586 do_test where-6.8a { 587 cksort { 588 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 589 } 590 } {1 100 4 2 99 9 3 98 16 nosort} 591 do_test where-6.8b { 592 cksort { 593 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3 594 } 595 } {9 92 100 7 94 64 5 96 36 nosort} 596 } 597 do_test where-6.9.1 { 598 cksort { 599 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 600 } 601 } {1 100 4 nosort} 602 do_test where-6.9.1.1 { 603 cksort { 604 SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3 605 } 606 } {1 100 4 nosort} 607 do_test where-6.9.1.2 { 608 cksort { 609 SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3 610 } 611 } {1 100 4 nosort} 612 do_test where-6.9.2 { 613 cksort { 614 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 615 } 616 } {1 100 4 nosort} 617 do_test where-6.9.3 { 618 cksort { 619 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3 620 } 621 } {1 100 4 nosort} 622 do_test where-6.9.4 { 623 cksort { 624 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3 625 } 626 } {1 100 4 nosort} 627 do_test where-6.9.5 { 628 cksort { 629 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3 630 } 631 } {1 100 4 nosort} 632 do_test where-6.9.6 { 633 cksort { 634 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3 635 } 636 } {1 100 4 nosort} 637 do_test where-6.9.7 { 638 cksort { 639 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3 640 } 641 } {1 100 4 nosort} 642 do_test where-6.9.8 { 643 cksort { 644 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3 645 } 646 } {1 100 4 nosort} 647 do_test where-6.9.9 { 648 cksort { 649 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3 650 } 651 } {1 100 4 nosort} 652 do_test where-6.10 { 653 cksort { 654 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 655 } 656 } {1 100 4 nosort} 657 do_test where-6.11 { 658 cksort { 659 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 660 } 661 } {1 100 4 nosort} 662 do_test where-6.12 { 663 cksort { 664 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 665 } 666 } {1 100 4 nosort} 667 do_test where-6.13 { 668 cksort { 669 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 670 } 671 } {100 1 10201 99 2 10000 98 3 9801 nosort} 672 do_test where-6.13.1 { 673 cksort { 674 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3 675 } 676 } {100 1 10201 99 2 10000 98 3 9801 sort} 677 do_test where-6.14 { 678 cksort { 679 SELECT * FROM t3 ORDER BY b LIMIT 3 680 } 681 } {100 1 10201 99 2 10000 98 3 9801 nosort} 682 do_test where-6.15 { 683 cksort { 684 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 685 } 686 } {1 0 2 1 3 1 nosort} 687 do_test where-6.16 { 688 cksort { 689 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 690 } 691 } {1 0 2 1 3 1 sort} 692 do_test where-6.19 { 693 cksort { 694 SELECT y FROM t1 ORDER BY w LIMIT 3; 695 } 696 } {4 9 16 nosort} 697 do_test where-6.20 { 698 cksort { 699 SELECT y FROM t1 ORDER BY rowid LIMIT 3; 700 } 701 } {4 9 16 nosort} 702 do_test where-6.21 { 703 cksort { 704 SELECT y FROM t1 ORDER BY rowid, y LIMIT 3; 705 } 706 } {4 9 16 nosort} 707 do_test where-6.22 { 708 cksort { 709 SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3; 710 } 711 } {4 9 16 nosort} 712 do_test where-6.23 { 713 cksort { 714 SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3; 715 } 716 } {9 16 25 nosort} 717 do_test where-6.24 { 718 cksort { 719 SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3; 720 } 721 } {9 16 25 nosort} 722 do_test where-6.25 { 723 cksort { 724 SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid; 725 } 726 } {9 16 nosort} 727 do_test where-6.26 { 728 cksort { 729 SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid; 730 } 731 } {4 9 16 25 nosort} 732 do_test where-6.27 { 733 cksort { 734 SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y; 735 } 736 } {4 9 16 25 nosort} 737 738 739 # Tests for reverse-order sorting. 740 # 741 do_test where-7.1 { 742 cksort { 743 SELECT w FROM t1 WHERE x=3 ORDER BY y; 744 } 745 } {8 9 10 11 12 13 14 15 nosort} 746 do_test where-7.2 { 747 cksort { 748 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC; 749 } 750 } {15 14 13 12 11 10 9 8 nosort} 751 do_test where-7.3 { 752 cksort { 753 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3; 754 } 755 } {10 11 12 nosort} 756 do_test where-7.4 { 757 cksort { 758 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3; 759 } 760 } {15 14 13 nosort} 761 do_test where-7.5 { 762 cksort { 763 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC; 764 } 765 } {15 14 13 12 11 nosort} 766 do_test where-7.6 { 767 cksort { 768 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC; 769 } 770 } {15 14 13 12 11 10 nosort} 771 do_test where-7.7 { 772 cksort { 773 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC; 774 } 775 } {12 11 10 nosort} 776 do_test where-7.8 { 777 cksort { 778 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC; 779 } 780 } {13 12 11 10 nosort} 781 do_test where-7.9 { 782 cksort { 783 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC; 784 } 785 } {13 12 11 nosort} 786 do_test where-7.10 { 787 cksort { 788 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC; 789 } 790 } {12 11 10 nosort} 791 do_test where-7.11 { 792 cksort { 793 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y; 794 } 795 } {10 11 12 nosort} 796 do_test where-7.12 { 797 cksort { 798 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y; 799 } 800 } {10 11 12 13 nosort} 801 do_test where-7.13 { 802 cksort { 803 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y; 804 } 805 } {11 12 13 nosort} 806 do_test where-7.14 { 807 cksort { 808 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y; 809 } 810 } {10 11 12 nosort} 811 do_test where-7.15 { 812 cksort { 813 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y; 814 } 815 } {nosort} 816 do_test where-7.16 { 817 cksort { 818 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y; 819 } 820 } {8 nosort} 821 do_test where-7.17 { 822 cksort { 823 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y; 824 } 825 } {nosort} 826 do_test where-7.18 { 827 cksort { 828 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y; 829 } 830 } {15 nosort} 831 do_test where-7.19 { 832 cksort { 833 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC; 834 } 835 } {nosort} 836 do_test where-7.20 { 837 cksort { 838 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC; 839 } 840 } {8 nosort} 841 do_test where-7.21 { 842 cksort { 843 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC; 844 } 845 } {nosort} 846 do_test where-7.22 { 847 cksort { 848 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC; 849 } 850 } {15 nosort} 851 do_test where-7.23 { 852 cksort { 853 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y; 854 } 855 } {nosort} 856 do_test where-7.24 { 857 cksort { 858 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y; 859 } 860 } {1 nosort} 861 do_test where-7.25 { 862 cksort { 863 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y; 864 } 865 } {nosort} 866 do_test where-7.26 { 867 cksort { 868 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y; 869 } 870 } {100 nosort} 871 do_test where-7.27 { 872 cksort { 873 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC; 874 } 875 } {nosort} 876 do_test where-7.28 { 877 cksort { 878 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC; 879 } 880 } {1 nosort} 881 do_test where-7.29 { 882 cksort { 883 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC; 884 } 885 } {nosort} 886 do_test where-7.30 { 887 cksort { 888 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC; 889 } 890 } {100 nosort} 891 do_test where-7.31 { 892 cksort { 893 SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3 894 } 895 } {10201 10000 9801 nosort} 896 do_test where-7.32 { 897 cksort { 898 SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC 899 } 900 } {16 9 4 nosort} 901 do_test where-7.33 { 902 cksort { 903 SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC 904 } 905 } {25 16 9 4 nosort} 906 do_test where-7.34 { 907 cksort { 908 SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC 909 } 910 } {16 9 nosort} 911 do_test where-7.35 { 912 cksort { 913 SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC 914 } 915 } {16 9 4 nosort} 916 917 do_test where-8.1 { 918 execsql { 919 CREATE TABLE t4 AS SELECT * FROM t1; 920 CREATE INDEX i4xy ON t4(x,y); 921 } 922 cksort { 923 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; 924 } 925 } {30 29 28 nosort} 926 do_test where-8.2 { 927 execsql { 928 DELETE FROM t4; 929 } 930 cksort { 931 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; 932 } 933 } {nosort} 934 935 # Make sure searches with an index work with an empty table. 936 # 937 do_test where-9.1 { 938 execsql { 939 CREATE TABLE t5(x PRIMARY KEY); 940 SELECT * FROM t5 WHERE x<10; 941 } 942 } {} 943 do_test where-9.2 { 944 execsql { 945 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC; 946 } 947 } {} 948 do_test where-9.3 { 949 execsql { 950 SELECT * FROM t5 WHERE x=10; 951 } 952 } {} 953 954 do_test where-10.1 { 955 execsql { 956 SELECT 1 WHERE abs(random())<0 957 } 958 } {} 959 do_test where-10.2 { 960 proc tclvar_func {vname} {return [set ::$vname]} 961 db function tclvar tclvar_func 962 set ::v1 0 963 execsql { 964 SELECT count(*) FROM t1 WHERE tclvar('v1'); 965 } 966 } {0} 967 do_test where-10.3 { 968 set ::v1 1 969 execsql { 970 SELECT count(*) FROM t1 WHERE tclvar('v1'); 971 } 972 } {100} 973 do_test where-10.4 { 974 set ::v1 1 975 proc tclvar_func {vname} { 976 upvar #0 $vname v 977 set v [expr {!$v}] 978 return $v 979 } 980 execsql { 981 SELECT count(*) FROM t1 WHERE tclvar('v1'); 982 } 983 } {50} 984 985 # Ticket #1376. The query below was causing a segfault. 986 # The problem was the age-old error of calling realloc() on an 987 # array while there are still pointers to individual elements of 988 # that array. 989 # 990 do_test where-11.1 { 991 execsql { 992 CREATE TABLE t99(Dte INT, X INT); 993 DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR 994 (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR 995 (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR 996 (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR 997 (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR 998 (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR 999 (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR 1000 (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR 1001 (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR 1002 (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR 1003 (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR 1004 (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR 1005 (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR 1006 (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR 1007 (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR 1008 (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR 1009 (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR 1010 (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR 1011 (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR 1012 (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR 1013 (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR 1014 (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611); 1015 } 1016 } {} 1017 1018 # Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY 1019 # KEY. 1020 # 1021 do_test where-12.1 { 1022 execsql { 1023 CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT); 1024 INSERT INTO t6 VALUES(1,'one'); 1025 INSERT INTO t6 VALUES(4,'four'); 1026 CREATE INDEX t6i1 ON t6(b); 1027 } 1028 cksort { 1029 SELECT * FROM t6 ORDER BY b; 1030 } 1031 } {4 four 1 one nosort} 1032 do_test where-12.2 { 1033 cksort { 1034 SELECT * FROM t6 ORDER BY b, a; 1035 } 1036 } {4 four 1 one nosort} 1037 do_test where-12.3 { 1038 cksort { 1039 SELECT * FROM t6 ORDER BY a; 1040 } 1041 } {1 one 4 four nosort} 1042 do_test where-12.4 { 1043 cksort { 1044 SELECT * FROM t6 ORDER BY a, b; 1045 } 1046 } {1 one 4 four nosort} 1047 do_test where-12.5 { 1048 cksort { 1049 SELECT * FROM t6 ORDER BY b DESC; 1050 } 1051 } {1 one 4 four nosort} 1052 do_test where-12.6 { 1053 cksort { 1054 SELECT * FROM t6 ORDER BY b DESC, a DESC; 1055 } 1056 } {1 one 4 four nosort} 1057 do_test where-12.7 { 1058 cksort { 1059 SELECT * FROM t6 ORDER BY b DESC, a ASC; 1060 } 1061 } {1 one 4 four sort} 1062 do_test where-12.8 { 1063 cksort { 1064 SELECT * FROM t6 ORDER BY b ASC, a DESC; 1065 } 1066 } {4 four 1 one sort} 1067 do_test where-12.9 { 1068 cksort { 1069 SELECT * FROM t6 ORDER BY a DESC; 1070 } 1071 } {4 four 1 one nosort} 1072 do_test where-12.10 { 1073 cksort { 1074 SELECT * FROM t6 ORDER BY a DESC, b DESC; 1075 } 1076 } {4 four 1 one nosort} 1077 do_test where-12.11 { 1078 cksort { 1079 SELECT * FROM t6 ORDER BY a DESC, b ASC; 1080 } 1081 } {4 four 1 one nosort} 1082 do_test where-12.12 { 1083 cksort { 1084 SELECT * FROM t6 ORDER BY a ASC, b DESC; 1085 } 1086 } {1 one 4 four nosort} 1087 do_test where-13.1 { 1088 execsql { 1089 CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT); 1090 INSERT INTO t7 VALUES(1,'one'); 1091 INSERT INTO t7 VALUES(4,'four'); 1092 CREATE INDEX t7i1 ON t7(b); 1093 } 1094 cksort { 1095 SELECT * FROM t7 ORDER BY b; 1096 } 1097 } {4 four 1 one nosort} 1098 do_test where-13.2 { 1099 cksort { 1100 SELECT * FROM t7 ORDER BY b, a; 1101 } 1102 } {4 four 1 one nosort} 1103 do_test where-13.3 { 1104 cksort { 1105 SELECT * FROM t7 ORDER BY a; 1106 } 1107 } {1 one 4 four nosort} 1108 do_test where-13.4 { 1109 cksort { 1110 SELECT * FROM t7 ORDER BY a, b; 1111 } 1112 } {1 one 4 four nosort} 1113 do_test where-13.5 { 1114 cksort { 1115 SELECT * FROM t7 ORDER BY b DESC; 1116 } 1117 } {1 one 4 four nosort} 1118 do_test where-13.6 { 1119 cksort { 1120 SELECT * FROM t7 ORDER BY b DESC, a DESC; 1121 } 1122 } {1 one 4 four nosort} 1123 do_test where-13.7 { 1124 cksort { 1125 SELECT * FROM t7 ORDER BY b DESC, a ASC; 1126 } 1127 } {1 one 4 four sort} 1128 do_test where-13.8 { 1129 cksort { 1130 SELECT * FROM t7 ORDER BY b ASC, a DESC; 1131 } 1132 } {4 four 1 one sort} 1133 do_test where-13.9 { 1134 cksort { 1135 SELECT * FROM t7 ORDER BY a DESC; 1136 } 1137 } {4 four 1 one nosort} 1138 do_test where-13.10 { 1139 cksort { 1140 SELECT * FROM t7 ORDER BY a DESC, b DESC; 1141 } 1142 } {4 four 1 one nosort} 1143 do_test where-13.11 { 1144 cksort { 1145 SELECT * FROM t7 ORDER BY a DESC, b ASC; 1146 } 1147 } {4 four 1 one nosort} 1148 do_test where-13.12 { 1149 cksort { 1150 SELECT * FROM t7 ORDER BY a ASC, b DESC; 1151 } 1152 } {1 one 4 four nosort} 1153 1154 # Ticket #2211. 1155 # 1156 # When optimizing out ORDER BY clauses, make sure that trailing terms 1157 # of the ORDER BY clause do not reference other tables in a join. 1158 # 1159 if {[permutation] != "no_optimization"} { 1160 do_test where-14.1 { 1161 execsql { 1162 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100)); 1163 INSERT INTO t8(a,b) VALUES(1,'one'); 1164 INSERT INTO t8(a,b) VALUES(4,'four'); 1165 } 1166 cksort { 1167 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b 1168 } 1169 } {1/4 1/1 4/4 4/1 nosort} 1170 do_test where-14.2 { 1171 cksort { 1172 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC 1173 } 1174 } {1/1 1/4 4/1 4/4 nosort} 1175 do_test where-14.3 { 1176 cksort { 1177 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b 1178 } 1179 } {1/4 1/1 4/4 4/1 nosort} 1180 do_test where-14.4 { 1181 cksort { 1182 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC 1183 } 1184 } {1/4 1/1 4/4 4/1 nosort} 1185 do_test where-14.5 { 1186 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1187 cksort { 1188 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b 1189 } 1190 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} 1191 do_test where-14.6 { 1192 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1193 cksort { 1194 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC 1195 } 1196 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} 1197 do_test where-14.7 { 1198 cksort { 1199 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b 1200 } 1201 } {4/1 4/4 1/1 1/4 sort} 1202 do_test where-14.7.1 { 1203 cksort { 1204 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b 1205 } 1206 } {4/1 4/4 1/1 1/4 sort} 1207 do_test where-14.7.2 { 1208 cksort { 1209 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b 1210 } 1211 } {4/4 4/1 1/4 1/1 nosort} 1212 do_test where-14.8 { 1213 cksort { 1214 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC 1215 } 1216 } {4/4 4/1 1/4 1/1 sort} 1217 do_test where-14.9 { 1218 cksort { 1219 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b 1220 } 1221 } {4/4 4/1 1/4 1/1 sort} 1222 do_test where-14.10 { 1223 cksort { 1224 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC 1225 } 1226 } {4/1 4/4 1/1 1/4 sort} 1227 do_test where-14.11 { 1228 cksort { 1229 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b 1230 } 1231 } {4/1 4/4 1/1 1/4 sort} 1232 do_test where-14.12 { 1233 cksort { 1234 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC 1235 } 1236 } {4/4 4/1 1/4 1/1 sort} 1237 } ;# {permutation != "no_optimization"} 1238 1239 # Ticket #2445. 1240 # 1241 # There was a crash that could occur when a where clause contains an 1242 # alias for an expression in the result set, and that expression retrieves 1243 # a column of the second or subsequent table in a join. 1244 # 1245 do_test where-15.1 { 1246 execsql { 1247 CREATE TEMP TABLE t1 (a, b, c, d, e); 1248 CREATE TEMP TABLE t2 (f); 1249 SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ; 1250 } 1251 } {} 1252 1253 # Ticket #3408. 1254 # 1255 # The branch of code in where.c that generated rowid lookups was 1256 # incorrectly deallocating a constant register, meaning that if the 1257 # vdbe code ran more than once, the second time around the constant 1258 # value may have been clobbered by some other value. 1259 # 1260 do_test where-16.1 { 1261 execsql { 1262 CREATE TABLE a1(id INTEGER PRIMARY KEY, v); 1263 CREATE TABLE a2(id INTEGER PRIMARY KEY, v); 1264 INSERT INTO a1 VALUES(1, 'one'); 1265 INSERT INTO a1 VALUES(2, 'two'); 1266 INSERT INTO a2 VALUES(1, 'one'); 1267 INSERT INTO a2 VALUES(2, 'two'); 1268 } 1269 } {} 1270 do_test where-16.2 { 1271 execsql { 1272 SELECT * FROM a2 CROSS JOIN a1 WHERE a1.id=1 AND a1.v='one'; 1273 } 1274 } {1 one 1 one 2 two 1 one} 1275 1276 # The actual problem reported in #3408. 1277 do_test where-16.3 { 1278 execsql { 1279 CREATE TEMP TABLE foo(idx INTEGER); 1280 INSERT INTO foo VALUES(1); 1281 INSERT INTO foo VALUES(1); 1282 INSERT INTO foo VALUES(1); 1283 INSERT INTO foo VALUES(2); 1284 INSERT INTO foo VALUES(2); 1285 CREATE TEMP TABLE bar(stuff INTEGER); 1286 INSERT INTO bar VALUES(100); 1287 INSERT INTO bar VALUES(200); 1288 INSERT INTO bar VALUES(300); 1289 } 1290 } {} 1291 do_test where-16.4 { 1292 execsql { 1293 SELECT bar.RowID id FROM foo, bar WHERE foo.idx = bar.RowID AND id = 2; 1294 } 1295 } {2 2} 1296 1297 integrity_check {where-99.0} 1298 1299 #--------------------------------------------------------------------- 1300 # These tests test that a bug surrounding the use of ForceInt has been 1301 # fixed in where.c. 1302 # 1303 do_test where-17.1 { 1304 execsql { 1305 CREATE TABLE tbooking ( 1306 id INTEGER PRIMARY KEY, 1307 eventtype INTEGER NOT NULL 1308 ); 1309 INSERT INTO tbooking VALUES(42, 3); 1310 INSERT INTO tbooking VALUES(43, 4); 1311 } 1312 } {} 1313 do_test where-17.2 { 1314 execsql { 1315 SELECT a.id 1316 FROM tbooking AS a 1317 WHERE a.eventtype=3; 1318 } 1319 } {42} 1320 do_test where-17.3 { 1321 execsql { 1322 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id) 1323 FROM tbooking AS a 1324 WHERE a.eventtype=3; 1325 } 1326 } {42 43} 1327 do_test where-17.4 { 1328 execsql { 1329 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id) 1330 FROM (SELECT 1.5 AS id) AS a 1331 } 1332 } {1.5 42} 1333 do_test where-17.5 { 1334 execsql { 1335 CREATE TABLE tother(a, b); 1336 INSERT INTO tother VALUES(1, 3.7); 1337 SELECT id, a FROM tbooking, tother WHERE id>a; 1338 } 1339 } {42 1 43 1} 1340 1341 # Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03 1342 # Segfault during query involving LEFT JOIN column in the ORDER BY clause. 1343 # 1344 do_execsql_test where-18.1 { 1345 CREATE TABLE t181(a); 1346 CREATE TABLE t182(b,c); 1347 INSERT INTO t181 VALUES(1); 1348 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL; 1349 } {1} 1350 do_execsql_test where-18.1rj { 1351 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL; 1352 } {1} 1353 do_execsql_test where-18.2 { 1354 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; 1355 } {1} 1356 do_execsql_test where-18.3 { 1357 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c; 1358 } {1} 1359 do_execsql_test where-18.3rj { 1360 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c; 1361 } {1} 1362 do_execsql_test where-18.4 { 1363 INSERT INTO t181 VALUES(1),(1),(1),(1); 1364 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; 1365 } {1} 1366 do_execsql_test where-18.4rj { 1367 SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c; 1368 } {1} 1369 do_execsql_test where-18.5 { 1370 INSERT INTO t181 VALUES(2); 1371 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a; 1372 } {1 2} 1373 do_execsql_test where-18.6 { 1374 INSERT INTO t181 VALUES(2); 1375 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL; 1376 } {1 2} 1377 1378 # Make sure the OR optimization works on a JOIN 1379 # 1380 do_execsql_test where-19.0 { 1381 CREATE TABLE t191(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d); 1382 CREATE INDEX t191a ON t1(a); 1383 CREATE INDEX t191b ON t1(b); 1384 CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT); 1385 1386 EXPLAIN QUERY PLAN 1387 SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1; 1388 } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/} 1389 1390 # 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a] 1391 # Index on expressions leads to an incorrect answer for a LEFT JOIN 1392 # 1393 do_execsql_test where-20.0 { 1394 CREATE TABLE t201(x); 1395 CREATE TABLE t202(y, z); 1396 INSERT INTO t201 VALUES('key'); 1397 INSERT INTO t202 VALUES('key', -1); 1398 CREATE INDEX t202i ON t202(y, ifnull(z, 0)); 1399 SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0; 1400 } {0} 1401 1402 do_execsql_test where-21.0 { 1403 CREATE TABLE t12(a, b, c); 1404 CREATE TABLE t13(x); 1405 CREATE INDEX t12ab ON t12(b, a); 1406 CREATE INDEX t12ac ON t12(c, a); 1407 1408 INSERT INTO t12 VALUES(4, 0, 1); 1409 INSERT INTO t12 VALUES(4, 1, 0); 1410 INSERT INTO t12 VALUES(5, 0, 1); 1411 INSERT INTO t12 VALUES(5, 1, 0); 1412 1413 INSERT INTO t13 VALUES(1), (2), (3), (4); 1414 } 1415 do_execsql_test where-21.1 { 1416 SELECT * FROM t12 WHERE 1417 a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10) 1418 AND (b=1 OR c=1); 1419 } { 1420 4 1 0 1421 4 0 1 1422 } 1423 1424 # 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a] 1425 # Incorrect result in LEFT JOIN when STAT4 is enabled. 1426 # 1427 sqlite3 db :memory: 1428 do_execsql_test where-22.1 { 1429 CREATE TABLE t1(a INT); 1430 CREATE INDEX t1a ON t1(a); 1431 INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL); 1432 CREATE TABLE t2(dummy INT); 1433 SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL; 1434 } {5} 1435 1436 # 20190-02-22: A bug introduced by checkin 1437 # https://www.sqlite.org/src/info/fa792714ae62fa98. 1438 # 1439 do_execsql_test where-23.0 { 1440 DROP TABLE IF EXISTS t1; 1441 DROP TABLE IF EXISTS t2; 1442 CREATE TABLE t1(a INTEGER PRIMARY KEY); 1443 INSERT INTO t1(a) VALUES(1),(2),(3); 1444 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT); 1445 INSERT INTO t2(y) VALUES(2),(3); 1446 SELECT * FROM t1, t2 WHERE a=y AND y=3; 1447 } {3 2 3} 1448 1449 #------------------------------------------------------------------------- 1450 # 1451 reset_db 1452 do_execsql_test where-24.0 { 1453 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 1454 INSERT INTO t1 VALUES(1, 'one'); 1455 INSERT INTO t1 VALUES(2, 'two'); 1456 INSERT INTO t1 VALUES(3, 'three'); 1457 INSERT INTO t1 VALUES(4, 'four'); 1458 } 1459 1460 foreach {tn sql res} { 1461 1 "SELECT b FROM t1" {one two three four} 1462 2 "SELECT b FROM t1 WHERE a<4" {one two three} 1463 3 "SELECT b FROM t1 WHERE a>1" {two three four} 1464 4 "SELECT b FROM t1 WHERE a>1 AND a<4" {two three} 1465 1466 5 "SELECT b FROM t1 WHERE a>? AND a<4" {} 1467 6 "SELECT b FROM t1 WHERE a>1 AND a<?" {} 1468 7 "SELECT b FROM t1 WHERE a>? AND a<?" {} 1469 1470 7 "SELECT b FROM t1 WHERE a>=? AND a<=4" {} 1471 8 "SELECT b FROM t1 WHERE a>=1 AND a<=?" {} 1472 9 "SELECT b FROM t1 WHERE a>=? AND a<=?" {} 1473 } { 1474 set rev [list] 1475 foreach r $res { set rev [concat $r $rev] } 1476 1477 do_execsql_test where-24.$tn.1 "$sql" $res 1478 do_execsql_test where-24.$tn.2 "$sql ORDER BY rowid" $res 1479 do_execsql_test where-24.$tn.3 "$sql ORDER BY rowid DESC" $rev 1480 1481 do_execsql_test where-24-$tn.4 " 1482 BEGIN; 1483 DELETE FROM t1; 1484 $sql; 1485 $sql ORDER BY rowid; 1486 $sql ORDER BY rowid DESC; 1487 ROLLBACK; 1488 " 1489 } 1490 1491 #------------------------------------------------------------------------- 1492 # 1493 reset_db 1494 do_execsql_test where-25.0 { 1495 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 1496 CREATE UNIQUE INDEX i1 ON t1(c); 1497 INSERT INTO t1 VALUES(1, 'one', 'i'); 1498 INSERT INTO t1 VALUES(2, 'two', 'ii'); 1499 1500 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); 1501 CREATE UNIQUE INDEX i2 ON t2(c); 1502 INSERT INTO t2 VALUES(1, 'one', 'i'); 1503 INSERT INTO t2 VALUES(2, 'two', 'ii'); 1504 INSERT INTO t2 VALUES(3, 'three', 'iii'); 1505 1506 PRAGMA writable_schema = 1; 1507 UPDATE sqlite_schema SET rootpage = ( 1508 SELECT rootpage FROM sqlite_schema WHERE name = 'i2' 1509 ) WHERE name = 'i1'; 1510 } 1511 db close 1512 sqlite3 db test.db 1513 do_catchsql_test where-25.1 { 1514 DELETE FROM t1 WHERE c='iii' 1515 } {1 {database disk image is malformed}} 1516 do_catchsql_test where-25.2 { 1517 INSERT INTO t1 VALUES(4, 'four', 'iii') 1518 ON CONFLICT(c) DO UPDATE SET b=NULL 1519 } {1 {database disk image is malformed}} 1520 1521 reset_db 1522 do_execsql_test where-25.3 { 1523 CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; 1524 CREATE UNIQUE INDEX i1 ON t1(c); 1525 INSERT INTO t1 VALUES(1, 'one', 'i'); 1526 INSERT INTO t1 VALUES(2, 'two', 'ii'); 1527 1528 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); 1529 CREATE UNIQUE INDEX i2 ON t2(c); 1530 INSERT INTO t2 VALUES(1, 'one', 'i'); 1531 INSERT INTO t2 VALUES(2, 'two', 'ii'); 1532 INSERT INTO t2 VALUES(3, 'three', 'iii'); 1533 1534 PRAGMA writable_schema = 1; 1535 UPDATE sqlite_schema SET rootpage = ( 1536 SELECT rootpage FROM sqlite_schema WHERE name = 'i2' 1537 ) WHERE name = 'i1'; 1538 } 1539 db close 1540 sqlite3 db test.db 1541 do_catchsql_test where-25.4 { 1542 SELECT * FROM t1 WHERE c='iii' 1543 } {0 {}} 1544 do_catchsql_test where-25.5 { 1545 INSERT INTO t1 VALUES(4, 'four', 'iii') 1546 ON CONFLICT(c) DO UPDATE SET b=NULL 1547 } {1 {corrupt database}} 1548 1549 # 2019-08-21 Ticket https://www.sqlite.org/src/info/d9f584e936c7a8d0 1550 # 1551 db close 1552 sqlite3 db :memory: 1553 do_execsql_test where-26.1 { 1554 CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT); 1555 INSERT INTO t0(c0, c1) VALUES (1, 'a'); 1556 CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT); 1557 INSERT INTO t1(c0, c1) VALUES (1, 'a'); 1558 SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0; 1559 } {1 a} 1560 do_execsql_test where-26.2 { 1561 SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0; 1562 } {1 a} 1563 do_execsql_test where-26.3 { 1564 SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0; 1565 } {1 a} 1566 do_execsql_test where-26.4 { 1567 SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0; 1568 } {1 a} 1569 do_execsql_test where-26.5 { 1570 SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0; 1571 } {1} 1572 do_execsql_test where-26.6 { 1573 SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1; 1574 } {1} 1575 do_execsql_test where-26.7 { 1576 SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0; 1577 } {1} 1578 do_execsql_test where-26.8 { 1579 SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1; 1580 } {1} 1581 1582 # 2021-07-19 https://sqlite.org/forum/forumpost/2bdb86a068 1583 # Lose of precision when doing comparisons between integer and 1584 # floating point values that are near 9223372036854775807 in the 1585 # OP_SeekGE opcode (and similar). 1586 # 1587 # Valgrind documentation acknowledges that under valgrind, FP calculations 1588 # may not be as accurate as on x86/amd64 hardware. This seems to be causing 1589 # these tests to fail. 1590 # 1591 # https://valgrind.org/docs/manual/manual-core.html#manual-core.limits 1592 # 1593 if {[permutation]!="valgrind"} { 1594 reset_db 1595 do_execsql_test where-27.1 { 1596 CREATE TABLE t1(a INTEGER PRIMARY KEY); 1597 INSERT INTO t1(a) VALUES(9223372036854775807); 1598 SELECT 1 FROM t1 WHERE a>=(9223372036854775807+1); 1599 } {} 1600 do_execsql_test where-27.2 { 1601 SELECT a>=9223372036854775807+1 FROM t1; 1602 } {0} 1603 } 1604 1605 # 2022-05-10 dbsqlfuzz 4c5e3e89bc251d28378be88233f531b84ec66901 1606 # 1607 reset_db 1608 do_execsql_test where-28.1 { 1609 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT); 1610 CREATE INDEX t1b ON t1(b,b,b,b,b,b,b,b,b,b,b,b,b); 1611 INSERT INTO t1(a,b) VALUES(1,1),(15,2),(19,5); 1612 UPDATE t1 SET b=999 WHERE a IN (SELECT 15) AND b IN (1,2); 1613 SELECT * FROM t1; 1614 } { 1615 1 1 1616 15 999 1617 19 5 1618 } 1619 1620 finish_test