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.1.1.1 2012/02/21 17:04:16 misho 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