wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
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