1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2001 September 15 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. The 15# focus of this file is testing the use of indices in WHERE clases. 16# 17# $Id: where.test,v 1.17 2003/06/15 23:42:25 drh Exp $ 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22# Build some test data 23# 24do_test where-1.0 { 25 execsql { 26 CREATE TABLE t1(w int, x int, y int); 27 CREATE TABLE t2(p int, q int, r int, s int); 28 } 29 for {set i 1} {$i<=100} {incr i} { 30 set w $i 31 set x [expr {int(log($i)/log(2))}] 32 set y [expr {$i*$i + 2*$i + 1}] 33 execsql "INSERT INTO t1 VALUES($w,$x,$y)" 34 } 35 execsql { 36 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; 37 CREATE INDEX i1w ON t1(w); 38 CREATE INDEX i1xy ON t1(x,y); 39 CREATE INDEX i2p ON t2(p); 40 CREATE INDEX i2r ON t2(r); 41 CREATE INDEX i2qs ON t2(q, s); 42 } 43} {} 44 45# Do an SQL statement. Append the search count to the end of the result. 46# 47proc count sql { 48 set ::sqlite_search_count 0 49 return [concat [execsql $sql] $::sqlite_search_count] 50} 51 52# Verify that queries use an index. We are using the special variable 53# "sqlite_search_count" which tallys the number of executions of MoveTo 54# and Next operators in the VDBE. By verifing that the search count is 55# small we can be assured that indices are being used properly. 56# 57do_test where-1.1 { 58 count {SELECT x, y FROM t1 WHERE w=10} 59} {3 121 3} 60do_test where-1.2 { 61 count {SELECT x, y FROM t1 WHERE w=11} 62} {3 144 3} 63do_test where-1.3 { 64 count {SELECT x, y FROM t1 WHERE 11=w} 65} {3 144 3} 66do_test where-1.4 { 67 count {SELECT x, y FROM t1 WHERE 11=w AND x>2} 68} {3 144 3} 69do_test where-1.5 { 70 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} 71} {3 144 3} 72do_test where-1.6 { 73 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} 74} {3 144 3} 75do_test where-1.7 { 76 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} 77} {3 144 3} 78do_test where-1.8 { 79 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} 80} {3 144 3} 81do_test where-1.9 { 82 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} 83} {3 144 3} 84do_test where-1.10 { 85 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} 86} {3 121 3} 87do_test where-1.11 { 88 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} 89} {3 100 3} 90 91# New for SQLite version 2.1: Verify that that inequality constraints 92# are used correctly. 93# 94do_test where-1.12 { 95 count {SELECT w FROM t1 WHERE x=3 AND y<100} 96} {8 3} 97do_test where-1.13 { 98 count {SELECT w FROM t1 WHERE x=3 AND 100>y} 99} {8 3} 100do_test where-1.14 { 101 count {SELECT w FROM t1 WHERE 3=x AND y<100} 102} {8 3} 103do_test where-1.15 { 104 count {SELECT w FROM t1 WHERE 3=x AND 100>y} 105} {8 3} 106do_test where-1.16 { 107 count {SELECT w FROM t1 WHERE x=3 AND y<=100} 108} {8 9 5} 109do_test where-1.17 { 110 count {SELECT w FROM t1 WHERE x=3 AND 100>=y} 111} {8 9 5} 112do_test where-1.18 { 113 count {SELECT w FROM t1 WHERE x=3 AND y>225} 114} {15 3} 115do_test where-1.19 { 116 count {SELECT w FROM t1 WHERE x=3 AND 225<y} 117} {15 3} 118do_test where-1.20 { 119 count {SELECT w FROM t1 WHERE x=3 AND y>=225} 120} {14 15 5} 121do_test where-1.21 { 122 count {SELECT w FROM t1 WHERE x=3 AND 225<=y} 123} {14 15 5} 124do_test where-1.22 { 125 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} 126} {11 12 5} 127do_test where-1.23 { 128 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} 129} {10 11 12 13 9} 130do_test where-1.24 { 131 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y} 132} {11 12 5} 133do_test where-1.25 { 134 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} 135} {10 11 12 13 9} 136 137# Need to work on optimizing the BETWEEN operator. 138# 139# do_test where-1.26 { 140# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} 141# } {10 11 12 13 9} 142 143do_test where-1.27 { 144 count {SELECT w FROM t1 WHERE x=3 AND y+1==122} 145} {10 17} 146do_test where-1.28 { 147 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} 148} {10 99} 149do_test where-1.29 { 150 count {SELECT w FROM t1 WHERE y==121} 151} {10 99} 152 153 154do_test where-1.30 { 155 count {SELECT w FROM t1 WHERE w>97} 156} {98 99 100 6} 157do_test where-1.31 { 158 count {SELECT w FROM t1 WHERE w>=97} 159} {97 98 99 100 8} 160do_test where-1.33 { 161 count {SELECT w FROM t1 WHERE w==97} 162} {97 3} 163do_test where-1.34 { 164 count {SELECT w FROM t1 WHERE w+1==98} 165} {97 99} 166do_test where-1.35 { 167 count {SELECT w FROM t1 WHERE w<3} 168} {1 2 4} 169do_test where-1.36 { 170 count {SELECT w FROM t1 WHERE w<=3} 171} {1 2 3 6} 172do_test where-1.37 { 173 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} 174} {1 2 3 199} 175 176do_test where-1.38 { 177 count {SELECT (w) FROM t1 WHERE (w)>(97)} 178} {98 99 100 6} 179do_test where-1.39 { 180 count {SELECT (w) FROM t1 WHERE (w)>=(97)} 181} {97 98 99 100 8} 182do_test where-1.40 { 183 count {SELECT (w) FROM t1 WHERE (w)==(97)} 184} {97 3} 185do_test where-1.41 { 186 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)} 187} {97 99} 188 189 190# Do the same kind of thing except use a join as the data source. 191# 192do_test where-2.1 { 193 count { 194 SELECT w, p FROM t2, t1 195 WHERE x=q AND y=s AND r=8977 196 } 197} {34 67 6} 198do_test where-2.2 { 199 count { 200 SELECT w, p FROM t2, t1 201 WHERE x=q AND s=y AND r=8977 202 } 203} {34 67 6} 204do_test where-2.3 { 205 count { 206 SELECT w, p FROM t2, t1 207 WHERE x=q AND s=y AND r=8977 AND w>10 208 } 209} {34 67 6} 210do_test where-2.4 { 211 count { 212 SELECT w, p FROM t2, t1 213 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 214 } 215} {34 67 6} 216do_test where-2.5 { 217 count { 218 SELECT w, p FROM t2, t1 219 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 220 } 221} {34 67 6} 222do_test where-2.6 { 223 count { 224 SELECT w, p FROM t2, t1 225 WHERE x=q AND p=77 AND s=y AND w>5 226 } 227} {24 77 6} 228do_test where-2.7 { 229 count { 230 SELECT w, p FROM t1, t2 231 WHERE x=q AND p>77 AND s=y AND w=5 232 } 233} {5 96 6} 234 235# Lets do a 3-way join. 236# 237do_test where-3.1 { 238 count { 239 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 240 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 241 } 242} {11 90 11 9} 243do_test where-3.2 { 244 count { 245 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 246 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 247 } 248} {12 89 12 9} 249do_test where-3.3 { 250 count { 251 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 252 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y 253 } 254} {15 86 86 9} 255 256# Test to see that the special case of a constant WHERE clause is 257# handled. 258# 259do_test where-4.1 { 260 count { 261 SELECT * FROM t1 WHERE 0 262 } 263} {0} 264do_test where-4.2 { 265 count { 266 SELECT * FROM t1 WHERE 1 LIMIT 1 267 } 268} {1 0 4 1} 269do_test where-4.3 { 270 execsql { 271 SELECT 99 WHERE 0 272 } 273} {} 274do_test where-4.4 { 275 execsql { 276 SELECT 99 WHERE 1 277 } 278} {99} 279 280# Verify that IN operators in a WHERE clause are handled correctly. 281# 282do_test where-5.1 { 283 count { 284 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; 285 } 286} {1 0 4 2 1 9 3 1 16 0} 287do_test where-5.2 { 288 count { 289 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; 290 } 291} {1 0 4 2 1 9 3 1 16 199} 292do_test where-5.3 { 293 count { 294 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; 295 } 296} {1 0 4 2 1 9 3 1 16 10} 297do_test where-5.4 { 298 count { 299 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; 300 } 301} {1 0 4 2 1 9 3 1 16 199} 302do_test where-5.5 { 303 count { 304 SELECT * FROM t1 WHERE rowid IN 305 (select rowid from t1 where rowid IN (-1,2,4)) 306 ORDER BY 1; 307 } 308} {2 1 9 4 2 25 1} 309do_test where-5.6 { 310 count { 311 SELECT * FROM t1 WHERE rowid+0 IN 312 (select rowid from t1 where rowid IN (-1,2,4)) 313 ORDER BY 1; 314 } 315} {2 1 9 4 2 25 199} 316do_test where-5.7 { 317 count { 318 SELECT * FROM t1 WHERE w IN 319 (select rowid from t1 where rowid IN (-1,2,4)) 320 ORDER BY 1; 321 } 322} {2 1 9 4 2 25 7} 323do_test where-5.8 { 324 count { 325 SELECT * FROM t1 WHERE w+0 IN 326 (select rowid from t1 where rowid IN (-1,2,4)) 327 ORDER BY 1; 328 } 329} {2 1 9 4 2 25 199} 330do_test where-5.9 { 331 count { 332 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; 333 } 334} {2 1 9 3 1 16 6} 335do_test where-5.10 { 336 count { 337 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; 338 } 339} {2 1 9 3 1 16 199} 340do_test where-5.11 { 341 count { 342 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; 343 } 344} {79 6 6400 89 6 8100 199} 345do_test where-5.12 { 346 count { 347 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; 348 } 349} {79 6 6400 89 6 8100 74} 350do_test where-5.13 { 351 count { 352 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; 353 } 354} {2 1 9 3 1 16 6} 355do_test where-5.14 { 356 count { 357 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; 358 } 359} {2 1 9 6} 360 361# This procedure executes the SQL. Then it checks the generated program 362# for the SQL and appends a "nosort" to the result if the program contains the 363# SortCallback opcode. If the program does not contain the SortCallback 364# opcode it appends "sort" 365# 366proc cksort {sql} { 367 set data [execsql $sql] 368 set prog [execsql "EXPLAIN $sql"] 369 if {[regexp SortCallback $prog]} {set x sort} {set x nosort} 370 lappend data $x 371 return $data 372} 373# Check out the logic that attempts to implement the ORDER BY clause 374# using an index rather than by sorting. 375# 376do_test where-6.1 { 377 execsql { 378 CREATE TABLE t3(a,b,c); 379 CREATE INDEX t3a ON t3(a); 380 CREATE INDEX t3bc ON t3(b,c); 381 CREATE INDEX t3acb ON t3(a,c,b); 382 INSERT INTO t3 SELECT w, 101-w, y FROM t1; 383 SELECT count(*), sum(a), sum(b), sum(c) FROM t3; 384 } 385} {100 5050 5050 348550} 386do_test where-6.2 { 387 cksort { 388 SELECT * FROM t3 ORDER BY a LIMIT 3 389 } 390} {1 100 4 2 99 9 3 98 16 nosort} 391do_test where-6.3 { 392 cksort { 393 SELECT * FROM t3 ORDER BY a+1 LIMIT 3 394 } 395} {1 100 4 2 99 9 3 98 16 sort} 396do_test where-6.4 { 397 cksort { 398 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 399 } 400} {1 100 4 2 99 9 3 98 16 nosort} 401do_test where-6.5 { 402 cksort { 403 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 404 } 405} {1 100 4 2 99 9 3 98 16 nosort} 406do_test where-6.6 { 407 cksort { 408 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 409 } 410} {1 100 4 2 99 9 3 98 16 nosort} 411do_test where-6.7 { 412 cksort { 413 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3 414 } 415} {1 100 4 2 99 9 3 98 16 sort} 416do_test where-6.8 { 417 cksort { 418 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 419 } 420} {1 100 4 2 99 9 3 98 16 sort} 421do_test where-6.9.1 { 422 cksort { 423 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 424 } 425} {1 100 4 nosort} 426do_test where-6.9.2 { 427 cksort { 428 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 429 } 430} {1 100 4 nosort} 431do_test where-6.9.3 { 432 cksort { 433 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3 434 } 435} {1 100 4 nosort} 436do_test where-6.9.4 { 437 cksort { 438 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3 439 } 440} {1 100 4 nosort} 441do_test where-6.9.5 { 442 cksort { 443 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3 444 } 445} {1 100 4 nosort} 446do_test where-6.9.6 { 447 cksort { 448 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3 449 } 450} {1 100 4 nosort} 451do_test where-6.9.7 { 452 cksort { 453 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3 454 } 455} {1 100 4 sort} 456do_test where-6.9.8 { 457 cksort { 458 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3 459 } 460} {1 100 4 sort} 461do_test where-6.9.9 { 462 cksort { 463 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3 464 } 465} {1 100 4 sort} 466do_test where-6.10 { 467 cksort { 468 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 469 } 470} {1 100 4 nosort} 471do_test where-6.11 { 472 cksort { 473 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 474 } 475} {1 100 4 nosort} 476do_test where-6.12 { 477 cksort { 478 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 479 } 480} {1 100 4 nosort} 481do_test where-6.13 { 482 cksort { 483 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 484 } 485} {100 1 10201 99 2 10000 98 3 9801 nosort} 486do_test where-6.13.1 { 487 cksort { 488 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3 489 } 490} {100 1 10201 99 2 10000 98 3 9801 sort} 491do_test where-6.14 { 492 cksort { 493 SELECT * FROM t3 ORDER BY b LIMIT 3 494 } 495} {100 1 10201 99 2 10000 98 3 9801 nosort} 496do_test where-6.15 { 497 cksort { 498 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 499 } 500} {1 0 2 1 3 1 nosort} 501do_test where-6.16 { 502 cksort { 503 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 504 } 505} {1 0 2 1 3 1 sort} 506do_test where-6.17 { 507 cksort { 508 SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; 509 } 510} {4 121 10201 sort} 511do_test where-6.18 { 512 cksort { 513 SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; 514 } 515} {4 9 16 sort} 516do_test where-6.19 { 517 cksort { 518 SELECT y FROM t1 ORDER BY w LIMIT 3; 519 } 520} {4 9 16 nosort} 521 522# Tests for reverse-order sorting. 523# 524do_test where-7.1 { 525 cksort { 526 SELECT w FROM t1 WHERE x=3 ORDER BY y; 527 } 528} {8 9 10 11 12 13 14 15 nosort} 529do_test where-7.2 { 530 cksort { 531 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC; 532 } 533} {15 14 13 12 11 10 9 8 nosort} 534do_test where-7.3 { 535 cksort { 536 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3; 537 } 538} {10 11 12 nosort} 539do_test where-7.4 { 540 cksort { 541 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3; 542 } 543} {15 14 13 nosort} 544do_test where-7.5 { 545 cksort { 546 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC; 547 } 548} {15 14 13 12 11 nosort} 549do_test where-7.6 { 550 cksort { 551 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC; 552 } 553} {15 14 13 12 11 10 nosort} 554do_test where-7.7 { 555 cksort { 556 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC; 557 } 558} {12 11 10 nosort} 559do_test where-7.8 { 560 cksort { 561 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC; 562 } 563} {13 12 11 10 nosort} 564do_test where-7.9 { 565 cksort { 566 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC; 567 } 568} {13 12 11 nosort} 569do_test where-7.10 { 570 cksort { 571 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC; 572 } 573} {12 11 10 nosort} 574do_test where-7.11 { 575 cksort { 576 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y; 577 } 578} {10 11 12 nosort} 579do_test where-7.12 { 580 cksort { 581 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y; 582 } 583} {10 11 12 13 nosort} 584do_test where-7.13 { 585 cksort { 586 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y; 587 } 588} {11 12 13 nosort} 589do_test where-7.14 { 590 cksort { 591 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y; 592 } 593} {10 11 12 nosort} 594do_test where-7.15 { 595 cksort { 596 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y; 597 } 598} {nosort} 599do_test where-7.16 { 600 cksort { 601 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y; 602 } 603} {8 nosort} 604do_test where-7.17 { 605 cksort { 606 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y; 607 } 608} {nosort} 609do_test where-7.18 { 610 cksort { 611 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y; 612 } 613} {15 nosort} 614do_test where-7.19 { 615 cksort { 616 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC; 617 } 618} {nosort} 619do_test where-7.20 { 620 cksort { 621 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC; 622 } 623} {8 nosort} 624do_test where-7.21 { 625 cksort { 626 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC; 627 } 628} {nosort} 629do_test where-7.22 { 630 cksort { 631 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC; 632 } 633} {15 nosort} 634do_test where-7.23 { 635 cksort { 636 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y; 637 } 638} {nosort} 639do_test where-7.24 { 640 cksort { 641 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y; 642 } 643} {1 nosort} 644do_test where-7.25 { 645 cksort { 646 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y; 647 } 648} {nosort} 649do_test where-7.26 { 650 cksort { 651 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y; 652 } 653} {100 nosort} 654do_test where-7.27 { 655 cksort { 656 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC; 657 } 658} {nosort} 659do_test where-7.28 { 660 cksort { 661 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC; 662 } 663} {1 nosort} 664do_test where-7.29 { 665 cksort { 666 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC; 667 } 668} {nosort} 669do_test where-7.30 { 670 cksort { 671 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC; 672 } 673} {100 nosort} 674 675do_test where-8.1 { 676 execsql { 677 CREATE TABLE t4 AS SELECT * FROM t1; 678 CREATE INDEX i4xy ON t4(x,y); 679 } 680 cksort { 681 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; 682 } 683} {30 29 28 nosort} 684do_test where-8.2 { 685 execsql { 686 DELETE FROM t4; 687 } 688 cksort { 689 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; 690 } 691} {nosort} 692 693# Make sure searches with an index work with an empty table. 694# 695do_test where-9.1 { 696 execsql { 697 CREATE TABLE t5(x PRIMARY KEY); 698 SELECT * FROM t5 WHERE x<10; 699 } 700} {} 701do_test where-9.2 { 702 execsql { 703 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC; 704 } 705} {} 706do_test where-9.3 { 707 execsql { 708 SELECT * FROM t5 WHERE x=10; 709 } 710} {} 711 712do_test where-10.1 { 713 execsql { 714 SELECT 1 WHERE abs(random())<0 715 } 716} {} 717do_test where-10.2 { 718 proc tclvar_func {vname} {return [set ::$vname]} 719 db function tclvar tclvar_func 720 set ::v1 0 721 execsql { 722 SELECT count(*) FROM t1 WHERE tclvar('v1'); 723 } 724} {0} 725do_test where-10.3 { 726 set ::v1 1 727 execsql { 728 SELECT count(*) FROM t1 WHERE tclvar('v1'); 729 } 730} {100} 731do_test where-10.4 { 732 set ::v1 1 733 proc tclvar_func {vname} { 734 upvar #0 $vname v 735 set v [expr {!$v}] 736 return $v 737 } 738 execsql { 739 SELECT count(*) FROM t1 WHERE tclvar('v1'); 740 } 741} {50} 742 743integrity_check {where-99.0} 744 745finish_test 746