diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/analyze3.test | 27 | ||||
| -rw-r--r-- | test/check.test | 6 | ||||
| -rw-r--r-- | test/corruptG.test | 75 | ||||
| -rw-r--r-- | test/func.test | 27 | ||||
| -rw-r--r-- | test/index.test | 17 | ||||
| -rw-r--r-- | test/index6.test | 230 | ||||
| -rw-r--r-- | test/loadext2.test | 28 | ||||
| -rw-r--r-- | test/where2.test | 11 | ||||
| -rw-r--r-- | test/where8.test | 5 |
9 files changed, 422 insertions, 4 deletions
diff --git a/test/analyze3.test b/test/analyze3.test index c25d04422..1e95d591b 100644 --- a/test/analyze3.test +++ b/test/analyze3.test @@ -43,6 +43,8 @@ ifcapable !stat3 { # analyze3-5.*: Check that the query plans of applicable statements are # invalidated if the values of SQL parameter are modified # using the clear_bindings() or transfer_bindings() APIs. +# +# analyze3-6.*: Test that the problem fixed by commit [127a5b776d] is fixed. # proc getvar {varname} { uplevel #0 set $varname } @@ -612,4 +614,29 @@ do_test analyze3-5.1.3 { sqlite3_finalize $S1 } {SQLITE_OK} +#------------------------------------------------------------------------- + +do_test analyze3-6.1 { + execsql { DROP TABLE IF EXISTS t1 } + execsql BEGIN + execsql { CREATE TABLE t1(a, b, c) } + for {set i 0} {$i < 1000} {incr i} { + execsql "INSERT INTO t1 VALUES([expr $i/100], 'x', [expr $i/10])" + } + execsql { + CREATE INDEX i1 ON t1(a, b); + CREATE INDEX i2 ON t1(c); + } + execsql COMMIT + execsql ANALYZE +} {} + +do_eqp_test analyze3-6-3 { + SELECT * FROM t1 WHERE a = 5 AND c = 13; +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}} + +do_eqp_test analyze3-6-2 { + SELECT * FROM t1 WHERE a = 5 AND b > 'w' AND c = 13; +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}} + finish_test diff --git a/test/check.test b/test/check.test index 99b72ac8a..f7ebc2cb4 100644 --- a/test/check.test +++ b/test/check.test @@ -451,5 +451,11 @@ do_test 7.8 { catchsql { INSERT INTO t6 VALUES(12) } db2 } {1 {constraint failed}} +# 2013-08-02: Silently ignore database name qualifiers in CHECK constraints. +# +do_execsql_test 8.1 { + CREATE TABLE t810(a, CHECK( main.t810.a>0 )); + CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 )); +} {} finish_test diff --git a/test/corruptG.test b/test/corruptG.test new file mode 100644 index 000000000..bf62efea2 --- /dev/null +++ b/test/corruptG.test @@ -0,0 +1,75 @@ +# 2013-08-01 +# +# 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 +set testprefix corruptG + +# Do not use a codec for tests in this file, as the database file is +# manipulated directly using tcl scripts (using the [hexio_write] command). +# +do_not_use_codec + +# Create a simple database with a single entry. Then corrupt the +# header-size varint on the index payload so that it maps into a +# negative number. Try to use the database. +# + +do_execsql_test 1.1 { + PRAGMA page_size=512; + CREATE TABLE t1(a,b,c); + INSERT INTO t1(rowid,a,b,c) VALUES(52,'abc','xyz','123'); + CREATE INDEX t1abc ON t1(a,b,c); +} + +# Corrupt the file +db close +hexio_write test.db [expr {3*512 - 15}] 888080807f +sqlite3 db test.db + +# Try to use the file. +do_test 1.2 { + catchsql { + SELECT c FROM t1 WHERE a>'abc'; + } +} {0 {}} +do_test 1.3 { + catchsql { + PRAGMA integrity_check + } +} {0 ok} +do_test 1.4 { + catchsql { + SELECT c FROM t1 ORDER BY a; + } +} {1 {database disk image is malformed}} + +# Corrupt the same file in a slightly different way. Make the record header +# sane, but corrupt one of the serial_type value to indicate a huge payload +# such that the payload begins in allocated space but overflows the buffer. +# +db close +hexio_write test.db [expr {3*512-15}] 0513ff7f01 +sqlite3 db test.db + +do_test 2.1 { + catchsql { + SELECT rowid FROM t1 WHERE a='abc' and b='xyz123456789XYZ'; + } + # The following test result is brittle. The point above is to try to + # force a buffer overread by a corrupt database file. If we get an + # incorrect answer from a corrupt database file, that is OK. If the + # result below changes, that just means that "undefined behavior" has + # changed. +} {0 52} + +finish_test diff --git a/test/func.test b/test/func.test index 4ab768846..d8303f8bf 100644 --- a/test/func.test +++ b/test/func.test @@ -14,6 +14,7 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl +set testprefix func # Create a table to work with. # @@ -682,6 +683,32 @@ do_test func-13.7 { lappend res [sqlite3_finalize $STMT] } {{0 0} {1 0} SQLITE_OK} +# Test that auxiliary data is discarded when a statement is reset. +do_execsql_test 13.8.1 { + SELECT test_auxdata('constant') FROM t4; +} {0 1} +do_execsql_test 13.8.2 { + SELECT test_auxdata('constant') FROM t4; +} {0 1} +db cache flush +do_execsql_test 13.8.3 { + SELECT test_auxdata('constant') FROM t4; +} {0 1} +set V "one" +do_execsql_test 13.8.4 { + SELECT test_auxdata($V), $V FROM t4; +} {0 one 1 one} +set V "two" +do_execsql_test 13.8.5 { + SELECT test_auxdata($V), $V FROM t4; +} {0 two 1 two} +db cache flush +set V "three" +do_execsql_test 13.8.6 { + SELECT test_auxdata($V), $V FROM t4; +} {0 three 1 three} + + # Make sure that a function with a very long name is rejected do_test func-14.1 { catch { diff --git a/test/index.test b/test/index.test index 790bed908..2b95bad83 100644 --- a/test/index.test +++ b/test/index.test @@ -715,6 +715,23 @@ do_test index-20.2 { DROP INDEX "t6i1"; } } {} + +# Try to create a TEMP index on a non-TEMP table. */ +# +do_test index-21.1 { + catchsql { + CREATE INDEX temp.i21 ON t6(c); + } +} {1 {cannot create a TEMP index on non-TEMP table "t6"}} +do_test index-21.2 { + catchsql { + CREATE TEMP TABLE t6(x); + INSERT INTO temp.t6 values(1),(5),(9); + CREATE INDEX temp.i21 ON t6(x); + SELECT x FROM t6 ORDER BY x DESC; + } +} {0 {9 5 1}} + finish_test diff --git a/test/index6.test b/test/index6.test new file mode 100644 index 000000000..e9ea570b8 --- /dev/null +++ b/test/index6.test @@ -0,0 +1,230 @@ +# 2013-07-31 +# +# 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. +# +#*********************************************************************** +# +# Test cases for partial indices +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +load_static_extension db wholenumber; +do_test index6-1.1 { + # Able to parse and manage partial indices + execsql { + CREATE TABLE t1(a,b,c); + CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL; + CREATE INDEX t1b ON t1(b) WHERE b>10; + CREATE VIRTUAL TABLE nums USING wholenumber; + INSERT INTO t1(a,b,c) + SELECT CASE WHEN value%3!=0 THEN value END, value, value + FROM nums WHERE value<=20; + SELECT count(a), count(b) FROM t1; + PRAGMA integrity_check; + } +} {14 20 ok} + +# Error conditions during parsing... +# +do_test index6-1.2 { + catchsql { + CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL; + } +} {1 {no such column: x}} +do_test index6-1.3 { + catchsql { + CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1); + } +} {1 {subqueries prohibited in partial index WHERE clauses}} +do_test index6-1.4 { + catchsql { + CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; + } +} {1 {parameters prohibited in partial index WHERE clauses}} +do_test index6-1.5 { + catchsql { + CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); + } +} {1 {functions prohibited in partial index WHERE clauses}} +do_test index6-1.6 { + catchsql { + CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; + } +} {1 {functions prohibited in partial index WHERE clauses}} + +do_test index6-1.10 { + execsql { + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 20 t1a {14 1} t1b {10 1} ok} + +# STAT1 shows the partial indices have a reduced number of +# rows. +# +do_test index6-1.11 { + execsql { + UPDATE t1 SET a=b; + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 20 t1a {20 1} t1b {10 1} ok} + +do_test index6-1.11 { + execsql { + UPDATE t1 SET a=NULL WHERE b%3!=0; + UPDATE t1 SET b=b+100; + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 20 t1a {6 1} t1b {20 1} ok} + +do_test index6-1.12 { + execsql { + UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END; + UPDATE t1 SET b=b-100; + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 20 t1a {13 1} t1b {10 1} ok} + +do_test index6-1.13 { + execsql { + DELETE FROM t1 WHERE b BETWEEN 8 AND 12; + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 15 t1a {10 1} t1b {8 1} ok} + +do_test index6-1.14 { + execsql { + REINDEX; + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {{} 15 t1a {10 1} t1b {8 1} ok} + +do_test index6-1.15 { + execsql { + CREATE INDEX t1c ON t1(c); + ANALYZE; + SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; + PRAGMA integrity_check; + } +} {t1a {10 1} t1b {8 1} t1c {15 1} ok} + +# Queries use partial indices as appropriate times. +# +do_test index6-2.1 { + execsql { + CREATE TABLE t2(a,b); + INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000; + UPDATE t2 SET a=NULL WHERE b%5==0; + CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL; + SELECT count(*) FROM t2 WHERE a IS NOT NULL; + } +} {800} +do_test index6-2.2 { + execsql { + EXPLAIN QUERY PLAN + SELECT * FROM t2 WHERE a=5; + } +} {/.* TABLE t2 USING INDEX t2a1 .*/} +do_test index6-2.3 { + execsql { + EXPLAIN QUERY PLAN + SELECT * FROM t2 WHERE a IS NOT NULL; + } +} {/.* TABLE t2 USING INDEX t2a1 .*/} +do_test index6-2.4 { + execsql { + EXPLAIN QUERY PLAN + SELECT * FROM t2 WHERE a IS NULL; + } +} {~/.*INDEX t2a1.*/} + +do_execsql_test index6-2.101 { + DROP INDEX t2a1; + UPDATE t2 SET a=b, b=b+10000; + SELECT b FROM t2 WHERE a=15; +} {10015} +do_execsql_test index6-2.102 { + CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200; + SELECT b FROM t2 WHERE a=15; + PRAGMA integrity_check; +} {10015 ok} +do_execsql_test index6-2.102eqp { + EXPLAIN QUERY PLAN + SELECT b FROM t2 WHERE a=15; +} {~/.*INDEX t2a2.*/} +do_execsql_test index6-2.103 { + SELECT b FROM t2 WHERE a=15 AND a<100; +} {10015} +do_execsql_test index6-2.103eqp { + EXPLAIN QUERY PLAN + SELECT b FROM t2 WHERE a=15 AND a<100; +} {/.*INDEX t2a2.*/} +do_execsql_test index6-2.104 { + SELECT b FROM t2 WHERE a=515 AND a>200; +} {10515} +do_execsql_test index6-2.104eqp { + EXPLAIN QUERY PLAN + SELECT b FROM t2 WHERE a=515 AND a>200; +} {/.*INDEX t2a2.*/} + +# Partial UNIQUE indices +# +do_execsql_test index6-3.1 { + CREATE TABLE t3(a,b); + INSERT INTO t3 SELECT value, value FROM nums WHERE value<200; + UPDATE t3 SET a=999 WHERE b%5!=0; + CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999; +} {} +do_test index6-3.2 { + # unable to insert a duplicate row a-value that is not 999. + catchsql { + INSERT INTO t3(a,b) VALUES(150, 'test1'); + } +} {1 {column a is not unique}} +do_test index6-3.3 { + # can insert multiple rows with a==999 because such rows are not + # part of the unique index. + catchsql { + INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2'); + } +} {0 {}} +do_execsql_test index6-3.4 { + SELECT count(*) FROM t3 WHERE a=999; +} {162} +integrity_check index6-3.5 + +do_execsql_test index6-4.0 { + VACUUM; + PRAGMA integrity_check; +} {ok} + +# Silently ignore database name qualifiers in partial indices. +# +do_execsql_test index6-5.0 { + CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10; + /* ^^^^^-- ignored */ + ANALYZE; + SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10; + SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b'; +} {6 6} + +finish_test diff --git a/test/loadext2.test b/test/loadext2.test index 3d01539b3..d5b6ea8d7 100644 --- a/test/loadext2.test +++ b/test/loadext2.test @@ -43,6 +43,19 @@ do_test loadext2-1.2 { } } {1 {no such function: cube}} +# Extensions loaders not currently registered +# +do_test loadext2-1.2.1 { + sqlite3_cancel_auto_extension_sqr +} {0} +do_test loadext2-1.2.2 { + sqlite3_cancel_auto_extension_sqr +} {0} +do_test loadext2-1.2.3 { + sqlite3_cancel_auto_extension_sqr +} {0} + + # Register auto-loaders. Still functions do not exist. # do_test loadext2-1.3 { @@ -76,8 +89,19 @@ do_test loadext2-1.6 { # Reset extension auto loading. Existing extensions still exist. # -do_test loadext2-1.7 { - sqlite3_reset_auto_extension +do_test loadext2-1.7.1 { + sqlite3_cancel_auto_extension_sqr +} {1} +do_test loadext2-1.7.2 { + sqlite3_cancel_auto_extension_sqr +} {0} +do_test loadext2-1.7.3 { + sqlite3_cancel_auto_extension_cube +} {1} +do_test loadext2-1.7.4 { + sqlite3_cancel_auto_extension_cube +} {0} +do_test loadext2-1.7.5 { catchsql { SELECT sqr(2) } diff --git a/test/where2.test b/test/where2.test index 3d4dfc912..c827ecc7b 100644 --- a/test/where2.test +++ b/test/where2.test @@ -699,5 +699,16 @@ do_test where2-11.4 { } } {4 8 10} +# Verify that the OR clause is used in an outer loop even when +# the OR clause scores slightly better on an inner loop. +do_execsql_test where2-12.1 { + CREATE TABLE t12(x INTEGER PRIMARY KEY, y); + CREATE INDEX t12y ON t12(y); + EXPLAIN QUERY PLAN + SELECT a.x, b.x + FROM t12 AS a JOIN t12 AS b ON a.y=b.x + WHERE (b.x=$abc OR b.y=$abc); +} {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/} + finish_test diff --git a/test/where8.test b/test/where8.test index 6890e3ac5..912717929 100644 --- a/test/where8.test +++ b/test/where8.test @@ -212,8 +212,9 @@ do_test where8-3.4 { do_test where8-3.5 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen') + ORDER BY +a, +d; } -} {2 2 2 4 3 3 3 4 0 0} +} {2 2 2 4 3 3 3 4 0 1} do_test where8-3.6 { # The first part of the WHERE clause in this query, (a=2 OR a=3) is @@ -233,7 +234,7 @@ do_test where8-3.7 { WHERE a = 2 AND (d = a OR e = 'sixteen') ORDER BY t1.rowid } -} {2 2 2 4 0 0} +} {/2 2 2 4 0 [01]/} do_test where8-3.8 { execsql_status { SELECT a, d |