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