Annotation of embedaddon/sqlite3/test/collate4.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: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: db collate TEXT text_collate
! 21: proc text_collate {a b} {
! 22: return [string compare $a $b]
! 23: }
! 24:
! 25: # Do an SQL statement. Append the search count to the end of the result.
! 26: #
! 27: proc count sql {
! 28: set ::sqlite_search_count 0
! 29: return [concat [execsql $sql] $::sqlite_search_count]
! 30: }
! 31:
! 32: # This procedure executes the SQL. Then it checks the generated program
! 33: # for the SQL and appends a "nosort" to the result if the program contains the
! 34: # SortCallback opcode. If the program does not contain the SortCallback
! 35: # opcode it appends "sort"
! 36: #
! 37: proc cksort {sql} {
! 38: set ::sqlite_sort_count 0
! 39: set data [execsql $sql]
! 40: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 41: lappend data $x
! 42: return $data
! 43: }
! 44:
! 45: #
! 46: # Test cases are organized roughly as follows:
! 47: #
! 48: # collate4-1.* ORDER BY.
! 49: # collate4-2.* WHERE clauses.
! 50: # collate4-3.* constraints (primary key, unique).
! 51: # collate4-4.* simple min() or max() queries.
! 52: # collate4-5.* REINDEX command
! 53: # collate4-6.* INTEGER PRIMARY KEY indices.
! 54: #
! 55:
! 56: #
! 57: # These tests - collate4-1.* - check that indices are correctly
! 58: # selected or not selected to implement ORDER BY clauses when
! 59: # user defined collation sequences are involved.
! 60: #
! 61: # Because these tests also exercise all the different ways indices
! 62: # can be created, they also serve to verify that indices are correctly
! 63: # initialised with user-defined collation sequences when they are
! 64: # created.
! 65: #
! 66: # Tests named collate4-1.1.* use indices with a single column. Tests
! 67: # collate4-1.2.* use indices with two columns.
! 68: #
! 69: do_test collate4-1.1.0 {
! 70: execsql {
! 71: CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
! 72: INSERT INTO collate4t1 VALUES( 'a', 'a' );
! 73: INSERT INTO collate4t1 VALUES( 'b', 'b' );
! 74: INSERT INTO collate4t1 VALUES( NULL, NULL );
! 75: INSERT INTO collate4t1 VALUES( 'B', 'B' );
! 76: INSERT INTO collate4t1 VALUES( 'A', 'A' );
! 77: CREATE INDEX collate4i1 ON collate4t1(a);
! 78: CREATE INDEX collate4i2 ON collate4t1(b);
! 79: }
! 80: } {}
! 81: do_test collate4-1.1.1 {
! 82: cksort {SELECT a FROM collate4t1 ORDER BY a}
! 83: } {{} a A b B nosort}
! 84: do_test collate4-1.1.2 {
! 85: cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}
! 86: } {{} a A b B nosort}
! 87: do_test collate4-1.1.3 {
! 88: cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
! 89: } {{} A B a b sort}
! 90: do_test collate4-1.1.4 {
! 91: cksort {SELECT b FROM collate4t1 ORDER BY b}
! 92: } {{} A B a b nosort}
! 93: do_test collate4-1.1.5 {
! 94: cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
! 95: } {{} A B a b nosort}
! 96: do_test collate4-1.1.6 {
! 97: cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
! 98: } {{} a A b B sort}
! 99:
! 100: do_test collate4-1.1.7 {
! 101: execsql {
! 102: CREATE TABLE collate4t2(
! 103: a PRIMARY KEY COLLATE NOCASE,
! 104: b UNIQUE COLLATE TEXT
! 105: );
! 106: INSERT INTO collate4t2 VALUES( 'a', 'a' );
! 107: INSERT INTO collate4t2 VALUES( NULL, NULL );
! 108: INSERT INTO collate4t2 VALUES( 'B', 'B' );
! 109: }
! 110: } {}
! 111: do_test collate4-1.1.8 {
! 112: cksort {SELECT a FROM collate4t2 ORDER BY a}
! 113: } {{} a B nosort}
! 114: do_test collate4-1.1.9 {
! 115: cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}
! 116: } {{} a B nosort}
! 117: do_test collate4-1.1.10 {
! 118: cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}
! 119: } {{} B a sort}
! 120: do_test collate4-1.1.11 {
! 121: cksort {SELECT b FROM collate4t2 ORDER BY b}
! 122: } {{} B a nosort}
! 123: do_test collate4-1.1.12 {
! 124: cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}
! 125: } {{} B a nosort}
! 126: do_test collate4-1.1.13 {
! 127: cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}
! 128: } {{} a B sort}
! 129:
! 130: do_test collate4-1.1.14 {
! 131: execsql {
! 132: CREATE TABLE collate4t3(
! 133: b COLLATE TEXT,
! 134: a COLLATE NOCASE,
! 135: UNIQUE(a), PRIMARY KEY(b)
! 136: );
! 137: INSERT INTO collate4t3 VALUES( 'a', 'a' );
! 138: INSERT INTO collate4t3 VALUES( NULL, NULL );
! 139: INSERT INTO collate4t3 VALUES( 'B', 'B' );
! 140: }
! 141: } {}
! 142: do_test collate4-1.1.15 {
! 143: cksort {SELECT a FROM collate4t3 ORDER BY a}
! 144: } {{} a B nosort}
! 145: do_test collate4-1.1.16 {
! 146: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}
! 147: } {{} a B nosort}
! 148: do_test collate4-1.1.17 {
! 149: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}
! 150: } {{} B a sort}
! 151: do_test collate4-1.1.18 {
! 152: cksort {SELECT b FROM collate4t3 ORDER BY b}
! 153: } {{} B a nosort}
! 154: do_test collate4-1.1.19 {
! 155: cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}
! 156: } {{} B a nosort}
! 157: do_test collate4-1.1.20 {
! 158: cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}
! 159: } {{} a B sort}
! 160:
! 161: do_test collate4-1.1.21 {
! 162: execsql {
! 163: CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);
! 164: INSERT INTO collate4t4 VALUES( 'a', 'a' );
! 165: INSERT INTO collate4t4 VALUES( 'b', 'b' );
! 166: INSERT INTO collate4t4 VALUES( NULL, NULL );
! 167: INSERT INTO collate4t4 VALUES( 'B', 'B' );
! 168: INSERT INTO collate4t4 VALUES( 'A', 'A' );
! 169: CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
! 170: CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
! 171: }
! 172: } {}
! 173: do_test collate4-1.1.22 {
! 174: cksort {SELECT a FROM collate4t4 ORDER BY a}
! 175: } {{} a A b B sort}
! 176: do_test collate4-1.1.23 {
! 177: cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
! 178: } {{} a A b B sort}
! 179: do_test collate4-1.1.24 {
! 180: cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
! 181: } {{} A B a b nosort}
! 182: do_test collate4-1.1.25 {
! 183: cksort {SELECT b FROM collate4t4 ORDER BY b}
! 184: } {{} A B a b sort}
! 185: do_test collate4-1.1.26 {
! 186: cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
! 187: } {{} A B a b sort}
! 188: do_test collate4-1.1.27 {
! 189: cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
! 190: } {{} a A b B nosort}
! 191:
! 192: do_test collate4-1.1.30 {
! 193: execsql {
! 194: DROP TABLE collate4t1;
! 195: DROP TABLE collate4t2;
! 196: DROP TABLE collate4t3;
! 197: DROP TABLE collate4t4;
! 198: }
! 199: } {}
! 200:
! 201: do_test collate4-1.2.0 {
! 202: execsql {
! 203: CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
! 204: INSERT INTO collate4t1 VALUES( 'a', 'a' );
! 205: INSERT INTO collate4t1 VALUES( 'b', 'b' );
! 206: INSERT INTO collate4t1 VALUES( NULL, NULL );
! 207: INSERT INTO collate4t1 VALUES( 'B', 'B' );
! 208: INSERT INTO collate4t1 VALUES( 'A', 'A' );
! 209: CREATE INDEX collate4i1 ON collate4t1(a, b);
! 210: }
! 211: } {}
! 212: do_test collate4-1.2.1 {
! 213: cksort {SELECT a FROM collate4t1 ORDER BY a}
! 214: } {{} A a B b nosort}
! 215: do_test collate4-1.2.2 {
! 216: cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
! 217: } {{} A a B b nosort}
! 218: do_test collate4-1.2.3 {
! 219: cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
! 220: } {{} A B a b sort}
! 221: do_test collate4-1.2.4 {
! 222: cksort {SELECT a FROM collate4t1 ORDER BY a, b}
! 223: } {{} A a B b nosort}
! 224: do_test collate4-1.2.5 {
! 225: cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
! 226: } {{} a A b B sort}
! 227: do_test collate4-1.2.6 {
! 228: cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
! 229: } {{} A a B b nosort}
! 230:
! 231: do_test collate4-1.2.7 {
! 232: execsql {
! 233: CREATE TABLE collate4t2(
! 234: a COLLATE NOCASE,
! 235: b COLLATE TEXT,
! 236: PRIMARY KEY(a, b)
! 237: );
! 238: INSERT INTO collate4t2 VALUES( 'a', 'a' );
! 239: INSERT INTO collate4t2 VALUES( NULL, NULL );
! 240: INSERT INTO collate4t2 VALUES( 'B', 'B' );
! 241: }
! 242: } {}
! 243: do_test collate4-1.2.8 {
! 244: cksort {SELECT a FROM collate4t2 ORDER BY a}
! 245: } {{} a B nosort}
! 246: do_test collate4-1.2.9 {
! 247: cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}
! 248: } {{} a B nosort}
! 249: do_test collate4-1.2.10 {
! 250: cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}
! 251: } {{} B a sort}
! 252: do_test collate4-1.2.11 {
! 253: cksort {SELECT a FROM collate4t2 ORDER BY a, b}
! 254: } {{} a B nosort}
! 255: do_test collate4-1.2.12 {
! 256: cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}
! 257: } {{} a B sort}
! 258: do_test collate4-1.2.13 {
! 259: cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}
! 260: } {{} a B nosort}
! 261:
! 262: do_test collate4-1.2.14 {
! 263: execsql {
! 264: CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);
! 265: INSERT INTO collate4t3 VALUES( 'a', 'a' );
! 266: INSERT INTO collate4t3 VALUES( 'b', 'b' );
! 267: INSERT INTO collate4t3 VALUES( NULL, NULL );
! 268: INSERT INTO collate4t3 VALUES( 'B', 'B' );
! 269: INSERT INTO collate4t3 VALUES( 'A', 'A' );
! 270: CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
! 271: }
! 272: } {}
! 273: do_test collate4-1.2.15 {
! 274: cksort {SELECT a FROM collate4t3 ORDER BY a}
! 275: } {{} a A b B sort}
! 276: do_test collate4-1.2.16 {
! 277: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
! 278: } {{} a A b B sort}
! 279: do_test collate4-1.2.17 {
! 280: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
! 281: } {{} A B a b nosort}
! 282: do_test collate4-1.2.18 {
! 283: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
! 284: } {{} A B a b sort}
! 285: do_test collate4-1.2.19 {
! 286: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}
! 287: } {{} A B a b nosort}
! 288: do_test collate4-1.2.20 {
! 289: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}
! 290: } {{} A B a b sort}
! 291: do_test collate4-1.2.21 {
! 292: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}
! 293: } {b a B A {} nosort}
! 294: do_test collate4-1.2.22 {
! 295: cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}
! 296: } {b a B A {} sort}
! 297: do_test collate4-1.2.23 {
! 298: cksort {SELECT a FROM collate4t3
! 299: ORDER BY a COLLATE text DESC, b COLLATE nocase}
! 300: } {b a B A {} sort}
! 301: do_test collate4-1.2.24 {
! 302: cksort {SELECT a FROM collate4t3
! 303: ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}
! 304: } {b a B A {} nosort}
! 305:
! 306: do_test collate4-1.2.25 {
! 307: execsql {
! 308: DROP TABLE collate4t1;
! 309: DROP TABLE collate4t2;
! 310: DROP TABLE collate4t3;
! 311: }
! 312: } {}
! 313:
! 314: #
! 315: # These tests - collate4-2.* - check that indices are correctly
! 316: # selected or not selected to implement WHERE clauses when user
! 317: # defined collation sequences are involved.
! 318: #
! 319: # Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
! 320: # operators.
! 321: #
! 322: do_test collate4-2.1.0 {
! 323: execsql {
! 324: PRAGMA automatic_index=OFF;
! 325: CREATE TABLE collate4t1(a COLLATE NOCASE);
! 326: CREATE TABLE collate4t2(b COLLATE TEXT);
! 327:
! 328: INSERT INTO collate4t1 VALUES('a');
! 329: INSERT INTO collate4t1 VALUES('A');
! 330: INSERT INTO collate4t1 VALUES('b');
! 331: INSERT INTO collate4t1 VALUES('B');
! 332: INSERT INTO collate4t1 VALUES('c');
! 333: INSERT INTO collate4t1 VALUES('C');
! 334: INSERT INTO collate4t1 VALUES('d');
! 335: INSERT INTO collate4t1 VALUES('D');
! 336: INSERT INTO collate4t1 VALUES('e');
! 337: INSERT INTO collate4t1 VALUES('D');
! 338:
! 339: INSERT INTO collate4t2 VALUES('A');
! 340: INSERT INTO collate4t2 VALUES('Z');
! 341: }
! 342: } {}
! 343: do_test collate4-2.1.1 {
! 344: count {
! 345: SELECT * FROM collate4t2, collate4t1 WHERE a = b;
! 346: }
! 347: } {A a A A 19}
! 348: do_test collate4-2.1.2 {
! 349: execsql {
! 350: CREATE INDEX collate4i1 ON collate4t1(a);
! 351: }
! 352: count {
! 353: SELECT * FROM collate4t2, collate4t1 WHERE a = b;
! 354: }
! 355: } {A a A A 5}
! 356: do_test collate4-2.1.3 {
! 357: count {
! 358: SELECT * FROM collate4t2, collate4t1 WHERE b = a;
! 359: }
! 360: } {A A 19}
! 361: do_test collate4-2.1.4 {
! 362: execsql {
! 363: DROP INDEX collate4i1;
! 364: CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
! 365: }
! 366: count {
! 367: SELECT * FROM collate4t2, collate4t1 WHERE a = b;
! 368: }
! 369: } {A a A A 19}
! 370: do_test collate4-2.1.5 {
! 371: count {
! 372: SELECT * FROM collate4t2, collate4t1 WHERE b = a;
! 373: }
! 374: } {A A 4}
! 375: ifcapable subquery {
! 376: do_test collate4-2.1.6 {
! 377: count {
! 378: SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
! 379: }
! 380: } {a A 10}
! 381: do_test collate4-2.1.7 {
! 382: execsql {
! 383: DROP INDEX collate4i1;
! 384: CREATE INDEX collate4i1 ON collate4t1(a);
! 385: }
! 386: count {
! 387: SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
! 388: }
! 389: } {a A 6}
! 390: do_test collate4-2.1.8 {
! 391: count {
! 392: SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
! 393: }
! 394: } {a A 5}
! 395: do_test collate4-2.1.9 {
! 396: execsql {
! 397: DROP INDEX collate4i1;
! 398: CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
! 399: }
! 400: count {
! 401: SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
! 402: }
! 403: } {a A 9}
! 404: }
! 405: do_test collate4-2.1.10 {
! 406: execsql {
! 407: DROP TABLE collate4t1;
! 408: DROP TABLE collate4t2;
! 409: }
! 410: } {}
! 411:
! 412: do_test collate4-2.2.0 {
! 413: execsql {
! 414: CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
! 415: CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
! 416:
! 417: INSERT INTO collate4t1 VALUES('0', '0', '0');
! 418: INSERT INTO collate4t1 VALUES('0', '0', '1');
! 419: INSERT INTO collate4t1 VALUES('0', '1', '0');
! 420: INSERT INTO collate4t1 VALUES('0', '1', '1');
! 421: INSERT INTO collate4t1 VALUES('1', '0', '0');
! 422: INSERT INTO collate4t1 VALUES('1', '0', '1');
! 423: INSERT INTO collate4t1 VALUES('1', '1', '0');
! 424: INSERT INTO collate4t1 VALUES('1', '1', '1');
! 425: insert into collate4t2 SELECT * FROM collate4t1;
! 426: }
! 427: } {}
! 428: do_test collate4-2.2.1 {
! 429: count {
! 430: SELECT * FROM collate4t2 NOT INDEXED NATURAL JOIN collate4t1 NOT INDEXED;
! 431: }
! 432: } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 63}
! 433: do_test collate4-2.2.1b {
! 434: execsql {
! 435: CREATE INDEX collate4i1 ON collate4t1(a, b, c);
! 436: }
! 437: count {
! 438: SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
! 439: }
! 440: } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 29}
! 441: do_test collate4-2.2.2 {
! 442: execsql {
! 443: DROP INDEX collate4i1;
! 444: CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
! 445: }
! 446: count {
! 447: SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
! 448: }
! 449: } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 22}
! 450:
! 451: do_test collate4-2.2.10 {
! 452: execsql {
! 453: DROP TABLE collate4t1;
! 454: DROP TABLE collate4t2;
! 455: }
! 456: } {}
! 457:
! 458: #
! 459: # These tests - collate4-3.* verify that indices that implement
! 460: # UNIQUE and PRIMARY KEY constraints operate correctly with user
! 461: # defined collation sequences.
! 462: #
! 463: do_test collate4-3.0 {
! 464: execsql {
! 465: CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
! 466: }
! 467: } {}
! 468: do_test collate4-3.1 {
! 469: catchsql {
! 470: INSERT INTO collate4t1 VALUES('abc');
! 471: INSERT INTO collate4t1 VALUES('ABC');
! 472: }
! 473: } {1 {column a is not unique}}
! 474: do_test collate4-3.2 {
! 475: execsql {
! 476: SELECT * FROM collate4t1;
! 477: }
! 478: } {abc}
! 479: do_test collate4-3.3 {
! 480: catchsql {
! 481: INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
! 482: }
! 483: } {1 {column a is not unique}}
! 484: do_test collate4-3.4 {
! 485: catchsql {
! 486: INSERT INTO collate4t1 VALUES(1);
! 487: UPDATE collate4t1 SET a = 'abc';
! 488: }
! 489: } {1 {column a is not unique}}
! 490: do_test collate4-3.5 {
! 491: execsql {
! 492: DROP TABLE collate4t1;
! 493: CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
! 494: }
! 495: } {}
! 496: do_test collate4-3.6 {
! 497: catchsql {
! 498: INSERT INTO collate4t1 VALUES('abc');
! 499: INSERT INTO collate4t1 VALUES('ABC');
! 500: }
! 501: } {1 {column a is not unique}}
! 502: do_test collate4-3.7 {
! 503: execsql {
! 504: SELECT * FROM collate4t1;
! 505: }
! 506: } {abc}
! 507: do_test collate4-3.8 {
! 508: catchsql {
! 509: INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
! 510: }
! 511: } {1 {column a is not unique}}
! 512: do_test collate4-3.9 {
! 513: catchsql {
! 514: INSERT INTO collate4t1 VALUES(1);
! 515: UPDATE collate4t1 SET a = 'abc';
! 516: }
! 517: } {1 {column a is not unique}}
! 518: do_test collate4-3.10 {
! 519: execsql {
! 520: DROP TABLE collate4t1;
! 521: CREATE TABLE collate4t1(a);
! 522: CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
! 523: }
! 524: } {}
! 525: do_test collate4-3.11 {
! 526: catchsql {
! 527: INSERT INTO collate4t1 VALUES('abc');
! 528: INSERT INTO collate4t1 VALUES('ABC');
! 529: }
! 530: } {1 {column a is not unique}}
! 531: do_test collate4-3.12 {
! 532: execsql {
! 533: SELECT * FROM collate4t1;
! 534: }
! 535: } {abc}
! 536: do_test collate4-3.13 {
! 537: catchsql {
! 538: INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
! 539: }
! 540: } {1 {column a is not unique}}
! 541: do_test collate4-3.14 {
! 542: catchsql {
! 543: INSERT INTO collate4t1 VALUES(1);
! 544: UPDATE collate4t1 SET a = 'abc';
! 545: }
! 546: } {1 {column a is not unique}}
! 547:
! 548: do_test collate4-3.15 {
! 549: execsql {
! 550: DROP TABLE collate4t1;
! 551: }
! 552: } {}
! 553:
! 554: # Mimic the SQLite 2 collation type NUMERIC.
! 555: db collate numeric numeric_collate
! 556: proc numeric_collate {lhs rhs} {
! 557: if {$lhs == $rhs} {return 0}
! 558: return [expr ($lhs>$rhs)?1:-1]
! 559: }
! 560:
! 561: #
! 562: # These tests - collate4-4.* check that min() and max() only ever
! 563: # use indices constructed with built-in collation type numeric.
! 564: #
! 565: # CHANGED: min() and max() now use the collation type. If there
! 566: # is an indice that can be used, it is used.
! 567: #
! 568: do_test collate4-4.0 {
! 569: execsql {
! 570: CREATE TABLE collate4t1(a COLLATE TEXT);
! 571: INSERT INTO collate4t1 VALUES('2');
! 572: INSERT INTO collate4t1 VALUES('10');
! 573: INSERT INTO collate4t1 VALUES('20');
! 574: INSERT INTO collate4t1 VALUES('104');
! 575: }
! 576: } {}
! 577: do_test collate4-4.1 {
! 578: count {
! 579: SELECT max(a) FROM collate4t1
! 580: }
! 581: } {20 3}
! 582: do_test collate4-4.2 {
! 583: count {
! 584: SELECT min(a) FROM collate4t1
! 585: }
! 586: } {10 3}
! 587: do_test collate4-4.3 {
! 588: # Test that the index with collation type TEXT is used.
! 589: execsql {
! 590: CREATE INDEX collate4i1 ON collate4t1(a);
! 591: }
! 592: count {
! 593: SELECT min(a) FROM collate4t1;
! 594: }
! 595: } {10 1}
! 596: do_test collate4-4.4 {
! 597: count {
! 598: SELECT max(a) FROM collate4t1;
! 599: }
! 600: } {20 0}
! 601: do_test collate4-4.5 {
! 602: # Test that the index with collation type NUMERIC is not used.
! 603: execsql {
! 604: DROP INDEX collate4i1;
! 605: CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
! 606: }
! 607: count {
! 608: SELECT min(a) FROM collate4t1;
! 609: }
! 610: } {10 3}
! 611: do_test collate4-4.6 {
! 612: count {
! 613: SELECT max(a) FROM collate4t1;
! 614: }
! 615: } {20 3}
! 616: do_test collate4-4.7 {
! 617: execsql {
! 618: DROP TABLE collate4t1;
! 619: }
! 620: } {}
! 621:
! 622: # Also test the scalar min() and max() functions.
! 623: #
! 624: do_test collate4-4.8 {
! 625: execsql {
! 626: CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
! 627: INSERT INTO collate4t1 VALUES('11', '101');
! 628: INSERT INTO collate4t1 VALUES('101', '11')
! 629: }
! 630: } {}
! 631: do_test collate4-4.9 {
! 632: execsql {
! 633: SELECT max(a, b) FROM collate4t1;
! 634: }
! 635: } {11 11}
! 636: do_test collate4-4.10 {
! 637: execsql {
! 638: SELECT max(b, a) FROM collate4t1;
! 639: }
! 640: } {101 101}
! 641: do_test collate4-4.11 {
! 642: execsql {
! 643: SELECT max(a, '101') FROM collate4t1;
! 644: }
! 645: } {11 101}
! 646: do_test collate4-4.12 {
! 647: execsql {
! 648: SELECT max('101', a) FROM collate4t1;
! 649: }
! 650: } {11 101}
! 651: do_test collate4-4.13 {
! 652: execsql {
! 653: SELECT max(b, '101') FROM collate4t1;
! 654: }
! 655: } {101 101}
! 656: do_test collate4-4.14 {
! 657: execsql {
! 658: SELECT max('101', b) FROM collate4t1;
! 659: }
! 660: } {101 101}
! 661:
! 662: do_test collate4-4.15 {
! 663: execsql {
! 664: DROP TABLE collate4t1;
! 665: }
! 666: } {}
! 667:
! 668: #
! 669: # These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
! 670: # indices do not confuse collation sequences.
! 671: #
! 672: # These indices are never used for sorting in SQLite. And you can't
! 673: # create another index on an INTEGER PRIMARY KEY column, so we don't have
! 674: # to test that.
! 675: # (Revised 2004-Nov-22): The ROWID can be used for sorting now.
! 676: #
! 677: do_test collate4-6.0 {
! 678: execsql {
! 679: CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
! 680: INSERT INTO collate4t1 VALUES(101);
! 681: INSERT INTO collate4t1 VALUES(10);
! 682: INSERT INTO collate4t1 VALUES(15);
! 683: }
! 684: } {}
! 685: do_test collate4-6.1 {
! 686: cksort {
! 687: SELECT * FROM collate4t1 ORDER BY 1;
! 688: }
! 689: } {10 15 101 nosort}
! 690: do_test collate4-6.2 {
! 691: cksort {
! 692: SELECT * FROM collate4t1 ORDER BY oid;
! 693: }
! 694: } {10 15 101 nosort}
! 695: do_test collate4-6.3 {
! 696: cksort {
! 697: SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
! 698: }
! 699: } {10 101 15 sort}
! 700:
! 701: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate4.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