diff options
Diffstat (limited to 'test')
38 files changed, 867 insertions, 112 deletions
diff --git a/test/analyze6.test b/test/analyze6.test index 74b7ec798..eaa9d731b 100644 --- a/test/analyze6.test +++ b/test/analyze6.test @@ -61,14 +61,14 @@ do_test analyze6-1.0 { # do_test analyze6-1.1 { eqp {SELECT count(*) FROM ev, cat WHERE x=y} -} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} +} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} # The same plan is chosen regardless of the order of the tables in the # FROM clause. # do_test analyze6-1.2 { eqp {SELECT count(*) FROM cat, ev WHERE x=y} -} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} +} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}} # Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30 diff --git a/test/autovacuum.test b/test/autovacuum.test index 1aef18f33..bba40e301 100644 --- a/test/autovacuum.test +++ b/test/autovacuum.test @@ -114,7 +114,7 @@ foreach delete_order $delete_orders { } do_test autovacuum-1.$tn.($delete).3 { execsql { - select a from av1 + select a from av1 order by rowid } } $::tbl_data } diff --git a/test/backcompat.test b/test/backcompat.test index 509dfe530..dd40fed23 100644 --- a/test/backcompat.test +++ b/test/backcompat.test @@ -213,7 +213,9 @@ unset ::incompatible # do_allbackcompat_test { if {[code1 {sqlite3 -version}] >= "3.7.0" + && [code1 {set ::sqlite_options(wal)}] && [code2 {sqlite3 -version}] >= "3.7.0" + && [code2 {set ::sqlite_options(wal)}] } { do_test backcompat-2.1.1 { sql1 { diff --git a/test/collate4.test b/test/collate4.test index 12bc16ef2..6b3a1c7ae 100644 --- a/test/collate4.test +++ b/test/collate4.test @@ -94,7 +94,7 @@ do_test collate4-1.1.5 { cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} } {{} A B a b nosort} do_test collate4-1.1.6 { - cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} + cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid} } {{} a A b B sort} do_test collate4-1.1.7 { @@ -171,13 +171,13 @@ do_test collate4-1.1.21 { } } {} do_test collate4-1.1.22 { - cksort {SELECT a FROM collate4t4 ORDER BY a} + cksort {SELECT a FROM collate4t4 ORDER BY a, rowid} } {{} a A b B sort} do_test collate4-1.1.23 { - cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE} + cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid} } {{} a A b B sort} do_test collate4-1.1.24 { - cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT} + cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid} } {{} A B a b nosort} do_test collate4-1.1.25 { cksort {SELECT b FROM collate4t4 ORDER BY b} @@ -222,7 +222,7 @@ do_test collate4-1.2.4 { cksort {SELECT a FROM collate4t1 ORDER BY a, b} } {{} A a B b nosort} do_test collate4-1.2.5 { - cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} + cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid} } {{} a A b B sort} do_test collate4-1.2.6 { cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text} @@ -271,10 +271,10 @@ do_test collate4-1.2.14 { } } {} do_test collate4-1.2.15 { - cksort {SELECT a FROM collate4t3 ORDER BY a} + cksort {SELECT a FROM collate4t3 ORDER BY a, rowid} } {{} a A b B sort} do_test collate4-1.2.16 { - cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase} + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid} } {{} a A b B sort} do_test collate4-1.2.17 { cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text} @@ -364,7 +364,8 @@ do_test collate4-2.1.4 { CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); } count { - SELECT * FROM collate4t2, collate4t1 WHERE a = b; + SELECT * FROM collate4t2, collate4t1 WHERE a = b + ORDER BY collate4t2.rowid, collate4t1.rowid } } {A a A A 19} do_test collate4-2.1.5 { @@ -375,7 +376,8 @@ do_test collate4-2.1.5 { ifcapable subquery { do_test collate4-2.1.6 { count { - SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2); + SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) + ORDER BY rowid } } {a A 10} do_test collate4-2.1.7 { @@ -384,7 +386,8 @@ ifcapable subquery { CREATE INDEX collate4i1 ON collate4t1(a); } count { - SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2); + SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) + ORDER BY rowid } } {a A 6} do_test collate4-2.1.8 { @@ -398,7 +401,7 @@ ifcapable subquery { CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); } count { - SELECT a FROM collate4t1 WHERE a IN ('z', 'a'); + SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid; } } {a A 9} } diff --git a/test/collate5.test b/test/collate5.test index e5bea7a5a..5f8697ea5 100644 --- a/test/collate5.test +++ b/test/collate5.test @@ -221,7 +221,7 @@ do_test collate5-3.0 { execsql { SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1; } -} {a A a A b B b B n N} +} {/[aA] [aA] [aA] [aA] [bB] [bB] [bB] [bB] [nN] [nN]/} do_test collate5-3.1 { execsql { SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1; @@ -282,7 +282,7 @@ do_test collate5-4.2 { execsql { SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b; } -} {A 1.0 2 b 2 1 B 3 1} +} {/[aA] 1(.0)? 2 [bB] 2 1 [bB] 3 1/} do_test collate5-4.3 { execsql { DROP TABLE collate5t1; diff --git a/test/corruptD.test b/test/corruptD.test index 393d41ee3..2423cd428 100644 --- a/test/corruptD.test +++ b/test/corruptD.test @@ -107,12 +107,12 @@ proc restore_file {} { do_test corruptD-1.1.1 { incr_change_counter hexio_write test.db [expr 1024+1] FFFF - catchsql { SELECT * FROM t1 } + catchsql { SELECT * FROM t1 ORDER BY rowid } } {1 {database disk image is malformed}} do_test corruptD-1.1.2 { incr_change_counter hexio_write test.db [expr 1024+1] [hexio_render_int32 1021] - catchsql { SELECT * FROM t1 } + catchsql { SELECT * FROM t1 ORDER BY rowid } } {1 {database disk image is malformed}} #------------------------------------------------------------------------- diff --git a/test/corruptE.test b/test/corruptE.test index 507721d85..48292ab2e 100644 --- a/test/corruptE.test +++ b/test/corruptE.test @@ -49,7 +49,7 @@ do_test corruptE-1.1 { INSERT OR IGNORE INTO t1 SELECT x*17,y FROM t1; INSERT OR IGNORE INTO t1 SELECT x*19,y FROM t1; CREATE INDEX t1i1 ON t1(x); - CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0; + CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0 ORDER BY rowid; COMMIT; } } {} diff --git a/test/coveridxscan.test b/test/coveridxscan.test new file mode 100644 index 000000000..7b3c0b0be --- /dev/null +++ b/test/coveridxscan.test @@ -0,0 +1,93 @@ +# 2012 September 17 +# +# 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. +# +#*********************************************************************** +# +# Tests for the optimization which attempts to use a covering index +# for a full-table scan (under the theory that the index will be smaller +# and require less I/O and hence will run faster.) +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +set testprefix coveridxscan + +do_test 1.1 { + db eval { + CREATE TABLE t1(a,b,c); + INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1); + CREATE INDEX t1ab ON t1(a,b); + CREATE INDEX t1b ON t1(b); + SELECT a FROM t1; + } + # covering index used for the scan, hence values are increasing +} {3 4 5} + +do_test 1.2 { + db eval { + SELECT a, c FROM t1; + } + # There is no covering index, hence the values are in rowid order +} {5 3 4 2 3 1} + +do_test 1.3 { + db eval { + SELECT b FROM t1; + } + # Choice of two indices: use the one with fewest columns +} {2 4 8} + +do_test 2.1 { + optimization_control db cover-idx-scan 0 + db eval {SELECT a FROM t1} + # With the optimization turned off, output in rowid order +} {5 4 3} +do_test 2.2 { + db eval {SELECT a, c FROM t1} +} {5 3 4 2 3 1} +do_test 2.3 { + db eval {SELECT b FROM t1} +} {4 8 2} + +db close +sqlite3_shutdown +sqlite3_config_cis 0 +sqlite3 db test.db + +do_test 3.1 { + db eval {SELECT a FROM t1} + # With the optimization configured off, output in rowid order +} {5 4 3} +do_test 3.2 { + db eval {SELECT a, c FROM t1} +} {5 3 4 2 3 1} +do_test 3.3 { + db eval {SELECT b FROM t1} +} {4 8 2} + +db close +sqlite3_shutdown +sqlite3_config_cis 1 +sqlite3 db test.db + +# The CIS optimization is enabled again. Covering indices are once again +# used for all table scans. +do_test 4.1 { + db eval {SELECT a FROM t1} +} {3 4 5} +do_test 4.2 { + db eval {SELECT a, c FROM t1} +} {5 3 4 2 3 1} +do_test 4.3 { + db eval {SELECT b FROM t1} +} {2 4 8} + + +finish_test diff --git a/test/dbstatus2.test b/test/dbstatus2.test index b2ec15665..18bb0870b 100644 --- a/test/dbstatus2.test +++ b/test/dbstatus2.test @@ -85,10 +85,12 @@ do_test 2.3 { db_write db 1 } {0 4 0} do_test 2.4 { db_write db 0 } {0 0 0} do_test 2.5 { db_write db 1 } {0 0 0} -do_test 2.6 { - execsql { PRAGMA journal_mode = WAL } - db_write db 1 -} {0 1 0} +ifcapable wal { + do_test 2.6 { + execsql { PRAGMA journal_mode = WAL } + db_write db 1 + } {0 1 0} +} do_test 2.7 { execsql { INSERT INTO t1 VALUES(5, randomblob(600)) } db_write db diff --git a/test/distinct.test b/test/distinct.test index 3a3354456..0d32628ef 100644 --- a/test/distinct.test +++ b/test/distinct.test @@ -175,10 +175,26 @@ foreach {tn sql temptables res} { } do_execsql_test 2.A { - SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o; + SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid; } {a A a A} - - +do_test 3.0 { + db eval { + CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b)); + INSERT INTO t3 VALUES + (null, null, 1), + (null, null, 2), + (null, 3, 4), + (null, 3, 5), + (6, null, 7), + (6, null, 8); + SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b; + } +} {{} {} {} 3 6 {}} +do_test 3.1 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b; + }] +} {0} finish_test diff --git a/test/e_createtable.test b/test/e_createtable.test index 822182815..35f7330c4 100644 --- a/test/e_createtable.test +++ b/test/e_createtable.test @@ -1591,7 +1591,7 @@ foreach {tn tbl res ac data} { " $res do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac - do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data + do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data } catchsql COMMIT diff --git a/test/e_fkey.test b/test/e_fkey.test index 5b27e03b5..69e086864 100644 --- a/test/e_fkey.test +++ b/test/e_fkey.test @@ -2060,7 +2060,7 @@ do_test e_fkey-45.1 { do_test e_fkey-45.2 { execsql { DELETE FROM pA WHERE rowid = 3; - SELECT quote(x) FROM pA; + SELECT quote(x) FROM pA ORDER BY rowid; } } {X'0000' X'9999' X'1234'} do_test e_fkey-45.3 { @@ -2069,7 +2069,7 @@ do_test e_fkey-45.3 { do_test e_fkey-45.4 { execsql { UPDATE pA SET x = X'8765' WHERE rowid = 4; - SELECT quote(x) FROM pA; + SELECT quote(x) FROM pA ORDER BY rowid; } } {X'0000' X'9999' X'8765'} do_test e_fkey-45.5 { @@ -2325,7 +2325,7 @@ do_test e_fkey-51.1 { do_test e_fkey-51.2 { execsql { UPDATE parent SET x = 22; - SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child; + SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child; } } {22 21 23 xxx 22} do_test e_fkey-51.3 { @@ -2335,7 +2335,7 @@ do_test e_fkey-51.3 { INSERT INTO parent VALUES(-1); INSERT INTO child VALUES(-1); UPDATE parent SET x = 22; - SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child; + SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child; } } {22 23 21 xxx 23} diff --git a/test/e_select.test b/test/e_select.test index e5949af67..fb63d051d 100644 --- a/test/e_select.test +++ b/test/e_select.test @@ -1023,10 +1023,10 @@ do_execsql_test e_select-4.9.0 { # do_select_tests e_select-4.9 { 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { - 4,5 f 1 o 7,6 s 3,2 t + /#,# f 1 o #,# s #,# t/ } 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { - 1,4,3,2 10 5,7,6 18 + 1,2,3,4 10 5,6,7 18 } 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 4 1,5 2,6 3,7 @@ -1040,7 +1040,7 @@ do_select_tests e_select-4.9 { # values are considered equal. # do_select_tests e_select-4.10 { - 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} + 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} } @@ -1745,12 +1745,12 @@ do_select_tests e_select-8.4 { 1 2 7 1 2 8 1 4 93 1 5 -1 } 8 "SELECT z, x FROM d1 ORDER BY 2" { - 3 1 8 1 7 1 -20 1 - 93 1 -1 1 -1 2 93 2 + /# 1 # 1 # 1 # 1 + # 1 # 1 # 2 # 2/ } 9 "SELECT z, x FROM d1 ORDER BY 1" { - -20 1 -1 2 -1 1 3 1 - 7 1 8 1 93 2 93 1 + /-20 1 -1 # -1 # 3 1 + 7 1 8 1 93 # 93 #/ } } @@ -1766,10 +1766,10 @@ do_select_tests e_select-8.5 { 94 94 9 8 4 0 0 -19 } 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { - 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 + /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ } 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { - -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 + /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ } } diff --git a/test/eqp.test b/test/eqp.test index 0e663f0a3..454f2afbd 100644 --- a/test/eqp.test +++ b/test/eqp.test @@ -62,7 +62,7 @@ do_eqp_test 1.3 { do_eqp_test 1.4 { SELECT a FROM t1 ORDER BY +a } { - 0 0 0 {SCAN TABLE t1 (~1000000 rows)} + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 1.5 { @@ -166,7 +166,7 @@ det 2.3.2 "SELECT min(x) FROM t2" { 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} } det 2.3.3 "SELECT min(x), max(x) FROM t2" { - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} } det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { @@ -339,7 +339,7 @@ do_eqp_test 4.3.1 { SELECT x FROM t1 UNION SELECT x FROM t2 } { 1 0 0 {SCAN TABLE t1 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } @@ -347,7 +347,7 @@ do_eqp_test 4.3.2 { SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 } { 2 0 0 {SCAN TABLE t1 (~1000000 rows)} - 3 0 0 {SCAN TABLE t2 (~1000000 rows)} + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 4 0 0 {SCAN TABLE t1 (~1000000 rows)} 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} @@ -447,7 +447,7 @@ det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { det 5.9 { SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 } { - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} @@ -471,7 +471,7 @@ det 5.10 { # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)} - 0 1 1 {SCAN TABLE t1 (~1000000 rows)} + 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} } # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM @@ -479,8 +479,8 @@ det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # USING TEMP B-TREE (UNION) det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { - 1 0 0 {SCAN TABLE t1 (~1000000 rows)} - 2 0 0 {SCAN TABLE t2 (~1000000 rows)} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } diff --git a/test/full.test b/test/full.test new file mode 100644 index 000000000..a8fe37144 --- /dev/null +++ b/test/full.test @@ -0,0 +1,20 @@ +# 2012 September 12 +# +# 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 runs the "full" test suite. It is a peer of the quick.test +# and all.test scripts. +# + +set testdir [file dirname $argv0] +source $testdir/permutations.test + +run_test_suite full + +finish_test diff --git a/test/in5.test b/test/in5.test new file mode 100644 index 000000000..8a43b8d44 --- /dev/null +++ b/test/in5.test @@ -0,0 +1,138 @@ +# 2012 September 18 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_test in5-1.1 { + execsql { + CREATE TABLE t1x(x INTEGER PRIMARY KEY); + INSERT INTO t1x VALUES(1),(3),(5),(7),(9); + CREATE TABLE t1y(y INTEGER UNIQUE); + INSERT INTO t1y VALUES(2),(4),(6),(8); + CREATE TABLE t1z(z TEXT UNIQUE); + INSERT INTO t1z VALUES('a'),('c'),('e'),('g'); + CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d TEXT); + INSERT INTO t2 VALUES(1,2,'a','12a'),(1,2,'b','12b'), + (2,3,'g','23g'),(3,5,'c','35c'), + (4,6,'h','46h'),(5,6,'e','56e'); + CREATE TABLE t3x AS SELECT x FROM t1x; + CREATE TABLE t3y AS SELECT y FROM t1y; + CREATE TABLE t3z AS SELECT z FROM t1z; + SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY c; + } +} {12a 56e} +do_test in5-1.2 { + execsql { + SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; + } +} {23g} +do_test in5-1.3 { + execsql { + SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d; + } +} {12a 56e} + + +do_test in5-2.1 { + execsql { + CREATE INDEX t2abc ON t2(a,b,c); + SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; + } +} {12a 56e} +do_test in5-2.2 { + execsql { + SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; + } +} {23g} +do_test in5-2.3 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z + }] +} {0} +do_test in5-2.4 { + execsql { + SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d; + } +} {12a 56e} +do_test in5-2.5.1 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t3x AND b IN t1y AND c IN t1z + }] +} {1} +do_test in5-2.5.2 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t3y AND c IN t1z + }] +} {1} +do_test in5-2.5.3 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t3z + }] +} {1} + +do_test in5-3.1 { + execsql { + DROP INDEX t2abc; + CREATE INDEX t2ab ON t2(a,b); + SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; + } +} {12a 56e} +do_test in5-3.2 { + execsql { + SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; + } +} {23g} +do_test in5-3.3 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z + }] +} {0} + +do_test in5-4.1 { + execsql { + DROP INDEX t2ab; + CREATE INDEX t2abcd ON t2(a,b,c,d); + SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; + } +} {12a 56e} +do_test in5-4.2 { + execsql { + SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; + } +} {23g} +do_test in5-4.3 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z + }] +} {0} + + +do_test in5-5.1 { + execsql { + DROP INDEX t2abcd; + CREATE INDEX t2cbad ON t2(c,b,a,d); + SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; + } +} {12a 56e} +do_test in5-5.2 { + execsql { + SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; + } +} {23g} +do_test in5-5.3 { + regexp {OpenEphemeral} [db eval { + EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z + }] +} {0} + +finish_test diff --git a/test/incrblob.test b/test/incrblob.test index 1880128f8..7cc99dd98 100644 --- a/test/incrblob.test +++ b/test/incrblob.test @@ -437,7 +437,7 @@ if {[permutation] != "memsubsys1"} { } {} do_test incrblob-6.2 { execsql { - SELECT rowid FROM blobs + SELECT rowid FROM blobs ORDER BY rowid } } {1 2 3} do_test incrblob-6.3 { diff --git a/test/intpkey.test b/test/intpkey.test index 05b6cdf04..db3942128 100644 --- a/test/intpkey.test +++ b/test/intpkey.test @@ -376,7 +376,7 @@ do_test intpkey-5.1 { } {0 zero entry 0} do_test intpkey-5.2 { execsql { - SELECT rowid, a FROM t1 + SELECT rowid, a FROM t1 ORDER BY rowid } } {-4 -4 0 0 5 5 6 6 11 11} diff --git a/test/like.test b/test/like.test index 767efd582..80ba41858 100644 --- a/test/like.test +++ b/test/like.test @@ -406,7 +406,7 @@ do_test like-5.2 { do_test like-5.3 { execsql { CREATE TABLE t2(x TEXT COLLATE NOCASE); - INSERT INTO t2 SELECT * FROM t1; + INSERT INTO t2 SELECT * FROM t1 ORDER BY rowid; CREATE INDEX i2 ON t2(x COLLATE NOCASE); } set sqlite_like_count 0 @@ -662,8 +662,8 @@ ifcapable like_opt&&!icu { set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25' }] - regexp {INDEX i2} $res - } {0} + regexp {SCAN TABLE t2} $res + } {1} } do_test like-9.5.1 { set res [sqlite3_exec_hex db { diff --git a/test/lock.test b/test/lock.test index 22f359c1a..6ec85eea3 100644 --- a/test/lock.test +++ b/test/lock.test @@ -247,11 +247,34 @@ do_test lock-2.8 { execsql {UPDATE t1 SET a = 0 WHERE 0} catchsql {BEGIN EXCLUSIVE;} db2 } {1 {database is locked}} +do_test lock-2.8b { + db2 eval {PRAGMA busy_timeout} +} {400} do_test lock-2.9 { db2 timeout 0 execsql COMMIT } {} +do_test lock-2.9b { + db2 eval {PRAGMA busy_timeout} +} {0} integrity_check lock-2.10 +do_test lock-2.11 { + db2 eval {PRAGMA busy_timeout(400)} + execsql BEGIN + execsql {UPDATE t1 SET a = 0 WHERE 0} + catchsql {BEGIN EXCLUSIVE;} db2 +} {1 {database is locked}} +do_test lock-2.11b { + db2 eval {PRAGMA busy_timeout} +} {400} +do_test lock-2.12 { + db2 eval {PRAGMA busy_timeout(0)} + execsql COMMIT +} {} +do_test lock-2.12b { + db2 eval {PRAGMA busy_timeout} +} {0} +integrity_check lock-2.13 # Try to start two transactions in a row # diff --git a/test/orderby1.test b/test/orderby1.test new file mode 100644 index 000000000..400659b47 --- /dev/null +++ b/test/orderby1.test @@ -0,0 +1,426 @@ +# 2012 Sept 27 +# +# 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 that the optimizations that disable +# ORDER BY clauses when the natural order of a query is correct. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix orderby1 + +# Generate test data for a join. Verify that the join gets the +# correct answer. +# +do_test 1.0 { + db eval { + BEGIN; + CREATE TABLE album( + aid INTEGER PRIMARY KEY, + title TEXT UNIQUE NOT NULL + ); + CREATE TABLE track( + tid INTEGER PRIMARY KEY, + aid INTEGER NOT NULL REFERENCES album, + tn INTEGER NOT NULL, + name TEXT, + UNIQUE(aid, tn) + ); + INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); + INSERT INTO track VALUES + (NULL, 1, 1, 'one-a'), + (NULL, 2, 2, 'two-b'), + (NULL, 3, 3, 'three-c'), + (NULL, 1, 3, 'one-c'), + (NULL, 2, 1, 'two-a'), + (NULL, 3, 1, 'three-a'); + COMMIT; + } +} {} +do_test 1.1a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} + +# Verify that the ORDER BY clause is optimized out +# +do_test 1.1b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {~/ORDER BY/} ;# ORDER BY optimized out + +# The same query with ORDER BY clause optimization disabled via + operators +# should give exactly the same answer. +# +do_test 1.2a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {one-a one-c two-a two-b three-a three-c} + +# The output is sorted manually in this case. +# +do_test 1.2b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms + +# The same query with ORDER BY optimizations turned off via built-in test. +# +do_test 1.3a { + optimization_control db order-by-idx-join 0 + db cache flush + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} +do_test 1.3b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {/ORDER BY/} ;# separate sorting pass due to disabled optimization +optimization_control db all 1 +db cache flush + +# Reverse order sorts +# +do_test 1.4a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {three-a three-c two-a two-b one-a one-c} +do_test 1.4b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn + } +} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting +do_test 1.4c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC + + +do_test 1.5a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} +do_test 1.5b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting +do_test 1.5c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC + +do_test 1.6a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {three-c three-a two-b two-a one-c one-a} +do_test 1.6b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC + } +} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting +do_test 1.6c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {~/ORDER BY/} ;# ORDER BY optimized-out + + +# Reconstruct the test data to use indices rather than integer primary keys. +# +do_test 2.0 { + db eval { + BEGIN; + DROP TABLE album; + DROP TABLE track; + CREATE TABLE album( + aid INT PRIMARY KEY, + title TEXT NOT NULL + ); + CREATE INDEX album_i1 ON album(title, aid); + CREATE TABLE track( + aid INTEGER NOT NULL REFERENCES album, + tn INTEGER NOT NULL, + name TEXT, + UNIQUE(aid, tn) + ); + INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); + INSERT INTO track VALUES + (1, 1, 'one-a'), + (2, 2, 'two-b'), + (3, 3, 'three-c'), + (1, 3, 'one-c'), + (2, 1, 'two-a'), + (3, 1, 'three-a'); + COMMIT; + } +} {} +do_test 2.1a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} + +# Verify that the ORDER BY clause is optimized out +# +do_test 2.1b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {~/ORDER BY/} ;# ORDER BY optimized out + +# The same query with ORDER BY clause optimization disabled via + operators +# should give exactly the same answer. +# +do_test 2.2a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {one-a one-c two-a two-b three-a three-c} + +# The output is sorted manually in this case. +# +do_test 2.2b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms + +# The same query with ORDER BY optimizations turned off via built-in test. +# +do_test 2.3a { + optimization_control db order-by-idx-join 0 + db cache flush + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} +do_test 2.3b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {/ORDER BY/} ;# separate sorting pass due to disabled optimization +optimization_control db all 1 +db cache flush + +# Reverse order sorts +# +do_test 2.4a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {three-a three-c two-a two-b one-a one-c} +do_test 2.4b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn + } +} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting +do_test 2.4c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC + + +do_test 2.5a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} +do_test 2.5b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting +do_test 2.5c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to mixed ASC/DESC + +do_test 2.6a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {three-c three-a two-b two-a one-c one-a} +do_test 2.6b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC + } +} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting +do_test 2.6c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {~/ORDER BY/} ;# ORDER BY optimized-out + + +# Generate another test dataset, but this time using mixed ASC/DESC indices. +# +do_test 3.0 { + db eval { + BEGIN; + DROP TABLE album; + DROP TABLE track; + CREATE TABLE album( + aid INTEGER PRIMARY KEY, + title TEXT UNIQUE NOT NULL + ); + CREATE TABLE track( + tid INTEGER PRIMARY KEY, + aid INTEGER NOT NULL REFERENCES album, + tn INTEGER NOT NULL, + name TEXT, + UNIQUE(aid ASC, tn DESC) + ); + INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); + INSERT INTO track VALUES + (NULL, 1, 1, 'one-a'), + (NULL, 2, 2, 'two-b'), + (NULL, 3, 3, 'three-c'), + (NULL, 1, 3, 'one-c'), + (NULL, 2, 1, 'two-a'), + (NULL, 3, 1, 'three-a'); + COMMIT; + } +} {} +do_test 3.1a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} + +# Verify that the ORDER BY clause is optimized out +# +do_test 3.1b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {~/ORDER BY/} ;# ORDER BY optimized out + +# The same query with ORDER BY clause optimization disabled via + operators +# should give exactly the same answer. +# +do_test 3.2a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {one-c one-a two-b two-a three-c three-a} + +# The output is sorted manually in this case. +# +do_test 3.2b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms + +# The same query with ORDER BY optimizations turned off via built-in test. +# +do_test 3.3a { + optimization_control db order-by-idx-join 0 + db cache flush + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {one-c one-a two-b two-a three-c three-a} +do_test 3.3b { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to disabled optimization +optimization_control db all 1 +db cache flush + +# Without the mixed ASC/DESC on ORDER BY +# +do_test 3.4a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {one-a one-c two-a two-b three-a three-c} +do_test 3.4b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn + } +} {one-a one-c two-a two-b three-a three-c} ;# verify same order after sorting +do_test 3.4c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn + } +} {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC + + +do_test 3.5a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {three-c three-a two-b two-a one-c one-a} +do_test 3.5b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC + } +} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting +do_test 3.5c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC + } +} {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC + + +do_test 3.6a { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {three-a three-c two-a two-b one-a one-c} +do_test 3.6b { + db eval { + SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn + } +} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting +do_test 3.6c { + db eval { + EXPLAIN QUERY PLAN + SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn + } +} {~/ORDER BY/} ;# inverted ASC/DESC is optimized out + + +finish_test diff --git a/test/permutations.test b/test/permutations.test index 6bbdd9b83..18377e8aa 100644 --- a/test/permutations.test +++ b/test/permutations.test @@ -99,7 +99,7 @@ if {$::tcl_platform(platform)!="unix"} { set alltests [test_set $alltests -exclude { all.test async.test quick.test veryquick.test memleak.test permutations.test soak.test fts3.test - mallocAll.test rtree.test session.test + mallocAll.test rtree.test full.test session.test }] set allquicktests [test_set $alltests -exclude { diff --git a/test/shell1.test b/test/shell1.test index 47f9e41d0..5c49d9053 100644 --- a/test/shell1.test +++ b/test/shell1.test @@ -680,6 +680,15 @@ do_test shell1-3.26.4 { catchcmd "test.db" ".width 1 1" # this should be treated the same as a '1' width for col 1 and 2 } {0 {}} +do_test shell1-3.26.5 { + catchcmd "test.db" ".mode column\n.width 10 -10\nSELECT 'abcdefg', 123456;" + # this should be treated the same as a '1' width for col 1 and 2 +} {0 {abcdefg 123456}} +do_test shell1-3.26.6 { + catchcmd "test.db" ".mode column\n.width -10 10\nSELECT 'abcdefg', 123456;" + # this should be treated the same as a '1' width for col 1 and 2 +} {0 { abcdefg 123456 }} + # .timer ON|OFF Turn the CPU timer measurement on or off do_test shell1-3.27.1 { @@ -701,6 +710,10 @@ do_test shell1-3-28.1 { ".log stdout\nSELECT coalesce(sqlite_log(123,'hello'),'456');" } "0 {(123) hello\n456}" +do_test shell1-3-29.1 { + catchcmd "test.db" ".print this is a test" +} {0 {this is a test}} + # Test the output of the ".dump" command # do_test shell1-4.1 { diff --git a/test/spellfix.test b/test/spellfix.test index 245fee24e..afef981d2 100644 --- a/test/spellfix.test +++ b/test/spellfix.test @@ -68,7 +68,7 @@ do_test 1.1 { } {} foreach {tn word res} { - 1 raxpi* {rasping 5 rasped 5 raspberry 6 rasp 4 rasps 4} + 1 raxpi* {rasping 5 rasped 5 ragweed 5 raspberry 6 rasp 4} 2 ril* {rail 4 railed 4 railer 4 railers 4 railing 4} 3 rilis* {realism 6 realist 6 realistic 6 realistically 6 realists 6} 4 reail* {real 3 realest 3 realign 3 realigned 3 realigning 3} diff --git a/test/stat.test b/test/stat.test index 926d9b740..ac88f7acb 100644 --- a/test/stat.test +++ b/test/stat.test @@ -76,11 +76,16 @@ do_test stat-1.4 { do_execsql_test stat-2.1 { CREATE TABLE t3(a PRIMARY KEY, b); INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222)); - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; - INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; + INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + ORDER BY rowid; + INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + ORDER BY rowid; + INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + ORDER BY rowid; + INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + ORDER BY rowid; + INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3 + ORDER BY rowid; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name != 'sqlite_master'; } [list \ diff --git a/test/tclsqlite.test b/test/tclsqlite.test index 9245bd701..51eea9848 100644 --- a/test/tclsqlite.test +++ b/test/tclsqlite.test @@ -143,11 +143,11 @@ do_test tcl-1.21 { set v [catch {db total_changes xyz} msg] lappend v $msg } {1 {wrong # args: should be "db total_changes "}} -do_test tcl-1.20 { +do_test tcl-1.22 { set v [catch {db copy} msg] lappend v $msg } {1 {wrong # args: should be "db copy CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"}} -do_test tcl-1.21 { +do_test tcl-1.23 { set v [catch {sqlite3 db2 test.db -vfs nosuchvfs} msg] lappend v $msg } {1 {no such vfs: nosuchvfs}} diff --git a/test/tester.tcl b/test/tester.tcl index 237561ca5..8b2b4ec6a 100644 --- a/test/tester.tcl +++ b/test/tester.tcl @@ -31,6 +31,7 @@ # Test the capability of the SQLite version built into the interpreter to # determine if a specific test can be run: # +# capable EXPR # ifcapable EXPR # # Calulate checksums based on database contents: @@ -134,7 +135,7 @@ proc getFileRetries {} { # NOTE: Return the default number of retries for [file] operations. A # value of zero or less here means "disabled". # - return [expr {$::tcl_platform(platform) eq "windows" ? 10 : 0}] + return [expr {$::tcl_platform(platform) eq "windows" ? 50 : 0}] } return $::G(file-retries) } @@ -537,16 +538,19 @@ proc do_test {name cmd expected} { } else { if {[regexp {^~?/.*/$} $expected]} { if {[string index $expected 0]=="~"} { - set re [string range $expected 2 end-1] + set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]] set ok [expr {![regexp $re $result]}] } else { - set re [string range $expected 1 end-1] + set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]] set ok [regexp $re $result] } } else { set ok [expr {[string compare $result $expected]==0}] } if {!$ok} { + # if {![info exists ::testprefix] || $::testprefix eq ""} { + # error "no test prefix" + # } puts "\nExpected: \[$expected\]\n Got: \[$result\]" fail_test $name } else { @@ -1000,6 +1004,12 @@ proc fix_ifcapable_expr {expr} { return $ret } +# Returns non-zero if the capabilities are present; zero otherwise. +# +proc capable {expr} { + set e [fix_ifcapable_expr $expr]; return [expr ($e)] +} + # Evaluate a boolean expression of capabilities. If true, execute the # code. Omit the code if false. # diff --git a/test/tkt-385a5b56b9.test b/test/tkt-385a5b56b9.test index 614e82d0d..818486486 100644 --- a/test/tkt-385a5b56b9.test +++ b/test/tkt-385a5b56b9.test @@ -39,7 +39,7 @@ do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } { } do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } { - 0 0 0 {SCAN TABLE t2 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)} } do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } { @@ -51,4 +51,3 @@ do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } { } finish_test - diff --git a/test/tkt-5d863f876e.test b/test/tkt-5d863f876e.test index 0a9017de1..86024e300 100644 --- a/test/tkt-5d863f876e.test +++ b/test/tkt-5d863f876e.test @@ -17,6 +17,8 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl +set ::testprefix tkt-5d863f876e +ifcapable !wal {finish_test ; return } do_multiclient_test tn { do_test $tn.1 { diff --git a/test/tkt-78e04e52ea.test b/test/tkt-78e04e52ea.test index 9524d845e..a664ceb9e 100644 --- a/test/tkt-78e04e52ea.test +++ b/test/tkt-78e04e52ea.test @@ -44,7 +44,7 @@ do_test tkt-78e04-1.4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%'; } -} {0 0 0 {SCAN TABLE (~500000 rows)}} +} {0 0 0 {SCAN TABLE USING COVERING INDEX i1 (~500000 rows)}} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; diff --git a/test/tkt-80ba201079.test b/test/tkt-80ba201079.test index 0122e95f2..ea0799b56 100644 --- a/test/tkt-80ba201079.test +++ b/test/tkt-80ba201079.test @@ -17,7 +17,7 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl -set ::testprefix tkt-80ba2 +set ::testprefix tkt-80ba201079 do_test tkt-80ba2-100 { db eval { diff --git a/test/tkt-cbd054fa6b.test b/test/tkt-cbd054fa6b.test index 180acf56d..51e01991d 100644 --- a/test/tkt-cbd054fa6b.test +++ b/test/tkt-cbd054fa6b.test @@ -50,7 +50,7 @@ do_test tkt-cbd05-1.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x { A B C D E F G H I}} +} {/t1 t1_x .[ ABCDEFGHI]{10}./} do_test tkt-cbd05-2.1 { db eval { @@ -82,6 +82,6 @@ do_test tkt-cbd05-2.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x { A B C D E F G H I}} +} {/t1 t1_x .[ ABCDEFGHI]{10}./} finish_test diff --git a/test/triggerC.test b/test/triggerC.test index 12a5e4ac0..db37ab3b6 100644 --- a/test/triggerC.test +++ b/test/triggerC.test @@ -222,7 +222,7 @@ foreach {n tdefn rc} { execsql $tdefn catchsql { INSERT INTO t2 VALUES(10); - SELECT * FROM t2; + SELECT * FROM t2 ORDER BY rowid; } } $rc } @@ -547,7 +547,7 @@ foreach {n insert log} { eval concat [execsql " DELETE FROM log; $insert ; - SELECT * FROM log; + SELECT * FROM log ORDER BY rowid; "] } [join $log " "] } @@ -584,8 +584,8 @@ foreach {n dml t5g t5} { execsql " BEGIN; $dml ; - SELECT * FROM t5g; - SELECT * FROM t5; + SELECT * FROM t5g ORDER BY rowid; + SELECT * FROM t5 ORDER BY rowid; ROLLBACK; " } [concat $t5g $t5] @@ -611,8 +611,8 @@ foreach {n dml t5g t5} { execsql " BEGIN; $dml ; - SELECT * FROM t5g; - SELECT * FROM t5; + SELECT * FROM t5g ORDER BY rowid; + SELECT * FROM t5 ORDER BY rowid; ROLLBACK; " } [concat $t5g $t5] @@ -633,8 +633,8 @@ foreach {n dml t5g t5} { execsql " BEGIN; $dml ; - SELECT * FROM t5g; - SELECT * FROM t5; + SELECT * FROM t5g ORDER BY rowid; + SELECT * FROM t5 ORDER BY rowid; ROLLBACK; " } [concat $t5g $t5] diff --git a/test/unordered.test b/test/unordered.test index 6c7c2bb25..4aa8310f8 100644 --- a/test/unordered.test +++ b/test/unordered.test @@ -51,7 +51,7 @@ foreach idxmode {ordered unordered} { 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} 4 "SELECT max(a) FROM t1" {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} - {0 0 0 {SEARCH TABLE t1 (~1 rows)}} + {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}} 5 "SELECT group_concat(b) FROM t1 GROUP BY a" {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}} {0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} diff --git a/test/wal8.test b/test/wal8.test index 4b97de7ae..339953895 100644 --- a/test/wal8.test +++ b/test/wal8.test @@ -26,6 +26,7 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix wal8 +ifcapable !wal {finish_test ; return } db close forcedelete test.db test.db-wal diff --git a/test/where.test b/test/where.test index 3826a5f64..83fec2cf6 100644 --- a/test/where.test +++ b/test/where.test @@ -1098,24 +1098,24 @@ do_test where-14.3 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b } -} {1/1 1/4 4/1 4/4 nosort} +} {1/4 1/1 4/4 4/1 nosort} do_test where-14.4 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC } -} {1/1 1/4 4/1 4/4 nosort} +} {1/4 1/1 4/4 4/1 nosort} do_test where-14.5 { # This test case changed from "nosort" to "sort". See ticket 2a5629202f. cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b } -} {4/1 4/4 1/1 1/4 sort} +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/} do_test where-14.6 { # This test case changed from "nosort" to "sort". See ticket 2a5629202f. cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC } -} {4/1 4/4 1/1 1/4 sort} +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/} do_test where-14.7 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b @@ -1130,7 +1130,7 @@ do_test where-14.7.2 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b } -} {4/1 4/4 1/1 1/4 nosort} +} {4/4 4/1 1/4 1/1 nosort} do_test where-14.8 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC diff --git a/test/where9.test b/test/where9.test index 23260a6b6..d618208ad 100644 --- a/test/where9.test +++ b/test/where9.test @@ -692,7 +692,7 @@ do_test where9-6.5.3 { do_test where9-6.5.4 { db eval { SELECT count(*) FROM t1 UNION ALL - SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87); + SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid; ROLLBACK; } } {99 105 131 157 182 183 184 185 186 187} diff --git a/test/zerodamage.test b/test/zerodamage.test index 3d18c8dea..217749a4a 100644 --- a/test/zerodamage.test +++ b/test/zerodamage.test @@ -18,7 +18,7 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl -set testprefix wal5 +set testprefix zerodamage ifcapable !vtab { finish_test @@ -89,31 +89,33 @@ do_test zerodamage-2.1 { concat [file_control_powersafe_overwrite db -1] [set ::max_journal_size] } {0 0 24704} -# Run a WAL-mode transaction with POWERSAFE_OVERWRITE on to verify that the -# WAL file does not get too big. -# -do_test zerodamage-3.0 { - db eval { - PRAGMA journal_mode=WAL; - } - db close - sqlite3 db file:test.db?psow=TRUE -uri 1 - db eval { - UPDATE t1 SET y=randomblob(50) WHERE x=124; - } - file size test.db-wal -} {1080} +ifcapable wal { + # Run a WAL-mode transaction with POWERSAFE_OVERWRITE on to verify that the + # WAL file does not get too big. + # + do_test zerodamage-3.0 { + db eval { + PRAGMA journal_mode=WAL; + } + db close + sqlite3 db file:test.db?psow=TRUE -uri 1 + db eval { + UPDATE t1 SET y=randomblob(50) WHERE x=124; + } + file size test.db-wal + } {1080} -# Repeat the previous with POWERSAFE_OVERWRITE off. Verify that the WAL file -# is padded. -# -do_test zerodamage-3.1 { - db close - sqlite3 db file:test.db?psow=FALSE -uri 1 - db eval { - UPDATE t1 SET y=randomblob(50) WHERE x=124; - } - file size test.db-wal -} {8416} + # Repeat the previous with POWERSAFE_OVERWRITE off. Verify that the WAL file + # is padded. + # + do_test zerodamage-3.1 { + db close + sqlite3 db file:test.db?psow=FALSE -uri 1 + db eval { + UPDATE t1 SET y=randomblob(50) WHERE x=124; + } + file size test.db-wal + } {8416} +} finish_test |