Annotation of embedaddon/sqlite3/test/collate2.test, revision 1.1
1.1 ! misho 1: #
! 2: # 2001 September 15
! 3: #
! 4: # The author disclaims copyright to this source code. In place of
! 5: # a legal notice, here is a blessing:
! 6: #
! 7: # May you do good and not evil.
! 8: # May you find forgiveness for yourself and forgive others.
! 9: # May you share freely, never taking more than you give.
! 10: #
! 11: #***********************************************************************
! 12: # This file implements regression tests for SQLite library. The
! 13: # focus of this script is page cache subsystem.
! 14: #
! 15: # $Id: collate2.test,v 1.6 2008/08/20 16:35:10 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: #
! 21: # Tests are organised as follows:
! 22: #
! 23: # collate2-1.* WHERE <expr> expressions (sqliteExprIfTrue).
! 24: # collate2-2.* WHERE NOT <expr> expressions (sqliteExprIfFalse).
! 25: # collate2-3.* SELECT <expr> expressions (sqliteExprCode).
! 26: # collate2-4.* Precedence of collation/data types in binary comparisons
! 27: # collate2-5.* JOIN syntax.
! 28: #
! 29:
! 30: # Create a collation type BACKWARDS for use in testing. This collation type
! 31: # is similar to the built-in TEXT collation type except the order of
! 32: # characters in each string is reversed before the comparison is performed.
! 33: db collate BACKWARDS backwards_collate
! 34: proc backwards_collate {a b} {
! 35: set ra {};
! 36: set rb {}
! 37: foreach c [split $a {}] { set ra $c$ra }
! 38: foreach c [split $b {}] { set rb $c$rb }
! 39: return [string compare $ra $rb]
! 40: }
! 41:
! 42: # The following values are used in these tests:
! 43: # NULL aa ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB
! 44: #
! 45: # The collation orders for each of the tested collation types are:
! 46: #
! 47: # BINARY: NULL AA AB Aa Ab BA BB Ba Bb aA aB aa ab bA bB ba bb
! 48: # NOCASE: NULL aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB
! 49: # BACKWARDS: NULL AA BA aA bA AB BB aB bB Aa Ba aa ba Ab Bb ab bb
! 50: #
! 51: # These tests verify that the default collation type for a column is used
! 52: # for comparison operators (<, >, <=, >=, =) involving that column and
! 53: # an expression that is not a column with a default collation type.
! 54: #
! 55: # The collation sequences BINARY and NOCASE are built-in, the BACKWARDS
! 56: # collation sequence is implemented by the TCL proc backwards_collate
! 57: # above.
! 58: #
! 59: do_test collate2-1.0 {
! 60: execsql {
! 61: CREATE TABLE collate2t1(
! 62: a COLLATE BINARY,
! 63: b COLLATE NOCASE,
! 64: c COLLATE BACKWARDS
! 65: );
! 66: INSERT INTO collate2t1 VALUES( NULL, NULL, NULL );
! 67:
! 68: INSERT INTO collate2t1 VALUES( 'aa', 'aa', 'aa' );
! 69: INSERT INTO collate2t1 VALUES( 'ab', 'ab', 'ab' );
! 70: INSERT INTO collate2t1 VALUES( 'ba', 'ba', 'ba' );
! 71: INSERT INTO collate2t1 VALUES( 'bb', 'bb', 'bb' );
! 72:
! 73: INSERT INTO collate2t1 VALUES( 'aA', 'aA', 'aA' );
! 74: INSERT INTO collate2t1 VALUES( 'aB', 'aB', 'aB' );
! 75: INSERT INTO collate2t1 VALUES( 'bA', 'bA', 'bA' );
! 76: INSERT INTO collate2t1 VALUES( 'bB', 'bB', 'bB' );
! 77:
! 78: INSERT INTO collate2t1 VALUES( 'Aa', 'Aa', 'Aa' );
! 79: INSERT INTO collate2t1 VALUES( 'Ab', 'Ab', 'Ab' );
! 80: INSERT INTO collate2t1 VALUES( 'Ba', 'Ba', 'Ba' );
! 81: INSERT INTO collate2t1 VALUES( 'Bb', 'Bb', 'Bb' );
! 82:
! 83: INSERT INTO collate2t1 VALUES( 'AA', 'AA', 'AA' );
! 84: INSERT INTO collate2t1 VALUES( 'AB', 'AB', 'AB' );
! 85: INSERT INTO collate2t1 VALUES( 'BA', 'BA', 'BA' );
! 86: INSERT INTO collate2t1 VALUES( 'BB', 'BB', 'BB' );
! 87: }
! 88: if {[info exists collate_test_use_index]} {
! 89: execsql {
! 90: CREATE INDEX collate2t1_i1 ON collate2t1(a);
! 91: CREATE INDEX collate2t1_i2 ON collate2t1(b);
! 92: CREATE INDEX collate2t1_i3 ON collate2t1(c);
! 93: }
! 94: }
! 95: } {}
! 96: do_test collate2-1.1 {
! 97: execsql {
! 98: SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;
! 99: }
! 100: } {ab bA bB ba bb}
! 101: do_test collate2-1.1.1 {
! 102: execsql {
! 103: SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1;
! 104: }
! 105: } {ab bA bB ba bb}
! 106: do_test collate2-1.1.2 {
! 107: execsql {
! 108: SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1;
! 109: }
! 110: } {ab bA bB ba bb}
! 111: do_test collate2-1.1.3 {
! 112: execsql {
! 113: SELECT a FROM collate2t1 WHERE c COLLATE binary > 'aa' ORDER BY 1;
! 114: }
! 115: } {ab bA bB ba bb}
! 116: do_test collate2-1.2 {
! 117: execsql {
! 118: SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;
! 119: }
! 120: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 121: do_test collate2-1.2.1 {
! 122: execsql {
! 123: SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa'
! 124: ORDER BY 1, oid;
! 125: }
! 126: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 127: do_test collate2-1.2.2 {
! 128: execsql {
! 129: SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa'
! 130: ORDER BY 1, oid;
! 131: }
! 132: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 133: do_test collate2-1.2.3 {
! 134: execsql {
! 135: SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa'
! 136: ORDER BY 1, oid;
! 137: }
! 138: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 139: do_test collate2-1.2.4 {
! 140: execsql {
! 141: SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY +b;
! 142: }
! 143: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 144: do_test collate2-1.2.5 {
! 145: execsql {
! 146: SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' ORDER BY +b;
! 147: }
! 148: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 149: do_test collate2-1.2.6 {
! 150: execsql {
! 151: SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' ORDER BY +b;
! 152: }
! 153: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 154: do_test collate2-1.2.7 {
! 155: execsql {
! 156: SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' ORDER BY +b;
! 157: }
! 158: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 159: do_test collate2-1.3 {
! 160: execsql {
! 161: SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;
! 162: }
! 163: } {ba Ab Bb ab bb}
! 164: do_test collate2-1.3.1 {
! 165: execsql {
! 166: SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa'
! 167: ORDER BY 1;
! 168: }
! 169: } {ba Ab Bb ab bb}
! 170: do_test collate2-1.3.2 {
! 171: execsql {
! 172: SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa'
! 173: ORDER BY 1;
! 174: }
! 175: } {ba Ab Bb ab bb}
! 176: do_test collate2-1.3.3 {
! 177: execsql {
! 178: SELECT c FROM collate2t1 WHERE c COLLATE backwards > 'aa'
! 179: ORDER BY 1;
! 180: }
! 181: } {ba Ab Bb ab bb}
! 182: do_test collate2-1.4 {
! 183: execsql {
! 184: SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;
! 185: }
! 186: } {AA AB Aa Ab BA BB Ba Bb aA aB}
! 187: do_test collate2-1.5 {
! 188: execsql {
! 189: SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;
! 190: }
! 191: } {}
! 192: do_test collate2-1.5.1 {
! 193: execsql {
! 194: SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b;
! 195: }
! 196: } {}
! 197: do_test collate2-1.6 {
! 198: execsql {
! 199: SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1;
! 200: }
! 201: } {AA BA aA bA AB BB aB bB Aa Ba}
! 202: do_test collate2-1.7 {
! 203: execsql {
! 204: SELECT a FROM collate2t1 WHERE a = 'aa';
! 205: }
! 206: } {aa}
! 207: do_test collate2-1.8 {
! 208: execsql {
! 209: SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;
! 210: }
! 211: } {aa aA Aa AA}
! 212: do_test collate2-1.9 {
! 213: execsql {
! 214: SELECT c FROM collate2t1 WHERE c = 'aa';
! 215: }
! 216: } {aa}
! 217: do_test collate2-1.10 {
! 218: execsql {
! 219: SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1;
! 220: }
! 221: } {aa ab bA bB ba bb}
! 222: do_test collate2-1.11 {
! 223: execsql {
! 224: SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid;
! 225: }
! 226: } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 227: do_test collate2-1.12 {
! 228: execsql {
! 229: SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1;
! 230: }
! 231: } {aa ba Ab Bb ab bb}
! 232: do_test collate2-1.13 {
! 233: execsql {
! 234: SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1;
! 235: }
! 236: } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
! 237: do_test collate2-1.14 {
! 238: execsql {
! 239: SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;
! 240: }
! 241: } {aa aA Aa AA}
! 242: do_test collate2-1.15 {
! 243: execsql {
! 244: SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1;
! 245: }
! 246: } {AA BA aA bA AB BB aB bB Aa Ba aa}
! 247: do_test collate2-1.16 {
! 248: execsql {
! 249: SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
! 250: }
! 251: } {Aa Ab BA BB Ba Bb}
! 252: do_test collate2-1.17 {
! 253: execsql {
! 254: SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
! 255: }
! 256: } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 257: do_test collate2-1.17.1 {
! 258: execsql {
! 259: SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY +b;
! 260: }
! 261: } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 262: do_test collate2-1.18 {
! 263: execsql {
! 264: SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
! 265: }
! 266: } {Aa Ba aa ba Ab Bb}
! 267: do_test collate2-1.19 {
! 268: execsql {
! 269: SELECT a FROM collate2t1 WHERE
! 270: CASE a WHEN 'aa' THEN 1 ELSE 0 END
! 271: ORDER BY 1, oid;
! 272: }
! 273: } {aa}
! 274: do_test collate2-1.20 {
! 275: execsql {
! 276: SELECT b FROM collate2t1 WHERE
! 277: CASE b WHEN 'aa' THEN 1 ELSE 0 END
! 278: ORDER BY 1, oid;
! 279: }
! 280: } {aa aA Aa AA}
! 281: do_test collate2-1.21 {
! 282: execsql {
! 283: SELECT c FROM collate2t1 WHERE
! 284: CASE c WHEN 'aa' THEN 1 ELSE 0 END
! 285: ORDER BY 1, oid;
! 286: }
! 287: } {aa}
! 288:
! 289: ifcapable subquery {
! 290: do_test collate2-1.22 {
! 291: execsql {
! 292: SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
! 293: }
! 294: } {aa bb}
! 295: do_test collate2-1.23 {
! 296: execsql {
! 297: SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
! 298: }
! 299: } {aa aA Aa AA bb bB Bb BB}
! 300: do_test collate2-1.24 {
! 301: execsql {
! 302: SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
! 303: }
! 304: } {aa bb}
! 305: do_test collate2-1.25 {
! 306: execsql {
! 307: SELECT a FROM collate2t1
! 308: WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 309: }
! 310: } {aa bb}
! 311: do_test collate2-1.26 {
! 312: execsql {
! 313: SELECT b FROM collate2t1
! 314: WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 315: }
! 316: } {aa bb aA bB Aa Bb AA BB}
! 317: do_test collate2-1.27 {
! 318: execsql {
! 319: SELECT c FROM collate2t1
! 320: WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 321: }
! 322: } {aa bb}
! 323: } ;# ifcapable subquery
! 324:
! 325: do_test collate2-2.1 {
! 326: execsql {
! 327: SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
! 328: }
! 329: } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
! 330: do_test collate2-2.2 {
! 331: execsql {
! 332: SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid;
! 333: }
! 334: } {aa aA Aa AA}
! 335: do_test collate2-2.3 {
! 336: execsql {
! 337: SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1;
! 338: }
! 339: } {AA BA aA bA AB BB aB bB Aa Ba aa}
! 340: do_test collate2-2.4 {
! 341: execsql {
! 342: SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1;
! 343: }
! 344: } {aa ab bA bB ba bb}
! 345: do_test collate2-2.5 {
! 346: execsql {
! 347: SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid;
! 348: }
! 349: } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 350: do_test collate2-2.6 {
! 351: execsql {
! 352: SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1;
! 353: }
! 354: } {aa ba Ab Bb ab bb}
! 355: do_test collate2-2.7 {
! 356: execsql {
! 357: SELECT a FROM collate2t1 WHERE NOT a = 'aa';
! 358: }
! 359: } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 360: do_test collate2-2.8 {
! 361: execsql {
! 362: SELECT b FROM collate2t1 WHERE NOT b = 'aa';
! 363: }
! 364: } {ab ba bb aB bA bB Ab Ba Bb AB BA BB}
! 365: do_test collate2-2.9 {
! 366: execsql {
! 367: SELECT c FROM collate2t1 WHERE NOT c = 'aa';
! 368: }
! 369: } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 370: do_test collate2-2.10 {
! 371: execsql {
! 372: SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1;
! 373: }
! 374: } {AA AB Aa Ab BA BB Ba Bb aA aB}
! 375: do_test collate2-2.11 {
! 376: execsql {
! 377: SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;
! 378: }
! 379: } {}
! 380: do_test collate2-2.12 {
! 381: execsql {
! 382: SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1;
! 383: }
! 384: } {AA BA aA bA AB BB aB bB Aa Ba}
! 385: do_test collate2-2.13 {
! 386: execsql {
! 387: SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1;
! 388: }
! 389: } {ab bA bB ba bb}
! 390: do_test collate2-2.14 {
! 391: execsql {
! 392: SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid;
! 393: }
! 394: } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
! 395: do_test collate2-2.15 {
! 396: execsql {
! 397: SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1;
! 398: }
! 399: } {ba Ab Bb ab bb}
! 400: do_test collate2-2.16 {
! 401: execsql {
! 402: SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
! 403: }
! 404: } {AA AB aA aB aa ab bA bB ba bb}
! 405: do_test collate2-2.17 {
! 406: execsql {
! 407: SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
! 408: }
! 409: } {}
! 410: do_test collate2-2.18 {
! 411: execsql {
! 412: SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
! 413: }
! 414: } {AA BA aA bA AB BB aB bB ab bb}
! 415: do_test collate2-2.19 {
! 416: execsql {
! 417: SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END;
! 418: }
! 419: } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 420: do_test collate2-2.20 {
! 421: execsql {
! 422: SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END;
! 423: }
! 424: } {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
! 425: do_test collate2-2.21 {
! 426: execsql {
! 427: SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
! 428: }
! 429: } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 430:
! 431: ifcapable subquery {
! 432: do_test collate2-2.22 {
! 433: execsql {
! 434: SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
! 435: }
! 436: } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 437: do_test collate2-2.23 {
! 438: execsql {
! 439: SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
! 440: }
! 441: } {ab ba aB bA Ab Ba AB BA}
! 442: do_test collate2-2.24 {
! 443: execsql {
! 444: SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
! 445: }
! 446: } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 447: do_test collate2-2.25 {
! 448: execsql {
! 449: SELECT a FROM collate2t1
! 450: WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 451: }
! 452: } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 453: do_test collate2-2.26 {
! 454: execsql {
! 455: SELECT b FROM collate2t1
! 456: WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 457: }
! 458: } {ab ba aB bA Ab Ba AB BA}
! 459: do_test collate2-2.27 {
! 460: execsql {
! 461: SELECT c FROM collate2t1
! 462: WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
! 463: }
! 464: } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
! 465: }
! 466:
! 467: do_test collate2-3.1 {
! 468: execsql {
! 469: SELECT a > 'aa' FROM collate2t1;
! 470: }
! 471: } {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
! 472: do_test collate2-3.2 {
! 473: execsql {
! 474: SELECT b > 'aa' FROM collate2t1;
! 475: }
! 476: } {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1}
! 477: do_test collate2-3.3 {
! 478: execsql {
! 479: SELECT c > 'aa' FROM collate2t1;
! 480: }
! 481: } {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
! 482: do_test collate2-3.4 {
! 483: execsql {
! 484: SELECT a < 'aa' FROM collate2t1;
! 485: }
! 486: } {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
! 487: do_test collate2-3.5 {
! 488: execsql {
! 489: SELECT b < 'aa' FROM collate2t1;
! 490: }
! 491: } {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
! 492: do_test collate2-3.6 {
! 493: execsql {
! 494: SELECT c < 'aa' FROM collate2t1;
! 495: }
! 496: } {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
! 497: do_test collate2-3.7 {
! 498: execsql {
! 499: SELECT a = 'aa' FROM collate2t1;
! 500: }
! 501: } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
! 502: do_test collate2-3.8 {
! 503: execsql {
! 504: SELECT b = 'aa' FROM collate2t1;
! 505: }
! 506: } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
! 507: do_test collate2-3.9 {
! 508: execsql {
! 509: SELECT c = 'aa' FROM collate2t1;
! 510: }
! 511: } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
! 512: do_test collate2-3.10 {
! 513: execsql {
! 514: SELECT a <= 'aa' FROM collate2t1;
! 515: }
! 516: } {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
! 517: do_test collate2-3.11 {
! 518: execsql {
! 519: SELECT b <= 'aa' FROM collate2t1;
! 520: }
! 521: } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
! 522: do_test collate2-3.12 {
! 523: execsql {
! 524: SELECT c <= 'aa' FROM collate2t1;
! 525: }
! 526: } {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
! 527: do_test collate2-3.13 {
! 528: execsql {
! 529: SELECT a >= 'aa' FROM collate2t1;
! 530: }
! 531: } {{} 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
! 532: do_test collate2-3.14 {
! 533: execsql {
! 534: SELECT b >= 'aa' FROM collate2t1;
! 535: }
! 536: } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
! 537: do_test collate2-3.15 {
! 538: execsql {
! 539: SELECT c >= 'aa' FROM collate2t1;
! 540: }
! 541: } {{} 1 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
! 542: do_test collate2-3.16 {
! 543: execsql {
! 544: SELECT a BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
! 545: }
! 546: } {{} 0 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1}
! 547: do_test collate2-3.17 {
! 548: execsql {
! 549: SELECT b BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
! 550: }
! 551: } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
! 552: do_test collate2-3.18 {
! 553: execsql {
! 554: SELECT c BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
! 555: }
! 556: } {{} 1 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0}
! 557: do_test collate2-3.19 {
! 558: execsql {
! 559: SELECT CASE a WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
! 560: }
! 561: } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
! 562: do_test collate2-3.20 {
! 563: execsql {
! 564: SELECT CASE b WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
! 565: }
! 566: } {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
! 567: do_test collate2-3.21 {
! 568: execsql {
! 569: SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
! 570: }
! 571: } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
! 572:
! 573: ifcapable subquery {
! 574: do_test collate2-3.22 {
! 575: execsql {
! 576: SELECT a IN ('aa', 'bb') FROM collate2t1;
! 577: }
! 578: } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
! 579: do_test collate2-3.23 {
! 580: execsql {
! 581: SELECT b IN ('aa', 'bb') FROM collate2t1;
! 582: }
! 583: } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
! 584: do_test collate2-3.24 {
! 585: execsql {
! 586: SELECT c IN ('aa', 'bb') FROM collate2t1;
! 587: }
! 588: } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
! 589: do_test collate2-3.25 {
! 590: execsql {
! 591: SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
! 592: FROM collate2t1;
! 593: }
! 594: } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
! 595: do_test collate2-3.26 {
! 596: execsql {
! 597: SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
! 598: FROM collate2t1;
! 599: }
! 600: } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
! 601: do_test collate2-3.27 {
! 602: execsql {
! 603: SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
! 604: FROM collate2t1;
! 605: }
! 606: } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
! 607: }
! 608:
! 609: do_test collate2-4.0 {
! 610: execsql {
! 611: CREATE TABLE collate2t2(b COLLATE binary);
! 612: CREATE TABLE collate2t3(b text);
! 613: INSERT INTO collate2t2 VALUES('aa');
! 614: INSERT INTO collate2t3 VALUES('aa');
! 615: }
! 616: } {}
! 617:
! 618: # Test that when both sides of a binary comparison operator have
! 619: # default collation types, the collate type for the leftmost term
! 620: # is used.
! 621: do_test collate2-4.1 {
! 622: execsql {
! 623: SELECT collate2t1.a FROM collate2t1, collate2t2
! 624: WHERE collate2t1.b = collate2t2.b;
! 625: }
! 626: } {aa aA Aa AA}
! 627: do_test collate2-4.2 {
! 628: execsql {
! 629: SELECT collate2t1.a FROM collate2t1, collate2t2
! 630: WHERE collate2t2.b = collate2t1.b;
! 631: }
! 632: } {aa}
! 633:
! 634: # Test that when one side has a default collation type and the other
! 635: # does not, the collation type is used.
! 636: do_test collate2-4.3 {
! 637: execsql {
! 638: SELECT collate2t1.a FROM collate2t1, collate2t3
! 639: WHERE collate2t1.b = collate2t3.b||'';
! 640: }
! 641: } {aa aA Aa AA}
! 642: do_test collate2-4.4 {
! 643: execsql {
! 644: SELECT collate2t1.a FROM collate2t1, collate2t3
! 645: WHERE collate2t3.b||'' = collate2t1.b;
! 646: }
! 647: } {aa aA Aa AA}
! 648:
! 649: do_test collate2-4.5 {
! 650: execsql {
! 651: DROP TABLE collate2t3;
! 652: }
! 653: } {}
! 654:
! 655: #
! 656: # Test that the default collation types are used when the JOIN syntax
! 657: # is used in place of a WHERE clause.
! 658: #
! 659: # SQLite transforms the JOIN syntax into a WHERE clause internally, so
! 660: # the focus of these tests is to ensure that the table on the left-hand-side
! 661: # of the join determines the collation type used.
! 662: #
! 663: do_test collate2-5.0 {
! 664: execsql {
! 665: SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b);
! 666: }
! 667: } {aa aA Aa AA}
! 668: do_test collate2-5.1 {
! 669: execsql {
! 670: SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b);
! 671: }
! 672: } {aa}
! 673: do_test collate2-5.2 {
! 674: execsql {
! 675: SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2;
! 676: }
! 677: } {aa aA Aa AA}
! 678: do_test collate2-5.3 {
! 679: execsql {
! 680: SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
! 681: }
! 682: } {aa}
! 683: do_test collate2-5.4 {
! 684: execsql {
! 685: SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
! 686: }
! 687: } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
! 688: do_test collate2-5.5 {
! 689: execsql {
! 690: SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
! 691: }
! 692: } {aa aa}
! 693:
! 694: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate2.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