# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the the library is able to correctly # handle file-format 3 (version 2.6.x) databases. # # $Id: format3.test,v 1.1 2002/08/31 16:52:45 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a bunch of data to sort against # do_test format3-1.0 { set fd [open data.txt w] puts $fd "1\tone\t0\tI\t3.141592653" puts $fd "2\ttwo\t1\tII\t2.15" puts $fd "3\tthree\t1\tIII\t4221.0" puts $fd "4\tfour\t2\tIV\t-0.0013442" puts $fd "5\tfive\t2\tV\t-11" puts $fd "6\tsix\t2\tVI\t0.123" puts $fd "7\tseven\t2\tVII\t123.0" puts $fd "8\teight\t3\tVIII\t-1.6" close $fd execsql { CREATE TABLE t1( n int, v varchar(10), log int, roman varchar(10), flt real ); COPY t1 FROM 'data.txt' } file delete data.txt db close set ::bt [btree_open test.db] btree_begin_transaction $::bt set m [btree_get_meta $::bt] set m [lreplace $m 2 2 3] eval btree_update_meta $::bt $m btree_commit $::bt btree_close $::bt sqlite db test.db execsql {SELECT count(*) FROM t1} } {8} do_test format3-1.1 { execsql {SELECT n FROM t1 ORDER BY n} } {1 2 3 4 5 6 7 8} do_test format3-1.1.1 { execsql {SELECT n FROM t1 ORDER BY n ASC} } {1 2 3 4 5 6 7 8} do_test format3-1.1.1 { execsql {SELECT ALL n FROM t1 ORDER BY n ASC} } {1 2 3 4 5 6 7 8} do_test format3-1.2 { execsql {SELECT n FROM t1 ORDER BY n DESC} } {8 7 6 5 4 3 2 1} do_test format3-1.3a { execsql {SELECT v FROM t1 ORDER BY v} } {eight five four one seven six three two} do_test format3-1.3b { execsql {SELECT n FROM t1 ORDER BY v} } {8 5 4 1 7 6 3 2} do_test format3-1.4 { execsql {SELECT n FROM t1 ORDER BY v DESC} } {2 3 6 7 1 4 5 8} do_test format3-1.5 { execsql {SELECT flt FROM t1 ORDER BY flt} } {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0} do_test format3-1.6 { execsql {SELECT flt FROM t1 ORDER BY flt DESC} } {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11} do_test format3-1.7 { execsql {SELECT roman FROM t1 ORDER BY roman} } {I II III IV V VI VII VIII} do_test format3-1.8 { execsql {SELECT n FROM t1 ORDER BY log, flt} } {1 2 3 5 4 6 7 8} do_test format3-1.8.1 { execsql {SELECT n FROM t1 ORDER BY log asc, flt} } {1 2 3 5 4 6 7 8} do_test format3-1.8.2 { execsql {SELECT n FROM t1 ORDER BY log, flt ASC} } {1 2 3 5 4 6 7 8} do_test format3-1.8.3 { execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc} } {1 2 3 5 4 6 7 8} do_test format3-1.9 { execsql {SELECT n FROM t1 ORDER BY log, flt DESC} } {1 3 2 7 6 4 5 8} do_test format3-1.9.1 { execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC} } {1 3 2 7 6 4 5 8} do_test format3-1.10 { execsql {SELECT n FROM t1 ORDER BY log DESC, flt} } {8 5 4 6 7 2 3 1} do_test format3-1.11 { execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC} } {8 7 6 4 5 3 2 1} # These tests are designed to reach some hard-to-reach places # inside the string comparison routines. # # (Later) The sorting behavior changed in 2.7.0. But we will # keep these tests. You can never have too many test cases! # do_test format3-2.1.1 { execsql { UPDATE t1 SET v='x' || -flt; UPDATE t1 SET v='x-2b' where v=='x-0.123'; SELECT v FROM t1 ORDER BY v; } } {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11} do_test format3-2.1.2 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999); } } {x-4221 x-123 x-3.141592653 x-2.15 x0.0013442 x1.6 x11 x-2b} do_test format3-2.1.3 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0; } } {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11} do_test format3-2.1.4 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999) DESC; } } {x-2b x11 x1.6 x0.0013442 x-2.15 x-3.141592653 x-123 x-4221} do_test format3-2.1.5 { execsql { SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC; } } {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221} # This is a bug fix for 2.2.4. # Strings are normally mapped to upper-case for a caseless comparison. # But this can cause problems for characters in between 'Z' and 'a'. # do_test format3-3.1 { execsql { CREATE TABLE t2(a,b); INSERT INTO t2 VALUES('AGLIENTU',1); INSERT INTO t2 VALUES('AGLIE`',2); INSERT INTO t2 VALUES('AGNA',3); SELECT a, b FROM t2 ORDER BY a; } } {AGLIENTU 1 AGLIE` 2 AGNA 3} do_test format3-3.2 { execsql { SELECT a, b FROM t2 ORDER BY a DESC; } } {AGNA 3 AGLIE` 2 AGLIENTU 1} do_test format3-3.3 { execsql { DELETE FROM t2; INSERT INTO t2 VALUES('aglientu',1); INSERT INTO t2 VALUES('aglie`',2); INSERT INTO t2 VALUES('agna',3); SELECT a, b FROM t2 ORDER BY a; } } {aglie` 2 aglientu 1 agna 3} do_test format3-3.4 { execsql { SELECT a, b FROM t2 ORDER BY a DESC; } } {agna 3 aglientu 1 aglie` 2} # Version 2.7.0 testing. # do_test format3-4.1 { execsql { INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5); INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5); INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4); INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3); SELECT n FROM t1 ORDER BY n; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test format3-4.2 { execsql { SELECT n||'' FROM t1 ORDER BY 1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test format3-4.3 { execsql { SELECT n+0 FROM t1 ORDER BY 1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test format3-4.4 { execsql { SELECT n||'' FROM t1 ORDER BY 1 DESC; } } {12 11 10 9 8 7 6 5 4 3 2 1} do_test format3-4.5 { execsql { SELECT n+0 FROM t1 ORDER BY 1 DESC; } } {12 11 10 9 8 7 6 5 4 3 2 1} do_test format3-4.6 { execsql { SELECT v FROM t1 ORDER BY 1; } } {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} do_test format3-4.7 { execsql { SELECT v FROM t1 ORDER BY 1 DESC; } } {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} do_test format3-4.8 { execsql { SELECT substr(v,2,99) FROM t1 ORDER BY 1; } } {-4.0e9 -4221 -123 -3.141592653 -2.15 0.0013442 1.6 2.7 11 5.0e10 01234567890123456789 -2b} # Build some new test data, this time with indices. # do_test format3-5.0 { execsql { DROP TABLE t1; CREATE TABLE t1(w int, x text, y blob); DROP TABLE t2; CREATE TABLE t2(p varchar(1), q clob, r real, s numeric(8)); } for {set i 1} {$i<=100} {incr i} { set w $i set x [expr {int(log($i)/log(2))}] set y [expr {$i*$i + 2*$i + 1}] execsql "INSERT INTO t1 VALUES($w,$x,$y)" } execsql { INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; CREATE INDEX i1w ON t1(w); CREATE INDEX i1xy ON t1(x,y); CREATE INDEX i2p ON t2(p); CREATE INDEX i2r ON t2(r); CREATE INDEX i2qs ON t2(q, s); } } {} # Do an SQL statement. Append the search count to the end of the result. # proc count sql { set ::sqlite_search_count 0 return [concat [execsql $sql] $::sqlite_search_count] } # Verify that queries use an index. We are using the special variable # "sqlite_search_count" which tallys the number of executions of MoveTo # and Next operators in the VDBE. By verifing that the search count is # small we can be assured that indices are being used properly. # do_test format3-5.1 { db close sqlite db test.db count {SELECT x, y FROM t1 WHERE w=10} } {3 121 3} do_test format3-5.2 { count {SELECT x, y FROM t1 WHERE w=11} } {3 144 3} do_test format3-5.3 { count {SELECT x, y FROM t1 WHERE 11=w} } {3 144 3} do_test format3-5.4 { count {SELECT x, y FROM t1 WHERE 11=w AND x>2} } {3 144 3} do_test format3-5.5 { count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} } {3 144 3} do_test format3-5.6 { count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} } {3 144 3} do_test format3-5.7 { count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} } {3 144 3} do_test format3-5.8 { count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} } {3 144 3} do_test format3-5.9 { count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} } {3 144 3} do_test format3-5.10 { count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} } {3 121 3} do_test format3-5.11 { count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} } {3 100 3} # New for SQLite version 2.1: Verify that that inequality constraints # are used correctly. # do_test format3-5.12 { count {SELECT w FROM t1 WHERE x=3 AND y<100} } {8 3} do_test format3-5.13 { count {SELECT w FROM t1 WHERE x=3 AND 100>y} } {8 3} do_test format3-5.14 { count {SELECT w FROM t1 WHERE 3=x AND y<100} } {8 3} do_test format3-5.15 { count {SELECT w FROM t1 WHERE 3=x AND 100>y} } {8 3} do_test format3-5.16 { count {SELECT w FROM t1 WHERE x=3 AND y<=100} } {8 9 5} do_test format3-5.17 { count {SELECT w FROM t1 WHERE x=3 AND 100>=y} } {8 9 5} do_test format3-5.18 { count {SELECT w FROM t1 WHERE x=3 AND y>225} } {15 3} do_test format3-5.19 { count {SELECT w FROM t1 WHERE x=3 AND 225=225} } {14 15 5} do_test format3-5.21 { count {SELECT w FROM t1 WHERE x=3 AND 225<=y} } {14 15 5} do_test format3-5.22 { count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} } {11 12 5} do_test format3-5.23 { count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} } {10 11 12 13 9} do_test format3-5.24 { count {SELECT w FROM t1 WHERE x=3 AND 121y} } {11 12 5} do_test format3-5.25 { count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} } {10 11 12 13 9} # Need to work on optimizing the BETWEEN operator. # # do_test format3-5.26 { # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} # } {10 11 12 13 9} do_test format3-5.27 { count {SELECT w FROM t1 WHERE x=3 AND y+1==122} } {10 17} do_test format3-5.28 { count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} } {10 99} do_test format3-5.29 { count {SELECT w FROM t1 WHERE y==121} } {10 99} do_test format3-5.30 { count {SELECT w FROM t1 WHERE w>97} } {98 99 100 6} do_test format3-5.31 { count {SELECT w FROM t1 WHERE w>=97} } {97 98 99 100 8} do_test format3-5.33 { count {SELECT w FROM t1 WHERE w==97} } {97 3} do_test format3-5.34 { count {SELECT w FROM t1 WHERE w+1==98} } {97 99} do_test format3-5.35 { count {SELECT w FROM t1 WHERE w<3} } {1 2 4} do_test format3-5.36 { count {SELECT w FROM t1 WHERE w<=3} } {1 2 3 6} do_test format3-5.37 { count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} } {1 2 3 199} # Do the same kind of thing except use a join as the data source. # do_test format3-6.1 { db close sqlite db test.db count { SELECT w, p FROM t2, t1 WHERE x=q AND y=s AND r=8977 } } {34 67 6} do_test format3-6.2 { count { SELECT w, p FROM t2, t1 WHERE x=q AND s=y AND r=8977 } } {34 67 6} do_test format3-6.3 { count { SELECT w, p FROM t2, t1 WHERE x=q AND s=y AND r=8977 AND w>10 } } {34 67 6} do_test format3-6.4 { count { SELECT w, p FROM t2, t1 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 } } {34 67 6} do_test format3-6.5 { count { SELECT w, p FROM t2, t1 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 } } {34 67 6} do_test format3-6.6 { count { SELECT w, p FROM t2, t1 WHERE x=q AND p=77 AND s=y AND w>5 } } {24 77 6} do_test format3-6.7 { count { SELECT w, p FROM t1, t2 WHERE x=q AND p>77 AND s=y AND w=5 } } {5 96 6} # Lets do a 3-way join. # do_test format3-7.1 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 } } {11 90 11 9} do_test format3-7.2 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 } } {12 89 12 9} do_test format3-7.3 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y } } {15 86 86 9} # Test to see that the special case of a constant WHERE clause is # handled. # do_test format3-8.1 { count { SELECT * FROM t1 WHERE 0 } } {0} do_test format3-8.2 { count { SELECT * FROM t1 WHERE 1 LIMIT 1 } } {1 0 4 1} do_test format3-8.3 { execsql { SELECT 99 WHERE 0 } } {} do_test format3-8.4 { execsql { SELECT 99 WHERE 1 } } {99} # Verify that IN operators in a WHERE clause are handled correctly. # do_test format3-9.1 { count { SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 0} do_test format3-9.2 { count { SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 199} do_test format3-9.3 { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 10} do_test format3-9.4 { count { SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 199} do_test format3-9.5 { count { SELECT * FROM t1 WHERE rowid IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 1} do_test format3-9.6 { count { SELECT * FROM t1 WHERE rowid+0 IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 199} do_test format3-9.7 { count { SELECT * FROM t1 WHERE w IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 7} do_test format3-9.8 { count { SELECT * FROM t1 WHERE w+0 IN (select rowid from t1 where rowid IN (-1,2,4)) ORDER BY 1; } } {2 1 9 4 2 25 199} do_test format3-9.9 { count { SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test format3-9.10 { count { SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 199} do_test format3-9.11 { count { SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 199} do_test format3-9.12 { count { SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 74} do_test format3-9.13 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test format3-9.14 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; } } {2 1 9 6} # This procedure executes the SQL. Then it checks the generated program # for the SQL and appends a "nosort" to the result if the program contains the # SortCallback opcode. If the program does not contain the SortCallback # opcode it appends "sort" # proc cksort {sql} { set data [execsql $sql] set prog [execsql "EXPLAIN $sql"] if {[regexp SortCallback $prog]} {set x sort} {set x nosort} lappend data $x return $data } # Check out the logic that attempts to implement the ORDER BY clause # using an index rather than by sorting. # do_test format3-10.1 { execsql { CREATE TABLE t3(a,b,c); CREATE INDEX t3a ON t3(a); CREATE INDEX t3bc ON t3(b,c); CREATE INDEX t3acb ON t3(a,c,b); INSERT INTO t3 SELECT w, 101-w, y FROM t1; SELECT count(*), sum(a), sum(b), sum(c) FROM t3; } } {100 5050 5050 348550} do_test format3-10.2 { cksort { SELECT * FROM t3 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test format3-10.3 { cksort { SELECT * FROM t3 ORDER BY a+1 LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test format3-10.4 { cksort { SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test format3-10.5 { cksort { SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test format3-10.6 { cksort { SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 nosort} do_test format3-10.7 { cksort { SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test format3-10.8 { cksort { SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 } } {1 100 4 2 99 9 3 98 16 sort} do_test format3-10.9 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 } } {1 100 4 nosort} do_test format3-10.10 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 } } {1 100 4 nosort} do_test format3-10.11 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 } } {1 100 4 nosort} do_test format3-10.12 { cksort { SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 } } {1 100 4 nosort} do_test format3-10.13 { cksort { SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 } } {100 1 10201 99 2 10000 98 3 9801 sort} do_test format3-10.14 { cksort { SELECT * FROM t3 ORDER BY b LIMIT 3 } } {100 1 10201 99 2 10000 98 3 9801 nosort} do_test format3-10.15 { cksort { SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 } } {1 0 2 1 3 1 nosort} do_test format3-10.16 { cksort { SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 } } {1 0 2 1 3 1 sort} do_test format3-10.17 { cksort { SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; } } {4 121 10201 sort} do_test format3-10.18 { cksort { SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; } } {4 9 16 sort} do_test format3-10.19 { cksort { SELECT y FROM t1 ORDER BY w LIMIT 3; } } {4 9 16 nosort} # Check that all comparisons are numeric. Similar tests in misc1.test # check the same comparisons on a format4+ database and find that some # are numeric and some are text. # do_test format3-11.1 { execsql {SELECT '0'=='0.0'} } {1} do_test format3-11.2 { execsql {SELECT '0'==0.0} } {1} do_test format3-11.3 { execsql {SELECT '12345678901234567890'=='12345678901234567891'} } {1} do_test format3-11.4 { execsql { CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE); INSERT INTO t6 VALUES('0','0.0'); SELECT * FROM t6; } } {0 0.0} do_test format3-11.5 { execsql { INSERT OR IGNORE INTO t6 VALUES(0.0,'x'); SELECT * FROM t6; } } {0 0.0} do_test format3-11.6 { execsql { INSERT OR IGNORE INTO t6 VALUES('y',0); SELECT * FROM t6; } } {0 0.0} do_test format3-11.7 { execsql { CREATE TABLE t7(x INTEGER, y TEXT, z); INSERT INTO t7 VALUES(0,0,1); INSERT INTO t7 VALUES(0.0,0,2); INSERT INTO t7 VALUES(0,0.0,3); INSERT INTO t7 VALUES(0.0,0.0,4); SELECT DISTINCT x, y FROM t7 ORDER BY z; } } {0 0} finish_test