Annotation of embedaddon/sqlite3/test/where2.test, revision 1.1
1.1 ! misho 1: # 2005 July 28
! 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 clauses
! 13: # based on recent changes to the optimizer.
! 14: #
! 15: # $Id: where2.test,v 1.15 2009/02/02 01:50:40 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Build some test data
! 21: #
! 22: do_test where2-1.0 {
! 23: execsql {
! 24: BEGIN;
! 25: CREATE TABLE t1(w int, x int, y int, z int);
! 26: }
! 27: for {set i 1} {$i<=100} {incr i} {
! 28: set w $i
! 29: set x [expr {int(log($i)/log(2))}]
! 30: set y [expr {$i*$i + 2*$i + 1}]
! 31: set z [expr {$x+$y}]
! 32: ifcapable tclvar {
! 33: execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
! 34: } else {
! 35: execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
! 36: }
! 37: }
! 38: execsql {
! 39: CREATE UNIQUE INDEX i1w ON t1(w);
! 40: CREATE INDEX i1xy ON t1(x,y);
! 41: CREATE INDEX i1zyx ON t1(z,y,x);
! 42: COMMIT;
! 43: }
! 44: } {}
! 45:
! 46: # Do an SQL statement. Append the search count to the end of the result.
! 47: #
! 48: proc count sql {
! 49: set ::sqlite_search_count 0
! 50: return [concat [execsql $sql] $::sqlite_search_count]
! 51: }
! 52:
! 53: # This procedure executes the SQL. Then it checks to see if the OP_Sort
! 54: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
! 55: # to the result. If no OP_Sort happened, then "nosort" is appended.
! 56: #
! 57: # This procedure is used to check to make sure sorting is or is not
! 58: # occurring as expected.
! 59: #
! 60: proc cksort {sql} {
! 61: set data [execsql $sql]
! 62: if {[db status sort]} {set x sort} {set x nosort}
! 63: lappend data $x
! 64: return $data
! 65: }
! 66:
! 67: # This procedure executes the SQL. Then it appends to the result the
! 68: # "sort" or "nosort" keyword (as in the cksort procedure above) then
! 69: # it appends the ::sqlite_query_plan variable.
! 70: #
! 71: proc queryplan {sql} {
! 72: set ::sqlite_sort_count 0
! 73: set data [execsql $sql]
! 74: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 75: lappend data $x
! 76: return [concat $data $::sqlite_query_plan]
! 77: }
! 78:
! 79:
! 80: # Prefer a UNIQUE index over another index.
! 81: #
! 82: do_test where2-1.1 {
! 83: queryplan {
! 84: SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
! 85: }
! 86: } {85 6 7396 7402 nosort t1 i1w}
! 87:
! 88: # Always prefer a rowid== constraint over any other index.
! 89: #
! 90: do_test where2-1.3 {
! 91: queryplan {
! 92: SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
! 93: }
! 94: } {85 6 7396 7402 nosort t1 *}
! 95:
! 96: # When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
! 97: #
! 98: do_test where2-2.1 {
! 99: queryplan {
! 100: SELECT * FROM t1 WHERE w=85 ORDER BY random();
! 101: }
! 102: } {85 6 7396 7402 nosort t1 i1w}
! 103: do_test where2-2.2 {
! 104: queryplan {
! 105: SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random();
! 106: }
! 107: } {85 6 7396 7402 sort t1 i1xy}
! 108: do_test where2-2.3 {
! 109: queryplan {
! 110: SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
! 111: }
! 112: } {85 6 7396 7402 nosort t1 *}
! 113:
! 114:
! 115: # Efficient handling of forward and reverse table scans.
! 116: #
! 117: do_test where2-3.1 {
! 118: queryplan {
! 119: SELECT * FROM t1 ORDER BY rowid LIMIT 2
! 120: }
! 121: } {1 0 4 4 2 1 9 10 nosort t1 *}
! 122: do_test where2-3.2 {
! 123: queryplan {
! 124: SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
! 125: }
! 126: } {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
! 127:
! 128: # The IN operator can be used by indices at multiple layers
! 129: #
! 130: ifcapable subquery {
! 131: do_test where2-4.1 {
! 132: queryplan {
! 133: SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
! 134: AND x>0 AND x<10
! 135: ORDER BY w
! 136: }
! 137: } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
! 138: do_test where2-4.2 {
! 139: queryplan {
! 140: SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
! 141: AND x>0 AND x<10
! 142: ORDER BY w
! 143: }
! 144: } {99 6 10000 10006 sort t1 i1zyx}
! 145: do_test where2-4.3 {
! 146: queryplan {
! 147: SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
! 148: AND x>0 AND x<10
! 149: ORDER BY w
! 150: }
! 151: } {99 6 10000 10006 sort t1 i1zyx}
! 152: ifcapable compound {
! 153: do_test where2-4.4 {
! 154: queryplan {
! 155: SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
! 156: AND y IN (10000,10201)
! 157: AND x>0 AND x<10
! 158: ORDER BY w
! 159: }
! 160: } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
! 161: do_test where2-4.5 {
! 162: queryplan {
! 163: SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
! 164: AND y IN (SELECT 10000 UNION SELECT 10201)
! 165: AND x>0 AND x<10
! 166: ORDER BY w
! 167: }
! 168: } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
! 169: }
! 170: do_test where2-4.6 {
! 171: queryplan {
! 172: SELECT * FROM t1
! 173: WHERE x IN (1,2,3,4,5,6,7,8)
! 174: AND y IN (10000,10001,10002,10003,10004,10005)
! 175: ORDER BY 2
! 176: }
! 177: } {99 6 10000 10006 sort t1 i1xy}
! 178:
! 179: # Duplicate entires on the RHS of an IN operator do not cause duplicate
! 180: # output rows.
! 181: #
! 182: do_test where2-4.6 {
! 183: queryplan {
! 184: SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
! 185: ORDER BY w
! 186: }
! 187: } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
! 188: ifcapable compound {
! 189: do_test where2-4.7 {
! 190: queryplan {
! 191: SELECT * FROM t1 WHERE z IN (
! 192: SELECT 10207 UNION ALL SELECT 10006
! 193: UNION ALL SELECT 10006 UNION ALL SELECT 10207)
! 194: ORDER BY w
! 195: }
! 196: } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
! 197: }
! 198:
! 199: } ;# ifcapable subquery
! 200:
! 201: # The use of an IN operator disables the index as a sorter.
! 202: #
! 203: do_test where2-5.1 {
! 204: queryplan {
! 205: SELECT * FROM t1 WHERE w=99 ORDER BY w
! 206: }
! 207: } {99 6 10000 10006 nosort t1 i1w}
! 208:
! 209: ifcapable subquery {
! 210: do_test where2-5.2 {
! 211: queryplan {
! 212: SELECT * FROM t1 WHERE w IN (99) ORDER BY w
! 213: }
! 214: } {99 6 10000 10006 sort t1 i1w}
! 215: }
! 216:
! 217: # Verify that OR clauses get translated into IN operators.
! 218: #
! 219: set ::idx {}
! 220: ifcapable subquery {set ::idx i1w}
! 221: do_test where2-6.1.1 {
! 222: queryplan {
! 223: SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
! 224: }
! 225: } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
! 226: do_test where2-6.1.2 {
! 227: queryplan {
! 228: SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w
! 229: }
! 230: } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
! 231: do_test where2-6.2 {
! 232: queryplan {
! 233: SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
! 234: }
! 235: } [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
! 236:
! 237: do_test where2-6.3 {
! 238: queryplan {
! 239: SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
! 240: }
! 241: } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
! 242: do_test where2-6.4 {
! 243: queryplan {
! 244: SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
! 245: }
! 246: } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
! 247:
! 248: set ::idx {}
! 249: ifcapable subquery {set ::idx i1zyx}
! 250: do_test where2-6.5 {
! 251: queryplan {
! 252: SELECT b.* FROM t1 a, t1 b
! 253: WHERE a.w=1 AND (a.y=b.z OR b.z=10)
! 254: ORDER BY +b.w
! 255: }
! 256: } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
! 257: do_test where2-6.6 {
! 258: queryplan {
! 259: SELECT b.* FROM t1 a, t1 b
! 260: WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
! 261: ORDER BY +b.w
! 262: }
! 263: } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
! 264:
! 265: # Ticket #2249. Make sure the OR optimization is not attempted if
! 266: # comparisons between columns of different affinities are needed.
! 267: #
! 268: do_test where2-6.7 {
! 269: execsql {
! 270: CREATE TABLE t2249a(a TEXT UNIQUE);
! 271: CREATE TABLE t2249b(b INTEGER);
! 272: INSERT INTO t2249a VALUES('0123');
! 273: INSERT INTO t2249b VALUES(123);
! 274: }
! 275: queryplan {
! 276: -- Because a is type TEXT and b is type INTEGER, both a and b
! 277: -- will attempt to convert to NUMERIC before the comparison.
! 278: -- They will thus compare equal.
! 279: --
! 280: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
! 281: }
! 282: } {123 0123 nosort t2249b {} t2249a {}}
! 283: do_test where2-6.9 {
! 284: queryplan {
! 285: -- The + operator removes affinity from the rhs. No conversions
! 286: -- occur and the comparison is false. The result is an empty set.
! 287: --
! 288: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
! 289: }
! 290: } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
! 291: do_test where2-6.9.2 {
! 292: # The same thing but with the expression flipped around.
! 293: queryplan {
! 294: SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
! 295: }
! 296: } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
! 297: do_test where2-6.10 {
! 298: queryplan {
! 299: -- Use + on both sides of the comparison to disable indices
! 300: -- completely. Make sure we get the same result.
! 301: --
! 302: SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
! 303: }
! 304: } {nosort t2249b {} t2249a {}}
! 305: do_test where2-6.11 {
! 306: # This will not attempt the OR optimization because of the a=b
! 307: # comparison.
! 308: queryplan {
! 309: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
! 310: }
! 311: } {123 0123 nosort t2249b {} t2249a {}}
! 312: do_test where2-6.11.2 {
! 313: # Permutations of the expression terms.
! 314: queryplan {
! 315: SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
! 316: }
! 317: } {123 0123 nosort t2249b {} t2249a {}}
! 318: do_test where2-6.11.3 {
! 319: # Permutations of the expression terms.
! 320: queryplan {
! 321: SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
! 322: }
! 323: } {123 0123 nosort t2249b {} t2249a {}}
! 324: do_test where2-6.11.4 {
! 325: # Permutations of the expression terms.
! 326: queryplan {
! 327: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
! 328: }
! 329: } {123 0123 nosort t2249b {} t2249a {}}
! 330: ifcapable explain&&subquery {
! 331: # These tests are not run if subquery support is not included in the
! 332: # build. This is because these tests test the "a = 1 OR a = 2" to
! 333: # "a IN (1, 2)" optimisation transformation, which is not enabled if
! 334: # subqueries and the IN operator is not available.
! 335: #
! 336: do_test where2-6.12 {
! 337: # In this case, the +b disables the affinity conflict and allows
! 338: # the OR optimization to be used again. The result is now an empty
! 339: # set, the same as in where2-6.9.
! 340: queryplan {
! 341: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
! 342: }
! 343: } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
! 344: do_test where2-6.12.2 {
! 345: # In this case, the +b disables the affinity conflict and allows
! 346: # the OR optimization to be used again. The result is now an empty
! 347: # set, the same as in where2-6.9.
! 348: queryplan {
! 349: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
! 350: }
! 351: } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
! 352: do_test where2-6.12.3 {
! 353: # In this case, the +b disables the affinity conflict and allows
! 354: # the OR optimization to be used again. The result is now an empty
! 355: # set, the same as in where2-6.9.
! 356: queryplan {
! 357: SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
! 358: }
! 359: } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
! 360: do_test where2-6.13 {
! 361: # The addition of +a on the second term disabled the OR optimization.
! 362: # But we should still get the same empty-set result as in where2-6.9.
! 363: queryplan {
! 364: SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
! 365: }
! 366: } {nosort t2249b {} t2249a {}}
! 367: }
! 368:
! 369: # Variations on the order of terms in a WHERE clause in order
! 370: # to make sure the OR optimizer can recognize them all.
! 371: do_test where2-6.20 {
! 372: queryplan {
! 373: SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
! 374: }
! 375: } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
! 376: ifcapable explain&&subquery {
! 377: # These tests are not run if subquery support is not included in the
! 378: # build. This is because these tests test the "a = 1 OR a = 2" to
! 379: # "a IN (1, 2)" optimisation transformation, which is not enabled if
! 380: # subqueries and the IN operator is not available.
! 381: #
! 382: do_test where2-6.21 {
! 383: queryplan {
! 384: SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
! 385: }
! 386: } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
! 387: do_test where2-6.22 {
! 388: queryplan {
! 389: SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
! 390: }
! 391: } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
! 392: do_test where2-6.23 {
! 393: queryplan {
! 394: SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
! 395: }
! 396: } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
! 397: }
! 398:
! 399: # Unique queries (queries that are guaranteed to return only a single
! 400: # row of result) do not call the sorter. But all tables must give
! 401: # a unique result. If any one table in the join does not give a unique
! 402: # result then sorting is necessary.
! 403: #
! 404: do_test where2-7.1 {
! 405: cksort {
! 406: create table t8(a unique, b, c);
! 407: insert into t8 values(1,2,3);
! 408: insert into t8 values(2,3,4);
! 409: create table t9(x,y);
! 410: insert into t9 values(2,4);
! 411: insert into t9 values(2,3);
! 412: select y from t8, t9 where a=1 order by a, y;
! 413: }
! 414: } {3 4 sort}
! 415: do_test where2-7.2 {
! 416: cksort {
! 417: select * from t8 where a=1 order by b, c
! 418: }
! 419: } {1 2 3 nosort}
! 420: do_test where2-7.3 {
! 421: cksort {
! 422: select * from t8, t9 where a=1 and y=3 order by b, x
! 423: }
! 424: } {1 2 3 2 3 sort}
! 425: do_test where2-7.4 {
! 426: cksort {
! 427: create unique index i9y on t9(y);
! 428: select * from t8, t9 where a=1 and y=3 order by b, x
! 429: }
! 430: } {1 2 3 2 3 nosort}
! 431:
! 432: # Ticket #1807. Using IN constrains on multiple columns of
! 433: # a multi-column index.
! 434: #
! 435: ifcapable subquery {
! 436: do_test where2-8.1 {
! 437: execsql {
! 438: SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
! 439: }
! 440: } {}
! 441: do_test where2-8.2 {
! 442: execsql {
! 443: SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
! 444: }
! 445: } {}
! 446: execsql {CREATE TABLE tx AS SELECT * FROM t1}
! 447: do_test where2-8.3 {
! 448: execsql {
! 449: SELECT w FROM t1
! 450: WHERE x IN (SELECT x FROM tx WHERE rowid<0)
! 451: AND +y IN (SELECT y FROM tx WHERE rowid=1)
! 452: }
! 453: } {}
! 454: do_test where2-8.4 {
! 455: execsql {
! 456: SELECT w FROM t1
! 457: WHERE x IN (SELECT x FROM tx WHERE rowid=1)
! 458: AND y IN (SELECT y FROM tx WHERE rowid<0)
! 459: }
! 460: } {}
! 461: #set sqlite_where_trace 1
! 462: do_test where2-8.5 {
! 463: execsql {
! 464: CREATE INDEX tx_xyz ON tx(x, y, z, w);
! 465: SELECT w FROM tx
! 466: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 467: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 468: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
! 469: }
! 470: } {12 13 14}
! 471: do_test where2-8.6 {
! 472: execsql {
! 473: SELECT w FROM tx
! 474: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 475: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
! 476: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 477: }
! 478: } {12 13 14}
! 479: do_test where2-8.7 {
! 480: execsql {
! 481: SELECT w FROM tx
! 482: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
! 483: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 484: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 485: }
! 486: } {10 11 12 13 14 15}
! 487: do_test where2-8.8 {
! 488: execsql {
! 489: SELECT w FROM tx
! 490: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 491: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 492: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 493: }
! 494: } {10 11 12 13 14 15 16 17 18 19 20}
! 495: do_test where2-8.9 {
! 496: execsql {
! 497: SELECT w FROM tx
! 498: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 499: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 500: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
! 501: }
! 502: } {}
! 503: do_test where2-8.10 {
! 504: execsql {
! 505: SELECT w FROM tx
! 506: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 507: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
! 508: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 509: }
! 510: } {}
! 511: do_test where2-8.11 {
! 512: execsql {
! 513: SELECT w FROM tx
! 514: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
! 515: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 516: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 517: }
! 518: } {}
! 519: do_test where2-8.12 {
! 520: execsql {
! 521: SELECT w FROM tx
! 522: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 523: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 524: AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
! 525: }
! 526: } {}
! 527: do_test where2-8.13 {
! 528: execsql {
! 529: SELECT w FROM tx
! 530: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 531: AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
! 532: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 533: }
! 534: } {}
! 535: do_test where2-8.14 {
! 536: execsql {
! 537: SELECT w FROM tx
! 538: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
! 539: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 540: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 541: }
! 542: } {}
! 543: do_test where2-8.15 {
! 544: execsql {
! 545: SELECT w FROM tx
! 546: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 547: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 548: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
! 549: }
! 550: } {}
! 551: do_test where2-8.16 {
! 552: execsql {
! 553: SELECT w FROM tx
! 554: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
! 555: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
! 556: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 557: }
! 558: } {}
! 559: do_test where2-8.17 {
! 560: execsql {
! 561: SELECT w FROM tx
! 562: WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
! 563: AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
! 564: AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
! 565: }
! 566: } {}
! 567: do_test where2-8.18 {
! 568: execsql {
! 569: SELECT w FROM tx
! 570: WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
! 571: AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
! 572: AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
! 573: }
! 574: } {}
! 575: do_test where2-8.19 {
! 576: execsql {
! 577: SELECT w FROM tx
! 578: WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
! 579: AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
! 580: AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
! 581: }
! 582: } {}
! 583: do_test where2-8.20 {
! 584: execsql {
! 585: SELECT w FROM tx
! 586: WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
! 587: AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
! 588: AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
! 589: }
! 590: } {}
! 591: }
! 592:
! 593: # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
! 594: # when we have an index on A and B.
! 595: #
! 596: ifcapable or_opt&&tclvar {
! 597: do_test where2-9.1 {
! 598: execsql {
! 599: BEGIN;
! 600: CREATE TABLE t10(a,b,c);
! 601: INSERT INTO t10 VALUES(1,1,1);
! 602: INSERT INTO t10 VALUES(1,2,2);
! 603: INSERT INTO t10 VALUES(1,3,3);
! 604: }
! 605: for {set i 4} {$i<=1000} {incr i} {
! 606: execsql {INSERT INTO t10 VALUES(1,$i,$i)}
! 607: }
! 608: execsql {
! 609: CREATE INDEX i10 ON t10(a,b);
! 610: COMMIT;
! 611: SELECT count(*) FROM t10;
! 612: }
! 613: } 1000
! 614: ifcapable subquery {
! 615: do_test where2-9.2 {
! 616: count {
! 617: SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
! 618: }
! 619: } {1 2 2 1 3 3 7}
! 620: }
! 621: }
! 622:
! 623: # Indices with redundant columns
! 624: #
! 625: do_test where2-11.1 {
! 626: execsql {
! 627: CREATE TABLE t11(a,b,c,d);
! 628: CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice.
! 629: INSERT INTO t11 VALUES(1,2,3,4);
! 630: INSERT INTO t11 VALUES(5,6,7,8);
! 631: INSERT INTO t11 VALUES(1,2,9,10);
! 632: INSERT INTO t11 VALUES(5,11,12,13);
! 633: SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c;
! 634: }
! 635: } {3 9}
! 636: do_test where2-11.2 {
! 637: execsql {
! 638: CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column
! 639: SELECT d FROM t11 WHERE c=9;
! 640: }
! 641: } {10}
! 642: do_test where2-11.3 {
! 643: execsql {
! 644: SELECT d FROM t11 WHERE c IN (1,2,3,4,5);
! 645: }
! 646: } {4}
! 647: do_test where2-11.4 {
! 648: execsql {
! 649: SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d;
! 650: }
! 651: } {4 8 10}
! 652:
! 653:
! 654: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to where2.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