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