| blob | 337b15d6094b86d669c05dd7e42208cb12fb24e0 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the CREATE TABLE statement.
16 #
17 # $Id: sort.test,v 1.9 2003/04/18 17:45:15 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Create a bunch of data to sort against
23 #
24 do_test sort-1.0 {
25 set fd [open data.txt w]
26 puts $fd "1\tone\t0\tI\t3.141592653"
27 puts $fd "2\ttwo\t1\tII\t2.15"
28 puts $fd "3\tthree\t1\tIII\t4221.0"
29 puts $fd "4\tfour\t2\tIV\t-0.0013442"
30 puts $fd "5\tfive\t2\tV\t-11"
31 puts $fd "6\tsix\t2\tVI\t0.123"
32 puts $fd "7\tseven\t2\tVII\t123.0"
33 puts $fd "8\teight\t3\tVIII\t-1.6"
34 close $fd
35 execsql {
36 CREATE TABLE t1(
37 n int,
38 v varchar(10),
39 log int,
40 roman varchar(10),
41 flt real
42 );
43 COPY t1 FROM 'data.txt'
44 }
45 file delete data.txt
46 execsql {SELECT count(*) FROM t1}
47 } {8}
49 do_test sort-1.1 {
50 execsql {SELECT n FROM t1 ORDER BY n}
51 } {1 2 3 4 5 6 7 8}
52 do_test sort-1.1.1 {
53 execsql {SELECT n FROM t1 ORDER BY n ASC}
54 } {1 2 3 4 5 6 7 8}
55 do_test sort-1.1.1 {
56 execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
57 } {1 2 3 4 5 6 7 8}
58 do_test sort-1.2 {
59 execsql {SELECT n FROM t1 ORDER BY n DESC}
60 } {8 7 6 5 4 3 2 1}
61 do_test sort-1.3a {
62 execsql {SELECT v FROM t1 ORDER BY v}
63 } {eight five four one seven six three two}
64 do_test sort-1.3b {
65 execsql {SELECT n FROM t1 ORDER BY v}
66 } {8 5 4 1 7 6 3 2}
67 do_test sort-1.4 {
68 execsql {SELECT n FROM t1 ORDER BY v DESC}
69 } {2 3 6 7 1 4 5 8}
70 do_test sort-1.5 {
71 execsql {SELECT flt FROM t1 ORDER BY flt}
72 } {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
73 do_test sort-1.6 {
74 execsql {SELECT flt FROM t1 ORDER BY flt DESC}
75 } {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
76 do_test sort-1.7 {
77 execsql {SELECT roman FROM t1 ORDER BY roman}
78 } {I II III IV V VI VII VIII}
79 do_test sort-1.8 {
80 execsql {SELECT n FROM t1 ORDER BY log, flt}
81 } {1 2 3 5 4 6 7 8}
82 do_test sort-1.8.1 {
83 execsql {SELECT n FROM t1 ORDER BY log asc, flt}
84 } {1 2 3 5 4 6 7 8}
85 do_test sort-1.8.2 {
86 execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
87 } {1 2 3 5 4 6 7 8}
88 do_test sort-1.8.3 {
89 execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
90 } {1 2 3 5 4 6 7 8}
91 do_test sort-1.9 {
92 execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
93 } {1 3 2 7 6 4 5 8}
94 do_test sort-1.9.1 {
95 execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
96 } {1 3 2 7 6 4 5 8}
97 do_test sort-1.10 {
98 execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
99 } {8 5 4 6 7 2 3 1}
100 do_test sort-1.11 {
101 execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
102 } {8 7 6 4 5 3 2 1}
104 # These tests are designed to reach some hard-to-reach places
105 # inside the string comparison routines.
106 #
107 # (Later) The sorting behavior changed in 2.7.0. But we will
108 # keep these tests. You can never have too many test cases!
109 #
110 do_test sort-2.1.1 {
111 execsql {
112 UPDATE t1 SET v='x' || -flt;
113 UPDATE t1 SET v='x-2b' where v=='x-0.123';
114 SELECT v FROM t1 ORDER BY v;
115 }
116 } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
117 do_test sort-2.1.2 {
118 execsql {
119 SELECT v FROM t1 ORDER BY substr(v,2,999);
120 }
121 } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
122 do_test sort-2.1.3 {
123 execsql {
124 SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
125 }
126 } {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11}
127 do_test sort-2.1.4 {
128 execsql {
129 SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
130 }
131 } {x11 x1.6 x0.0013442 x-4221 x-3.141592653 x-2b x-2.15 x-123}
132 do_test sort-2.1.5 {
133 execsql {
134 SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
135 }
136 } {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221}
138 # This is a bug fix for 2.2.4.
139 # Strings are normally mapped to upper-case for a caseless comparison.
140 # But this can cause problems for characters in between 'Z' and 'a'.
141 #
142 do_test sort-3.1 {
143 execsql {
144 CREATE TABLE t2(a,b);
145 INSERT INTO t2 VALUES('AGLIENTU',1);
146 INSERT INTO t2 VALUES('AGLIE`',2);
147 INSERT INTO t2 VALUES('AGNA',3);
148 SELECT a, b FROM t2 ORDER BY a;
149 }
150 } {AGLIENTU 1 AGLIE` 2 AGNA 3}
151 do_test sort-3.2 {
152 execsql {
153 SELECT a, b FROM t2 ORDER BY a DESC;
154 }
155 } {AGNA 3 AGLIE` 2 AGLIENTU 1}
156 do_test sort-3.3 {
157 execsql {
158 DELETE FROM t2;
159 INSERT INTO t2 VALUES('aglientu',1);
160 INSERT INTO t2 VALUES('aglie`',2);
161 INSERT INTO t2 VALUES('agna',3);
162 SELECT a, b FROM t2 ORDER BY a;
163 }
164 } {aglie` 2 aglientu 1 agna 3}
165 do_test sort-3.4 {
166 execsql {
167 SELECT a, b FROM t2 ORDER BY a DESC;
168 }
169 } {agna 3 aglientu 1 aglie` 2}
171 # Version 2.7.0 testing.
172 #
173 do_test sort-4.1 {
174 execsql {
175 INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5);
176 INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5);
177 INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4);
178 INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3);
179 SELECT n FROM t1 ORDER BY n;
180 }
181 } {1 2 3 4 5 6 7 8 9 10 11 12}
182 do_test sort-4.2 {
183 execsql {
184 SELECT n||'' FROM t1 ORDER BY 1;
185 }
186 } {1 10 11 12 2 3 4 5 6 7 8 9}
187 do_test sort-4.3 {
188 execsql {
189 SELECT n+0 FROM t1 ORDER BY 1;
190 }
191 } {1 2 3 4 5 6 7 8 9 10 11 12}
192 do_test sort-4.4 {
193 execsql {
194 SELECT n||'' FROM t1 ORDER BY 1 DESC;
195 }
196 } {9 8 7 6 5 4 3 2 12 11 10 1}
197 do_test sort-4.5 {
198 execsql {
199 SELECT n+0 FROM t1 ORDER BY 1 DESC;
200 }
201 } {12 11 10 9 8 7 6 5 4 3 2 1}
202 do_test sort-4.6 {
203 execsql {
204 SELECT v FROM t1 ORDER BY 1;
205 }
206 } {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}
207 do_test sort-4.7 {
208 execsql {
209 SELECT v FROM t1 ORDER BY 1 DESC;
210 }
211 } {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}
212 do_test sort-4.8 {
213 execsql {
214 SELECT substr(v,2,99) FROM t1 ORDER BY 1;
215 }
216 } {-123 -2.15 -2b -3.141592653 -4.0e9 -4221 0.0013442 01234567890123456789 1.6 11 2.7 5.0e10}
217 #do_test sort-4.9 {
218 # execsql {
219 # SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1;
220 # }
221 #} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18}
223 do_test sort-5.1 {
224 execsql {
225 create table t3(a,b);
226 insert into t3 values(5,NULL);
227 insert into t3 values(6,NULL);
228 insert into t3 values(3,NULL);
229 insert into t3 values(4,'cd');
230 insert into t3 values(1,'ab');
231 insert into t3 values(2,NULL);
232 select a from t3 order by b, a;
233 }
234 } {2 3 5 6 1 4}
235 do_test sort-5.2 {
236 execsql {
237 select a from t3 order by b, a desc;
238 }
239 } {6 5 3 2 1 4}
240 do_test sort-5.3 {
241 execsql {
242 select a from t3 order by b desc, a;
243 }
244 } {4 1 2 3 5 6}
245 do_test sort-5.4 {
246 execsql {
247 select a from t3 order by b desc, a desc;
248 }
249 } {4 1 6 5 3 2}
251 do_test sort-6.1 {
252 execsql {
253 create index i3 on t3(b,a);
254 select a from t3 order by b, a;
255 }
256 } {2 3 5 6 1 4}
257 do_test sort-6.2 {
258 execsql {
259 select a from t3 order by b, a desc;
260 }
261 } {6 5 3 2 1 4}
262 do_test sort-6.3 {
263 execsql {
264 select a from t3 order by b desc, a;
265 }
266 } {4 1 2 3 5 6}
267 do_test sort-6.4 {
268 execsql {
269 select a from t3 order by b desc, a desc;
270 }
271 } {4 1 6 5 3 2}
273 do_test sort-7.1 {
274 execsql {
275 CREATE TABLE t4(
276 a INTEGER,
277 b VARCHAR(30)
278 );
279 INSERT INTO t4 VALUES(1,1);
280 INSERT INTO t4 VALUES(2,2);
281 INSERT INTO t4 VALUES(11,11);
282 INSERT INTO t4 VALUES(12,12);
283 SELECT a FROM t4 ORDER BY 1;
284 }
285 } {1 2 11 12}
286 do_test sort-7.2 {
287 execsql {
288 SELECT b FROM t4 ORDER BY 1
289 }
290 } {1 11 12 2}
291 do_test sort-7.3 {
292 execsql {
293 CREATE VIEW v4 AS SELECT * FROM t4;
294 SELECT a FROM v4 ORDER BY 1;
295 }
296 } {1 2 11 12}
297 do_test sort-7.4 {
298 execsql {
299 SELECT b FROM v4 ORDER BY 1;
300 }
301 } {1 11 12 2}
302 do_test sort-7.5 {
303 execsql {
304 SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
305 }
306 } {1 2 11 12}
307 do_test sort-7.6 {
308 execsql {
309 SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
310 }
311 } {1 2 11 12}
312 do_test sort-7.7 {
313 execsql {
314 SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
315 }
316 } {1 2 11 12}
317 do_test sort-7.8 {
318 execsql {
319 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
320 }
321 } {1 11 12 2}
322 do_test sort-7.9 {
323 execsql {
324 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
325 }
326 } {1 2 11 12}
327 do_test sort-7.10 {
328 execsql {
329 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;
330 }
331 } {1 2 11 12}
332 do_test sort-7.11 {
333 execsql {
334 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
335 }
336 } {1 11 12 2}
337 do_test sort-7.12 {
338 execsql {
339 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE blob;
340 }
341 } {1 11 12 2}
342 do_test sort-7.13 {
343 execsql {
344 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE clob;
345 }
346 } {1 11 12 2}
347 do_test sort-7.14 {
348 execsql {
349 SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE varchar;
350 }
351 } {1 11 12 2}
353 # Ticket #297
354 #
355 do_test sort-8.1 {
356 execsql {
357 CREATE TABLE t5(a real, b text);
358 INSERT INTO t5 VALUES(100,'A1');
359 INSERT INTO t5 VALUES(100.0,'A2');
360 SELECT * FROM t5 ORDER BY a, b;
361 }
362 } {100 A1 100.0 A2}
364 finish_test