Annotation of embedaddon/sqlite3/test/collate5.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 file is testing DISTINCT, UNION, INTERSECT and EXCEPT
! 14: # SELECT statements that use user-defined collation sequences. Also
! 15: # GROUP BY clauses that use user-defined collation sequences.
! 16: #
! 17: # $Id: collate5.test,v 1.7 2008/09/16 11:58:20 drh Exp $
! 18:
! 19: set testdir [file dirname $argv0]
! 20: source $testdir/tester.tcl
! 21:
! 22:
! 23: #
! 24: # Tests are organised as follows:
! 25: # collate5-1.* - DISTINCT
! 26: # collate5-2.* - Compound SELECT
! 27: # collate5-3.* - ORDER BY on compound SELECT
! 28: # collate5-4.* - GROUP BY
! 29:
! 30: # Create the collation sequence 'TEXT', purely for asthetic reasons. The
! 31: # test cases in this script could just as easily use BINARY.
! 32: db collate TEXT [list string compare]
! 33:
! 34: # Mimic the SQLite 2 collation type NUMERIC.
! 35: db collate numeric numeric_collate
! 36: proc numeric_collate {lhs rhs} {
! 37: if {$lhs == $rhs} {return 0}
! 38: return [expr ($lhs>$rhs)?1:-1]
! 39: }
! 40:
! 41: #
! 42: # These tests - collate5-1.* - focus on the DISTINCT keyword.
! 43: #
! 44: do_test collate5-1.0 {
! 45: execsql {
! 46: CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text);
! 47:
! 48: INSERT INTO collate5t1 VALUES('a', 'apple');
! 49: INSERT INTO collate5t1 VALUES('A', 'Apple');
! 50: INSERT INTO collate5t1 VALUES('b', 'banana');
! 51: INSERT INTO collate5t1 VALUES('B', 'banana');
! 52: INSERT INTO collate5t1 VALUES('n', NULL);
! 53: INSERT INTO collate5t1 VALUES('N', NULL);
! 54: }
! 55: } {}
! 56: do_test collate5-1.1 {
! 57: execsql {
! 58: SELECT DISTINCT a FROM collate5t1;
! 59: }
! 60: } {a b n}
! 61: do_test collate5-1.2 {
! 62: execsql {
! 63: SELECT DISTINCT b FROM collate5t1;
! 64: }
! 65: } {apple Apple banana {}}
! 66: do_test collate5-1.3 {
! 67: execsql {
! 68: SELECT DISTINCT a, b FROM collate5t1;
! 69: }
! 70: } {a apple A Apple b banana n {}}
! 71:
! 72: # Ticket #3376
! 73: #
! 74: do_test collate5-1.11 {
! 75: execsql {
! 76: CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY);
! 77: INSERT INTO tkt3376 VALUES('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
! 78: INSERT INTO tkt3376 VALUES('ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789');
! 79: SELECT DISTINCT a FROM tkt3376;
! 80: }
! 81: } {abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789}
! 82: do_test collate5-1.12 {
! 83: sqlite3 db2 :memory:
! 84: db2 eval {
! 85: PRAGMA encoding=UTF16le;
! 86: CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY);
! 87: INSERT INTO tkt3376 VALUES('abc');
! 88: INSERT INTO tkt3376 VALUES('ABX');
! 89: SELECT DISTINCT a FROM tkt3376;
! 90: }
! 91: } {abc ABX}
! 92: catch {db2 close}
! 93:
! 94: # The remainder of this file tests compound SELECT statements.
! 95: # Omit it if the library is compiled such that they are omitted.
! 96: #
! 97: ifcapable !compound {
! 98: finish_test
! 99: return
! 100: }
! 101:
! 102: #
! 103: # Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT
! 104: # queries that use user-defined collation sequences.
! 105: #
! 106: # collate5-2.1.* - UNION
! 107: # collate5-2.2.* - INTERSECT
! 108: # collate5-2.3.* - EXCEPT
! 109: #
! 110: do_test collate5-2.0 {
! 111: execsql {
! 112: CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase);
! 113:
! 114: INSERT INTO collate5t2 VALUES('a', 'apple');
! 115: INSERT INTO collate5t2 VALUES('A', 'apple');
! 116: INSERT INTO collate5t2 VALUES('b', 'banana');
! 117: INSERT INTO collate5t2 VALUES('B', 'Banana');
! 118: }
! 119: } {}
! 120:
! 121: do_test collate5-2.1.1 {
! 122: execsql {
! 123: SELECT a FROM collate5t1 UNION select a FROM collate5t2;
! 124: }
! 125: } {A B N}
! 126: do_test collate5-2.1.2 {
! 127: execsql {
! 128: SELECT a FROM collate5t2 UNION select a FROM collate5t1;
! 129: }
! 130: } {A B N a b n}
! 131: do_test collate5-2.1.3 {
! 132: execsql {
! 133: SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2;
! 134: }
! 135: } {A Apple A apple B Banana b banana N {}}
! 136: do_test collate5-2.1.4 {
! 137: execsql {
! 138: SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1;
! 139: }
! 140: } {A Apple B banana N {} a apple b banana n {}}
! 141:
! 142: do_test collate5-2.2.1 {
! 143: execsql {
! 144: SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;
! 145: }
! 146: } {N}
! 147: do_test collate5-2.2.2 {
! 148: execsql {
! 149: SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';
! 150: }
! 151: } {A a}
! 152: do_test collate5-2.2.3 {
! 153: execsql {
! 154: SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;
! 155: }
! 156: } {A Apple N {}}
! 157: do_test collate5-2.2.4 {
! 158: execsql {
! 159: SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1
! 160: where a != 'a';
! 161: }
! 162: } {A apple a apple}
! 163:
! 164: do_test collate5-2.3.1 {
! 165: execsql {
! 166: SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;
! 167: }
! 168: } {A B}
! 169: do_test collate5-2.3.2 {
! 170: execsql {
! 171: SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';
! 172: }
! 173: } {B b}
! 174: do_test collate5-2.3.3 {
! 175: execsql {
! 176: SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2;
! 177: }
! 178: } {a apple B banana}
! 179: do_test collate5-2.3.4 {
! 180: execsql {
! 181: SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1;
! 182: }
! 183: } {A apple B Banana a apple b banana}
! 184:
! 185: #
! 186: # This test ensures performs a UNION operation with a bunch of different
! 187: # length records. The goal is to test that the logic that compares records
! 188: # for the compound SELECT operators works with record lengths that lie
! 189: # either side of the troublesome 256 and 65536 byte marks.
! 190: #
! 191: set ::lens [list \
! 192: 0 1 2 3 4 5 6 7 8 9 \
! 193: 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \
! 194: 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \
! 195: 65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \
! 196: 65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \
! 197: 65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ]
! 198: do_test collate5-2.4.0 {
! 199: execsql {
! 200: BEGIN;
! 201: CREATE TABLE collate5t3(a, b);
! 202: }
! 203: foreach ii $::lens {
! 204: execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');"
! 205: }
! 206: expr [llength [execsql {
! 207: COMMIT;
! 208: SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3;
! 209: }]] / 2
! 210: } [llength $::lens]
! 211: do_test collate5-2.4.1 {
! 212: execsql {DROP TABLE collate5t3;}
! 213: } {}
! 214: unset ::lens
! 215:
! 216: #
! 217: # These tests - collate5-3.* - focus on compound SELECT queries that
! 218: # feature ORDER BY clauses.
! 219: #
! 220: do_test collate5-3.0 {
! 221: execsql {
! 222: SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1;
! 223: }
! 224: } {a A a A b B b B n N}
! 225: do_test collate5-3.1 {
! 226: execsql {
! 227: SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1;
! 228: }
! 229: } {A A B B N a a b b n}
! 230: do_test collate5-3.2 {
! 231: execsql {
! 232: SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2
! 233: ORDER BY 1 COLLATE TEXT;
! 234: }
! 235: } {A A B B N a a b b n}
! 236:
! 237: do_test collate5-3.3 {
! 238: execsql {
! 239: CREATE TABLE collate5t_cn(a COLLATE NUMERIC);
! 240: CREATE TABLE collate5t_ct(a COLLATE TEXT);
! 241: INSERT INTO collate5t_cn VALUES('1');
! 242: INSERT INTO collate5t_cn VALUES('11');
! 243: INSERT INTO collate5t_cn VALUES('101');
! 244: INSERT INTO collate5t_ct SELECT * FROM collate5t_cn;
! 245: }
! 246: } {}
! 247: do_test collate5-3.4 {
! 248: execsql {
! 249: SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1;
! 250: }
! 251: } {1 11 101}
! 252: do_test collate5-3.5 {
! 253: execsql {
! 254: SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1;
! 255: }
! 256: } {1 101 11}
! 257:
! 258: do_test collate5-3.20 {
! 259: execsql {
! 260: DROP TABLE collate5t_cn;
! 261: DROP TABLE collate5t_ct;
! 262: DROP TABLE collate5t1;
! 263: DROP TABLE collate5t2;
! 264: }
! 265: } {}
! 266:
! 267: do_test collate5-4.0 {
! 268: execsql {
! 269: CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC);
! 270: INSERT INTO collate5t1 VALUES('a', '1');
! 271: INSERT INTO collate5t1 VALUES('A', '1.0');
! 272: INSERT INTO collate5t1 VALUES('b', '2');
! 273: INSERT INTO collate5t1 VALUES('B', '3');
! 274: }
! 275: } {}
! 276: do_test collate5-4.1 {
! 277: string tolower [execsql {
! 278: SELECT a, count(*) FROM collate5t1 GROUP BY a;
! 279: }]
! 280: } {a 2 b 2}
! 281: do_test collate5-4.2 {
! 282: execsql {
! 283: SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b;
! 284: }
! 285: } {A 1.0 2 b 2 1 B 3 1}
! 286: do_test collate5-4.3 {
! 287: execsql {
! 288: DROP TABLE collate5t1;
! 289: }
! 290: } {}
! 291:
! 292: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate5.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