1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
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 $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Create a bunch of data to sort against
23#
24do_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}
48
49do_test sort-1.1 {
50  execsql {SELECT n FROM t1 ORDER BY n}
51} {1 2 3 4 5 6 7 8}
52do_test sort-1.1.1 {
53  execsql {SELECT n FROM t1 ORDER BY n ASC}
54} {1 2 3 4 5 6 7 8}
55do_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}
58do_test sort-1.2 {
59  execsql {SELECT n FROM t1 ORDER BY n DESC}
60} {8 7 6 5 4 3 2 1}
61do_test sort-1.3a {
62  execsql {SELECT v FROM t1 ORDER BY v}
63} {eight five four one seven six three two}
64do_test sort-1.3b {
65  execsql {SELECT n FROM t1 ORDER BY v}
66} {8 5 4 1 7 6 3 2}
67do_test sort-1.4 {
68  execsql {SELECT n FROM t1 ORDER BY v DESC}
69} {2 3 6 7 1 4 5 8}
70do_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}
73do_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}
76do_test sort-1.7 {
77  execsql {SELECT roman FROM t1 ORDER BY roman}
78} {I II III IV V VI VII VIII}
79do_test sort-1.8 {
80  execsql {SELECT n FROM t1 ORDER BY log, flt}
81} {1 2 3 5 4 6 7 8}
82do_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}
85do_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}
88do_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}
91do_test sort-1.9 {
92  execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
93} {1 3 2 7 6 4 5 8}
94do_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}
97do_test sort-1.10 {
98  execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
99} {8 5 4 6 7 2 3 1}
100do_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}
103
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#
110do_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}
117do_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}
122do_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}
127do_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}
132do_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}
137
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#
142do_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}
151do_test sort-3.2 {
152  execsql {
153    SELECT a, b FROM t2 ORDER BY a DESC;
154  }
155} {AGNA 3 AGLIE` 2 AGLIENTU 1}
156do_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}
165do_test sort-3.4 {
166  execsql {
167    SELECT a, b FROM t2 ORDER BY a DESC;
168  }
169} {agna 3 aglientu 1 aglie` 2}
170
171# Version 2.7.0 testing.
172#
173do_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}
182do_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}
187do_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}
192do_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}
197do_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}
202do_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}
207do_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}
212do_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}
222
223do_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}
235do_test sort-5.2 {
236  execsql {
237    select a from t3 order by b, a desc;
238  }
239} {6 5 3 2 1 4}
240do_test sort-5.3 {
241  execsql {
242    select a from t3 order by b desc, a;
243  }
244} {4 1 2 3 5 6}
245do_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}
250
251do_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}
257do_test sort-6.2 {
258  execsql {
259    select a from t3 order by b, a desc;
260  }
261} {6 5 3 2 1 4}
262do_test sort-6.3 {
263  execsql {
264    select a from t3 order by b desc, a;
265  }
266} {4 1 2 3 5 6}
267do_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}
272
273do_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}
286do_test sort-7.2 {
287  execsql {
288    SELECT b FROM t4 ORDER BY 1
289  }
290} {1 11 12 2}
291do_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}
297do_test sort-7.4 {
298  execsql {
299    SELECT b FROM v4 ORDER BY 1;
300  }
301} {1 11 12 2}
302do_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}
307do_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}
312do_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}
317do_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}
322do_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}
327do_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}
332do_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}
337do_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}
342do_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}
347do_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}
352
353# Ticket #297
354#
355do_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}
363
364finish_test
365