| blob | 6d527c33ce365abf68bcf5caab2bc2af59c6c8c5 |
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.19 2005/02/02 01:10:45 danielk1977 Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
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}
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 -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}
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}
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 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
110 do_test sort-2.1.2 {
111 execsql {
112 SELECT v FROM t1 ORDER BY substr(v,2,999);
113 }
114 } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
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 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11}
120 do_test sort-2.1.4 {
121 execsql {
122 SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
123 }
124 } {x11 x1.6 x0.0013442 x-4221 x-3.141592653 x-2b x-2.15 x-123}
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 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221}
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}
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 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 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 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123}
205 do_test sort-4.8 {
206 execsql {
207 SELECT substr(v,2,99) FROM t1 ORDER BY 1;
208 }
209 } {-123 -2.15 -2b -3.141592653 -4.0e9 -4221 0.0013442 01234567890123456789 1.6 11 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}
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}
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}
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}
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}
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
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}
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 A1 100.0 A2}
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
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}
434 finish_test