Annotation of embedaddon/sqlite3/test/sort.test, revision 1.1
1.1 ! misho 1: # 2001 September 15.
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # a legal notice, here is a blessing:
! 5: #
! 6: # May you do good and not evil.
! 7: # May you find forgiveness for yourself and forgive others.
! 8: # May you share freely, never taking more than you give.
! 9: #
! 10: #***********************************************************************
! 11: # This file implements regression tests for SQLite library. The
! 12: # focus of this file is testing the CREATE TABLE statement.
! 13: #
! 14: # $Id: sort.test,v 1.25 2005/11/14 22:29:06 drh Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Create a bunch of data to sort against
! 20: #
! 21: do_test sort-1.0 {
! 22: execsql {
! 23: CREATE TABLE t1(
! 24: n int,
! 25: v varchar(10),
! 26: log int,
! 27: roman varchar(10),
! 28: flt real
! 29: );
! 30: INSERT INTO t1 VALUES(1,'one',0,'I',3.141592653);
! 31: INSERT INTO t1 VALUES(2,'two',1,'II',2.15);
! 32: INSERT INTO t1 VALUES(3,'three',1,'III',4221.0);
! 33: INSERT INTO t1 VALUES(4,'four',2,'IV',-0.0013442);
! 34: INSERT INTO t1 VALUES(5,'five',2,'V',-11);
! 35: INSERT INTO t1 VALUES(6,'six',2,'VI',0.123);
! 36: INSERT INTO t1 VALUES(7,'seven',2,'VII',123.0);
! 37: INSERT INTO t1 VALUES(8,'eight',3,'VIII',-1.6);
! 38: }
! 39: execsql {SELECT count(*) FROM t1}
! 40: } {8}
! 41:
! 42: do_test sort-1.1 {
! 43: execsql {SELECT n FROM t1 ORDER BY n}
! 44: } {1 2 3 4 5 6 7 8}
! 45: do_test sort-1.1.1 {
! 46: execsql {SELECT n FROM t1 ORDER BY n ASC}
! 47: } {1 2 3 4 5 6 7 8}
! 48: do_test sort-1.1.1 {
! 49: execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
! 50: } {1 2 3 4 5 6 7 8}
! 51: do_test sort-1.2 {
! 52: execsql {SELECT n FROM t1 ORDER BY n DESC}
! 53: } {8 7 6 5 4 3 2 1}
! 54: do_test sort-1.3a {
! 55: execsql {SELECT v FROM t1 ORDER BY v}
! 56: } {eight five four one seven six three two}
! 57: do_test sort-1.3b {
! 58: execsql {SELECT n FROM t1 ORDER BY v}
! 59: } {8 5 4 1 7 6 3 2}
! 60: do_test sort-1.4 {
! 61: execsql {SELECT n FROM t1 ORDER BY v DESC}
! 62: } {2 3 6 7 1 4 5 8}
! 63: do_test sort-1.5 {
! 64: execsql {SELECT flt FROM t1 ORDER BY flt}
! 65: } {-11.0 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
! 66: do_test sort-1.6 {
! 67: execsql {SELECT flt FROM t1 ORDER BY flt DESC}
! 68: } {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11.0}
! 69: do_test sort-1.7 {
! 70: execsql {SELECT roman FROM t1 ORDER BY roman}
! 71: } {I II III IV V VI VII VIII}
! 72: do_test sort-1.8 {
! 73: execsql {SELECT n FROM t1 ORDER BY log, flt}
! 74: } {1 2 3 5 4 6 7 8}
! 75: do_test sort-1.8.1 {
! 76: execsql {SELECT n FROM t1 ORDER BY log asc, flt}
! 77: } {1 2 3 5 4 6 7 8}
! 78: do_test sort-1.8.2 {
! 79: execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
! 80: } {1 2 3 5 4 6 7 8}
! 81: do_test sort-1.8.3 {
! 82: execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
! 83: } {1 2 3 5 4 6 7 8}
! 84: do_test sort-1.9 {
! 85: execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
! 86: } {1 3 2 7 6 4 5 8}
! 87: do_test sort-1.9.1 {
! 88: execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
! 89: } {1 3 2 7 6 4 5 8}
! 90: do_test sort-1.10 {
! 91: execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
! 92: } {8 5 4 6 7 2 3 1}
! 93: do_test sort-1.11 {
! 94: execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
! 95: } {8 7 6 4 5 3 2 1}
! 96:
! 97: # These tests are designed to reach some hard-to-reach places
! 98: # inside the string comparison routines.
! 99: #
! 100: # (Later) The sorting behavior changed in 2.7.0. But we will
! 101: # keep these tests. You can never have too many test cases!
! 102: #
! 103: do_test sort-2.1.1 {
! 104: execsql {
! 105: UPDATE t1 SET v='x' || -flt;
! 106: UPDATE t1 SET v='x-2b' where v=='x-0.123';
! 107: SELECT v FROM t1 ORDER BY v;
! 108: }
! 109: } {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0}
! 110: do_test sort-2.1.2 {
! 111: execsql {
! 112: SELECT v FROM t1 ORDER BY substr(v,2,999);
! 113: }
! 114: } {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0}
! 115: do_test sort-2.1.3 {
! 116: execsql {
! 117: SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
! 118: }
! 119: } {x-4221.0 x-123.0 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11.0}
! 120: do_test sort-2.1.4 {
! 121: execsql {
! 122: SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
! 123: }
! 124: } {x11.0 x1.6 x0.0013442 x-4221.0 x-3.141592653 x-2b x-2.15 x-123.0}
! 125: do_test sort-2.1.5 {
! 126: execsql {
! 127: SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
! 128: }
! 129: } {x11.0 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123.0 x-4221.0}
! 130:
! 131: # This is a bug fix for 2.2.4.
! 132: # Strings are normally mapped to upper-case for a caseless comparison.
! 133: # But this can cause problems for characters in between 'Z' and 'a'.
! 134: #
! 135: do_test sort-3.1 {
! 136: execsql {
! 137: CREATE TABLE t2(a,b);
! 138: INSERT INTO t2 VALUES('AGLIENTU',1);
! 139: INSERT INTO t2 VALUES('AGLIE`',2);
! 140: INSERT INTO t2 VALUES('AGNA',3);
! 141: SELECT a, b FROM t2 ORDER BY a;
! 142: }
! 143: } {AGLIENTU 1 AGLIE` 2 AGNA 3}
! 144: do_test sort-3.2 {
! 145: execsql {
! 146: SELECT a, b FROM t2 ORDER BY a DESC;
! 147: }
! 148: } {AGNA 3 AGLIE` 2 AGLIENTU 1}
! 149: do_test sort-3.3 {
! 150: execsql {
! 151: DELETE FROM t2;
! 152: INSERT INTO t2 VALUES('aglientu',1);
! 153: INSERT INTO t2 VALUES('aglie`',2);
! 154: INSERT INTO t2 VALUES('agna',3);
! 155: SELECT a, b FROM t2 ORDER BY a;
! 156: }
! 157: } {aglie` 2 aglientu 1 agna 3}
! 158: do_test sort-3.4 {
! 159: execsql {
! 160: SELECT a, b FROM t2 ORDER BY a DESC;
! 161: }
! 162: } {agna 3 aglientu 1 aglie` 2}
! 163:
! 164: # Version 2.7.0 testing.
! 165: #
! 166: do_test sort-4.1 {
! 167: execsql {
! 168: INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5);
! 169: INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5);
! 170: INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4);
! 171: INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3);
! 172: SELECT n FROM t1 ORDER BY n;
! 173: }
! 174: } {1 2 3 4 5 6 7 8 9 10 11 12}
! 175: do_test sort-4.2 {
! 176: execsql {
! 177: SELECT n||'' FROM t1 ORDER BY 1;
! 178: }
! 179: } {1 10 11 12 2 3 4 5 6 7 8 9}
! 180: do_test sort-4.3 {
! 181: execsql {
! 182: SELECT n+0 FROM t1 ORDER BY 1;
! 183: }
! 184: } {1 2 3 4 5 6 7 8 9 10 11 12}
! 185: do_test sort-4.4 {
! 186: execsql {
! 187: SELECT n||'' FROM t1 ORDER BY 1 DESC;
! 188: }
! 189: } {9 8 7 6 5 4 3 2 12 11 10 1}
! 190: do_test sort-4.5 {
! 191: execsql {
! 192: SELECT n+0 FROM t1 ORDER BY 1 DESC;
! 193: }
! 194: } {12 11 10 9 8 7 6 5 4 3 2 1}
! 195: do_test sort-4.6 {
! 196: execsql {
! 197: SELECT v FROM t1 ORDER BY 1;
! 198: }
! 199: } {x-123.0 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221.0 x0.0013442 x01234567890123456789 x1.6 x11.0 x2.7 x5.0e10}
! 200: do_test sort-4.7 {
! 201: execsql {
! 202: SELECT v FROM t1 ORDER BY 1 DESC;
! 203: }
! 204: } {x5.0e10 x2.7 x11.0 x1.6 x01234567890123456789 x0.0013442 x-4221.0 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123.0}
! 205: do_test sort-4.8 {
! 206: execsql {
! 207: SELECT substr(v,2,99) FROM t1 ORDER BY 1;
! 208: }
! 209: } {-123.0 -2.15 -2b -3.141592653 -4.0e9 -4221.0 0.0013442 01234567890123456789 1.6 11.0 2.7 5.0e10}
! 210: #do_test sort-4.9 {
! 211: # execsql {
! 212: # SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1;
! 213: # }
! 214: #} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18}
! 215:
! 216: do_test sort-5.1 {
! 217: execsql {
! 218: create table t3(a,b);
! 219: insert into t3 values(5,NULL);
! 220: insert into t3 values(6,NULL);
! 221: insert into t3 values(3,NULL);
! 222: insert into t3 values(4,'cd');
! 223: insert into t3 values(1,'ab');
! 224: insert into t3 values(2,NULL);
! 225: select a from t3 order by b, a;
! 226: }
! 227: } {2 3 5 6 1 4}
! 228: do_test sort-5.2 {
! 229: execsql {
! 230: select a from t3 order by b, a desc;
! 231: }
! 232: } {6 5 3 2 1 4}
! 233: do_test sort-5.3 {
! 234: execsql {
! 235: select a from t3 order by b desc, a;
! 236: }
! 237: } {4 1 2 3 5 6}
! 238: do_test sort-5.4 {
! 239: execsql {
! 240: select a from t3 order by b desc, a desc;
! 241: }
! 242: } {4 1 6 5 3 2}
! 243:
! 244: do_test sort-6.1 {
! 245: execsql {
! 246: create index i3 on t3(b,a);
! 247: select a from t3 order by b, a;
! 248: }
! 249: } {2 3 5 6 1 4}
! 250: do_test sort-6.2 {
! 251: execsql {
! 252: select a from t3 order by b, a desc;
! 253: }
! 254: } {6 5 3 2 1 4}
! 255: do_test sort-6.3 {
! 256: execsql {
! 257: select a from t3 order by b desc, a;
! 258: }
! 259: } {4 1 2 3 5 6}
! 260: do_test sort-6.4 {
! 261: execsql {
! 262: select a from t3 order by b desc, a desc;
! 263: }
! 264: } {4 1 6 5 3 2}
! 265:
! 266: do_test sort-7.1 {
! 267: execsql {
! 268: CREATE TABLE t4(
! 269: a INTEGER,
! 270: b VARCHAR(30)
! 271: );
! 272: INSERT INTO t4 VALUES(1,1);
! 273: INSERT INTO t4 VALUES(2,2);
! 274: INSERT INTO t4 VALUES(11,11);
! 275: INSERT INTO t4 VALUES(12,12);
! 276: SELECT a FROM t4 ORDER BY 1;
! 277: }
! 278: } {1 2 11 12}
! 279: do_test sort-7.2 {
! 280: execsql {
! 281: SELECT b FROM t4 ORDER BY 1
! 282: }
! 283: } {1 11 12 2}
! 284:
! 285: # Omit tests sort-7.3 to sort-7.8 if view support was disabled at
! 286: # compilatation time.
! 287: ifcapable view {
! 288: do_test sort-7.3 {
! 289: execsql {
! 290: CREATE VIEW v4 AS SELECT * FROM t4;
! 291: SELECT a FROM v4 ORDER BY 1;
! 292: }
! 293: } {1 2 11 12}
! 294: do_test sort-7.4 {
! 295: execsql {
! 296: SELECT b FROM v4 ORDER BY 1;
! 297: }
! 298: } {1 11 12 2}
! 299:
! 300: ifcapable compound {
! 301: do_test sort-7.5 {
! 302: execsql {
! 303: SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
! 304: }
! 305: } {1 2 11 12}
! 306: do_test sort-7.6 {
! 307: execsql {
! 308: SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
! 309: }
! 310: } {1 2 11 12 1 11 12 2} ;# text from t4.b and numeric from v4.a
! 311: do_test sort-7.7 {
! 312: execsql {
! 313: SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
! 314: }
! 315: } {1 2 11 12 1 11 12 2} ;# numeric from t4.a and text from v4.b
! 316: do_test sort-7.8 {
! 317: execsql {
! 318: SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
! 319: }
! 320: } {1 11 12 2}
! 321: } ;# ifcapable compound
! 322: } ;# ifcapable view
! 323:
! 324: #### Version 3 works differently here:
! 325: #do_test sort-7.9 {
! 326: # execsql {
! 327: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
! 328: # }
! 329: #} {1 2 11 12}
! 330: #do_test sort-7.10 {
! 331: # execsql {
! 332: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;
! 333: # }
! 334: #} {1 2 11 12}
! 335: #do_test sort-7.11 {
! 336: # execsql {
! 337: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
! 338: # }
! 339: #} {1 11 12 2}
! 340: #do_test sort-7.12 {
! 341: # execsql {
! 342: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE blob;
! 343: # }
! 344: #} {1 11 12 2}
! 345: #do_test sort-7.13 {
! 346: # execsql {
! 347: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE clob;
! 348: # }
! 349: #} {1 11 12 2}
! 350: #do_test sort-7.14 {
! 351: # execsql {
! 352: # SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE varchar;
! 353: # }
! 354: #} {1 11 12 2}
! 355:
! 356: # Ticket #297
! 357: #
! 358: do_test sort-8.1 {
! 359: execsql {
! 360: CREATE TABLE t5(a real, b text);
! 361: INSERT INTO t5 VALUES(100,'A1');
! 362: INSERT INTO t5 VALUES(100.0,'A2');
! 363: SELECT * FROM t5 ORDER BY a, b;
! 364: }
! 365: } {100.0 A1 100.0 A2}
! 366:
! 367:
! 368: ifcapable {bloblit} {
! 369: # BLOBs should sort after TEXT
! 370: #
! 371: do_test sort-9.1 {
! 372: execsql {
! 373: CREATE TABLE t6(x, y);
! 374: INSERT INTO t6 VALUES(1,1);
! 375: INSERT INTO t6 VALUES(2,'1');
! 376: INSERT INTO t6 VALUES(3,x'31');
! 377: INSERT INTO t6 VALUES(4,NULL);
! 378: SELECT x FROM t6 ORDER BY y;
! 379: }
! 380: } {4 1 2 3}
! 381: do_test sort-9.2 {
! 382: execsql {
! 383: SELECT x FROM t6 ORDER BY y DESC;
! 384: }
! 385: } {3 2 1 4}
! 386: do_test sort-9.3 {
! 387: execsql {
! 388: SELECT x FROM t6 WHERE y<1
! 389: }
! 390: } {}
! 391: do_test sort-9.4 {
! 392: execsql {
! 393: SELECT x FROM t6 WHERE y<'1'
! 394: }
! 395: } {1}
! 396: do_test sort-9.5 {
! 397: execsql {
! 398: SELECT x FROM t6 WHERE y<x'31'
! 399: }
! 400: } {1 2}
! 401: do_test sort-9.6 {
! 402: execsql {
! 403: SELECT x FROM t6 WHERE y>1
! 404: }
! 405: } {2 3}
! 406: do_test sort-9.7 {
! 407: execsql {
! 408: SELECT x FROM t6 WHERE y>'1'
! 409: }
! 410: } {3}
! 411: } ;# endif bloblit
! 412:
! 413: # Ticket #1092 - ORDER BY on rowid fields.
! 414: do_test sort-10.1 {
! 415: execsql {
! 416: CREATE TABLE t7(c INTEGER PRIMARY KEY);
! 417: INSERT INTO t7 VALUES(1);
! 418: INSERT INTO t7 VALUES(2);
! 419: INSERT INTO t7 VALUES(3);
! 420: INSERT INTO t7 VALUES(4);
! 421: }
! 422: } {}
! 423: do_test sort-10.2 {
! 424: execsql {
! 425: SELECT c FROM t7 WHERE c<=3 ORDER BY c DESC;
! 426: }
! 427: } {3 2 1}
! 428: do_test sort-10.3 {
! 429: execsql {
! 430: SELECT c FROM t7 WHERE c<3 ORDER BY c DESC;
! 431: }
! 432: } {2 1}
! 433:
! 434: # ticket #1358. Just because one table in a join gives a unique
! 435: # result does not mean they all do. We cannot disable sorting unless
! 436: # all tables in the join give unique results.
! 437: #
! 438: do_test sort-11.1 {
! 439: execsql {
! 440: create table t8(a unique, b, c);
! 441: insert into t8 values(1,2,3);
! 442: insert into t8 values(2,3,4);
! 443: create table t9(x,y);
! 444: insert into t9 values(2,4);
! 445: insert into t9 values(2,3);
! 446: select y from t8, t9 where a=1 order by a, y;
! 447: }
! 448: } {3 4}
! 449:
! 450: # Trouble reported on the mailing list. Check for overly aggressive
! 451: # (which is to say, incorrect) optimization of order-by with a rowid
! 452: # in a join.
! 453: #
! 454: do_test sort-12.1 {
! 455: execsql {
! 456: create table a (id integer primary key);
! 457: create table b (id integer primary key, aId integer, text);
! 458: insert into a values (1);
! 459: insert into b values (2, 1, 'xxx');
! 460: insert into b values (1, 1, 'zzz');
! 461: insert into b values (3, 1, 'yyy');
! 462: select a.id, b.id, b.text from a join b on (a.id = b.aId)
! 463: order by a.id, b.text;
! 464: }
! 465: } {1 2 xxx 1 3 yyy 1 1 zzz}
! 466:
! 467: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to sort.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test