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