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