wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15. |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # This file implements regression tests for SQLite library. The |
||
12 | # focus of this file is testing the CREATE TABLE statement. |
||
13 | # |
||
14 | # $Id: sort.test,v 1.25 2005/11/14 22:29:06 drh Exp $ |
||
15 | |||
16 | set testdir [file dirname $argv0] |
||
17 | source $testdir/tester.tcl |
||
18 | |||
19 | # Create a bunch of data to sort against |
||
20 | # |
||
21 | do_test sort-1.0 { |
||
22 | execsql { |
||
23 | CREATE TABLE t1( |
||
24 | n int, |
||
25 | v varchar(10), |
||
26 | log int, |
||
27 | roman varchar(10), |
||
28 | flt real |
||
29 | ); |
||
30 | INSERT INTO t1 VALUES(1,'one',0,'I',3.141592653); |
||
31 | INSERT INTO t1 VALUES(2,'two',1,'II',2.15); |
||
32 | INSERT INTO t1 VALUES(3,'three',1,'III',4221.0); |
||
33 | INSERT INTO t1 VALUES(4,'four',2,'IV',-0.0013442); |
||
34 | INSERT INTO t1 VALUES(5,'five',2,'V',-11); |
||
35 | INSERT INTO t1 VALUES(6,'six',2,'VI',0.123); |
||
36 | INSERT INTO t1 VALUES(7,'seven',2,'VII',123.0); |
||
37 | INSERT INTO t1 VALUES(8,'eight',3,'VIII',-1.6); |
||
38 | } |
||
39 | execsql {SELECT count(*) FROM t1} |
||
40 | } {8} |
||
41 | |||
42 | do_test sort-1.1 { |
||
43 | execsql {SELECT n FROM t1 ORDER BY n} |
||
44 | } {1 2 3 4 5 6 7 8} |
||
45 | do_test sort-1.1.1 { |
||
46 | execsql {SELECT n FROM t1 ORDER BY n ASC} |
||
47 | } {1 2 3 4 5 6 7 8} |
||
48 | do_test sort-1.1.1 { |
||
49 | execsql {SELECT ALL n FROM t1 ORDER BY n ASC} |
||
50 | } {1 2 3 4 5 6 7 8} |
||
51 | do_test sort-1.2 { |
||
52 | execsql {SELECT n FROM t1 ORDER BY n DESC} |
||
53 | } {8 7 6 5 4 3 2 1} |
||
54 | do_test sort-1.3a { |
||
55 | execsql {SELECT v FROM t1 ORDER BY v} |
||
56 | } {eight five four one seven six three two} |
||
57 | do_test sort-1.3b { |
||
58 | execsql {SELECT n FROM t1 ORDER BY v} |
||
59 | } {8 5 4 1 7 6 3 2} |
||
60 | do_test sort-1.4 { |
||
61 | execsql {SELECT n FROM t1 ORDER BY v DESC} |
||
62 | } {2 3 6 7 1 4 5 8} |
||
63 | do_test sort-1.5 { |
||
64 | execsql {SELECT flt FROM t1 ORDER BY flt} |
||
65 | } {-11.0 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0} |
||
66 | do_test sort-1.6 { |
||
67 | execsql {SELECT flt FROM t1 ORDER BY flt DESC} |
||
68 | } {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11.0} |
||
69 | do_test sort-1.7 { |
||
70 | execsql {SELECT roman FROM t1 ORDER BY roman} |
||
71 | } {I II III IV V VI VII VIII} |
||
72 | do_test sort-1.8 { |
||
73 | execsql {SELECT n FROM t1 ORDER BY log, flt} |
||
74 | } {1 2 3 5 4 6 7 8} |
||
75 | do_test sort-1.8.1 { |
||
76 | execsql {SELECT n FROM t1 ORDER BY log asc, flt} |
||
77 | } {1 2 3 5 4 6 7 8} |
||
78 | do_test sort-1.8.2 { |
||
79 | execsql {SELECT n FROM t1 ORDER BY log, flt ASC} |
||
80 | } {1 2 3 5 4 6 7 8} |
||
81 | do_test sort-1.8.3 { |
||
82 | execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc} |
||
83 | } {1 2 3 5 4 6 7 8} |
||
84 | do_test sort-1.9 { |
||
85 | execsql {SELECT n FROM t1 ORDER BY log, flt DESC} |
||
86 | } {1 3 2 7 6 4 5 8} |
||
87 | do_test sort-1.9.1 { |
||
88 | execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC} |
||
89 | } {1 3 2 7 6 4 5 8} |
||
90 | do_test sort-1.10 { |
||
91 | execsql {SELECT n FROM t1 ORDER BY log DESC, flt} |
||
92 | } {8 5 4 6 7 2 3 1} |
||
93 | do_test sort-1.11 { |
||
94 | execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} |
||
95 | } {8 7 6 4 5 3 2 1} |
||
96 | |||
97 | # These tests are designed to reach some hard-to-reach places |
||
98 | # inside the string comparison routines. |
||
99 | # |
||
100 | # (Later) The sorting behavior changed in 2.7.0. But we will |
||
101 | # keep these tests. You can never have too many test cases! |
||
102 | # |
||
103 | do_test sort-2.1.1 { |
||
104 | execsql { |
||
105 | UPDATE t1 SET v='x' || -flt; |
||
106 | UPDATE t1 SET v='x-2b' where v=='x-0.123'; |
||
107 | SELECT v FROM t1 ORDER BY v; |
||
108 | } |
||
109 | } {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0} |
||
110 | do_test sort-2.1.2 { |
||
111 | execsql { |
||
112 | SELECT v FROM t1 ORDER BY substr(v,2,999); |
||
113 | } |
||
114 | } {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0} |
||
115 | do_test sort-2.1.3 { |
||
116 | execsql { |
||
117 | SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; |
||
118 | } |
||
119 | } {x-4221.0 x-123.0 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11.0} |
||
120 | do_test sort-2.1.4 { |
||
121 | execsql { |
||
122 | SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; |
||
123 | } |
||
124 | } {x11.0 x1.6 x0.0013442 x-4221.0 x-3.141592653 x-2b x-2.15 x-123.0} |
||
125 | do_test sort-2.1.5 { |
||
126 | execsql { |
||
127 | SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; |
||
128 | } |
||
129 | } {x11.0 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123.0 x-4221.0} |
||
130 | |||
131 | # This is a bug fix for 2.2.4. |
||
132 | # Strings are normally mapped to upper-case for a caseless comparison. |
||
133 | # But this can cause problems for characters in between 'Z' and 'a'. |
||
134 | # |
||
135 | do_test sort-3.1 { |
||
136 | execsql { |
||
137 | CREATE TABLE t2(a,b); |
||
138 | INSERT INTO t2 VALUES('AGLIENTU',1); |
||
139 | INSERT INTO t2 VALUES('AGLIE`',2); |
||
140 | INSERT INTO t2 VALUES('AGNA',3); |
||
141 | SELECT a, b FROM t2 ORDER BY a; |
||
142 | } |
||
143 | } {AGLIENTU 1 AGLIE` 2 AGNA 3} |
||
144 | do_test sort-3.2 { |
||
145 | execsql { |
||
146 | SELECT a, b FROM t2 ORDER BY a DESC; |
||
147 | } |
||
148 | } {AGNA 3 AGLIE` 2 AGLIENTU 1} |
||
149 | do_test sort-3.3 { |
||
150 | execsql { |
||
151 | DELETE FROM t2; |
||
152 | INSERT INTO t2 VALUES('aglientu',1); |
||
153 | INSERT INTO t2 VALUES('aglie`',2); |
||
154 | INSERT INTO t2 VALUES('agna',3); |
||
155 | SELECT a, b FROM t2 ORDER BY a; |
||
156 | } |
||
157 | } {aglie` 2 aglientu 1 agna 3} |
||
158 | do_test sort-3.4 { |
||
159 | execsql { |
||
160 | SELECT a, b FROM t2 ORDER BY a DESC; |
||
161 | } |
||
162 | } {agna 3 aglientu 1 aglie` 2} |
||
163 | |||
164 | # Version 2.7.0 testing. |
||
165 | # |
||
166 | do_test sort-4.1 { |
||
167 | execsql { |
||
168 | INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); |
||
169 | INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); |
||
170 | INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); |
||
171 | INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); |
||
172 | SELECT n FROM t1 ORDER BY n; |
||
173 | } |
||
174 | } {1 2 3 4 5 6 7 8 9 10 11 12} |
||
175 | do_test sort-4.2 { |
||
176 | execsql { |
||
177 | SELECT n||'' FROM t1 ORDER BY 1; |
||
178 | } |
||
179 | } {1 10 11 12 2 3 4 5 6 7 8 9} |
||
180 | do_test sort-4.3 { |
||
181 | execsql { |
||
182 | SELECT n+0 FROM t1 ORDER BY 1; |
||
183 | } |
||
184 | } {1 2 3 4 5 6 7 8 9 10 11 12} |
||
185 | do_test sort-4.4 { |
||
186 | execsql { |
||
187 | SELECT n||'' FROM t1 ORDER BY 1 DESC; |
||
188 | } |
||
189 | } {9 8 7 6 5 4 3 2 12 11 10 1} |
||
190 | do_test sort-4.5 { |
||
191 | execsql { |
||
192 | SELECT n+0 FROM t1 ORDER BY 1 DESC; |
||
193 | } |
||
194 | } {12 11 10 9 8 7 6 5 4 3 2 1} |
||
195 | do_test sort-4.6 { |
||
196 | execsql { |
||
197 | SELECT v FROM t1 ORDER BY 1; |
||
198 | } |
||
199 | } {x-123.0 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221.0 x0.0013442 x01234567890123456789 x1.6 x11.0 x2.7 x5.0e10} |
||
200 | do_test sort-4.7 { |
||
201 | execsql { |
||
202 | SELECT v FROM t1 ORDER BY 1 DESC; |
||
203 | } |
||
204 | } {x5.0e10 x2.7 x11.0 x1.6 x01234567890123456789 x0.0013442 x-4221.0 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123.0} |
||
205 | do_test sort-4.8 { |
||
206 | execsql { |
||
207 | SELECT substr(v,2,99) FROM t1 ORDER BY 1; |
||
208 | } |
||
209 | } {-123.0 -2.15 -2b -3.141592653 -4.0e9 -4221.0 0.0013442 01234567890123456789 1.6 11.0 2.7 5.0e10} |
||
210 | #do_test sort-4.9 { |
||
211 | # execsql { |
||
212 | # SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1; |
||
213 | # } |
||
214 | #} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18} |
||
215 | |||
216 | do_test sort-5.1 { |
||
217 | execsql { |
||
218 | create table t3(a,b); |
||
219 | insert into t3 values(5,NULL); |
||
220 | insert into t3 values(6,NULL); |
||
221 | insert into t3 values(3,NULL); |
||
222 | insert into t3 values(4,'cd'); |
||
223 | insert into t3 values(1,'ab'); |
||
224 | insert into t3 values(2,NULL); |
||
225 | select a from t3 order by b, a; |
||
226 | } |
||
227 | } {2 3 5 6 1 4} |
||
228 | do_test sort-5.2 { |
||
229 | execsql { |
||
230 | select a from t3 order by b, a desc; |
||
231 | } |
||
232 | } {6 5 3 2 1 4} |
||
233 | do_test sort-5.3 { |
||
234 | execsql { |
||
235 | select a from t3 order by b desc, a; |
||
236 | } |
||
237 | } {4 1 2 3 5 6} |
||
238 | do_test sort-5.4 { |
||
239 | execsql { |
||
240 | select a from t3 order by b desc, a desc; |
||
241 | } |
||
242 | } {4 1 6 5 3 2} |
||
243 | |||
244 | do_test sort-6.1 { |
||
245 | execsql { |
||
246 | create index i3 on t3(b,a); |
||
247 | select a from t3 order by b, a; |
||
248 | } |
||
249 | } {2 3 5 6 1 4} |
||
250 | do_test sort-6.2 { |
||
251 | execsql { |
||
252 | select a from t3 order by b, a desc; |
||
253 | } |
||
254 | } {6 5 3 2 1 4} |
||
255 | do_test sort-6.3 { |
||
256 | execsql { |
||
257 | select a from t3 order by b desc, a; |
||
258 | } |
||
259 | } {4 1 2 3 5 6} |
||
260 | do_test sort-6.4 { |
||
261 | execsql { |
||
262 | select a from t3 order by b desc, a desc; |
||
263 | } |
||
264 | } {4 1 6 5 3 2} |
||
265 | |||
266 | do_test sort-7.1 { |
||
267 | execsql { |
||
268 | CREATE TABLE t4( |
||
269 | a INTEGER, |
||
270 | b VARCHAR(30) |
||
271 | ); |
||
272 | INSERT INTO t4 VALUES(1,1); |
||
273 | INSERT INTO t4 VALUES(2,2); |
||
274 | INSERT INTO t4 VALUES(11,11); |
||
275 | INSERT INTO t4 VALUES(12,12); |
||
276 | SELECT a FROM t4 ORDER BY 1; |
||
277 | } |
||
278 | } {1 2 11 12} |
||
279 | do_test sort-7.2 { |
||
280 | execsql { |
||
281 | SELECT b FROM t4 ORDER BY 1 |
||
282 | } |
||
283 | } {1 11 12 2} |
||
284 | |||
285 | # Omit tests sort-7.3 to sort-7.8 if view support was disabled at |
||
286 | # compilatation time. |
||
287 | ifcapable view { |
||
288 | do_test sort-7.3 { |
||
289 | execsql { |
||
290 | CREATE VIEW v4 AS SELECT * FROM t4; |
||
291 | SELECT a FROM v4 ORDER BY 1; |
||
292 | } |
||
293 | } {1 2 11 12} |
||
294 | do_test sort-7.4 { |
||
295 | execsql { |
||
296 | SELECT b FROM v4 ORDER BY 1; |
||
297 | } |
||
298 | } {1 11 12 2} |
||
299 | |||
300 | ifcapable compound { |
||
301 | do_test sort-7.5 { |
||
302 | execsql { |
||
303 | SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1; |
||
304 | } |
||
305 | } {1 2 11 12} |
||
306 | do_test sort-7.6 { |
||
307 | execsql { |
||
308 | SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1; |
||
309 | } |
||
310 | } {1 2 11 12 1 11 12 2} ;# text from t4.b and numeric from v4.a |
||
311 | do_test sort-7.7 { |
||
312 | execsql { |
||
313 | SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1; |
||
314 | } |
||
315 | } {1 2 11 12 1 11 12 2} ;# numeric from t4.a and text from v4.b |
||
316 | do_test sort-7.8 { |
||
317 | execsql { |
||
318 | SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1; |
||
319 | } |
||
320 | } {1 11 12 2} |
||
321 | } ;# ifcapable compound |
||
322 | } ;# ifcapable view |
||
323 | |||
324 | #### Version 3 works differently here: |
||
325 | #do_test sort-7.9 { |
||
326 | # execsql { |
||
327 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric; |
||
328 | # } |
||
329 | #} {1 2 11 12} |
||
330 | #do_test sort-7.10 { |
||
331 | # execsql { |
||
332 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer; |
||
333 | # } |
||
334 | #} {1 2 11 12} |
||
335 | #do_test sort-7.11 { |
||
336 | # execsql { |
||
337 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text; |
||
338 | # } |
||
339 | #} {1 11 12 2} |
||
340 | #do_test sort-7.12 { |
||
341 | # execsql { |
||
342 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE blob; |
||
343 | # } |
||
344 | #} {1 11 12 2} |
||
345 | #do_test sort-7.13 { |
||
346 | # execsql { |
||
347 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE clob; |
||
348 | # } |
||
349 | #} {1 11 12 2} |
||
350 | #do_test sort-7.14 { |
||
351 | # execsql { |
||
352 | # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE varchar; |
||
353 | # } |
||
354 | #} {1 11 12 2} |
||
355 | |||
356 | # Ticket #297 |
||
357 | # |
||
358 | do_test sort-8.1 { |
||
359 | execsql { |
||
360 | CREATE TABLE t5(a real, b text); |
||
361 | INSERT INTO t5 VALUES(100,'A1'); |
||
362 | INSERT INTO t5 VALUES(100.0,'A2'); |
||
363 | SELECT * FROM t5 ORDER BY a, b; |
||
364 | } |
||
365 | } {100.0 A1 100.0 A2} |
||
366 | |||
367 | |||
368 | ifcapable {bloblit} { |
||
369 | # BLOBs should sort after TEXT |
||
370 | # |
||
371 | do_test sort-9.1 { |
||
372 | execsql { |
||
373 | CREATE TABLE t6(x, y); |
||
374 | INSERT INTO t6 VALUES(1,1); |
||
375 | INSERT INTO t6 VALUES(2,'1'); |
||
376 | INSERT INTO t6 VALUES(3,x'31'); |
||
377 | INSERT INTO t6 VALUES(4,NULL); |
||
378 | SELECT x FROM t6 ORDER BY y; |
||
379 | } |
||
380 | } {4 1 2 3} |
||
381 | do_test sort-9.2 { |
||
382 | execsql { |
||
383 | SELECT x FROM t6 ORDER BY y DESC; |
||
384 | } |
||
385 | } {3 2 1 4} |
||
386 | do_test sort-9.3 { |
||
387 | execsql { |
||
388 | SELECT x FROM t6 WHERE y<1 |
||
389 | } |
||
390 | } {} |
||
391 | do_test sort-9.4 { |
||
392 | execsql { |
||
393 | SELECT x FROM t6 WHERE y<'1' |
||
394 | } |
||
395 | } {1} |
||
396 | do_test sort-9.5 { |
||
397 | execsql { |
||
398 | SELECT x FROM t6 WHERE y<x'31' |
||
399 | } |
||
400 | } {1 2} |
||
401 | do_test sort-9.6 { |
||
402 | execsql { |
||
403 | SELECT x FROM t6 WHERE y>1 |
||
404 | } |
||
405 | } {2 3} |
||
406 | do_test sort-9.7 { |
||
407 | execsql { |
||
408 | SELECT x FROM t6 WHERE y>'1' |
||
409 | } |
||
410 | } {3} |
||
411 | } ;# endif bloblit |
||
412 | |||
413 | # Ticket #1092 - ORDER BY on rowid fields. |
||
414 | do_test sort-10.1 { |
||
415 | execsql { |
||
416 | CREATE TABLE t7(c INTEGER PRIMARY KEY); |
||
417 | INSERT INTO t7 VALUES(1); |
||
418 | INSERT INTO t7 VALUES(2); |
||
419 | INSERT INTO t7 VALUES(3); |
||
420 | INSERT INTO t7 VALUES(4); |
||
421 | } |
||
422 | } {} |
||
423 | do_test sort-10.2 { |
||
424 | execsql { |
||
425 | SELECT c FROM t7 WHERE c<=3 ORDER BY c DESC; |
||
426 | } |
||
427 | } {3 2 1} |
||
428 | do_test sort-10.3 { |
||
429 | execsql { |
||
430 | SELECT c FROM t7 WHERE c<3 ORDER BY c DESC; |
||
431 | } |
||
432 | } {2 1} |
||
433 | |||
434 | # ticket #1358. Just because one table in a join gives a unique |
||
435 | # result does not mean they all do. We cannot disable sorting unless |
||
436 | # all tables in the join give unique results. |
||
437 | # |
||
438 | do_test sort-11.1 { |
||
439 | execsql { |
||
440 | create table t8(a unique, b, c); |
||
441 | insert into t8 values(1,2,3); |
||
442 | insert into t8 values(2,3,4); |
||
443 | create table t9(x,y); |
||
444 | insert into t9 values(2,4); |
||
445 | insert into t9 values(2,3); |
||
446 | select y from t8, t9 where a=1 order by a, y; |
||
447 | } |
||
448 | } {3 4} |
||
449 | |||
450 | # Trouble reported on the mailing list. Check for overly aggressive |
||
451 | # (which is to say, incorrect) optimization of order-by with a rowid |
||
452 | # in a join. |
||
453 | # |
||
454 | do_test sort-12.1 { |
||
455 | execsql { |
||
456 | create table a (id integer primary key); |
||
457 | create table b (id integer primary key, aId integer, text); |
||
458 | insert into a values (1); |
||
459 | insert into b values (2, 1, 'xxx'); |
||
460 | insert into b values (1, 1, 'zzz'); |
||
461 | insert into b values (3, 1, 'yyy'); |
||
462 | select a.id, b.id, b.text from a join b on (a.id = b.aId) |
||
463 | order by a.id, b.text; |
||
464 | } |
||
465 | } {1 2 xxx 1 3 yyy 1 1 zzz} |
||
466 | |||
467 | finish_test |