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 the library is able to correctly 16# handle file-format 3 (version 2.6.x) databases. 17# 18# $Id: format3.test,v 1.4 2003/12/23 02:17:35 drh Exp $ 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22 23# Create a bunch of data to sort against 24# 25do_test format3-1.0 { 26 set fd [open data.txt w] 27 puts $fd "1\tone\t0\tI\t3.141592653" 28 puts $fd "2\ttwo\t1\tII\t2.15" 29 puts $fd "3\tthree\t1\tIII\t4221.0" 30 puts $fd "4\tfour\t2\tIV\t-0.0013442" 31 puts $fd "5\tfive\t2\tV\t-11" 32 puts $fd "6\tsix\t2\tVI\t0.123" 33 puts $fd "7\tseven\t2\tVII\t123.0" 34 puts $fd "8\teight\t3\tVIII\t-1.6" 35 close $fd 36 execsql { 37 CREATE TABLE t1( 38 n int, 39 v varchar(10), 40 log int, 41 roman varchar(10), 42 flt real 43 ); 44 COPY t1 FROM 'data.txt' 45 } 46 file delete data.txt 47 db close 48 set ::bt [btree_open test.db] 49 btree_begin_transaction $::bt 50 set m [btree_get_meta $::bt] 51 set m [lreplace $m 2 2 3] 52 eval btree_update_meta $::bt $m 53 btree_commit $::bt 54 btree_close $::bt 55 sqlite db test.db 56 execsql {SELECT count(*) FROM t1} 57} {8} 58 59do_test format3-1.1 { 60 execsql {SELECT n FROM t1 ORDER BY n} 61} {1 2 3 4 5 6 7 8} 62do_test format3-1.1.1 { 63 execsql {SELECT n FROM t1 ORDER BY n ASC} 64} {1 2 3 4 5 6 7 8} 65do_test format3-1.1.1 { 66 execsql {SELECT ALL n FROM t1 ORDER BY n ASC} 67} {1 2 3 4 5 6 7 8} 68do_test format3-1.2 { 69 execsql {SELECT n FROM t1 ORDER BY n DESC} 70} {8 7 6 5 4 3 2 1} 71do_test format3-1.3a { 72 execsql {SELECT v FROM t1 ORDER BY v} 73} {eight five four one seven six three two} 74do_test format3-1.3b { 75 execsql {SELECT n FROM t1 ORDER BY v} 76} {8 5 4 1 7 6 3 2} 77do_test format3-1.4 { 78 execsql {SELECT n FROM t1 ORDER BY v DESC} 79} {2 3 6 7 1 4 5 8} 80do_test format3-1.5 { 81 execsql {SELECT flt FROM t1 ORDER BY flt} 82} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0} 83do_test format3-1.6 { 84 execsql {SELECT flt FROM t1 ORDER BY flt DESC} 85} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11} 86do_test format3-1.7 { 87 execsql {SELECT roman FROM t1 ORDER BY roman} 88} {I II III IV V VI VII VIII} 89do_test format3-1.8 { 90 execsql {SELECT n FROM t1 ORDER BY log, flt} 91} {1 2 3 5 4 6 7 8} 92do_test format3-1.8.1 { 93 execsql {SELECT n FROM t1 ORDER BY log asc, flt} 94} {1 2 3 5 4 6 7 8} 95do_test format3-1.8.2 { 96 execsql {SELECT n FROM t1 ORDER BY log, flt ASC} 97} {1 2 3 5 4 6 7 8} 98do_test format3-1.8.3 { 99 execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc} 100} {1 2 3 5 4 6 7 8} 101do_test format3-1.9 { 102 execsql {SELECT n FROM t1 ORDER BY log, flt DESC} 103} {1 3 2 7 6 4 5 8} 104do_test format3-1.9.1 { 105 execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC} 106} {1 3 2 7 6 4 5 8} 107do_test format3-1.10 { 108 execsql {SELECT n FROM t1 ORDER BY log DESC, flt} 109} {8 5 4 6 7 2 3 1} 110do_test format3-1.11 { 111 execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} 112} {8 7 6 4 5 3 2 1} 113 114# These tests are designed to reach some hard-to-reach places 115# inside the string comparison routines. 116# 117# (Later) The sorting behavior changed in 2.7.0. But we will 118# keep these tests. You can never have too many test cases! 119# 120do_test format3-2.1.1 { 121 execsql { 122 UPDATE t1 SET v='x' || -flt; 123 UPDATE t1 SET v='x-2b' where v=='x-0.123'; 124 SELECT v FROM t1 ORDER BY v; 125 } 126} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} 127do_test format3-2.1.2 { 128 execsql { 129 SELECT v FROM t1 ORDER BY substr(v,2,999); 130 } 131} {x-4221 x-123 x-3.141592653 x-2.15 x0.0013442 x1.6 x11 x-2b} 132do_test format3-2.1.3 { 133 execsql { 134 SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; 135 } 136} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11} 137do_test format3-2.1.4 { 138 execsql { 139 SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; 140 } 141} {x-2b x11 x1.6 x0.0013442 x-2.15 x-3.141592653 x-123 x-4221} 142do_test format3-2.1.5 { 143 execsql { 144 SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; 145 } 146} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221} 147 148# This is a bug fix for 2.2.4. 149# Strings are normally mapped to upper-case for a caseless comparison. 150# But this can cause problems for characters in between 'Z' and 'a'. 151# 152do_test format3-3.1 { 153 execsql { 154 CREATE TABLE t2(a,b); 155 INSERT INTO t2 VALUES('AGLIENTU',1); 156 INSERT INTO t2 VALUES('AGLIE`',2); 157 INSERT INTO t2 VALUES('AGNA',3); 158 SELECT a, b FROM t2 ORDER BY a; 159 } 160} {AGLIENTU 1 AGLIE` 2 AGNA 3} 161do_test format3-3.2 { 162 execsql { 163 SELECT a, b FROM t2 ORDER BY a DESC; 164 } 165} {AGNA 3 AGLIE` 2 AGLIENTU 1} 166do_test format3-3.3 { 167 execsql { 168 DELETE FROM t2; 169 INSERT INTO t2 VALUES('aglientu',1); 170 INSERT INTO t2 VALUES('aglie`',2); 171 INSERT INTO t2 VALUES('agna',3); 172 SELECT a, b FROM t2 ORDER BY a; 173 } 174} {aglie` 2 aglientu 1 agna 3} 175do_test format3-3.4 { 176 execsql { 177 SELECT a, b FROM t2 ORDER BY a DESC; 178 } 179} {agna 3 aglientu 1 aglie` 2} 180 181# Version 2.7.0 testing. 182# 183do_test format3-4.1 { 184 execsql { 185 INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); 186 INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); 187 INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); 188 INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); 189 SELECT n FROM t1 ORDER BY n; 190 } 191} {1 2 3 4 5 6 7 8 9 10 11 12} 192do_test format3-4.2 { 193 execsql { 194 SELECT n||'' FROM t1 ORDER BY 1; 195 } 196} {1 2 3 4 5 6 7 8 9 10 11 12} 197do_test format3-4.3 { 198 execsql { 199 SELECT n+0 FROM t1 ORDER BY 1; 200 } 201} {1 2 3 4 5 6 7 8 9 10 11 12} 202do_test format3-4.4 { 203 execsql { 204 SELECT n||'' FROM t1 ORDER BY 1 DESC; 205 } 206} {12 11 10 9 8 7 6 5 4 3 2 1} 207do_test format3-4.5 { 208 execsql { 209 SELECT n+0 FROM t1 ORDER BY 1 DESC; 210 } 211} {12 11 10 9 8 7 6 5 4 3 2 1} 212do_test format3-4.6 { 213 execsql { 214 SELECT v FROM t1 ORDER BY 1; 215 } 216} {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} 217do_test format3-4.7 { 218 execsql { 219 SELECT v FROM t1 ORDER BY 1 DESC; 220 } 221} {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} 222do_test format3-4.8 { 223 execsql { 224 SELECT substr(v,2,99) FROM t1 ORDER BY 1; 225 } 226} {-4.0e9 -4221 -123 -3.141592653 -2.15 0.0013442 1.6 2.7 11 5.0e10 01234567890123456789 -2b} 227 228# Build some new test data, this time with indices. 229# 230do_test format3-5.0 { 231 execsql { 232 DROP TABLE t1; 233 CREATE TABLE t1(w int, x text, y blob); 234 DROP TABLE t2; 235 CREATE TABLE t2(p varchar(1), q clob, r real, s numeric(8)); 236 } 237 for {set i 1} {$i<=100} {incr i} { 238 set w $i 239 set x [expr {int(log($i)/log(2))}] 240 set y [expr {$i*$i + 2*$i + 1}] 241 execsql "INSERT INTO t1 VALUES($w,$x,$y)" 242 } 243 execsql { 244 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; 245 CREATE INDEX i1w ON t1(w); 246 CREATE INDEX i1xy ON t1(x,y); 247 CREATE INDEX i2p ON t2(p); 248 CREATE INDEX i2r ON t2(r); 249 CREATE INDEX i2qs ON t2(q, s); 250 } 251} {} 252 253# Do an SQL statement. Append the search count to the end of the result. 254# 255proc count sql { 256 set ::sqlite_search_count 0 257 return [concat [execsql $sql] $::sqlite_search_count] 258} 259 260# Verify that queries use an index. We are using the special variable 261# "sqlite_search_count" which tallys the number of executions of MoveTo 262# and Next operators in the VDBE. By verifing that the search count is 263# small we can be assured that indices are being used properly. 264# 265do_test format3-5.1 { 266 db close 267 sqlite db test.db 268 count {SELECT x, y FROM t1 WHERE w=10} 269} {3 121 3} 270do_test format3-5.2 { 271 count {SELECT x, y FROM t1 WHERE w=11} 272} {3 144 3} 273do_test format3-5.3 { 274 count {SELECT x, y FROM t1 WHERE 11=w} 275} {3 144 3} 276do_test format3-5.4 { 277 count {SELECT x, y FROM t1 WHERE 11=w AND x>2} 278} {3 144 3} 279do_test format3-5.5 { 280 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} 281} {3 144 3} 282do_test format3-5.6 { 283 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} 284} {3 144 3} 285do_test format3-5.7 { 286 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} 287} {3 144 3} 288do_test format3-5.8 { 289 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} 290} {3 144 3} 291do_test format3-5.9 { 292 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} 293} {3 144 3} 294do_test format3-5.10 { 295 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} 296} {3 121 3} 297do_test format3-5.11 { 298 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} 299} {3 100 3} 300 301# New for SQLite version 2.1: Verify that that inequality constraints 302# are used correctly. 303# 304do_test format3-5.12 { 305 count {SELECT w FROM t1 WHERE x=3 AND y<100} 306} {8 3} 307do_test format3-5.13 { 308 count {SELECT w FROM t1 WHERE x=3 AND 100>y} 309} {8 3} 310do_test format3-5.14 { 311 count {SELECT w FROM t1 WHERE 3=x AND y<100} 312} {8 3} 313do_test format3-5.15 { 314 count {SELECT w FROM t1 WHERE 3=x AND 100>y} 315} {8 3} 316do_test format3-5.16 { 317 count {SELECT w FROM t1 WHERE x=3 AND y<=100} 318} {8 9 5} 319do_test format3-5.17 { 320 count {SELECT w FROM t1 WHERE x=3 AND 100>=y} 321} {8 9 5} 322do_test format3-5.18 { 323 count {SELECT w FROM t1 WHERE x=3 AND y>225} 324} {15 3} 325do_test format3-5.19 { 326 count {SELECT w FROM t1 WHERE x=3 AND 225<y} 327} {15 3} 328do_test format3-5.20 { 329 count {SELECT w FROM t1 WHERE x=3 AND y>=225} 330} {14 15 5} 331do_test format3-5.21 { 332 count {SELECT w FROM t1 WHERE x=3 AND 225<=y} 333} {14 15 5} 334do_test format3-5.22 { 335 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} 336} {11 12 5} 337do_test format3-5.23 { 338 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} 339} {10 11 12 13 9} 340do_test format3-5.24 { 341 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y} 342} {11 12 5} 343do_test format3-5.25 { 344 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} 345} {10 11 12 13 9} 346 347# Need to work on optimizing the BETWEEN operator. 348# 349# do_test format3-5.26 { 350# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} 351# } {10 11 12 13 9} 352 353do_test format3-5.27 { 354 count {SELECT w FROM t1 WHERE x=3 AND y+1==122} 355} {10 17} 356do_test format3-5.28 { 357 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} 358} {10 99} 359do_test format3-5.29 { 360 count {SELECT w FROM t1 WHERE y==121} 361} {10 99} 362 363 364do_test format3-5.30 { 365 count {SELECT w FROM t1 WHERE w>97} 366} {98 99 100 6} 367do_test format3-5.31 { 368 count {SELECT w FROM t1 WHERE w>=97} 369} {97 98 99 100 8} 370do_test format3-5.33 { 371 count {SELECT w FROM t1 WHERE w==97} 372} {97 3} 373do_test format3-5.34 { 374 count {SELECT w FROM t1 WHERE w+1==98} 375} {97 99} 376do_test format3-5.35 { 377 count {SELECT w FROM t1 WHERE w<3} 378} {1 2 4} 379do_test format3-5.36 { 380 count {SELECT w FROM t1 WHERE w<=3} 381} {1 2 3 6} 382do_test format3-5.37 { 383 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} 384} {1 2 3 199} 385 386 387# Do the same kind of thing except use a join as the data source. 388# 389do_test format3-6.1 { 390 db close 391 sqlite db test.db 392 count { 393 SELECT w, p FROM t2, t1 394 WHERE x=q AND y=s AND r=8977 395 } 396} {34 67 6} 397do_test format3-6.2 { 398 count { 399 SELECT w, p FROM t2, t1 400 WHERE x=q AND s=y AND r=8977 401 } 402} {34 67 6} 403do_test format3-6.3 { 404 count { 405 SELECT w, p FROM t2, t1 406 WHERE x=q AND s=y AND r=8977 AND w>10 407 } 408} {34 67 6} 409do_test format3-6.4 { 410 count { 411 SELECT w, p FROM t2, t1 412 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 413 } 414} {34 67 6} 415do_test format3-6.5 { 416 count { 417 SELECT w, p FROM t2, t1 418 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 419 } 420} {34 67 6} 421do_test format3-6.6 { 422 count { 423 SELECT w, p FROM t2, t1 424 WHERE x=q AND p=77 AND s=y AND w>5 425 } 426} {24 77 6} 427do_test format3-6.7 { 428 count { 429 SELECT w, p FROM t1, t2 430 WHERE x=q AND p>77 AND s=y AND w=5 431 } 432} {5 96 6} 433 434# Lets do a 3-way join. 435# 436do_test format3-7.1 { 437 count { 438 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 439 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 440 } 441} {11 90 11 9} 442do_test format3-7.2 { 443 count { 444 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 445 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 446 } 447} {12 89 12 9} 448do_test format3-7.3 { 449 count { 450 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C 451 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y 452 } 453} {15 86 86 9} 454 455# Test to see that the special case of a constant WHERE clause is 456# handled. 457# 458do_test format3-8.1 { 459 count { 460 SELECT * FROM t1 WHERE 0 461 } 462} {0} 463do_test format3-8.2 { 464 count { 465 SELECT * FROM t1 WHERE 1 LIMIT 1 466 } 467} {1 0 4 1} 468do_test format3-8.3 { 469 execsql { 470 SELECT 99 WHERE 0 471 } 472} {} 473do_test format3-8.4 { 474 execsql { 475 SELECT 99 WHERE 1 476 } 477} {99} 478 479# Verify that IN operators in a WHERE clause are handled correctly. 480# 481do_test format3-9.1 { 482 count { 483 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; 484 } 485} {1 0 4 2 1 9 3 1 16 0} 486do_test format3-9.2 { 487 count { 488 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; 489 } 490} {1 0 4 2 1 9 3 1 16 199} 491do_test format3-9.3 { 492 count { 493 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; 494 } 495} {1 0 4 2 1 9 3 1 16 10} 496do_test format3-9.4 { 497 count { 498 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; 499 } 500} {1 0 4 2 1 9 3 1 16 199} 501do_test format3-9.5 { 502 count { 503 SELECT * FROM t1 WHERE rowid IN 504 (select rowid from t1 where rowid IN (-1,2,4)) 505 ORDER BY 1; 506 } 507} {2 1 9 4 2 25 1} 508do_test format3-9.6 { 509 count { 510 SELECT * FROM t1 WHERE rowid+0 IN 511 (select rowid from t1 where rowid IN (-1,2,4)) 512 ORDER BY 1; 513 } 514} {2 1 9 4 2 25 199} 515do_test format3-9.7 { 516 count { 517 SELECT * FROM t1 WHERE w IN 518 (select rowid from t1 where rowid IN (-1,2,4)) 519 ORDER BY 1; 520 } 521} {2 1 9 4 2 25 7} 522do_test format3-9.8 { 523 count { 524 SELECT * FROM t1 WHERE w+0 IN 525 (select rowid from t1 where rowid IN (-1,2,4)) 526 ORDER BY 1; 527 } 528} {2 1 9 4 2 25 199} 529do_test format3-9.9 { 530 count { 531 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; 532 } 533} {2 1 9 3 1 16 6} 534do_test format3-9.10 { 535 count { 536 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; 537 } 538} {2 1 9 3 1 16 199} 539do_test format3-9.11 { 540 count { 541 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; 542 } 543} {79 6 6400 89 6 8100 199} 544do_test format3-9.12 { 545 count { 546 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; 547 } 548} {79 6 6400 89 6 8100 74} 549do_test format3-9.13 { 550 count { 551 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; 552 } 553} {2 1 9 3 1 16 6} 554do_test format3-9.14 { 555 count { 556 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; 557 } 558} {2 1 9 6} 559 560# This procedure executes the SQL. Then it checks the generated program 561# for the SQL and appends a "nosort" to the result if the program contains the 562# SortCallback opcode. If the program does not contain the SortCallback 563# opcode it appends "sort" 564# 565proc cksort {sql} { 566 set data [execsql $sql] 567 set prog [execsql "EXPLAIN $sql"] 568 if {[regexp SortCallback $prog]} {set x sort} {set x nosort} 569 lappend data $x 570 return $data 571} 572# Check out the logic that attempts to implement the ORDER BY clause 573# using an index rather than by sorting. 574# 575do_test format3-10.1 { 576 execsql { 577 CREATE TABLE t3(a,b,c); 578 CREATE INDEX t3a ON t3(a); 579 CREATE INDEX t3bc ON t3(b,c); 580 CREATE INDEX t3acb ON t3(a,c,b); 581 INSERT INTO t3 SELECT w, 101-w, y FROM t1; 582 SELECT count(*), sum(a), sum(b), sum(c) FROM t3; 583 } 584} {100 5050 5050 348550} 585do_test format3-10.2 { 586 cksort { 587 SELECT * FROM t3 ORDER BY a LIMIT 3 588 } 589} {1 100 4 2 99 9 3 98 16 nosort} 590do_test format3-10.3 { 591 cksort { 592 SELECT * FROM t3 ORDER BY a+1 LIMIT 3 593 } 594} {1 100 4 2 99 9 3 98 16 sort} 595do_test format3-10.4 { 596 cksort { 597 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 598 } 599} {1 100 4 2 99 9 3 98 16 nosort} 600do_test format3-10.5 { 601 cksort { 602 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 603 } 604} {1 100 4 2 99 9 3 98 16 nosort} 605do_test format3-10.6 { 606 cksort { 607 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 608 } 609} {1 100 4 2 99 9 3 98 16 nosort} 610do_test format3-10.7 { 611 cksort { 612 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3 613 } 614} {1 100 4 2 99 9 3 98 16 sort} 615do_test format3-10.8 { 616 cksort { 617 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 618 } 619} {1 100 4 2 99 9 3 98 16 sort} 620do_test format3-10.9 { 621 cksort { 622 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 623 } 624} {1 100 4 nosort} 625do_test format3-10.10 { 626 cksort { 627 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 628 } 629} {1 100 4 nosort} 630do_test format3-10.11 { 631 cksort { 632 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 633 } 634} {1 100 4 nosort} 635do_test format3-10.12 { 636 cksort { 637 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 638 } 639} {1 100 4 nosort} 640do_test format3-10.13 { 641 cksort { 642 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 643 } 644} {100 1 10201 99 2 10000 98 3 9801 nosort} 645do_test format3-10.13.1 { 646 cksort { 647 SELECT * FROM t3 WHERE a>0 ORDER BY a+1 DESC LIMIT 3 648 } 649} {100 1 10201 99 2 10000 98 3 9801 sort} 650do_test format3-10.14 { 651 cksort { 652 SELECT * FROM t3 ORDER BY b LIMIT 3 653 } 654} {100 1 10201 99 2 10000 98 3 9801 nosort} 655do_test format3-10.15 { 656 cksort { 657 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 658 } 659} {1 0 2 1 3 1 nosort} 660do_test format3-10.16 { 661 cksort { 662 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 663 } 664} {1 0 2 1 3 1 sort} 665do_test format3-10.17 { 666 cksort { 667 SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; 668 } 669} {4 121 10201 sort} 670do_test format3-10.18 { 671 cksort { 672 SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; 673 } 674} {4 9 16 sort} 675do_test format3-10.19 { 676 cksort { 677 SELECT y FROM t1 ORDER BY w LIMIT 3; 678 } 679} {4 9 16 nosort} 680 681# Check that all comparisons are numeric. Similar tests in misc1.test 682# check the same comparisons on a format4+ database and find that some 683# are numeric and some are text. 684# 685do_test format3-11.1 { 686 execsql {SELECT '0'=='0.0'} 687} {1} 688do_test format3-11.2 { 689 execsql {SELECT '0'==0.0} 690} {1} 691do_test format3-11.3 { 692 execsql {SELECT '123456789012345678901'=='123456789012345678900'} 693} {1} 694do_test format3-11.4 { 695 execsql { 696 CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE); 697 INSERT INTO t6 VALUES('0','0.0'); 698 SELECT * FROM t6; 699 } 700} {0 0.0} 701do_test format3-11.5 { 702 execsql { 703 INSERT OR IGNORE INTO t6 VALUES(0.0,'x'); 704 SELECT * FROM t6; 705 } 706} {0 0.0} 707do_test format3-11.6 { 708 execsql { 709 INSERT OR IGNORE INTO t6 VALUES('y',0); 710 SELECT * FROM t6; 711 } 712} {0 0.0} 713do_test format3-11.7 { 714 execsql { 715 CREATE TABLE t7(x INTEGER, y TEXT, z); 716 INSERT INTO t7 VALUES(0,0,1); 717 INSERT INTO t7 VALUES(0.0,0,2); 718 INSERT INTO t7 VALUES(0,0.0,3); 719 INSERT INTO t7 VALUES(0.0,0.0,4); 720 SELECT DISTINCT x, y FROM t7 ORDER BY z; 721 } 722} {0 0} 723 724# Make sure attempts to attach a format 3 database fail. 725# 726do_test format3-12.1 { 727 file delete -force test2.db 728 sqlite db2 test2.db 729 catchsql { 730 CREATE TABLE t8(x,y); 731 ATTACH DATABASE 'test.db' AS format3; 732 } db2; 733} {1 {incompatible file format in auxiliary database: format3}} 734do_test format3-12.2 { 735 catchsql { 736 ATTACH DATABASE 'test2.db' AS test2; 737 } 738} {1 {cannot attach auxiliary databases to an older format master database}} 739db2 close 740 741finish_test 742