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# 12# This file implements regression tests for SQLite library. The 13# focus of this file is testing the sorter (code in vdbesort.c). 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Create a bunch of data to sort against 20# 21do_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 42do_test sort-1.1 { 43 execsql {SELECT n FROM t1 ORDER BY n} 44} {1 2 3 4 5 6 7 8} 45do_test sort-1.1.1 { 46 execsql {SELECT n FROM t1 ORDER BY n ASC} 47} {1 2 3 4 5 6 7 8} 48do_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} 51do_test sort-1.2 { 52 execsql {SELECT n FROM t1 ORDER BY n DESC} 53} {8 7 6 5 4 3 2 1} 54do_test sort-1.3a { 55 execsql {SELECT v FROM t1 ORDER BY v} 56} {eight five four one seven six three two} 57do_test sort-1.3b { 58 execsql {SELECT n FROM t1 ORDER BY v} 59} {8 5 4 1 7 6 3 2} 60do_test sort-1.4 { 61 execsql {SELECT n FROM t1 ORDER BY v DESC} 62} {2 3 6 7 1 4 5 8} 63do_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} 66do_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} 69do_test sort-1.7 { 70 execsql {SELECT roman FROM t1 ORDER BY roman} 71} {I II III IV V VI VII VIII} 72do_test sort-1.8 { 73 execsql {SELECT n FROM t1 ORDER BY log, flt} 74} {1 2 3 5 4 6 7 8} 75do_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} 78do_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} 81do_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} 84do_test sort-1.9 { 85 execsql {SELECT n FROM t1 ORDER BY log, flt DESC} 86} {1 3 2 7 6 4 5 8} 87do_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} 90do_test sort-1.10 { 91 execsql {SELECT n FROM t1 ORDER BY log DESC, flt} 92} {8 5 4 6 7 2 3 1} 93do_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# 103do_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} 110do_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} 115do_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} 120do_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} 125do_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# 135do_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} 144do_test sort-3.2 { 145 execsql { 146 SELECT a, b FROM t2 ORDER BY a DESC; 147 } 148} {AGNA 3 AGLIE` 2 AGLIENTU 1} 149do_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} 158do_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# 166do_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} 175do_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} 180do_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} 185do_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} 190do_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} 195do_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} 200do_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} 205do_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 216do_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} 228do_test sort-5.2 { 229 execsql { 230 select a from t3 order by b, a desc; 231 } 232} {6 5 3 2 1 4} 233do_test sort-5.3 { 234 execsql { 235 select a from t3 order by b desc, a; 236 } 237} {4 1 2 3 5 6} 238do_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 244do_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} 250do_test sort-6.2 { 251 execsql { 252 select a from t3 order by b, a desc; 253 } 254} {6 5 3 2 1 4} 255do_test sort-6.3 { 256 execsql { 257 select a from t3 order by b desc, a; 258 } 259} {4 1 2 3 5 6} 260do_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 266do_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} 279do_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. 287ifcapable view { 288do_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} 294do_test sort-7.4 { 295 execsql { 296 SELECT b FROM v4 ORDER BY 1; 297 } 298} {1 11 12 2} 299 300ifcapable compound { 301do_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} 306do_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 311do_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 316do_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# 358do_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 368ifcapable {bloblit} { 369# BLOBs should sort after TEXT 370# 371do_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} 381do_test sort-9.2 { 382 execsql { 383 SELECT x FROM t6 ORDER BY y DESC; 384 } 385} {3 2 1 4} 386do_test sort-9.3 { 387 execsql { 388 SELECT x FROM t6 WHERE y<1 389 } 390} {} 391do_test sort-9.4 { 392 execsql { 393 SELECT x FROM t6 WHERE y<'1' 394 } 395} {1} 396do_test sort-9.5 { 397 execsql { 398 SELECT x FROM t6 WHERE y<x'31' 399 } 400} {1 2} 401do_test sort-9.6 { 402 execsql { 403 SELECT x FROM t6 WHERE y>1 404 } 405} {2 3} 406do_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. 414do_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} {} 423do_test sort-10.2 { 424 execsql { 425 SELECT c FROM t7 WHERE c<=3 ORDER BY c DESC; 426 } 427} {3 2 1} 428do_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# 438do_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# 454do_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#------------------------------------------------------------------------- 468# Check that the sorter in vdbesort.c sorts in a stable fashion. 469# 470do_execsql_test sort-13.0 { 471 CREATE TABLE t10(a, b); 472} 473do_test sort-13.1 { 474 db transaction { 475 for {set i 0} {$i < 100000} {incr i} { 476 execsql { INSERT INTO t10 VALUES( $i/10, $i%10 ) } 477 } 478 } 479} {} 480do_execsql_test sort-13.2 { 481 SELECT a, b FROM t10 ORDER BY a; 482} [db eval {SELECT a, b FROM t10 ORDER BY a, b}] 483do_execsql_test sort-13.3 { 484 PRAGMA cache_size = 5; 485 SELECT a, b FROM t10 ORDER BY a; 486} [db eval {SELECT a, b FROM t10 ORDER BY a, b}] 487 488#------------------------------------------------------------------------- 489# Sort some large ( > 4KiB) records. 490# 491proc cksum {x} { 492 set i1 1 493 set i2 2 494 binary scan $x c* L 495 foreach {a b} $L { 496 set i1 [expr (($i2<<3) + $a) & 0x7FFFFFFF] 497 set i2 [expr (($i1<<3) + $b) & 0x7FFFFFFF] 498 } 499 list $i1 $i2 500} 501db func cksum cksum 502 503do_execsql_test sort-14.0 { 504 PRAGMA cache_size = 5; 505 CREATE TABLE t11(a, b); 506 INSERT INTO t11 VALUES(randomblob(5000), NULL); 507 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --2 508 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --3 509 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --4 510 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --5 511 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --6 512 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --7 513 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --8 514 INSERT INTO t11 SELECT randomblob(5000), NULL FROM t11; --9 515 UPDATE t11 SET b = cksum(a); 516} 517 518foreach {tn mmap_limit} { 519 1 0 520 2 1000000 521} { 522 do_test sort-14.$tn { 523 sqlite3_test_control SQLITE_TESTCTRL_SORTER_MMAP db $mmap_limit 524 set prev "" 525 db eval { SELECT * FROM t11 ORDER BY b } { 526 if {$b != [cksum $a]} {error "checksum failed"} 527 if {[string compare $b $prev] < 0} {error "sort failed"} 528 set prev $b 529 } 530 set {} {} 531 } {} 532} 533 534#------------------------------------------------------------------------- 535# 536foreach {tn mmap_limit nWorker tmpstore coremutex fakeheap} { 537 1 0 3 file true false 538 2 0 3 file true true 539 3 0 0 file true false 540 4 1000000 3 file true false 541 5 0 0 memory false true 542} { 543 db close 544 sqlite3_shutdown 545 sqlite3_config_worker_threads $nWorker 546 if {$coremutex} { 547 sqlite3_config multithread 548 } else { 549 sqlite3_config singlethread 550 } 551 sqlite3_initialize 552 sorter_test_fakeheap $fakeheap 553 554 reset_db 555 sqlite3_test_control SQLITE_TESTCTRL_SORTER_MMAP db $mmap_limit 556 execsql "PRAGMA temp_store = $tmpstore" 557 558 set ten [string repeat X 10300] 559 set one [string repeat y 200] 560 561 do_execsql_test 15.$tn.1 { 562 PRAGMA cache_size = 5; 563 WITH rr AS ( 564 SELECT 4, $ten UNION ALL 565 SELECT 2, $one UNION ALL 566 SELECT 1, $ten UNION ALL 567 SELECT 3, $one 568 ) 569 SELECT * FROM rr ORDER BY 1; 570 } [list 1 $ten 2 $one 3 $one 4 $ten] 571 572 do_execsql_test 15.$tn.2 { 573 CREATE TABLE t1(a); 574 INSERT INTO t1 VALUES(4); 575 INSERT INTO t1 VALUES(5); 576 INSERT INTO t1 VALUES(3); 577 INSERT INTO t1 VALUES(2); 578 INSERT INTO t1 VALUES(6); 579 INSERT INTO t1 VALUES(1); 580 CREATE INDEX i1 ON t1(a); 581 SELECT * FROM t1 ORDER BY a; 582 } {1 2 3 4 5 6} 583 584 do_execsql_test 15.$tn.3 { 585 PRAGMA cache_size = 5; 586 WITH rr AS ( 587 SELECT 4, $ten UNION ALL 588 SELECT 2, $one 589 ) 590 SELECT * FROM rr ORDER BY 1; 591 } [list 2 $one 4 $ten] 592 593 sorter_test_fakeheap 0 594} 595 596db close 597sqlite3_shutdown 598sqlite3_config_worker_threads 0 599set t(0) singlethread 600set t(1) multithread 601set t(2) serialized 602sqlite3_config $t($sqlite_options(threadsafe)) 603sqlite3_initialize 604 605finish_test 606 607