Debian Codegraph @

test/collate5.test

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