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