Annotation of embedaddon/sqlite3/test/where8.test, revision 1.1
1.1 ! misho 1: # 2008 December 23
! 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 focus
! 12: # is testing of where.c. More specifically, the focus is the optimization
! 13: # of WHERE clauses that feature the OR operator.
! 14: #
! 15: # $Id: where8.test,v 1.9 2009/07/31 06:14:52 danielk1977 Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Test organization:
! 21: #
! 22: # where8-1.*: Tests to demonstrate simple cases work with a single table
! 23: # in the FROM clause.
! 24: #
! 25: # where8-2.*: Tests surrounding virtual tables and the OR optimization.
! 26: #
! 27: # where8-3.*: Tests with more than one table in the FROM clause.
! 28: #
! 29:
! 30: proc execsql_status {sql {db db}} {
! 31: set result [uplevel $db eval [list $sql]]
! 32: concat $result [db status step] [db status sort]
! 33: }
! 34:
! 35: proc execsql_status2 {sql {db db}} {
! 36: set ::sqlite_search_count 0
! 37: set result [uplevel [list execsql_status $sql $db]]
! 38: concat $result $::sqlite_search_count
! 39: }
! 40:
! 41: do_test where8-1.1 {
! 42: execsql {
! 43: CREATE TABLE t1(a, b TEXT, c);
! 44: CREATE INDEX i1 ON t1(a);
! 45: CREATE INDEX i2 ON t1(b);
! 46:
! 47: INSERT INTO t1 VALUES(1, 'one', 'I');
! 48: INSERT INTO t1 VALUES(2, 'two', 'II');
! 49: INSERT INTO t1 VALUES(3, 'three', 'III');
! 50: INSERT INTO t1 VALUES(4, 'four', 'IV');
! 51: INSERT INTO t1 VALUES(5, 'five', 'V');
! 52: INSERT INTO t1 VALUES(6, 'six', 'VI');
! 53: INSERT INTO t1 VALUES(7, 'seven', 'VII');
! 54: INSERT INTO t1 VALUES(8, 'eight', 'VIII');
! 55: INSERT INTO t1 VALUES(9, 'nine', 'IX');
! 56: INSERT INTO t1 VALUES(10, 'ten', 'X');
! 57: }
! 58: } {}
! 59:
! 60: do_test where8-1.2 {
! 61: execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' }
! 62: } {I IX 0 0 6}
! 63:
! 64: do_test where8-1.3 {
! 65: execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
! 66: } {IX X II 0 0 6}
! 67:
! 68: do_test where8-1.4 {
! 69: execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
! 70: } {IX X III II 0 0 9}
! 71:
! 72: do_test where8-1.5 {
! 73: execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
! 74: } {IX X V IV 0 0 9}
! 75:
! 76: do_test where8-1.6 {
! 77: execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
! 78: } {I III 0 1}
! 79:
! 80: do_test where8-1.7 {
! 81: execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }
! 82: } {I III 0 1}
! 83:
! 84: do_test where8-1.8 {
! 85: # 18 searches. 9 on the index cursor and 9 on the table cursor.
! 86: execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }
! 87: } {II III IV IX 0 0 18}
! 88:
! 89: do_test where8-1.9 {
! 90: execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }
! 91: } {IX X VIII 0 0 6}
! 92:
! 93: do_test where8-1.10 {
! 94: execsql_status2 {
! 95: SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight'
! 96: }
! 97: } {IX VIII 0 0 6}
! 98:
! 99: do_test where8-1.11 {
! 100: execsql_status2 {
! 101: SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine'
! 102: }
! 103: } {IV V VI IX 0 0 10}
! 104:
! 105: do_test where8-1.12.1 {
! 106: execsql_status2 {
! 107: SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5
! 108: }
! 109: } {I II III V 0 0 14}
! 110:
! 111: do_test where8-1.12.2 {
! 112: execsql_status2 {
! 113: SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5
! 114: }
! 115: } {I II III V 9 0 9}
! 116:
! 117: do_test where8-1.13 {
! 118: execsql_status2 {
! 119: SELECT c FROM t1
! 120: WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6
! 121: ORDER BY rowid
! 122: }
! 123: } {II III IV V VI 0 1 18}
! 124: do_test where8-1.14 {
! 125: execsql_status2 {
! 126: SELECT c FROM t1
! 127: WHERE
! 128: a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
! 129: b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
! 130: ORDER BY rowid
! 131: }
! 132: } {II III IV V VI VII VIII IX X 0 1 33}
! 133:
! 134: do_test where8-1.15 {
! 135: execsql_status2 {
! 136: SELECT c FROM t1 WHERE
! 137: a BETWEEN 2 AND 4 OR b = 'nine'
! 138: ORDER BY rowid
! 139: }
! 140: } {II III IV IX 0 1 12}
! 141:
! 142:
! 143: #--------------------------------------------------------------------------
! 144: # Tests where8-2.*: Virtual tables
! 145: #
! 146:
! 147: if 0 {
! 148: ifcapable vtab {
! 149: # Register the 'echo' module used for testing virtual tables.
! 150: #
! 151: register_echo_module [sqlite3_connection_pointer db]
! 152:
! 153: do_test where8-2.1 {
! 154: execsql {
! 155: CREATE VIRTUAL TABLE e1 USING echo(t1);
! 156: SELECT b FROM e1;
! 157: }
! 158: } {one two three four five six seven eight nine ten}
! 159:
! 160: do_test where8-2.2.1 {
! 161: set echo_module ""
! 162: execsql {
! 163: SELECT c FROM e1 WHERE a=1 OR b='three';
! 164: }
! 165: } {I III}
! 166: do_test where8-2.2.2 {
! 167: set echo_module
! 168: } {TODO: What should this be?}
! 169: }
! 170: }
! 171:
! 172: #--------------------------------------------------------------------------
! 173: # Tests where8-3.*: Cases with multiple tables in the FROM clause.
! 174: #
! 175: do_test where8-3.1 {
! 176: execsql {
! 177: CREATE TABLE t2(d, e, f);
! 178: CREATE INDEX i3 ON t2(d);
! 179: CREATE INDEX i4 ON t2(e);
! 180:
! 181: INSERT INTO t2 VALUES(1, NULL, 'I');
! 182: INSERT INTO t2 VALUES(2, 'four', 'IV');
! 183: INSERT INTO t2 VALUES(3, NULL, 'IX');
! 184: INSERT INTO t2 VALUES(4, 'sixteen', 'XVI');
! 185: INSERT INTO t2 VALUES(5, NULL, 'XXV');
! 186: INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI');
! 187: INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX');
! 188: INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV');
! 189: INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX');
! 190: INSERT INTO t2 VALUES(10, NULL, 'C');
! 191: }
! 192: } {}
! 193:
! 194: do_test where8-3.2 {
! 195: execsql_status {
! 196: SELECT a, d FROM t1, t2 WHERE b=e
! 197: }
! 198: } {4 2 9 0}
! 199:
! 200: do_test where8-3.3 {
! 201: execsql_status {
! 202: SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6
! 203: }
! 204: } {2 6 3 6 0 0}
! 205:
! 206: do_test where8-3.4 {
! 207: execsql_status {
! 208: SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a
! 209: }
! 210: } {2 2 3 3 0 0}
! 211:
! 212: do_test where8-3.5 {
! 213: execsql_status {
! 214: SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
! 215: }
! 216: } {2 2 2 4 3 3 3 4 0 0}
! 217:
! 218: do_test where8-3.6 {
! 219: # The first part of the WHERE clause in this query, (a=2 OR a=3) is
! 220: # transformed into "a IN (2, 3)". This is why the sort is required.
! 221: #
! 222: execsql_status {
! 223: SELECT a, d
! 224: FROM t1, t2
! 225: WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
! 226: ORDER BY t1.rowid
! 227: }
! 228: } {2 2 2 4 3 3 3 4 0 1}
! 229: do_test where8-3.7 {
! 230: execsql_status {
! 231: SELECT a, d
! 232: FROM t1, t2
! 233: WHERE a = 2 AND (d = a OR e = 'sixteen')
! 234: ORDER BY t1.rowid
! 235: }
! 236: } {2 2 2 4 0 0}
! 237: do_test where8-3.8 {
! 238: execsql_status {
! 239: SELECT a, d
! 240: FROM t1, t2
! 241: WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
! 242: ORDER BY t1.rowid
! 243: }
! 244: } {2 2 2 4 3 3 3 4 0 1}
! 245:
! 246: do_test where8-3.9 {
! 247: # The "OR c = 'IX'" term forces a linear scan.
! 248: execsql_status {
! 249: SELECT a, d
! 250: FROM t1, t2
! 251: WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')
! 252: ORDER BY t1.rowid
! 253: }
! 254: } {2 2 2 4 3 3 3 4 9 9 9 4 9 0}
! 255:
! 256: do_test where8-3.10 {
! 257: execsql_status {
! 258: SELECT d FROM t2 WHERE e IS NULL OR e = 'four'
! 259: }
! 260: } {1 3 5 10 2 0 0}
! 261:
! 262: do_test where8-3.11 {
! 263: execsql_status {
! 264: SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
! 265: }
! 266: } {1 1 2 2 3 3 4 2 4 4 0 0}
! 267: do_test where8-3.12 {
! 268: execsql_status {
! 269: SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
! 270: }
! 271: } {1 1 2 2 3 3 4 2 4 4 0 0}
! 272: do_test where8-3.13 {
! 273: execsql_status {
! 274: SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
! 275: }
! 276: } {1 1 2 2 3 3 4 2 4 4 9 0}
! 277:
! 278: do_test where8-3.14 {
! 279: execsql_status {
! 280: SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5
! 281: }
! 282: } {IV V 9 0}
! 283:
! 284: do_test where8-3.15 {
! 285: execsql_status {
! 286: SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
! 287: SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
! 288: )
! 289: ORDER BY c
! 290: }
! 291: } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}
! 292:
! 293: #-----------------------------------------------------------------------
! 294: # The following tests - where8-4.* - verify that adding or removing
! 295: # indexes does not change the results returned by various queries.
! 296: #
! 297: do_test where8-4.1 {
! 298: execsql {
! 299: BEGIN;
! 300: CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
! 301: CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
! 302: INSERT INTO t3 VALUES('hills', NULL, 1415926535);
! 303: INSERT INTO t3 VALUES('and', 'of', NULL);
! 304: INSERT INTO t3 VALUES('have', 'towering', 53594.08128);
! 305: INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not');
! 306: INSERT INTO t3 VALUES('same', 5028841971, NULL);
! 307: INSERT INTO t3 VALUES('onlookers', 'in', 8214808651);
! 308: INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL);
! 309: INSERT INTO t3 VALUES(1415926535, 'of', 'are');
! 310: INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots');
! 311: INSERT INTO t3 VALUES('over', 'the', 8628034825);
! 312: INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823);
! 313: INSERT INTO t3 VALUES('onlookers', 'same', 'and');
! 314: INSERT INTO t3 VALUES(NULL, 'light', 6939937510);
! 315: INSERT INTO t3 VALUES('from', 'their', 'viewed');
! 316: INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots');
! 317: INSERT INTO t3 VALUES('from', 'sometimes', 'unalike');
! 318: INSERT INTO t3 VALUES(1339.360726, 'light', 'have');
! 319: INSERT INTO t3 VALUES(6939937510, 3282306647, 'other');
! 320: INSERT INTO t3 VALUES('paintings', 8628034825, 'all');
! 321: INSERT INTO t3 VALUES('paintings', NULL, 'same');
! 322: INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike');
! 323: INSERT INTO t3 VALUES('Alpine', NULL, 'same');
! 324: INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over');
! 325: INSERT INTO t3 VALUES('villages', 'their', 'have');
! 326: INSERT INTO t3 VALUES('unalike', 'remarkably', 'in');
! 327: INSERT INTO t3 VALUES('and', 8979323846, 'and');
! 328: INSERT INTO t3 VALUES(NULL, 1415926535, 'an');
! 329: INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502);
! 330: INSERT INTO t3 VALUES('all', 3421170679, 'the');
! 331: INSERT INTO t3 VALUES('Not', 'and', 1415926535);
! 332: INSERT INTO t3 VALUES('of', 'other', 'light');
! 333: INSERT INTO t3 VALUES(NULL, 'towering', 'Not');
! 334: INSERT INTO t3 VALUES(346.0348610, NULL, 'other');
! 335: INSERT INTO t3 VALUES('Not', 378678316.5, NULL);
! 336: INSERT INTO t3 VALUES('snapshots', 8628034825, 'of');
! 337: INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and');
! 338: INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971);
! 339: INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots');
! 340: INSERT INTO t3 VALUES('writings', 8979323846, 8979323846);
! 341: INSERT INTO t3 VALUES('onlookers', 'his', 'in');
! 342: INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726);
! 343: INSERT INTO t3 VALUES('of', 'Alpine', 'and');
! 344: INSERT INTO t3 VALUES('onlookers', NULL, 'from');
! 345: INSERT INTO t3 VALUES('writings', 'it', 1339.360726);
! 346: INSERT INTO t3 VALUES('it', 'and', 'villages');
! 347: INSERT INTO t3 VALUES('an', 'the', 'villages');
! 348: INSERT INTO t3 VALUES(8214808651, 8214808651, 'same');
! 349: INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535);
! 350: INSERT INTO t3 VALUES(NULL, 8979323846, 'and');
! 351: INSERT INTO t3 VALUES(NULL, 'same', 1339.360726);
! 352: INSERT INTO t4 VALUES('his', 'from', 'an');
! 353: INSERT INTO t4 VALUES('snapshots', 'or', NULL);
! 354: INSERT INTO t4 VALUES('Alpine', 'have', 'it');
! 355: INSERT INTO t4 VALUES('have', 'peak', 'remarkably');
! 356: INSERT INTO t4 VALUES('hills', NULL, 'Not');
! 357: INSERT INTO t4 VALUES('same', 'from', 2643383279);
! 358: INSERT INTO t4 VALUES('have', 'angle', 8628034825);
! 359: INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823);
! 360: INSERT INTO t4 VALUES(0938446095, 'peak', 'of');
! 361: INSERT INTO t4 VALUES(8628034825, 'and', 'same');
! 362: INSERT INTO t4 VALUES('and', 271.2019091, 'their');
! 363: INSERT INTO t4 VALUES('the', 'of', 'remarkably');
! 364: INSERT INTO t4 VALUES('and', 3421170679, 1415926535);
! 365: INSERT INTO t4 VALUES('and', 'in', 'all');
! 366: INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots');
! 367: INSERT INTO t4 VALUES('it', 'are', 'have');
! 368: INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5);
! 369: INSERT INTO t4 VALUES('from', 1415926535, 8628034825);
! 370: INSERT INTO t4 VALUES('snapshots', 'angle', 'have');
! 371: INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not');
! 372: INSERT INTO t4 VALUES('peak', NULL, 0.4811174502);
! 373: INSERT INTO t4 VALUES('same', 'have', 'Alpine');
! 374: INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095);
! 375: INSERT INTO t4 VALUES(8979323846, 'his', 'an');
! 376: INSERT INTO t4 VALUES(NULL, 'and', 3282306647);
! 377: INSERT INTO t4 VALUES('remarkably', NULL, 'Not');
! 378: INSERT INTO t4 VALUES('villages', 4543.266482, 'his');
! 379: INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers');
! 380: INSERT INTO t4 VALUES(1339.360726, 'of', 'the');
! 381: INSERT INTO t4 VALUES('peak', 'other', 'peak');
! 382: INSERT INTO t4 VALUES('it', 'or', 8979323846);
! 383: INSERT INTO t4 VALUES('onlookers', 'Not', 'towering');
! 384: INSERT INTO t4 VALUES(NULL, 'peak', 'Not');
! 385: INSERT INTO t4 VALUES('of', 'have', 6939937510);
! 386: INSERT INTO t4 VALUES('light', 'hills', 0.4811174502);
! 387: INSERT INTO t4 VALUES(5028841971, 'Not', 'it');
! 388: INSERT INTO t4 VALUES('and', 'Not', NULL);
! 389: INSERT INTO t4 VALUES(346.0348610, 'villages', NULL);
! 390: INSERT INTO t4 VALUES(8979323846, NULL, 6939937510);
! 391: INSERT INTO t4 VALUES('an', 'light', 'peak');
! 392: INSERT INTO t4 VALUES(5028841971, 6939937510, 'light');
! 393: INSERT INTO t4 VALUES('sometimes', 'peak', 'peak');
! 394: INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an');
! 395: INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');
! 396: INSERT INTO t4 VALUES('from', 'of', 'all');
! 397: INSERT INTO t4 VALUES(0938446095, 'same', NULL);
! 398: INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);
! 399: INSERT INTO t4 VALUES('his', 'of', 378678316.5);
! 400: INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);
! 401: INSERT INTO t4 VALUES('hills', 'all', 'peak');
! 402: CREATE TABLE t5(s);
! 403: INSERT INTO t5 VALUES('tab-t5');
! 404: CREATE TABLE t6(t);
! 405: INSERT INTO t6 VALUES(123456);
! 406: COMMIT;
! 407: }
! 408: } {}
! 409:
! 410: catch {unset results}
! 411: catch {unset A}
! 412: catch {unset B}
! 413:
! 414: set A 2
! 415: foreach idxsql {
! 416: {
! 417: /* No indexes */
! 418: } {
! 419: CREATE INDEX i5 ON t3(a);
! 420: } {
! 421: CREATE INDEX i5 ON t3(a, b);
! 422: CREATE INDEX i6 ON t4(f);
! 423: } {
! 424: CREATE UNIQUE INDEX i5 ON t3(a, b);
! 425: CREATE INDEX i7 ON t3(c);
! 426: CREATE INDEX i6 ON t4(f);
! 427: CREATE INDEX i8 ON t4(h);
! 428: } {
! 429: CREATE INDEX i5 ON t3(a, b, c);
! 430: CREATE INDEX i6 ON t4(f, g, h);
! 431: CREATE INDEX i7 ON t3(c, b, a);
! 432: CREATE INDEX i8 ON t4(h, g, f);
! 433: }
! 434: } {
! 435:
! 436: execsql {
! 437: DROP INDEX IF EXISTS i5;
! 438: DROP INDEX IF EXISTS i6;
! 439: DROP INDEX IF EXISTS i7;
! 440: DROP INDEX IF EXISTS i8;
! 441: }
! 442: execsql $idxsql
! 443:
! 444: foreach {B sql} {
! 445: 1 { SELECT * FROM t3 WHERE c LIKE b }
! 446: 2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' }
! 447: 3 { SELECT * FROM t3 WHERE rowid LIKE '12%' }
! 448: 4 { SELECT * FROM t3 WHERE +c LIKE 'the%' }
! 449: 5 { SELECT * FROM t3 WHERE c LIKE 'the%' }
! 450: 6 { SELECT * FROM t3 WHERE c GLOB '*llo' }
! 451:
! 452: 7 { SELECT * FROM t3 WHERE a = 'angle' }
! 453: 8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 }
! 454: 9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f }
! 455: 10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h }
! 456: 11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL }
! 457: 12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 }
! 458:
! 459: 13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 }
! 460: 14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL }
! 461: 15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' }
! 462: 16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a }
! 463: 17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' }
! 464: 18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 }
! 465: 19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 }
! 466: 20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' }
! 467: 21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' }
! 468: 22 { SELECT * FROM t3 WHERE b = b AND a > 'are' }
! 469: 23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' }
! 470: 24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' }
! 471: 25 { SELECT * FROM t3 WHERE b = 'from' }
! 472: 26 { SELECT * FROM t3 WHERE b = 4543.266482 }
! 473: 27 { SELECT * FROM t3 WHERE c < 3282306647 }
! 474: 28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c }
! 475: 29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a }
! 476: 30 { SELECT * FROM t3 WHERE c = 'peak' }
! 477: 31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b }
! 478: 32 { SELECT * FROM t3 WHERE 'writings' <= b }
! 479: 33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 }
! 480: 34 { SELECT * FROM t3 WHERE a IS NULL }
! 481: 35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 }
! 482: 36 { SELECT * FROM t3 WHERE 'and' >= c }
! 483: 37 { SELECT * FROM t3 WHERE c < 'from' }
! 484: 38 { SELECT * FROM t3 WHERE 'his' < c OR b < b }
! 485: 39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b }
! 486: 40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b }
! 487: 41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a }
! 488: 42 { SELECT * FROM t3 WHERE b = c }
! 489: 43 { SELECT * FROM t3 WHERE c > a AND b < 'all' }
! 490: 44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a }
! 491: 45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' }
! 492: 46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL }
! 493: 47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b }
! 494: 48 { SELECT * FROM t3 WHERE a IS NULL }
! 495: 49 { SELECT * FROM t3 WHERE a > 'other' }
! 496: 50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c }
! 497: 51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b }
! 498: 52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b }
! 499: 53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 }
! 500: 54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b }
! 501: 55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c }
! 502: 56 { SELECT * FROM t3 WHERE b <= b AND c > b }
! 503: 57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c }
! 504: 58 { SELECT * FROM t3 WHERE b < c OR b = c }
! 505: 59 { SELECT * FROM t3 WHERE c < b AND b >= 'it' }
! 506: 60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a }
! 507: 61 { SELECT * FROM t3 WHERE b = c }
! 508: 62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b }
! 509: 63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' }
! 510: 64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a }
! 511: 65 { SELECT * FROM t3 WHERE a > c }
! 512: 66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b }
! 513: 67 { SELECT * FROM t3 WHERE c = a OR b < c }
! 514: 68 { SELECT * FROM t3 WHERE b > a }
! 515: 69 { SELECT * FROM t3 WHERE a < b OR a > 4543.266482 OR 'same' = b }
! 516: 70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b }
! 517: 71 { SELECT * FROM t3 WHERE c > a }
! 518: 72 { SELECT * FROM t3 WHERE c > b }
! 519: 73 { SELECT * FROM t3 WHERE b <= a }
! 520: 74 { SELECT * FROM t3 WHERE 3282306647 < b AND a >= 'or' OR a >= 378678316.5 }
! 521: 75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b }
! 522: 76 { SELECT * FROM t3 WHERE 'and' < b OR b < c OR c > 1339.360726 }
! 523: 77 { SELECT * FROM t3 WHERE b <= c }
! 524: 78 { SELECT * FROM t3 WHERE 'in' <= c }
! 525: 79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b }
! 526: 80 { SELECT * FROM t3 WHERE 'over' < b }
! 527: 81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b }
! 528: 82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b }
! 529: 83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' }
! 530: 84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL }
! 531: 85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 }
! 532: 86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL }
! 533: 87 { SELECT * FROM t3 WHERE b > 'the' }
! 534: 88 { SELECT * FROM t3 WHERE 'and' = a }
! 535: 89 { SELECT * FROM t3 WHERE b >= b }
! 536: 90 { SELECT * FROM t3 WHERE b >= 8979323846 }
! 537: 91 { SELECT * FROM t3 WHERE c <= a }
! 538: 92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 }
! 539: 93 { SELECT * FROM t3 WHERE c > 3282306647 }
! 540: 94 { SELECT * FROM t3 WHERE b > c }
! 541: 95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c }
! 542: 96 { SELECT * FROM t3 WHERE 'angle' > a }
! 543: 97 { SELECT * FROM t3 WHERE 'paintings' >= a }
! 544: 98 { SELECT * FROM t3 WHERE 'or' >= c }
! 545: 99 { SELECT * FROM t3 WHERE c < b }
! 546:
! 547:
! 548: 101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 }
! 549: 102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h }
! 550: 103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' }
! 551: 104 { SELECT * FROM t3, t4 WHERE c = a }
! 552: 105 { SELECT * FROM t3, t4 WHERE 'of' >= h }
! 553: 106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' }
! 554: 107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b }
! 555: 108 { SELECT * FROM t3, t4 WHERE a < 'the' }
! 556: 109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' }
! 557: 110 { SELECT * FROM t3, t4 WHERE c IS NULL }
! 558: 111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 }
! 559: 112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h }
! 560: 113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a }
! 561: 114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a }
! 562: 115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b }
! 563: 116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' }
! 564: 117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c }
! 565: 118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f }
! 566: 119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' }
! 567: 120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 }
! 568: 121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c }
! 569: 122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h }
! 570: 123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 }
! 571: 124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a }
! 572: 125 { SELECT * FROM t3, t4 WHERE h = 2643383279 }
! 573: 126 { SELECT * FROM t3, t4 WHERE a <= 'the' }
! 574: 127 { SELECT * FROM t3, t4 WHERE h <= c }
! 575: 128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f }
! 576: 129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' < b OR b > g }
! 577: 130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' }
! 578: 131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 }
! 579: 132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h }
! 580: 133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c }
! 581: 134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 }
! 582: 135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b }
! 583: 136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f }
! 584: 137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f }
! 585: 138 { SELECT * FROM t3, t4 WHERE 'other' >= g }
! 586: 139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 }
! 587: 140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' }
! 588: 141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 }
! 589: 142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f }
! 590: 143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 }
! 591: 144 { SELECT * FROM t3, t4 WHERE 5028841971 = h }
! 592: 145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g }
! 593: 146 { SELECT * FROM t3, t4 WHERE c < 5028841971 }
! 594: 147 { SELECT * FROM t3, t4 WHERE a > a }
! 595: 148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' }
! 596: 149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c }
! 597: 150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c }
! 598: 151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' }
! 599: 152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 }
! 600: 153 { SELECT * FROM t3, t4 WHERE 'it' = b }
! 601: 154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 }
! 602: 155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 }
! 603: 156 { SELECT * FROM t3, t4 WHERE 'and' <= b }
! 604: 157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a }
! 605: 158 { SELECT * FROM t3, t4 WHERE a < 'an' }
! 606: 159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f }
! 607: 160 { SELECT * FROM t3, t4 WHERE b = 'peak' }
! 608: 161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h }
! 609: 162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c }
! 610: 163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL }
! 611: 164 { SELECT * FROM t3, t4 WHERE a >= g }
! 612: 165 { SELECT * FROM t3, t4 WHERE c IS NULL }
! 613: 166 { SELECT * FROM t3, t4 WHERE h >= h }
! 614: 167 { SELECT * FROM t3, t4 WHERE 'over' <= h }
! 615: 168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b }
! 616: 169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 }
! 617: 170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g }
! 618: 171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g }
! 619: 172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f }
! 620: 173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c }
! 621: 174 { SELECT * FROM t3, t4 WHERE 'of' <= a }
! 622: 175 { SELECT * FROM t3, t4 WHERE f > 0938446095 }
! 623: 176 { SELECT * FROM t3, t4 WHERE a = g }
! 624: 177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' }
! 625: 178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f }
! 626: 179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f }
! 627: 180 { SELECT * FROM t3, t4 WHERE f < 'and' }
! 628: 181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h }
! 629: 182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' }
! 630: 183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c }
! 631: 184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' }
! 632: 185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h }
! 633: 186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 }
! 634: 187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f }
! 635: 188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' }
! 636: 189 { SELECT * FROM t3, t4 WHERE b < a }
! 637: 190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' }
! 638: 191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g }
! 639: 192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' }
! 640: 193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' }
! 641: 194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g }
! 642: 195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f }
! 643: 196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b }
! 644: 197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g }
! 645: 198 { SELECT * FROM t3, t4 WHERE g < 8979323846 }
! 646: 199 { SELECT * FROM t3, t4 WHERE 'are' <= b }
! 647: 200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825)
! 648: OR (a=6939937510 AND f=2643383279) }
! 649: 201 { SELECT * FROM t3, t4, t5, t6
! 650: WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5)
! 651: OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) }
! 652: 202 { SELECT * FROM t3, t4, t5, t6
! 653: WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5)
! 654: OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) }
! 655:
! 656: } {
! 657: do_test where8-4.$A.$B.1 {
! 658: unset -nocomplain R
! 659: set R [execsql $sql]
! 660: if {![info exists results($B)]} {
! 661: set results($B) $R
! 662: }
! 663: list
! 664: } {}
! 665:
! 666: do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)]
! 667: }
! 668: incr A
! 669: }
! 670: catch {unset results}
! 671: catch {unset A}
! 672: catch {unset B}
! 673:
! 674: # At one point the following tests provoked an invalid write error (writing
! 675: # to memory that had already been freed). It was not possible to demonstrate
! 676: # that this bug could cause a query to return bad data.
! 677: #
! 678: do_test where8-5.1 {
! 679: db close
! 680: sqlite3 db test.db
! 681: sqlite3_db_config_lookaside db 0 0 0
! 682: execsql {
! 683: CREATE TABLE tA(
! 684: a, b, c, d, e, f, g, h,
! 685: i, j, k, l, m, n, o, p
! 686: );
! 687: }
! 688: execsql {
! 689: SELECT * FROM tA WHERE
! 690: a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
! 691: i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
! 692: (p = 1 OR p = 2 OR p = 3)
! 693: }
! 694: } {}
! 695: do_test where8-5.2 {
! 696: execsql {
! 697: SELECT * FROM tA WHERE
! 698: a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
! 699: i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND
! 700: (p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7
! 701: }
! 702: } {}
! 703: do_test where8-5.3 {
! 704: execsql {
! 705: INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8);
! 706: CREATE UNIQUE INDEX tAI ON tA(p);
! 707: CREATE TABLE tB(x);
! 708: INSERT INTO tB VALUES('x');
! 709: }
! 710: execsql {
! 711: SELECT a, x FROM tA LEFT JOIN tB ON (
! 712: a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
! 713: i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
! 714: (p = 1 OR p = 2 OR p = 3)
! 715: )
! 716: }
! 717: } {1 {}}
! 718:
! 719: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to where8.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