diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/analyze6.test | 2 | ||||
| -rw-r--r-- | test/analyze9.test | 69 | ||||
| -rw-r--r-- | test/analyzeC.test | 167 | ||||
| -rw-r--r-- | test/capi3d.test | 37 | ||||
| -rw-r--r-- | test/hexlit.test | 114 | ||||
| -rw-r--r-- | test/join.test | 11 | ||||
| -rw-r--r-- | test/shell1.test | 9 | ||||
| -rw-r--r-- | test/shell5.test | 11 | ||||
| -rw-r--r-- | test/where2.test | 8 |
9 files changed, 419 insertions, 9 deletions
diff --git a/test/analyze6.test b/test/analyze6.test index f77f05324..31ace8eda 100644 --- a/test/analyze6.test +++ b/test/analyze6.test @@ -91,7 +91,7 @@ do_test analyze6-2.3 { } {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}} do_test analyze6-2.4 { execsql { - INSERT INTO t201 VALUES(1,2,3); + INSERT INTO t201 VALUES(1,2,3),(2,3,4),(3,4,5); ANALYZE t201; } eqp {SELECT * FROM t201 WHERE z=5} diff --git a/test/analyze9.test b/test/analyze9.test index d0d3b3524..0d72658df 100644 --- a/test/analyze9.test +++ b/test/analyze9.test @@ -1019,6 +1019,73 @@ foreach {tn where res} { do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res } -finish_test +proc int_to_char {i} { + set ret "" + set char [list a b c d e f g h i j] + foreach {div} {1000 100 10 1} { + append ret [lindex $char [expr ($i / $div) % 10]] + } + set ret +} +db func int_to_char int_to_char + +do_execsql_test 23.0 { + CREATE TABLE t4( + a COLLATE nocase, b, c, + d, e, f, + PRIMARY KEY(c, b, a) + ) WITHOUT ROWID; + CREATE INDEX i41 ON t4(e); + CREATE INDEX i42 ON t4(f); + + WITH data(a, b, c, d, e, f) AS ( + SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 + UNION ALL + SELECT + int_to_char(f+1), b, c, d, (e+1) % 2, f+1 + FROM data WHERE f<1024 + ) + INSERT INTO t4 SELECT a, b, c, d, e, f FROM data; + ANALYZE; +} {} + +do_eqp_test 23.1 { + SELECT * FROM t4 WHERE + (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300 +} { + 0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)} +} +do_eqp_test 23.2 { + SELECT * FROM t4 WHERE + (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300 +} { + 0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)} +} +do_execsql_test 24.0 { + CREATE TABLE t5(c, d, b, e, a, PRIMARY KEY(a, b, c)) WITHOUT ROWID; + WITH data(a, b, c, d, e) AS ( + SELECT 'z', 'y', 0, 0, 0 + UNION ALL + SELECT + a, CASE WHEN b='y' THEN 'n' ELSE 'y' END, c+1, e/250, e+1 + FROM data + WHERE e<1000 + ) + INSERT INTO t5(a, b, c, d, e) SELECT * FROM data; + CREATE INDEX t5d ON t5(d); + CREATE INDEX t5e ON t5(e); + ANALYZE; +} +foreach {tn where eqp} { + 1 "d=0 AND a='z' AND b='n' AND e<200" {/*t5d (d=? AND a=? AND b=?)*/} + 2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/} + + 3 "d=0 AND e<300" {/*t5d (d=?)*/} + 4 "d=0 AND e<200" {/*t5e (e<?)*/} +} { + do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp +} + +finish_test diff --git a/test/analyzeC.test b/test/analyzeC.test new file mode 100644 index 000000000..02faa9c7e --- /dev/null +++ b/test/analyzeC.test @@ -0,0 +1,167 @@ +# 2014-07-22 +# +# 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 contains automated tests used to verify that the text terms +# at the end of sqlite_stat1.stat are processed correctly. +# +# (1) "unordered" means that the index cannot be used for ORDER BY +# or for range queries +# +# (2) "sz=NNN" sets the relative size of the index entries +# +# (3) All other fields are silently ignored +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix analyzeC + +# Baseline case. Range queries work OK. Indexes can be used for +# ORDER BY. +# +do_execsql_test 1.0 { + CREATE TABLE t1(a,b,c); + INSERT INTO t1(a,b,c) + VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111); + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); + ANALYZE; + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1(tbl,idx,stat) + VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4'); + ANALYZE sqlite_master; + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {4 5 6 # 7 8 9 # 4 8 12 #} +do_execsql_test 1.1 { + EXPLAIN QUERY PLAN + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {/.* USING INDEX t1a .a>. AND a<...*/} +do_execsql_test 1.2 { + SELECT c FROM t1 ORDER BY a; +} {3 111 6 12 9 12} +do_execsql_test 1.3 { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {/.*SCAN TABLE t1 USING INDEX t1a.*/} +do_execsql_test 1.3x { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {~/.*B-TREE FOR ORDER BY.*/} + +# Now mark the t1a index as "unordered". Range queries and ORDER BY no +# longer use the index, but equality queries do. +# +do_execsql_test 2.0 { + UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a'; + ANALYZE sqlite_master; + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {4 5 6 # 7 8 9 # 4 8 12 #} +do_execsql_test 2.1 { + EXPLAIN QUERY PLAN + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {~/.*USING INDEX.*/} +do_execsql_test 2.2 { + SELECT c FROM t1 ORDER BY a; +} {3 111 6 12 9 12} +do_execsql_test 2.3 { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {~/.*USING INDEX.*/} +do_execsql_test 2.3x { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {/.*B-TREE FOR ORDER BY.*/} + +# Ignore extraneous text parameters in the sqlite_stat1.stat field. +# +do_execsql_test 3.0 { + UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11' + WHERE idx='t1a'; + ANALYZE sqlite_master; + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {4 5 6 # 7 8 9 # 4 8 12 #} +do_execsql_test 3.1 { + EXPLAIN QUERY PLAN + SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; +} {~/.*USING INDEX.*/} +do_execsql_test 3.2 { + SELECT c FROM t1 ORDER BY a; +} {3 111 6 12 9 12} +do_execsql_test 3.3 { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {~/.*USING INDEX.*/} +do_execsql_test 3.3x { + EXPLAIN QUERY PLAN + SELECT c FROM t1 ORDER BY a; +} {/.*B-TREE FOR ORDER BY.*/} + +# The sz=NNN parameter determines which index to scan +# +do_execsql_test 4.0 { + DROP INDEX t1a; + CREATE INDEX t1ab ON t1(a,b); + CREATE INDEX t1ca ON t1(c,a); + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1(tbl,idx,stat) + VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20'); + ANALYZE sqlite_master; + SELECT count(a) FROM t1; +} {6} +do_execsql_test 4.1 { + EXPLAIN QUERY PLAN + SELECT count(a) FROM t1; +} {/.*INDEX t1ab.*/} +do_execsql_test 4.2 { + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1(tbl,idx,stat) + VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10'); + ANALYZE sqlite_master; + SELECT count(a) FROM t1; +} {6} +do_execsql_test 4.3 { + EXPLAIN QUERY PLAN + SELECT count(a) FROM t1; +} {/.*INDEX t1ca.*/} + + +# The sz=NNN parameter works even if there is other extraneous text +# in the sqlite_stat1.stat column. +# +do_execsql_test 5.0 { + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1(tbl,idx,stat) + VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'), + ('t1','t1ca','12345 3 2 whatever sz=20 junk'); + ANALYZE sqlite_master; + SELECT count(a) FROM t1; +} {6} +do_execsql_test 5.1 { + EXPLAIN QUERY PLAN + SELECT count(a) FROM t1; +} {/.*INDEX t1ab.*/} +do_execsql_test 5.2 { + DELETE FROM sqlite_stat1; + INSERT INTO sqlite_stat1(tbl,idx,stat) + VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'), + ('t1','t1ab','12345 3 2 whatever sz=20 junk'); + ANALYZE sqlite_master; + SELECT count(a) FROM t1; +} {6} +do_execsql_test 5.3 { + EXPLAIN QUERY PLAN + SELECT count(a) FROM t1; +} {/.*INDEX t1ca.*/} + + + + +finish_test diff --git a/test/capi3d.test b/test/capi3d.test index ed3765b05..fb8abe86d 100644 --- a/test/capi3d.test +++ b/test/capi3d.test @@ -144,4 +144,41 @@ do_test capi3d-3.99 { sqlite3_stmt_busy 0 } {0} +#-------------------------------------------------------------------------- +# Test the sqlite3_stmt_busy() function with ROLLBACK statements. +# +reset_db + +do_execsql_test capi3d-4.1 { + CREATE TABLE t4(x,y); + BEGIN; +} + +do_test capi3d-4.2.1 { + breakpoint + set ::s1 [sqlite3_prepare_v2 db "ROLLBACK" -1 notused] + sqlite3_step $::s1 +} {SQLITE_DONE} + +do_test capi3d-4.2.2 { + sqlite3_stmt_busy $::s1 +} {1} + +do_catchsql_test capi3d-4.2.3 { + VACUUM +} {1 {cannot VACUUM - SQL statements in progress}} + +do_test capi3d-4.2.4 { + sqlite3_reset $::s1 +} {SQLITE_OK} + +do_catchsql_test capi3d-4.2.5 { + VACUUM +} {0 {}} + +do_test capi3d-4.2.6 { + sqlite3_finalize $::s1 +} {SQLITE_OK} + + finish_test diff --git a/test/hexlit.test b/test/hexlit.test new file mode 100644 index 000000000..10909e6f4 --- /dev/null +++ b/test/hexlit.test @@ -0,0 +1,114 @@ +# 2014-07-23 +# +# 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 tests for hexadecimal literals + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +proc hexlit1 {tnum val ans} { + do_execsql_test hexlit-$tnum "SELECT $val" $ans +} + +hexlit1 100 0x0 0 +hexlit1 101 0x0000000000000000000000000000000000000000000001 1 +hexlit1 102 0x2 2 +hexlit1 103 0x4 4 +hexlit1 104 0x8 8 +hexlit1 105 0x00000000000000000000000000000000000000000000010 16 +hexlit1 103 0x20 32 +hexlit1 106 0x40 64 +hexlit1 107 0x80 128 +hexlit1 108 0x100 256 +hexlit1 109 0x200 512 +hexlit1 110 0X400 1024 +hexlit1 111 0x800 2048 +hexlit1 112 0x1000 4096 +hexlit1 113 0x2000 8192 +hexlit1 114 0x4000 16384 +hexlit1 115 0x8000 32768 +hexlit1 116 0x10000 65536 +hexlit1 117 0x20000 131072 +hexlit1 118 0x40000 262144 +hexlit1 119 0x80000 524288 +hexlit1 120 0x100000 1048576 +hexlit1 121 0x200000 2097152 +hexlit1 122 0x400000 4194304 +hexlit1 123 0x800000 8388608 +hexlit1 124 0x1000000 16777216 +hexlit1 125 0x2000000 33554432 +hexlit1 126 0x4000000 67108864 +hexlit1 127 0x8000000 134217728 +hexlit1 128 0x10000000 268435456 +hexlit1 129 0x20000000 536870912 +hexlit1 130 0x40000000 1073741824 +hexlit1 131 0x80000000 2147483648 +hexlit1 132 0x100000000 4294967296 +hexlit1 133 0x200000000 8589934592 +hexlit1 134 0x400000000 17179869184 +hexlit1 135 0x800000000 34359738368 +hexlit1 136 0x1000000000 68719476736 +hexlit1 137 0x2000000000 137438953472 +hexlit1 138 0x4000000000 274877906944 +hexlit1 139 0x8000000000 549755813888 +hexlit1 140 0x10000000000 1099511627776 +hexlit1 141 0x20000000000 2199023255552 +hexlit1 142 0x40000000000 4398046511104 +hexlit1 143 0x80000000000 8796093022208 +hexlit1 144 0x100000000000 17592186044416 +hexlit1 145 0x200000000000 35184372088832 +hexlit1 146 0x400000000000 70368744177664 +hexlit1 147 0x800000000000 140737488355328 +hexlit1 148 0x1000000000000 281474976710656 +hexlit1 149 0x2000000000000 562949953421312 +hexlit1 150 0x4000000000000 1125899906842624 +hexlit1 151 0x8000000000000 2251799813685248 +hexlit1 152 0x10000000000000 4503599627370496 +hexlit1 153 0x20000000000000 9007199254740992 +hexlit1 154 0x40000000000000 18014398509481984 +hexlit1 155 0x80000000000000 36028797018963968 +hexlit1 156 0x100000000000000 72057594037927936 +hexlit1 157 0x200000000000000 144115188075855872 +hexlit1 158 0x400000000000000 288230376151711744 +hexlit1 159 0x800000000000000 576460752303423488 +hexlit1 160 0X1000000000000000 1152921504606846976 +hexlit1 161 0x2000000000000000 2305843009213693952 +hexlit1 162 0X4000000000000000 4611686018427387904 +hexlit1 163 0x8000000000000000 -9223372036854775808 +hexlit1 164 0XFFFFFFFFFFFFFFFF -1 + +for {set n 1} {$n < 0x10} {incr n} { + hexlit1 200.$n.1 0X[format %03X $n] $n + hexlit1 200.$n.2 0x[format %03X $n] $n + hexlit1 200.$n.3 0X[format %03x $n] $n + hexlit1 200.$n.4 0x[format %03x $n] $n +} + +# String literals that look like hex do not get cast or coerced. +# +do_execsql_test hexlit-300 { + CREATE TABLE t1(x INT, y REAL); + INSERT INTO t1 VALUES('1234','4567'),('0x1234','0x4567'); + SELECT typeof(x), x, typeof(y), y, '#' FROM t1 ORDER BY rowid; +} {integer 1234 real 4567.0 # text 0x1234 text 0x4567 #} +do_execsql_test hexlit-301 { + SELECT CAST('0x1234' AS INTEGER); +} {0} + +# Oversized hex literals are rejected +# +do_catchsql_test hexlist-400 { + SELECT 0x10000000000000000; +} {1 {hex literal too big: 0x10000000000000000}} + + +finish_test diff --git a/test/join.test b/test/join.test index 28d9ddf8b..4c83fa6b3 100644 --- a/test/join.test +++ b/test/join.test @@ -36,6 +36,17 @@ do_test join-1.2 { } } {1 2 3 2 3 4 3 4 5} +# A FROM clause of the form: "<table>, <table> ON <expr>" is not +# allowed by the SQLite syntax diagram, nor by any other SQL database +# engine that we are aware of. Nevertheless, historic versions of +# SQLite have allowed it. We need to continue to support it moving +# forward to prevent breakage of legacy applications. Though, we will +# not advertise it as being supported. +# +do_execsql_test join-1.2.1 { + SELECT t1.rowid, t2.rowid, '|' FROM t1, t2 ON t1.a=t2.b; +} {1 1 | 2 2 | 3 3 |} + do_test join-1.3 { execsql2 { SELECT * FROM t1 NATURAL JOIN t2; diff --git a/test/shell1.test b/test/shell1.test index e6fb0c28d..ab382e74c 100644 --- a/test/shell1.test +++ b/test/shell1.test @@ -588,14 +588,17 @@ db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;} # .separator STRING Change separator used by output mode and .import do_test shell1-3.22.1 { catchcmd "test.db" ".separator" -} {1 {Usage: .separator STRING}} +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} do_test shell1-3.22.2 { catchcmd "test.db" ".separator FOO" } {0 {}} do_test shell1-3.22.3 { + catchcmd "test.db" ".separator ABC XYZ" +} {0 {}} +do_test shell1-3.22.4 { # too many arguments - catchcmd "test.db" ".separator FOO BAD" -} {1 {Usage: .separator STRING}} + catchcmd "test.db" ".separator FOO BAD BAD2" +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} # .show Show the current values for various settings do_test shell1-3.23.1 { diff --git a/test/shell5.test b/test/shell5.test index 6e9dd2063..8d740cb98 100644 --- a/test/shell5.test +++ b/test/shell5.test @@ -55,14 +55,17 @@ do_test shell5-1.1.3 { # .separator STRING Change separator used by output mode and .import do_test shell5-1.2.1 { catchcmd "test.db" ".separator" -} {1 {Usage: .separator STRING}} +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} do_test shell5-1.2.2 { - catchcmd "test.db" ".separator FOO" + catchcmd "test.db" ".separator ONE" } {0 {}} do_test shell5-1.2.3 { + catchcmd "test.db" ".separator ONE TWO" +} {0 {}} +do_test shell5-1.2.4 { # too many arguments - catchcmd "test.db" ".separator FOO BAD" -} {1 {Usage: .separator STRING}} + catchcmd "test.db" ".separator ONE TWO THREE" +} {1 {Usage: .separator SEPARATOR ?NEWLINE?}} # separator should default to "|" do_test shell5-1.3.1 { diff --git a/test/where2.test b/test/where2.test index d9b2b23de..367eb0dfe 100644 --- a/test/where2.test +++ b/test/where2.test @@ -751,5 +751,13 @@ do_execsql_test where2-12.1 { } {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/} } +# Verify that all necessary OP_OpenRead opcodes occur in the OR optimization. +# +do_execsql_test where2-13.1 { + CREATE TABLE t13(a,b); + CREATE INDEX t13a ON t13(a); + INSERT INTO t13 VALUES(4,5); + SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4; +} {4 5} finish_test |