Annotation of embedaddon/sqlite3/test/whereB.test, revision 1.1
1.1 ! misho 1: # 2009 August 13
! 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 WHERE clause conditions with
! 13: # subtle affinity issues.
! 14: #
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # For this set of tests:
! 20: #
! 21: # * t1.y holds an integer value with affinity NONE
! 22: # * t2.b holds a text value with affinity TEXT
! 23: #
! 24: # These values are not equal and because neither affinity is NUMERIC
! 25: # no type conversion occurs.
! 26: #
! 27: do_test whereB-1.1 {
! 28: db eval {
! 29: CREATE TABLE t1(x,y); -- affinity of t1.y is NONE
! 30: INSERT INTO t1 VALUES(1,99);
! 31:
! 32: CREATE TABLE t2(a, b TEXT); -- affinity of t2.b is TEXT
! 33: CREATE INDEX t2b ON t2(b);
! 34: INSERT INTO t2 VALUES(2,99);
! 35:
! 36: SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
! 37: }
! 38: } {1 2 0}
! 39: do_test whereB-1.2 {
! 40: db eval {
! 41: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 42: }
! 43: } {}
! 44: do_test whereB-1.3 {
! 45: db eval {
! 46: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 47: }
! 48: } {}
! 49: do_test whereB-1.4 {
! 50: db eval {
! 51: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 52: }
! 53: } {}
! 54: do_test whereB-1.100 {
! 55: db eval {
! 56: DROP INDEX t2b;
! 57: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 58: }
! 59: } {}
! 60: do_test whereB-1.101 {
! 61: db eval {
! 62: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 63: }
! 64: } {}
! 65: do_test whereB-1.102 {
! 66: db eval {
! 67: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 68: }
! 69: } {}
! 70:
! 71: # For this set of tests:
! 72: #
! 73: # * t1.y holds a text value with affinity TEXT
! 74: # * t2.b holds an integer value with affinity NONE
! 75: #
! 76: # These values are not equal and because neither affinity is NUMERIC
! 77: # no type conversion occurs.
! 78: #
! 79: do_test whereB-2.1 {
! 80: db eval {
! 81: DROP TABLE t1;
! 82: DROP TABLE t2;
! 83:
! 84: CREATE TABLE t1(x, y TEXT); -- affinity of t1.y is TEXT
! 85: INSERT INTO t1 VALUES(1,99);
! 86:
! 87: CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
! 88: CREATE INDEX t2b ON t2(b);
! 89: INSERT INTO t2 VALUES(2,99);
! 90:
! 91: SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
! 92: }
! 93: } {1 2 0}
! 94: do_test whereB-2.2 {
! 95: db eval {
! 96: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 97: }
! 98: } {}
! 99: do_test whereB-2.3 {
! 100: db eval {
! 101: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 102: }
! 103: } {}
! 104: do_test whereB-2.4 {
! 105: db eval {
! 106: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 107: }
! 108: } {}
! 109: do_test whereB-2.100 {
! 110: db eval {
! 111: DROP INDEX t2b;
! 112: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 113: }
! 114: } {}
! 115: do_test whereB-2.101 {
! 116: db eval {
! 117: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 118: }
! 119: } {}
! 120: do_test whereB-2.102 {
! 121: db eval {
! 122: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 123: }
! 124: } {}
! 125:
! 126: # For this set of tests:
! 127: #
! 128: # * t1.y holds a text value with affinity NONE
! 129: # * t2.b holds an integer value with affinity NONE
! 130: #
! 131: # These values are not equal and because neither affinity is NUMERIC
! 132: # no type conversion occurs.
! 133: #
! 134: do_test whereB-3.1 {
! 135: db eval {
! 136: DROP TABLE t1;
! 137: DROP TABLE t2;
! 138:
! 139: CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
! 140: INSERT INTO t1 VALUES(1,99);
! 141:
! 142: CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
! 143: CREATE INDEX t2b ON t2(b);
! 144: INSERT INTO t2 VALUES(2,'99');
! 145:
! 146: SELECT x, a, y=b FROM t1, t2;
! 147: }
! 148: } {1 2 0}
! 149: do_test whereB-3.2 {
! 150: db eval {
! 151: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 152: }
! 153: } {}
! 154: do_test whereB-3.3 {
! 155: db eval {
! 156: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 157: }
! 158: } {}
! 159: do_test whereB-3.4 {
! 160: db eval {
! 161: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 162: }
! 163: } {}
! 164: do_test whereB-3.100 {
! 165: db eval {
! 166: DROP INDEX t2b;
! 167: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 168: }
! 169: } {}
! 170: do_test whereB-3.101 {
! 171: db eval {
! 172: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 173: }
! 174: } {}
! 175: do_test whereB-3.102 {
! 176: db eval {
! 177: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 178: }
! 179: } {}
! 180:
! 181:
! 182: # For this set of tests:
! 183: #
! 184: # * t1.y holds a text value with affinity NONE
! 185: # * t2.b holds an integer value with affinity NUMERIC
! 186: #
! 187: # Because t2.b has a numeric affinity, type conversion should occur
! 188: # and the two fields should be equal.
! 189: #
! 190: do_test whereB-4.1 {
! 191: db eval {
! 192: DROP TABLE t1;
! 193: DROP TABLE t2;
! 194:
! 195: CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
! 196: INSERT INTO t1 VALUES(1,'99');
! 197:
! 198: CREATE TABLE t2(a, b NUMERIC); -- affinity of t2.b is NUMERIC
! 199: CREATE INDEX t2b ON t2(b);
! 200: INSERT INTO t2 VALUES(2,99);
! 201:
! 202: SELECT x, a, y=b FROM t1, t2;
! 203: }
! 204: } {1 2 1}
! 205: do_test whereB-4.2 {
! 206: db eval {
! 207: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 208: }
! 209: } {1 2 1}
! 210: do_test whereB-4.3 {
! 211: db eval {
! 212: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 213: }
! 214: } {1 2 1}
! 215: do_test whereB-4.4 {
! 216: # In this case the unary "+" operator removes the column affinity so
! 217: # the columns compare false
! 218: db eval {
! 219: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 220: }
! 221: } {}
! 222: do_test whereB-4.100 {
! 223: db eval {
! 224: DROP INDEX t2b;
! 225: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 226: }
! 227: } {1 2 1}
! 228: do_test whereB-4.101 {
! 229: db eval {
! 230: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 231: }
! 232: } {1 2 1}
! 233: do_test whereB-4.102 {
! 234: # In this case the unary "+" operator removes the column affinity so
! 235: # the columns compare false
! 236: db eval {
! 237: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 238: }
! 239: } {}
! 240:
! 241:
! 242:
! 243: # For this set of tests:
! 244: #
! 245: # * t1.y holds a text value with affinity NONE
! 246: # * t2.b holds an integer value with affinity INTEGER
! 247: #
! 248: # Because t2.b has a numeric affinity, type conversion should occur
! 249: # and the two fields should be equal.
! 250: #
! 251: do_test whereB-5.1 {
! 252: db eval {
! 253: DROP TABLE t1;
! 254: DROP TABLE t2;
! 255:
! 256: CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
! 257: INSERT INTO t1 VALUES(1,'99');
! 258:
! 259: CREATE TABLE t2(a, b INT); -- affinity of t2.b is INTEGER
! 260: CREATE INDEX t2b ON t2(b);
! 261: INSERT INTO t2 VALUES(2,99);
! 262:
! 263: SELECT x, a, y=b FROM t1, t2;
! 264: }
! 265: } {1 2 1}
! 266: do_test whereB-5.2 {
! 267: db eval {
! 268: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 269: }
! 270: } {1 2 1}
! 271: do_test whereB-5.3 {
! 272: db eval {
! 273: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 274: }
! 275: } {1 2 1}
! 276: do_test whereB-5.4 {
! 277: # In this case the unary "+" operator removes the column affinity so
! 278: # the columns compare false
! 279: db eval {
! 280: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 281: }
! 282: } {}
! 283: do_test whereB-5.100 {
! 284: db eval {
! 285: DROP INDEX t2b;
! 286: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 287: }
! 288: } {1 2 1}
! 289: do_test whereB-5.101 {
! 290: db eval {
! 291: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 292: }
! 293: } {1 2 1}
! 294: do_test whereB-5.102 {
! 295: # In this case the unary "+" operator removes the column affinity so
! 296: # the columns compare false
! 297: db eval {
! 298: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 299: }
! 300: } {}
! 301:
! 302:
! 303: # For this set of tests:
! 304: #
! 305: # * t1.y holds a text value with affinity NONE
! 306: # * t2.b holds an integer value with affinity REAL
! 307: #
! 308: # Because t2.b has a numeric affinity, type conversion should occur
! 309: # and the two fields should be equal.
! 310: #
! 311: do_test whereB-6.1 {
! 312: db eval {
! 313: DROP TABLE t1;
! 314: DROP TABLE t2;
! 315:
! 316: CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
! 317: INSERT INTO t1 VALUES(1,'99');
! 318:
! 319: CREATE TABLE t2(a, b REAL); -- affinity of t2.b is REAL
! 320: CREATE INDEX t2b ON t2(b);
! 321: INSERT INTO t2 VALUES(2,99.0);
! 322:
! 323: SELECT x, a, y=b FROM t1, t2;
! 324: }
! 325: } {1 2 1}
! 326: do_test whereB-6.2 {
! 327: db eval {
! 328: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 329: }
! 330: } {1 2 1}
! 331: do_test whereB-6.3 {
! 332: db eval {
! 333: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 334: }
! 335: } {1 2 1}
! 336: do_test whereB-6.4 {
! 337: # In this case the unary "+" operator removes the column affinity so
! 338: # the columns compare false
! 339: db eval {
! 340: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 341: }
! 342: } {}
! 343: do_test whereB-6.100 {
! 344: db eval {
! 345: DROP INDEX t2b;
! 346: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 347: }
! 348: } {1 2 1}
! 349: do_test whereB-6.101 {
! 350: db eval {
! 351: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 352: }
! 353: } {1 2 1}
! 354: do_test whereB-6.102 {
! 355: # In this case the unary "+" operator removes the column affinity so
! 356: # the columns compare false
! 357: db eval {
! 358: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 359: }
! 360: } {}
! 361:
! 362:
! 363: # For this set of tests:
! 364: #
! 365: # * t1.y holds an integer value with affinity NUMERIC
! 366: # * t2.b holds a text value with affinity NONE
! 367: #
! 368: # Because t1.y has a numeric affinity, type conversion should occur
! 369: # and the two fields should be equal.
! 370: #
! 371: do_test whereB-7.1 {
! 372: db eval {
! 373: DROP TABLE t1;
! 374: DROP TABLE t2;
! 375:
! 376: CREATE TABLE t1(x, y NUMERIC); -- affinity of t1.y is NUMERIC
! 377: INSERT INTO t1 VALUES(1,99);
! 378:
! 379: CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
! 380: CREATE INDEX t2b ON t2(b);
! 381: INSERT INTO t2 VALUES(2,'99');
! 382:
! 383: SELECT x, a, y=b FROM t1, t2;
! 384: }
! 385: } {1 2 1}
! 386: do_test whereB-7.2 {
! 387: db eval {
! 388: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 389: }
! 390: } {1 2 1}
! 391: do_test whereB-7.3 {
! 392: db eval {
! 393: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 394: }
! 395: } {1 2 1}
! 396: do_test whereB-7.4 {
! 397: # In this case the unary "+" operator removes the column affinity so
! 398: # the columns compare false
! 399: db eval {
! 400: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 401: }
! 402: } {}
! 403: do_test whereB-7.100 {
! 404: db eval {
! 405: DROP INDEX t2b;
! 406: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 407: }
! 408: } {1 2 1}
! 409: do_test whereB-7.101 {
! 410: db eval {
! 411: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 412: }
! 413: } {1 2 1}
! 414: do_test whereB-7.102 {
! 415: # In this case the unary "+" operator removes the column affinity so
! 416: # the columns compare false
! 417: db eval {
! 418: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 419: }
! 420: } {}
! 421:
! 422: # For this set of tests:
! 423: #
! 424: # * t1.y holds an integer value with affinity INTEGER
! 425: # * t2.b holds a text value with affinity NONE
! 426: #
! 427: # Because t1.y has a numeric affinity, type conversion should occur
! 428: # and the two fields should be equal.
! 429: #
! 430: do_test whereB-8.1 {
! 431: db eval {
! 432: DROP TABLE t1;
! 433: DROP TABLE t2;
! 434:
! 435: CREATE TABLE t1(x, y INT); -- affinity of t1.y is INTEGER
! 436: INSERT INTO t1 VALUES(1,99);
! 437:
! 438: CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
! 439: CREATE INDEX t2b ON t2(b);
! 440: INSERT INTO t2 VALUES(2,'99');
! 441:
! 442: SELECT x, a, y=b FROM t1, t2;
! 443: }
! 444: } {1 2 1}
! 445: do_test whereB-8.2 {
! 446: db eval {
! 447: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 448: }
! 449: } {1 2 1}
! 450: do_test whereB-8.3 {
! 451: db eval {
! 452: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 453: }
! 454: } {1 2 1}
! 455: do_test whereB-8.4 {
! 456: # In this case the unary "+" operator removes the column affinity so
! 457: # the columns compare false
! 458: db eval {
! 459: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 460: }
! 461: } {}
! 462: do_test whereB-8.100 {
! 463: db eval {
! 464: DROP INDEX t2b;
! 465: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 466: }
! 467: } {1 2 1}
! 468: do_test whereB-8.101 {
! 469: db eval {
! 470: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 471: }
! 472: } {1 2 1}
! 473: do_test whereB-8.102 {
! 474: # In this case the unary "+" operator removes the column affinity so
! 475: # the columns compare false
! 476: db eval {
! 477: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 478: }
! 479: } {}
! 480:
! 481: # For this set of tests:
! 482: #
! 483: # * t1.y holds an integer value with affinity REAL
! 484: # * t2.b holds a text value with affinity NONE
! 485: #
! 486: # Because t1.y has a numeric affinity, type conversion should occur
! 487: # and the two fields should be equal.
! 488: #
! 489: do_test whereB-9.1 {
! 490: db eval {
! 491: DROP TABLE t1;
! 492: DROP TABLE t2;
! 493:
! 494: CREATE TABLE t1(x, y REAL); -- affinity of t1.y is REAL
! 495: INSERT INTO t1 VALUES(1,99.0);
! 496:
! 497: CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
! 498: CREATE INDEX t2b ON t2(b);
! 499: INSERT INTO t2 VALUES(2,'99');
! 500:
! 501: SELECT x, a, y=b FROM t1, t2;
! 502: }
! 503: } {1 2 1}
! 504: do_test whereB-9.2 {
! 505: db eval {
! 506: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 507: }
! 508: } {1 2 1}
! 509: do_test whereB-9.3 {
! 510: db eval {
! 511: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 512: }
! 513: } {1 2 1}
! 514: do_test whereB-9.4 {
! 515: # In this case the unary "+" operator removes the column affinity so
! 516: # the columns compare false
! 517: db eval {
! 518: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 519: }
! 520: } {}
! 521: do_test whereB-9.100 {
! 522: db eval {
! 523: DROP INDEX t2b;
! 524: SELECT x, a, y=b FROM t1, t2 WHERE y=b;
! 525: }
! 526: } {1 2 1}
! 527: do_test whereB-9.101 {
! 528: db eval {
! 529: SELECT x, a, y=b FROM t1, t2 WHERE b=y;
! 530: }
! 531: } {1 2 1}
! 532: do_test whereB-9.102 {
! 533: # In this case the unary "+" operator removes the column affinity so
! 534: # the columns compare false
! 535: db eval {
! 536: SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
! 537: }
! 538: } {}
! 539:
! 540:
! 541:
! 542:
! 543: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to whereB.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