| blob | 7cf5698454804c577699569b6dcec1cf72e8c333 |
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 testing collation sequences.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix collate1
20 #
21 # Tests are roughly organised as follows:
22 #
23 # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.
24 # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.
25 # collate1-3.* - ORDER BY using a default collation type. Also that an
26 # explict collate type overrides a default collate type.
27 # collate1-4.* - ORDER BY using a data type.
28 #
30 #
31 # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal
32 # number, then it is converted to one before the comparison is performed.
33 # Numbers are less than other strings. If neither argument is a number,
34 # [string compare] is used.
35 #
36 db collate HEX hex_collate
37 proc hex_collate {lhs rhs} {
38 set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]
39 set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]
40 if {$lhs_ishex && $rhs_ishex} {
41 set lhsx [scan $lhs %x]
42 set rhsx [scan $rhs %x]
43 if {$lhs < $rhs} {return -1}
44 if {$lhs == $rhs} {return 0}
45 if {$lhs > $rhs} {return 1}
46 }
47 if {$lhs_ishex} {
48 return -1;
49 }
50 if {$rhs_ishex} {
51 return 1;
52 }
53 return [string compare $lhs $rhs]
54 }
55 db function hex {format 0x%X}
57 # Mimic the SQLite 2 collation type NUMERIC.
58 db collate numeric numeric_collate
59 proc numeric_collate {lhs rhs} {
60 if {$lhs == $rhs} {return 0}
61 return [expr ($lhs>$rhs)?1:-1]
62 }
64 do_test collate1-1.0 {
65 execsql {
66 CREATE TABLE collate1t1(c1, c2);
67 INSERT INTO collate1t1 VALUES(45, hex(45));
68 INSERT INTO collate1t1 VALUES(NULL, NULL);
69 INSERT INTO collate1t1 VALUES(281, hex(281));
70 }
71 } {}
72 do_test collate1-1.1 {
73 execsql {
74 SELECT c2 FROM collate1t1 ORDER BY 1;
75 }
76 } {{} 0x119 0x2D}
77 do_test collate1-1.2 {
78 execsql {
79 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;
80 }
81 } {{} 0x2D 0x119}
82 do_test collate1-1.3 {
83 execsql {
84 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;
85 }
86 } {0x119 0x2D {}}
87 do_test collate1-1.4 {
88 execsql {
89 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;
90 }
91 } {{} 0x2D 0x119}
92 do_test collate1-1.5 {
93 execsql {
94 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1
95 }
96 } {{} 0x2D 0x119}
97 do_test collate1-1.6 {
98 execsql {
99 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC
100 }
101 } {{} 0x2D 0x119}
102 do_test collate1-1.7 {
103 execsql {
104 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC
105 }
106 } {0x119 0x2D {}}
107 do_test collate1-1.99 {
108 execsql {
109 DROP TABLE collate1t1;
110 }
111 } {}
113 do_test collate1-2.0 {
114 execsql {
115 CREATE TABLE collate1t1(c1, c2);
116 INSERT INTO collate1t1 VALUES('5', '0x11');
117 INSERT INTO collate1t1 VALUES('5', '0xA');
118 INSERT INTO collate1t1 VALUES(NULL, NULL);
119 INSERT INTO collate1t1 VALUES('7', '0xA');
120 INSERT INTO collate1t1 VALUES('11', '0x11');
121 INSERT INTO collate1t1 VALUES('11', '0x101');
122 }
123 } {}
124 do_test collate1-2.2 {
125 execsql {
126 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
127 }
128 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
129 do_test collate1-2.3 {
130 execsql {
131 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;
132 }
133 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
134 do_test collate1-2.4 {
135 execsql {
136 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
137 }
138 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
139 do_test collate1-2.5 {
140 execsql {
141 SELECT c1, c2 FROM collate1t1
142 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;
143 }
144 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
145 do_test collate1-2.6 {
146 execsql {
147 SELECT c1, c2 FROM collate1t1
148 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
149 }
150 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
151 do_test collate1-2.12.1 {
152 execsql {
153 SELECT c1 COLLATE numeric, c2 FROM collate1t1
154 ORDER BY 1, 2 COLLATE hex;
155 }
156 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
157 do_test collate1-2.12.2 {
158 execsql {
159 SELECT c1 COLLATE hex, c2 FROM collate1t1
160 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
161 }
162 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
163 do_test collate1-2.12.3 {
164 execsql {
165 SELECT c1, c2 COLLATE hex FROM collate1t1
166 ORDER BY 1 COLLATE numeric, 2;
167 }
168 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
169 do_test collate1-2.12.4 {
170 execsql {
171 SELECT c1 COLLATE numeric, c2 COLLATE hex
172 FROM collate1t1
173 ORDER BY 1, 2;
174 }
175 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
176 do_test collate1-2.13 {
177 execsql {
178 SELECT c1 COLLATE binary, c2 COLLATE hex
179 FROM collate1t1
180 ORDER BY 1, 2;
181 }
182 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
183 do_test collate1-2.14 {
184 execsql {
185 SELECT c1, c2
186 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
187 }
188 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
189 do_test collate1-2.15 {
190 execsql {
191 SELECT c1 COLLATE binary, c2 COLLATE hex
192 FROM collate1t1
193 ORDER BY 1 DESC, 2 DESC;
194 }
195 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
196 do_test collate1-2.16 {
197 execsql {
198 SELECT c1 COLLATE hex, c2 COLLATE binary
199 FROM collate1t1
200 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
201 }
202 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
203 do_test collate1-2.99 {
204 execsql {
205 DROP TABLE collate1t1;
206 }
207 } {}
209 #
210 # These tests ensure that the default collation type for a column is used
211 # by an ORDER BY clause correctly. The focus is all the different ways
212 # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.
213 #
214 do_test collate1-3.0 {
215 execsql {
216 CREATE TABLE collate1t1(a COLLATE hex, b);
217 INSERT INTO collate1t1 VALUES( '0x5', 5 );
218 INSERT INTO collate1t1 VALUES( '1', 1 );
219 INSERT INTO collate1t1 VALUES( '0x45', 69 );
220 INSERT INTO collate1t1 VALUES( NULL, NULL );
221 SELECT * FROM collate1t1 ORDER BY a;
222 }
223 } {{} {} 1 1 0x5 5 0x45 69}
225 do_test collate1-3.1 {
226 execsql {
227 SELECT * FROM collate1t1 ORDER BY 1;
228 }
229 } {{} {} 1 1 0x5 5 0x45 69}
230 do_test collate1-3.2 {
231 execsql {
232 SELECT * FROM collate1t1 ORDER BY collate1t1.a;
233 }
234 } {{} {} 1 1 0x5 5 0x45 69}
235 do_test collate1-3.3 {
236 execsql {
237 SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;
238 }
239 } {{} {} 1 1 0x5 5 0x45 69}
240 do_test collate1-3.4 {
241 execsql {
242 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;
243 }
244 } {{} {} 1 1 0x5 5 0x45 69}
245 do_test collate1-3.5 {
246 execsql {
247 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;
248 }
249 } {{} {} 0x45 69 0x5 5 1 1}
250 do_test collate1-3.5.1 {
251 execsql {
252 SELECT a COLLATE binary as c1, b as c2
253 FROM collate1t1 ORDER BY c1;
254 }
255 } {{} {} 0x45 69 0x5 5 1 1}
256 do_test collate1-3.6 {
257 execsql {
258 DROP TABLE collate1t1;
259 }
260 } {}
262 # Update for SQLite version 3. The collate1-4.* test cases were written
263 # before manifest types were introduced. The following test cases still
264 # work, due to the 'affinity' mechanism, but they don't prove anything
265 # about collation sequences.
266 #
267 do_test collate1-4.0 {
268 execsql {
269 CREATE TABLE collate1t1(c1 numeric, c2 text);
270 INSERT INTO collate1t1 VALUES(1, 1);
271 INSERT INTO collate1t1 VALUES(12, 12);
272 INSERT INTO collate1t1 VALUES(NULL, NULL);
273 INSERT INTO collate1t1 VALUES(101, 101);
274 }
275 } {}
276 do_test collate1-4.1 {
277 execsql {
278 SELECT c1 FROM collate1t1 ORDER BY 1;
279 }
280 } {{} 1 12 101}
281 do_test collate1-4.2 {
282 execsql {
283 SELECT c2 FROM collate1t1 ORDER BY 1;
284 }
285 } {{} 1 101 12}
286 do_test collate1-4.3 {
287 execsql {
288 SELECT c2+0 FROM collate1t1 ORDER BY 1;
289 }
290 } {{} 1 12 101}
291 do_test collate1-4.4 {
292 execsql {
293 SELECT c1||'' FROM collate1t1 ORDER BY 1;
294 }
295 } {{} 1 101 12}
296 do_test collate1-4.4.1 {
297 execsql {
298 SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;
299 }
300 } {{} 1 12 101}
301 do_test collate1-4.5 {
302 execsql {
303 DROP TABLE collate1t1;
304 }
305 } {}
307 # A problem reported on the mailing list: A CREATE TABLE statement
308 # is allowed to have two or more COLLATE clauses on the same column.
309 # That probably ought to be an error, but we allow it for backwards
310 # compatibility. Just make sure it works and doesn't leak memory.
311 #
312 do_test collate1-5.1 {
313 execsql {
314 CREATE TABLE c5(
315 id INTEGER PRIMARY KEY,
316 a TEXT COLLATE binary COLLATE nocase COLLATE rtrim,
317 b TEXT COLLATE nocase COLLATE binary,
318 c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase
319 );
320 INSERT INTO c5 VALUES(1, 'abc','abc','abc');
321 INSERT INTO c5 VALUES(2, 'abc ','ABC','ABC');
322 SELECT id FROM c5 WHERE a='abc' ORDER BY id;
323 }
324 } {1 2}
325 do_test collate1-5.2 {
326 execsql {
327 SELECT id FROM c5 WHERE b='abc' ORDER BY id;
328 }
329 } {1}
330 do_test collate1-5.3 {
331 execsql {
332 SELECT id FROM c5 WHERE c='abc' ORDER BY id;
333 }
334 } {1 2}
338 #-------------------------------------------------------------------------
339 # Fix problems with handling collation sequences named '"""'.
340 #
341 do_execsql_test 6.1 {
342 SELECT """""""";
343 } {\"\"\"}
345 do_catchsql_test 6.2 {
346 CREATE TABLE x1(a);
347 SELECT a FROM x1 ORDER BY a COLLATE """""""";
348 } {1 {no such collation sequence: """}}
350 do_catchsql_test 6.3 {
351 SELECT a FROM x1 ORDER BY 1 COLLATE """""""";
352 } {1 {no such collation sequence: """}}
354 do_catchsql_test 6.4 {
355 SELECT 0 UNION SELECT 0 ORDER BY 1 COLLATE """""""";
356 } {1 {no such collation sequence: """}}
358 db collate {"""} [list string compare -nocase]
360 do_execsql_test 6.5 {
361 PRAGMA foreign_keys = ON;
362 CREATE TABLE p1(a PRIMARY KEY COLLATE '"""');
363 CREATE TABLE c1(x, y REFERENCES p1);
364 } {}
366 do_execsql_test 6.6 {
367 INSERT INTO p1 VALUES('abc');
368 INSERT INTO c1 VALUES(1, 'ABC');
369 }
371 ifcapable foreignkey {
372 do_catchsql_test 6.7 {
373 DELETE FROM p1 WHERE rowid = 1
374 } {1 {FOREIGN KEY constraint failed}}
375 }
377 do_execsql_test 6.8 {
378 INSERT INTO p1 VALUES('abb');
379 INSERT INTO p1 VALUES('wxz');
380 INSERT INTO p1 VALUES('wxy');
382 INSERT INTO c1 VALUES(2, 'abb');
383 INSERT INTO c1 VALUES(3, 'wxz');
384 INSERT INTO c1 VALUES(4, 'WXY');
385 SELECT x, y FROM c1 ORDER BY y COLLATE """""""";
386 } {2 abb 1 ABC 4 WXY 3 wxz}
388 # 2015-04-15: Nested COLLATE operators
389 #
390 do_execsql_test 7.0 {
391 SELECT 'abc' UNION ALL SELECT 'DEF'
392 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE nocase;
393 } {abc DEF}
394 do_execsql_test 7.1 {
395 SELECT 'abc' UNION ALL SELECT 'DEF'
396 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE binary;
397 } {DEF abc}
398 do_execsql_test 7.2 {
399 SELECT 'abc' UNION ALL SELECT 'DEF'
400 ORDER BY 1 COLLATE binary COLLATE binary COLLATE binary COLLATE nocase;
401 } {abc DEF}
404 finish_test