1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
# 2001 September 15
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.4 2002/01/22 14:11:30 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Create a bunch of data to sort against
#
do_test sort-1.0 {
set fd [open data.txt w]
puts $fd "1\tone\t0\tI\t3.141592653"
puts $fd "2\ttwo\t1\tII\t2.15"
puts $fd "3\tthree\t1\tIII\t4221.0"
puts $fd "4\tfour\t2\tIV\t-0.0013442"
puts $fd "5\tfive\t2\tV\t-11"
puts $fd "6\tsix\t2\tVI\t0.123"
puts $fd "7\tseven\t2\tVII\t123.0"
puts $fd "8\teight\t3\tVIII\t-1.6"
close $fd
execsql {
CREATE TABLE t1(
n int,
v varchar(10),
log int,
roman varchar(10),
flt real
);
COPY t1 FROM 'data.txt'
}
file delete data.txt
execsql {SELECT count(*) FROM t1}
} {8}
do_test sort-1.1 {
execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {
execsql {SELECT n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {
execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test sort-1.2 {
execsql {SELECT n FROM t1 ORDER BY n DESC}
} {8 7 6 5 4 3 2 1}
do_test sort-1.3a {
execsql {SELECT v FROM t1 ORDER BY v}
} {eight five four one seven six three two}
do_test sort-1.3b {
execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test sort-1.4 {
execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test sort-1.5 {
execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
do_test sort-1.6 {
execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {
execsql {SELECT n FROM t1 ORDER BY log asc, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.2 {
execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.3 {
execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
} {1 2 3 5 4 6 7 8}
do_test sort-1.9 {
execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test sort-1.9.1 {
execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test sort-1.10 {
execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
} {8 5 4 6 7 2 3 1}
do_test sort-1.11 {
execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
} {8 7 6 4 5 3 2 1}
# These tests are designed to reach some hard-to-reach places
# inside the string comparison routines.
#
do_test sort-2.1 {
execsql {
UPDATE t1 SET v='x' || -flt;
UPDATE t1 SET v='x-2b' where v=='x-0.123';
SELECT v FROM t1 ORDER BY v;
}
} {x-2b x-2.15 x-3.141592653 x-123 x-4221 x0.0013442 x1.6 x11}
do_test sort-2.2 {
execsql {
UPDATE t1 SET v='x-2_' where v=='x0.0013442';
SELECT v FROM t1 ORDER BY v;
}
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
execsql {
UPDATE t1 SET v='x ' || (-1.3+0.01*n);
SELECT v FROM t1 ORDER BY v;
}
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}
# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test sort-3.1 {
execsql {
CREATE TABLE t2(a,b);
INSERT INTO t2 VALUES('AGLIENTU',1);
INSERT INTO t2 VALUES('AGLIE`',2);
INSERT INTO t2 VALUES('AGNA',3);
SELECT a, b FROM t2 ORDER BY a;
}
} {AGLIENTU 1 AGLIE` 2 AGNA 3}
do_test sort-3.2 {
execsql {
SELECT a, b FROM t2 ORDER BY a DESC;
}
} {AGNA 3 AGLIE` 2 AGLIENTU 1}
do_test sort-3.3 {
execsql {
DELETE FROM t2;
INSERT INTO t2 VALUES('aglientu',1);
INSERT INTO t2 VALUES('aglie`',2);
INSERT INTO t2 VALUES('agna',3);
SELECT a, b FROM t2 ORDER BY a;
}
} {aglie` 2 aglientu 1 agna 3}
do_test sort-3.4 {
execsql {
SELECT a, b FROM t2 ORDER BY a DESC;
}
} {agna 3 aglientu 1 aglie` 2}
finish_test
|
