aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/analyze8.test8
-rw-r--r--test/autoindex4.test52
-rw-r--r--test/backup.test1
-rw-r--r--test/backup5.test65
-rw-r--r--test/capi3d.test1
-rw-r--r--test/corruptH.test1
-rw-r--r--test/e_blobbytes.test76
-rw-r--r--test/e_blobclose.test171
-rw-r--r--test/e_blobopen.test549
-rw-r--r--test/e_blobwrite.test204
-rw-r--r--test/e_changes.test441
-rw-r--r--test/e_totalchanges.test213
-rw-r--r--test/e_wal.test229
-rw-r--r--test/fkey7.test17
-rw-r--r--test/misc8.test98
-rw-r--r--test/mmap1.test86
-rw-r--r--test/rollback2.test87
-rw-r--r--test/rollbackfault.test84
-rw-r--r--test/scanstatus.test398
-rw-r--r--test/skipscan6.test200
-rw-r--r--test/sort2.test1
-rw-r--r--test/tkt-f777251dc7a.test1
-rw-r--r--test/without_rowid5.test3
23 files changed, 2932 insertions, 54 deletions
diff --git a/test/analyze8.test b/test/analyze8.test
index 4384c3967..1079e6808 100644
--- a/test/analyze8.test
+++ b/test/analyze8.test
@@ -86,23 +86,23 @@ do_test 2.1 {
# range.
#
# Test 3.2 is a little unstable. It depends on the planner estimating
-# that (b BETWEEN 50 AND 54) will match more rows than (c BETWEEN
+# that (b BETWEEN 30 AND 34) will match more rows than (c BETWEEN
# 800000 AND 900000). Which is a pretty close call (50 vs. 32), so
# the planner could get it wrong with an unlucky set of samples. This
# case happens to work, but others ("b BETWEEN 40 AND 44" for example)
# will fail.
#
do_execsql_test 3.0 {
- SELECT count(*) FROM t1 WHERE b BETWEEN 50 AND 54;
+ SELECT count(*) FROM t1 WHERE b BETWEEN 30 AND 34;
SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000;
SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000;
} {50 376 32}
do_test 3.1 {
- eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000}
+ eqp {SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 0 AND 100000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
do_test 3.2 {
eqp {SELECT * FROM t1
- WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000}
+ WHERE b BETWEEN 30 AND 34 AND c BETWEEN 800000 AND 900000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
do_test 3.3 {
eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
diff --git a/test/autoindex4.test b/test/autoindex4.test
new file mode 100644
index 000000000..6d0865bf7
--- /dev/null
+++ b/test/autoindex4.test
@@ -0,0 +1,52 @@
+# 2014-10-24
+#
+# 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 script is testing automatic index creation logic,
+# and specifically creation of automatic partial indexes.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+do_execsql_test autoindex4-1.0 {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl');
+ CREATE TABLE t2(x,y);
+ INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp');
+
+ SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b;
+} {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |}
+do_execsql_test autoindex4-1.1 {
+ SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555;
+} {}
+
+do_execsql_test autoindex4-1.2 {
+ SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
+} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
+do_execsql_test autoindex4-1.3 {
+ SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
+} {234 def {} {} | 234 ghi {} {} |}
+do_execsql_test autoindex4-1.4 {
+ SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
+} {}
+
+
+do_execsql_test autoindex4-2.0 {
+ CREATE TABLE t3(e,f);
+ INSERT INTO t3 VALUES(123,654),(555,444),(234,987);
+
+ SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
+ FROM t3
+ ORDER BY rowid;
+} {1 123 654 | 0 555 444 | 4 234 987 |}
+
+finish_test
diff --git a/test/backup.test b/test/backup.test
index 444619c68..3b1e1db9e 100644
--- a/test/backup.test
+++ b/test/backup.test
@@ -217,6 +217,7 @@ foreach nPagePerStep {1 200} {
INSERT INTO ${file_dest}.t1 VALUES(1, randstr(1000,1000))
" $db_dest
}
+ execsql COMMIT $db_dest
}
# Backup the source database.
diff --git a/test/backup5.test b/test/backup5.test
new file mode 100644
index 000000000..c789adfa6
--- /dev/null
+++ b/test/backup5.test
@@ -0,0 +1,65 @@
+# 2014 November 13
+#
+# 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 backup5
+
+forcedelete test2.db
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(a, b);
+ INSERT INTO t2 VALUES(1, 1);
+ INSERT INTO t2 VALUES(2, 2);
+ INSERT INTO t2 VALUES(3, 3);
+}
+
+do_test 1.1 {
+ forcecopy test.db test.db2
+ db eval {
+ DROP TABLE t2;
+ INSERT INTO t1 VALUES(zeroblob(1000), zeroblob(1000));
+ INSERT INTO t1 VALUES(randomblob(1000), randomblob(1000));
+ }
+} {}
+
+do_test 1.2 {
+ sqlite3 db2 test.db2
+ set stmt [sqlite3_prepare_v2 db2 "SELECT * FROM t2" -1 dummy]
+ sqlite3_step $stmt
+} {SQLITE_ROW}
+
+do_test 1.3 {
+ list [catch { sqlite3_backup B db2 main db main } msg] $msg
+} {1 {sqlite3_backup_init() failed}}
+
+do_test 1.4 {
+ sqlite3_errmsg db2
+} {destination database is in use}
+
+do_test 1.5 {
+ sqlite3_reset $stmt
+ sqlite3_backup B db2 main db main
+ B step 200
+ B finish
+} {SQLITE_OK}
+
+do_test 1.6 {
+ list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
+} {SQLITE_ERROR SQLITE_ERROR}
+
+do_test 1.7 {
+ sqlite3_errmsg db2
+} {no such table: t2}
+
+finish_test
diff --git a/test/capi3d.test b/test/capi3d.test
index fb8abe86d..1459c5abf 100644
--- a/test/capi3d.test
+++ b/test/capi3d.test
@@ -155,7 +155,6 @@ do_execsql_test capi3d-4.1 {
}
do_test capi3d-4.2.1 {
- breakpoint
set ::s1 [sqlite3_prepare_v2 db "ROLLBACK" -1 notused]
sqlite3_step $::s1
} {SQLITE_DONE}
diff --git a/test/corruptH.test b/test/corruptH.test
index ee2bb1ee4..5c83cb3b9 100644
--- a/test/corruptH.test
+++ b/test/corruptH.test
@@ -64,7 +64,6 @@ do_test 1.2 {
} {}
do_test 1.3 {
-breakpoint
db eval { PRAGMA secure_delete=1 }
list [catch {
db eval { SELECT * FROM t1 WHERE a IN (1, 2) } {
diff --git a/test/e_blobbytes.test b/test/e_blobbytes.test
new file mode 100644
index 000000000..a6283ab85
--- /dev/null
+++ b/test/e_blobbytes.test
@@ -0,0 +1,76 @@
+# 2014 October 30
+#
+# 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 e_blobbytes
+
+do_execsql_test 1.0 {
+ CREATE TABLE q1(r INTEGER PRIMARY KEY, s TEXT);
+ WITH d(a, b) AS (
+ SELECT 0, ''
+ UNION ALL
+ SELECT a+1, b||'.' FROM d WHERE a<10000
+ )
+ INSERT INTO q1 SELECT * FROM d;
+}
+
+
+# EVIDENCE-OF: R-07796-55423 Returns the size in bytes of the BLOB
+# accessible via the successfully opened BLOB handle in its only
+# argument.
+#
+proc check_blob_size {tn rowid bytes} {
+ uplevel [list do_test $tn [subst -nocommands {
+ sqlite3_blob_open db main q1 s $rowid 0 B
+ set res [sqlite3_blob_bytes [set B]]
+ sqlite3_blob_close [set B]
+ set res
+ }] $bytes]
+}
+check_blob_size 1.1 43 43
+check_blob_size 1.2 391 391
+check_blob_size 1.3 6349 6349
+check_blob_size 1.4 2621 2621
+check_blob_size 1.5 7771 7771
+check_blob_size 1.6 7949 7949
+check_blob_size 1.7 4374 4374
+check_blob_size 1.8 2578 2578
+check_blob_size 1.9 7004 7004
+check_blob_size 1.10 2180 2180
+check_blob_size 1.11 3796 3796
+check_blob_size 1.12 7101 7101
+check_blob_size 1.13 7449 7449
+check_blob_size 1.14 7224 7224
+check_blob_size 1.15 3038 3038
+check_blob_size 1.16 1083 1083
+check_blob_size 1.17 5157 5157
+check_blob_size 1.18 6686 6686
+check_blob_size 1.19 6592 6592
+check_blob_size 1.20 0 0
+
+
+# EVIDENCE-OF: R-53088-19343 The incremental blob I/O routines can only
+# read or overwriting existing blob content; they cannot change the size
+# of a blob.
+#
+# Also demonstrated in other e_blobXXX.test files.
+#
+do_test 2.1 {
+ sqlite3_blob_open db main q1 s 86 1 B
+ list [catch { sqlite3_blob_write $B 86 "1" 1 } msg] $msg
+} {1 SQLITE_ERROR}
+sqlite3_blob_close $B
+
+finish_test
+
+
diff --git a/test/e_blobclose.test b/test/e_blobclose.test
new file mode 100644
index 000000000..a5d432d3b
--- /dev/null
+++ b/test/e_blobclose.test
@@ -0,0 +1,171 @@
+# 2014 October 30
+#
+# 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 e_blobclose
+
+set dots [string repeat . 40]
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a INTEGER PRIMARY KEY, b DOTS);
+ INSERT INTO x1 VALUES(-1, $dots);
+ INSERT INTO x1 VALUES(-10, $dots);
+ INSERT INTO x1 VALUES(-100, $dots);
+ INSERT INTO x1 VALUES(-1000, $dots);
+ INSERT INTO x1 VALUES(-10000, $dots);
+}
+
+# EVIDENCE-OF: R-03145-46390 This function closes an open BLOB handle.
+#
+# It's not clear how to test that a blob handle really is closed.
+# Attempting to use a closed blob handle will likely crash the process.
+# Assume here that if the SHARED lock on the db file is released,
+# the blob handle has been closed.
+#
+do_execsql_test 1.1 { PRAGMA lock_status } {main unlocked temp closed}
+sqlite3_blob_open db main x1 b -1 0 B
+do_execsql_test 1.2 { PRAGMA lock_status } {main shared temp closed}
+sqlite3_blob_close $B
+do_execsql_test 1.3 { PRAGMA lock_status } {main unlocked temp closed}
+
+
+# EVIDENCE-OF: R-34027-00617 If the blob handle being closed was opened
+# for read-write access, and if the database is in auto-commit mode and
+# there are no other open read-write blob handles or active write
+# statements, the current transaction is committed.
+#
+# 2.1.*: Transaction is not committed if there are other open
+# read-write blob handles.
+#
+# 2.2.*: Transaction is not committed if not in auto-commit mode.
+#
+# 2.3.*: Active write statements.
+#
+do_test 2.1.1 {
+ sqlite3_blob_open db main x1 b -100 1 B1
+ sqlite3_blob_open db main x1 b -1000 1 B2
+ sqlite3_blob_open db main x1 b -10000 1 B3
+ sqlite3_blob_open db main x1 b -10000 0 B4 ;# B4 is read-only!
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.1.2 {
+ sqlite3_blob_close $B1
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.1.3 {
+ sqlite3_blob_close $B2
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.1.4 {
+ sqlite3_blob_close $B3
+ execsql { PRAGMA lock_status }
+} {main shared temp closed}
+do_test 2.1.5 {
+ sqlite3_blob_close $B4
+ execsql { PRAGMA lock_status }
+} {main unlocked temp closed}
+
+do_test 2.2.1 {
+ sqlite3_blob_open db main x1 b -100 1 B1
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.2.2 {
+ execsql { BEGIN }
+ sqlite3_blob_close $B1
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.2.3 {
+ execsql { COMMIT }
+ execsql { PRAGMA lock_status }
+} {main unlocked temp closed}
+
+proc val {} {
+ sqlite3_blob_close $::B
+ db eval { PRAGMA lock_status }
+}
+db func val val
+do_test 2.3.1 {
+ sqlite3_blob_open db main x1 b -100 1 B
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.3.2 {
+ execsql { INSERT INTO x1 VALUES(15, val()) }
+ execsql { PRAGMA lock_status }
+} {main unlocked temp closed}
+do_test 2.3.3 {
+ execsql { SELECT * FROM x1 WHERE a = 15 }
+} {15 {main reserved temp closed}}
+
+# A reader does not inhibit commit.
+do_test 2.3.4 {
+ sqlite3_blob_open db main x1 b -100 1 B
+ execsql { PRAGMA lock_status }
+} {main reserved temp closed}
+do_test 2.3.5 {
+ execsql { SELECT a, val() FROM x1 LIMIT 1 }
+} {-10000 {main shared temp closed}}
+
+
+do_test 3.1 {
+ sqlite3_blob_open db main x1 b -10 1 B
+ execsql {
+ INSERT INTO x1 VALUES(1, 'abc');
+ SELECT * FROM x1 WHERE a=1;
+ }
+} {1 abc}
+do_test 3.2 {
+ sqlite3_blob_write $B 0 "abcdefghij" 10
+ execsql { SELECT * FROM x1 WHERE a=-10 }
+} {-10 abcdefghij..............................}
+
+do_test 3.3 {
+ sqlite3 db2 test.db
+ execsql { BEGIN ; SELECT * FROM x1 } db2
+ sqlite3_blob_close $B
+} {SQLITE_BUSY}
+
+# EVIDENCE-OF: R-41959-38737 Otherwise, if this function is passed a
+# valid open blob handle, the values returned by the sqlite3_errcode()
+# and sqlite3_errmsg() functions are set before returning.
+#
+do_test 3.4 {
+ list [sqlite3_errcode db] [sqlite3_errmsg db]
+} {SQLITE_BUSY {database is locked}}
+
+# EVIDENCE-OF: R-37801-37633 The BLOB handle is closed unconditionally.
+# Even if this routine returns an error code, the handle is still
+# closed.
+#
+# Test that the lock has been released. Assume this means the handle
+# is closed, even though blob_close() returned SQLITE_BUSY.
+#
+do_execsql_test 3.4 { PRAGMA lock_status } {main unlocked temp closed}
+
+# EVIDENCE-OF: R-35111-05628 If an error occurs while committing the
+# transaction, an error code is returned and the transaction rolled
+# back.
+#
+# Row 1 is removed (it was inserted this transaction) and row -10
+# is restored to its original state. Transaction has been rolled back.
+#
+do_execsql_test 3.5 {
+ SELECT * FROM x1 WHERE a IN (1, -10);
+} {-10 ........................................}
+
+# EVIDENCE-OF: R-25894-51060 Calling this routine with a null pointer
+# (such as would be returned by a failed call to sqlite3_blob_open()) is
+# a harmless no-op.
+#
+do_test 4.0 { sqlite3_blob_close 0 } {}
+
+finish_test
+
diff --git a/test/e_blobopen.test b/test/e_blobopen.test
new file mode 100644
index 000000000..01f62cdd7
--- /dev/null
+++ b/test/e_blobopen.test
@@ -0,0 +1,549 @@
+# 2014 October 30
+#
+# 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 e_blobopen
+
+forcedelete test.db2
+
+do_execsql_test 1.0 {
+ ATTACH 'test.db2' AS aux;
+
+ CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+ CREATE TEMP TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+ CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+
+ CREATE TABLE main.x1(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+ CREATE TEMP TABLE x2(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+ CREATE TABLE aux.x3(a INTEGER PRIMARY KEY, b TEXT, c BLOB);
+
+ INSERT INTO main.t1 VALUES(1, 'main one', X'0101');
+ INSERT INTO main.t1 VALUES(2, 'main two', X'0102');
+ INSERT INTO main.t1 VALUES(3, 'main three', X'0103');
+ INSERT INTO main.t1 VALUES(4, 'main four', X'0104');
+ INSERT INTO main.t1 VALUES(5, 'main five', X'0105');
+
+ INSERT INTO main.x1 VALUES(1, 'x main one', X'000101');
+ INSERT INTO main.x1 VALUES(2, 'x main two', X'000102');
+ INSERT INTO main.x1 VALUES(3, 'x main three', X'000103');
+ INSERT INTO main.x1 VALUES(4, 'x main four', X'000104');
+ INSERT INTO main.x1 VALUES(5, 'x main five', X'000105');
+
+ INSERT INTO temp.t1 VALUES(1, 'temp one', X'0201');
+ INSERT INTO temp.t1 VALUES(2, 'temp two', X'0202');
+ INSERT INTO temp.t1 VALUES(3, 'temp three', X'0203');
+ INSERT INTO temp.t1 VALUES(4, 'temp four', X'0204');
+ INSERT INTO temp.t1 VALUES(5, 'temp five', X'0205');
+
+ INSERT INTO temp.x2 VALUES(1, 'x temp one', X'000201');
+ INSERT INTO temp.x2 VALUES(2, 'x temp two', X'000202');
+ INSERT INTO temp.x2 VALUES(3, 'x temp three', X'000203');
+ INSERT INTO temp.x2 VALUES(4, 'x temp four', X'000204');
+ INSERT INTO temp.x2 VALUES(5, 'x temp five', X'000205');
+
+ INSERT INTO aux.t1 VALUES(1, 'aux one', X'0301');
+ INSERT INTO aux.t1 VALUES(2, 'aux two', X'0302');
+ INSERT INTO aux.t1 VALUES(3, 'aux three', X'0303');
+ INSERT INTO aux.t1 VALUES(4, 'aux four', X'0304');
+ INSERT INTO aux.t1 VALUES(5, 'aux five', X'0305');
+
+ INSERT INTO aux.x3 VALUES(1, 'x aux one', X'000301');
+ INSERT INTO aux.x3 VALUES(2, 'x aux two', X'000302');
+ INSERT INTO aux.x3 VALUES(3, 'x aux three', X'000303');
+ INSERT INTO aux.x3 VALUES(4, 'x aux four', X'000304');
+ INSERT INTO aux.x3 VALUES(5, 'x aux five', X'000305');
+}
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-37639-55938 This interfaces opens a handle to the BLOB
+# located in row iRow, column zColumn, table zTable in database zDb; in
+# other words, the same BLOB that would be selected by: SELECT zColumn
+# FROM zDb.zTable WHERE rowid = iRow;
+#
+proc read_blob {zDb zTab zCol iRow} {
+ sqlite3_blob_open db $zDb $zTab $zCol $iRow 0 B
+ set nByte [sqlite3_blob_bytes $B]
+ set data [sqlite3_blob_read $B 0 $nByte]
+ sqlite3_blob_close $B
+ return $data
+}
+
+do_test 1.1.1 { read_blob main t1 b 1 } "main one"
+do_test 1.1.2 { read_blob main t1 c 1 } "\01\01"
+do_test 1.1.3 { read_blob temp t1 b 1 } "temp one"
+do_test 1.1.4 { read_blob temp t1 c 1 } "\02\01"
+do_test 1.1.6 { read_blob aux t1 b 1 } "aux one"
+do_test 1.1.7 { read_blob aux t1 c 1 } "\03\01"
+
+do_test 1.2.1 { read_blob main t1 b 4 } "main four"
+do_test 1.2.2 { read_blob main t1 c 4 } "\01\04"
+do_test 1.2.3 { read_blob temp t1 b 4 } "temp four"
+do_test 1.2.4 { read_blob temp t1 c 4 } "\02\04"
+do_test 1.2.6 { read_blob aux t1 b 4 } "aux four"
+do_test 1.2.7 { read_blob aux t1 c 4 } "\03\04"
+
+do_test 1.3.1 { read_blob main x1 b 2 } "x main two"
+do_test 1.3.2 { read_blob main x1 c 2 } "\00\01\02"
+do_test 1.3.3 { read_blob temp x2 b 2 } "x temp two"
+do_test 1.3.4 { read_blob temp x2 c 2 } "\00\02\02"
+do_test 1.3.6 { read_blob aux x3 b 2 } "x aux two"
+do_test 1.3.7 { read_blob aux x3 c 2 } "\00\03\02"
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-27234-05761 Parameter zDb is not the filename that
+# contains the database, but rather the symbolic name of the database.
+# For attached databases, this is the name that appears after the AS
+# keyword in the ATTACH statement. For the main database file, the
+# database name is "main". For TEMP tables, the database name is "temp".
+#
+# The test cases immediately above demonstrate that the database name
+# for the main db, for TEMP tables and for those in attached databases
+# is correct. The following tests check that filenames cannot be
+# used as well.
+#
+do_test 2.1 {
+ list [catch { sqlite3_blob_open db "test.db" t1 b 1 0 B } msg] $msg
+} {1 SQLITE_ERROR}
+do_test 2.2 {
+ list [catch { sqlite3_blob_open db "test.db2" t1 b 1 0 B } msg] $msg
+} {1 SQLITE_ERROR}
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-50854-53979 If the flags parameter is non-zero, then
+# the BLOB is opened for read and write access.
+#
+# EVIDENCE-OF: R-03922-41160 If the flags parameter is zero, the BLOB is
+# opened for read-only access.
+#
+foreach {tn iRow flags} {
+ 1 1 0
+ 2 2 1
+ 3 3 -1
+ 4 4 2147483647
+ 5 5 -2147483648
+} {
+ do_test 3.$tn.1 {
+ sqlite3_blob_open db main x1 c $iRow $flags B
+ set n [sqlite3_blob_bytes $B]
+ sqlite3_blob_read $B 0 $n
+ } [binary format ccc 0 1 $iRow]
+
+ if {$flags==0} {
+ # Blob was opened for read-only access - writing returns an error.
+ do_test 3.$tn.2 {
+ list [catch { sqlite3_blob_write $B 0 xxx 3 } msg] $msg
+ } {1 SQLITE_READONLY}
+
+ do_execsql_test 3.$tn.3 {
+ SELECT c FROM x1 WHERE a=$iRow;
+ } [binary format ccc 0 1 $iRow]
+ } else {
+ # Blob was opened for read/write access - writing succeeds
+ do_test 3.$tn.4 {
+ list [catch { sqlite3_blob_write $B 0 xxx 3 } msg] $msg
+ } {0 {}}
+
+ do_execsql_test 3.$tn.5 {
+ SELECT c FROM x1 WHERE a=$iRow;
+ } {xxx}
+ }
+
+ sqlite3_blob_close $B
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 4.0 {
+ CREATE TABLE t1(x, y);
+ INSERT INTO t1 VALUES('abcd', 152);
+ INSERT INTO t1 VALUES(NULL, X'00010203');
+ INSERT INTO t1 VALUES('', 154.2);
+
+ CREATE TABLE t2(x PRIMARY KEY, y) WITHOUT ROWID;
+ INSERT INTO t2 VALUES(1, 'blob');
+
+ CREATE TABLE t3(a PRIMARY KEY, b, c, d, e, f, UNIQUE(e, f));
+ INSERT INTO t3 VALUES('aaaa', 'bbbb', 'cccc', 'dddd', 'eeee', 'ffff');
+ CREATE INDEX t3b ON t3(b);
+
+ CREATE TABLE p1(x PRIMARY KEY);
+ INSERT INTO p1 VALUES('abc');
+
+ CREATE TABLE c1(a INTEGER PRIMARY KEY, b REFERENCES p1);
+ INSERT INTO c1 VALUES(45, 'abc');
+}
+
+proc test_blob_open {tn zDb zTab zCol iRow flags errcode errmsg} {
+ global B
+ set B "0x1234"
+
+ if {$errcode=="SQLITE_OK"} {
+ set expected "0 {}"
+ } else {
+ set expected "1 $errcode"
+ }
+
+ set ::res [list [
+ catch { sqlite3_blob_open db $zDb $zTab $zCol $iRow $flags B } msg
+ ] $msg]
+ do_test 4.$tn.1 { set ::res } $expected
+
+ # EVIDENCE-OF: R-08940-21305 Unless it returns SQLITE_MISUSE, this
+ # function sets the database connection error code and message
+ # accessible via sqlite3_errcode() and sqlite3_errmsg() and related
+ # functions.
+ #
+ # This proc (test_blob_open) is used below to test various error and
+ # non-error conditions. But never SQLITE_MISUSE conditions. So these
+ # test cases are considered as partly verifying the requirement above.
+ # See below for a test of the SQLITE_MISUSE case.
+ #
+ do_test 4.$tn.2 {
+ sqlite3_errcode db
+ } $errcode
+ do_test 4.$tn.3 {
+ sqlite3_errmsg db
+ } $errmsg
+
+ # EVIDENCE-OF: R-31086-35521 On success, SQLITE_OK is returned and the
+ # new BLOB handle is stored in *ppBlob. Otherwise an error code is
+ # returned and, unless the error code is SQLITE_MISUSE, *ppBlob is set
+ # to NULL.
+ #
+ do_test 4.$tn.4 {
+ expr {$B == "0"}
+ } [expr {$errcode != "SQLITE_OK"}]
+
+ # EVIDENCE-OF: R-63421-15521 This means that, provided the API is not
+ # misused, it is always safe to call sqlite3_blob_close() on *ppBlob
+ # after this function it returns.
+ do_test 4.$tn.5 {
+ sqlite3_blob_close $B
+ } {}
+}
+
+# EVIDENCE-OF: R-31204-44780 Database zDb does not exist
+test_blob_open 1 nosuchdb t1 x 1 0 SQLITE_ERROR "no such table: nosuchdb.t1"
+
+# EVIDENCE-OF: R-28676-08005 Table zTable does not exist within database zDb
+test_blob_open 2 main tt1 x 1 0 SQLITE_ERROR "no such table: main.tt1"
+
+# EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
+test_blob_open 3 main t2 y 1 0 SQLITE_ERROR \
+ "cannot open table without rowid: t2"
+
+# EVIDENCE-OF: R-56376-21261 Column zColumn does not exist
+test_blob_open 4 main t1 z 2 0 SQLITE_ERROR "no such column: \"z\""
+
+# EVIDENCE-OF: R-28258-23166 Row iRow is not present in the table
+test_blob_open 5 main t1 y 6 0 SQLITE_ERROR "no such rowid: 6"
+
+# EVIDENCE-OF: R-11683-62380 The specified column of row iRow contains a
+# value that is not a TEXT or BLOB value
+test_blob_open 6 main t1 x 2 0 SQLITE_ERROR "cannot open value of type null"
+test_blob_open 7 main t1 y 1 0 SQLITE_ERROR "cannot open value of type integer"
+test_blob_open 8 main t1 y 3 0 SQLITE_ERROR "cannot open value of type real"
+
+# EVIDENCE-OF: R-34146-30782 Column zColumn is part of an index, PRIMARY
+# KEY or UNIQUE constraint and the blob is being opened for read/write
+# access
+#
+# Test cases 8.1.* show that such columns can be opened for read-access.
+# Tests 8.2.* show that read-write access is different. Columns "c" and "c"
+# are not part of an index, PK or UNIQUE constraint, so they work in both
+# cases.
+#
+test_blob_open 8.1.1 main t3 a 1 0 SQLITE_OK "not an error"
+test_blob_open 8.1.2 main t3 b 1 0 SQLITE_OK "not an error"
+test_blob_open 8.1.3 main t3 c 1 0 SQLITE_OK "not an error"
+test_blob_open 8.1.4 main t3 d 1 0 SQLITE_OK "not an error"
+test_blob_open 8.1.5 main t3 e 1 0 SQLITE_OK "not an error"
+test_blob_open 8.1.6 main t3 f 1 0 SQLITE_OK "not an error"
+
+set cannot "cannot open indexed column for writing"
+test_blob_open 8.2.1 main t3 a 1 8 SQLITE_ERROR $cannot
+test_blob_open 8.2.2 main t3 b 1 8 SQLITE_ERROR $cannot
+test_blob_open 8.2.3 main t3 c 1 8 SQLITE_OK "not an error"
+test_blob_open 8.2.4 main t3 d 1 8 SQLITE_OK "not an error"
+test_blob_open 8.2.5 main t3 e 1 8 SQLITE_ERROR $cannot
+test_blob_open 8.2.6 main t3 f 1 8 SQLITE_ERROR $cannot
+
+# EVIDENCE-OF: R-50117-55204 Foreign key constraints are enabled, column
+# zColumn is part of a child key definition and the blob is being opened
+# for read/write access
+#
+# 9.1: FK disabled, read-only access.
+# 9.2: FK disabled, read-only access.
+# 9.3: FK enabled, read/write access.
+# 9.4: FK enabled, read/write access.
+#
+test_blob_open 9.1 main c1 b 45 0 SQLITE_OK "not an error"
+test_blob_open 9.2 main c1 b 45 1 SQLITE_OK "not an error"
+execsql { PRAGMA foreign_keys = ON }
+test_blob_open 9.3 main c1 b 45 0 SQLITE_OK "not an error"
+test_blob_open 9.4 main c1 b 45 1 SQLITE_ERROR \
+ "cannot open foreign key column for writing"
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-08940-21305 Unless it returns SQLITE_MISUSE, this
+# function sets the database connection error code and message
+# accessible via sqlite3_errcode() and sqlite3_errmsg() and related
+# functions.
+#
+# This requirement is partially verified by the many uses of test
+# command [test_blob_open] above. All that is left is to verify the
+# SQLITE_MISUSE case.
+#
+# SQLITE_MISUSE is only returned if SQLITE_ENABLE_API_ARMOR is defined
+# during compilation.
+#
+ifcapable api_armor {
+ sqlite3_blob_open db main t1 x 1 0 B
+
+ do_test 10.1.1 {
+ list [catch {sqlite3_blob_open $B main t1 x 1 0 B2} msg] $msg
+ } {1 SQLITE_MISUSE}
+ do_test 10.1.2 {
+ list [sqlite3_errcode db] [sqlite3_errmsg db]
+ } {SQLITE_OK {not an error}}
+ sqlite3_blob_close $B
+
+ do_test 10.2.1 {
+ list [catch {sqlite3_blob_open db main {} x 1 0 B} msg] $msg
+ } {1 SQLITE_MISUSE}
+ do_test 10.2.2 {
+ list [sqlite3_errcode db] [sqlite3_errmsg db]
+ } {SQLITE_OK {not an error}}
+}
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-50542-62589 If the row that a BLOB handle points to is
+# modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the
+# BLOB handle is marked as "expired". This is true if any column of the
+# row is changed, even a column other than the one the BLOB handle is
+# open on.
+#
+# EVIDENCE-OF: R-48367-20048 Calls to sqlite3_blob_read() and
+# sqlite3_blob_write() for an expired BLOB handle fail with a return
+# code of SQLITE_ABORT.
+#
+# 11.2: read-only handle, DELETE.
+# 11.3: read-only handle, UPDATE.
+# 11.4: read-only handle, REPLACE.
+# 11.5: read/write handle, DELETE.
+# 11.6: read/write handle, UPDATE.
+# 11.7: read/write handle, REPLACE.
+#
+do_execsql_test 11.1 {
+ CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c UNIQUE);
+ INSERT INTO b1 VALUES(1, '1234567890', 1);
+ INSERT INTO b1 VALUES(2, '1234567890', 2);
+ INSERT INTO b1 VALUES(3, '1234567890', 3);
+ INSERT INTO b1 VALUES(4, '1234567890', 4);
+ INSERT INTO b1 VALUES(5, '1234567890', 5);
+ INSERT INTO b1 VALUES(6, '1234567890', 6);
+
+ CREATE TABLE b2(a INTEGER PRIMARY KEY, b, c UNIQUE);
+ INSERT INTO b2 VALUES(1, '1234567890', 1);
+ INSERT INTO b2 VALUES(2, '1234567890', 2);
+ INSERT INTO b2 VALUES(3, '1234567890', 3);
+ INSERT INTO b2 VALUES(4, '1234567890', 4);
+ INSERT INTO b2 VALUES(5, '1234567890', 5);
+ INSERT INTO b2 VALUES(6, '1234567890', 6);
+}
+
+do_test 11.2.1 {
+ sqlite3_blob_open db main b1 b 2 0 B
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.2.2 {
+ # Deleting a different row does not invalidate the blob handle.
+ execsql { DELETE FROM b1 WHERE a = 1 }
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.2.3 {
+ execsql { DELETE FROM b1 WHERE a = 2 }
+ list [catch { sqlite3_blob_read $B 0 10 } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.2.4 {
+ sqlite3_blob_close $B
+} {}
+
+do_test 11.3.1 {
+ sqlite3_blob_open db main b1 b 3 0 B
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.3.2 {
+ # Updating a different row
+ execsql { UPDATE b1 SET c = 42 WHERE a=4 }
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.3.3 {
+ execsql { UPDATE b1 SET c = 43 WHERE a=3 }
+ list [catch { sqlite3_blob_read $B 0 10 } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.3.4 {
+ sqlite3_blob_close $B
+} {}
+
+do_test 11.4.1 {
+ sqlite3_blob_open db main b1 b 6 0 B
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.4.2 {
+ # Replace a different row
+ execsql { INSERT OR REPLACE INTO b1 VALUES(10, 'abcdefghij', 5) }
+ sqlite3_blob_read $B 0 10
+} {1234567890}
+do_test 11.4.3 {
+ execsql { INSERT OR REPLACE INTO b1 VALUES(11, 'abcdefghij', 6) }
+ list [catch { sqlite3_blob_read $B 0 10 } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.4.4 {
+ sqlite3_blob_close $B
+} {}
+
+do_test 11.4.1 {
+ sqlite3_blob_open db main b2 b 2 1 B
+ sqlite3_blob_write $B 0 "abcdefghij"
+} {}
+do_test 11.4.2 {
+ # Deleting a different row does not invalidate the blob handle.
+ execsql { DELETE FROM b2 WHERE a = 1 }
+ sqlite3_blob_write $B 0 "ABCDEFGHIJ"
+} {}
+do_test 11.4.3 {
+ execsql { DELETE FROM b2 WHERE a = 2 }
+ list [catch { sqlite3_blob_write $B 0 "0987654321" } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.4.4 {
+ sqlite3_blob_close $B
+} {}
+
+do_test 11.5.1 {
+ sqlite3_blob_open db main b2 b 3 1 B
+ sqlite3_blob_write $B 0 "abcdefghij"
+} {}
+do_test 11.5.2 {
+ # Updating a different row
+ execsql { UPDATE b2 SET c = 42 WHERE a=4 }
+ sqlite3_blob_write $B 0 "ABCDEFGHIJ"
+} {}
+do_test 11.5.3 {
+ execsql { UPDATE b2 SET c = 43 WHERE a=3 }
+ list [catch { sqlite3_blob_write $B 0 "0987654321" } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.5.4 {
+ sqlite3_blob_close $B
+} {}
+
+do_test 11.6.1 {
+ sqlite3_blob_open db main b2 b 6 1 B
+ sqlite3_blob_write $B 0 "abcdefghij"
+} {}
+do_test 11.6.2 {
+ # Replace a different row
+ execsql { INSERT OR REPLACE INTO b2 VALUES(10, 'abcdefghij', 5) }
+ sqlite3_blob_write $B 0 "ABCDEFGHIJ"
+} {}
+do_test 11.6.3 {
+ execsql { INSERT OR REPLACE INTO b2 VALUES(11, 'abcdefghij', 6) }
+ list [catch { sqlite3_blob_write $B 0 "0987654321" } msg] $msg
+} {1 SQLITE_ABORT}
+do_test 11.6.4 {
+ sqlite3_blob_close $B
+} {}
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-45408-40694 Changes written into a BLOB prior to the
+# BLOB expiring are not rolled back by the expiration of the BLOB. Such
+# changes will eventually commit if the transaction continues to
+# completion.
+#
+do_execsql_test 12.1 {
+ CREATE TABLE b3(x INTEGER PRIMARY KEY, y TEXT, z INTEGER);
+ INSERT INTO b3 VALUES(22, '..........', NULL);
+}
+do_test 12.2 {
+ sqlite3_blob_open db main b3 y 22 1 B
+ sqlite3_blob_write $B 0 "xxxxx" 5
+} {}
+do_execsql_test 12.3 {
+ UPDATE b3 SET z = 'not null';
+}
+do_test 12.4 {
+ list [catch {sqlite3_blob_write $B 5 "xxxxx" 5} msg] $msg
+} {1 SQLITE_ABORT}
+do_execsql_test 12.5 {
+ SELECT * FROM b3;
+} {22 xxxxx..... {not null}}
+do_test 12.5 {
+ sqlite3_blob_close $B
+} {}
+do_execsql_test 12.6 {
+ SELECT * FROM b3;
+} {22 xxxxx..... {not null}}
+
+#-------------------------------------------------------------------------
+# EVIDENCE-OF: R-58813-55036 The sqlite3_bind_zeroblob() and
+# sqlite3_result_zeroblob() interfaces and the built-in zeroblob SQL
+# function may be used to create a zero-filled blob to read or write
+# using the incremental-blob interface.
+#
+do_execsql_test 13.1 {
+ CREATE TABLE c2(i INTEGER PRIMARY KEY, j);
+ INSERT INTO c2 VALUES(10, zeroblob(24));
+}
+
+do_test 13.2 {
+ set stmt [sqlite3_prepare_v2 db "INSERT INTO c2 VALUES(11, ?)" -1]
+ sqlite3_bind_zeroblob $stmt 1 45
+ sqlite3_step $stmt
+ sqlite3_finalize $stmt
+} {SQLITE_OK}
+
+# The blobs can be read:
+#
+do_test 13.3.1 {
+ sqlite3_blob_open db main c2 j 10 1 B
+ sqlite3_blob_open db main c2 j 11 1 B2
+ list [sqlite3_blob_bytes $B] [sqlite3_blob_bytes $B2]
+} {24 45}
+do_test 13.3.2 {
+ sqlite3_blob_read $B 0 24
+} [string repeat [binary format c 0] 24]
+do_test 13.3.3 {
+ sqlite3_blob_read $B2 0 45
+} [string repeat [binary format c 0] 45]
+
+# And also written:
+#
+do_test 13.4.1 {
+ sqlite3_blob_write $B 0 [string repeat [binary format c 1] 24]
+} {}
+do_test 13.4.2 {
+ sqlite3_blob_write $B2 0 [string repeat [binary format c 1] 45]
+} {}
+do_test 13.5 {
+ sqlite3_blob_close $B
+ sqlite3_blob_close $B2
+ execsql { SELECT j FROM c2 }
+} [list \
+ [string repeat [binary format c 1] 24] \
+ [string repeat [binary format c 1] 45] \
+]
+
+
+finish_test
+
diff --git a/test/e_blobwrite.test b/test/e_blobwrite.test
new file mode 100644
index 000000000..a0d33336d
--- /dev/null
+++ b/test/e_blobwrite.test
@@ -0,0 +1,204 @@
+# 2014 October 30
+#
+# 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 e_blobwrite
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-62898-22698 This function is used to write data into an
+# open BLOB handle from a caller-supplied buffer. N bytes of data are
+# copied from the buffer Z into the open BLOB, starting at offset
+# iOffset.
+#
+set dots [string repeat . 40]
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, t TEXT);
+ INSERT INTO t1 VALUES(-1, $dots);
+ INSERT INTO t1 VALUES(-2, $dots);
+ INSERT INTO t1 VALUES(-3, $dots);
+ INSERT INTO t1 VALUES(-4, $dots);
+ INSERT INTO t1 VALUES(-5, $dots);
+ INSERT INTO t1 VALUES(-6, $dots);
+}
+
+proc blob_write_test {tn id iOffset blob nData final} {
+ sqlite3_blob_open db main t1 t $id 1 B
+
+ # EVIDENCE-OF: R-45864-01884 On success, sqlite3_blob_write() returns
+ # SQLITE_OK. Otherwise, an error code or an extended error code is
+ # returned.
+ #
+ # This block tests the SQLITE_OK case in the requirement above (the
+ # Tcl sqlite3_blob_write() wrapper uses an empty string in place of
+ # "SQLITE_OK"). The error cases are tested by the "blob_write_error_test"
+ # tests below.
+ #
+ set res [sqlite3_blob_write $B $iOffset $blob $nData]
+ uplevel [list do_test $tn.1 [list set {} $res] {}]
+
+ sqlite3_blob_close $B
+ uplevel [list do_execsql_test $tn.3 "SELECT t FROM t1 WHERE a=$id" $final]
+}
+
+set blob "0123456789012345678901234567890123456789"
+blob_write_test 1.1 -1 0 $blob 10 { 0123456789.............................. }
+blob_write_test 1.2 -2 8 $blob 10 { ........0123456789...................... }
+blob_write_test 1.3 -3 8 $blob 1 { ........0............................... }
+blob_write_test 1.4 -4 18 $blob 22 { ..................0123456789012345678901 }
+blob_write_test 1.5 -5 18 $blob 0 { ........................................ }
+blob_write_test 1.6 -6 0 $blob 40 { 0123456789012345678901234567890123456789 }
+
+
+proc blob_write_error_test {tn B iOffset blob nData errcode errmsg} {
+
+ # In cases where the underlying sqlite3_blob_write() function returns
+ # SQLITE_OK, the Tcl wrapper returns an empty string. If the underlying
+ # function returns an error, the Tcl wrapper throws an exception with
+ # the error code as the Tcl exception message.
+ #
+ if {$errcode=="SQLITE_OK"} {
+ set ret ""
+ set isError 0
+ } else {
+ set ret $errcode
+ set isError 1
+ }
+
+ set cmd [list sqlite3_blob_write $B $iOffset $blob $nData]
+ uplevel [list do_test $tn.1 [subst -nocommands {
+ list [catch {$cmd} msg] [set msg]
+ }] [list $isError $ret]]
+
+ # EVIDENCE-OF: R-34782-18311 Unless SQLITE_MISUSE is returned, this
+ # function sets the database connection error code and message
+ # accessible via sqlite3_errcode() and sqlite3_errmsg() and related
+ # functions.
+ #
+ if {$errcode == "SQLITE_MISUSE"} { error "test proc misuse!" }
+ uplevel [list do_test $tn.2 [list sqlite3_errcode db] $errcode]
+ uplevel [list do_test $tn.3 [list sqlite3_errmsg db] $errmsg]
+}
+
+do_execsql_test 2.0 {
+ CREATE TABLE t2(a TEXT, b INTEGER PRIMARY KEY);
+ INSERT INTO t2 VALUES($dots, 43);
+ INSERT INTO t2 VALUES($dots, 44);
+ INSERT INTO t2 VALUES($dots, 45);
+}
+
+# EVIDENCE-OF: R-63341-57517 If the BLOB handle passed as the first
+# argument was not opened for writing (the flags parameter to
+# sqlite3_blob_open() was zero), this function returns SQLITE_READONLY.
+#
+sqlite3_blob_open db main t2 a 43 0 B
+blob_write_error_test 2.1 $B 0 $blob 10 \
+ SQLITE_READONLY {attempt to write a readonly database}
+sqlite3_blob_close $B
+
+# EVIDENCE-OF: R-29804-27366 If offset iOffset is less than N bytes from
+# the end of the BLOB, SQLITE_ERROR is returned and no data is written.
+#
+sqlite3_blob_open db main t2 a 44 3 B
+blob_write_error_test 2.2.1 $B 31 $blob 10 \
+ SQLITE_ERROR {SQL logic error or missing database}
+
+# Make a successful write to the blob handle. This shows that the
+# sqlite3_errcode() and sqlite3_errmsg() values are set even if the
+# blob_write() call succeeds (see requirement in the [blob_write_error_test]
+# proc).
+blob_write_error_test 2.2.1 $B 30 $blob 10 SQLITE_OK {not an error}
+
+# EVIDENCE-OF: R-58570-38916 If N or iOffset are less than zero
+# SQLITE_ERROR is returned and no data is written.
+#
+blob_write_error_test 2.2.2 $B 31 $blob -1 \
+ SQLITE_ERROR {SQL logic error or missing database}
+blob_write_error_test 2.2.3 $B 20 $blob 10 SQLITE_OK {not an error}
+blob_write_error_test 2.2.4 $B -1 $blob 10 \
+ SQLITE_ERROR {SQL logic error or missing database}
+sqlite3_blob_close $B
+
+# EVIDENCE-OF: R-20958-54138 An attempt to write to an expired BLOB
+# handle fails with an error code of SQLITE_ABORT.
+#
+do_test 2.3 {
+ sqlite3_blob_open db main t2 a 43 0 B
+ execsql { DELETE FROM t2 WHERE b=43 }
+} {}
+blob_write_error_test 2.3.1 $B 5 $blob 5 \
+ SQLITE_ABORT {callback requested query abort}
+do_test 2.3.2 {
+ execsql { SELECT 1, 2, 3 }
+ sqlite3_errcode db
+} {SQLITE_OK}
+blob_write_error_test 2.3.3 $B 5 $blob 5 \
+ SQLITE_ABORT {callback requested query abort}
+sqlite3_blob_close $B
+
+# EVIDENCE-OF: R-08382-59936 Writes to the BLOB that occurred before the
+# BLOB handle expired are not rolled back by the expiration of the
+# handle, though of course those changes might have been overwritten by
+# the statement that expired the BLOB handle or by other independent
+# statements.
+#
+# 3.1.*: not rolled back,
+# 3.2.*: overwritten.
+#
+do_execsql_test 3.0 {
+ CREATE TABLE t3(i INTEGER PRIMARY KEY, j TEXT, k TEXT);
+ INSERT INTO t3 VALUES(1, $dots, $dots);
+ INSERT INTO t3 VALUES(2, $dots, $dots);
+ SELECT * FROM t3 WHERE i=1;
+} {
+ 1
+ ........................................
+ ........................................
+}
+sqlite3_blob_open db main t3 j 1 1 B
+blob_write_error_test 3.1.1 $B 5 $blob 10 SQLITE_OK {not an error}
+do_execsql_test 3.1.2 {
+ UPDATE t3 SET k = 'xyz' WHERE i=1;
+ SELECT * FROM t3 WHERE i=1;
+} {
+ 1 .....0123456789......................... xyz
+}
+blob_write_error_test 3.1.3 $B 15 $blob 10 \
+ SQLITE_ABORT {callback requested query abort}
+sqlite3_blob_close $B
+do_execsql_test 3.1.4 {
+ SELECT * FROM t3 WHERE i=1;
+} {
+ 1 .....0123456789......................... xyz
+}
+
+sqlite3_blob_open db main t3 j 2 1 B
+blob_write_error_test 3.2.1 $B 5 $blob 10 SQLITE_OK {not an error}
+do_execsql_test 3.2.2 {
+ UPDATE t3 SET j = 'xyz' WHERE i=2;
+ SELECT * FROM t3 WHERE i=2;
+} {
+ 2 xyz ........................................
+}
+blob_write_error_test 3.2.3 $B 15 $blob 10 \
+ SQLITE_ABORT {callback requested query abort}
+sqlite3_blob_close $B
+do_execsql_test 3.2.4 {
+ SELECT * FROM t3 WHERE i=2;
+} {
+ 2 xyz ........................................
+}
+
+
+
+finish_test
+
diff --git a/test/e_changes.test b/test/e_changes.test
new file mode 100644
index 000000000..a77e22a2e
--- /dev/null
+++ b/test/e_changes.test
@@ -0,0 +1,441 @@
+# 2011 October 28
+#
+# 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 e_changes
+
+# Like [do_execsql_test], except it appends the value returned by
+# [db changes] to the result of executing the SQL script.
+#
+proc do_changes_test {tn sql res} {
+ uplevel [list \
+ do_test $tn "concat \[execsql {$sql}\] \[db changes\]" $res
+ ]
+}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-15996-49369 This function returns the number of rows
+# modified, inserted or deleted by the most recently completed INSERT,
+# UPDATE or DELETE statement on the database connection specified by the
+# only parameter.
+#
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
+ CREATE INDEX i1 ON t1(a);
+ CREATE INDEX i2 ON t2(y);
+}
+foreach {tn schema} {
+ 1 {
+ CREATE TABLE t1(a, b);
+ CREATE INDEX i1 ON t1(b);
+ }
+ 2 {
+ CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID;
+ CREATE INDEX i1 ON t1(b);
+ }
+} {
+ reset_db
+ execsql $schema
+
+ # Insert 1 row.
+ do_changes_test 1.$tn.1 { INSERT INTO t1 VALUES(0, 0) } 1
+
+ # Insert 10 rows.
+ do_changes_test 1.$tn.2 {
+ WITH rows(i, j) AS (
+ SELECT 1, 1 UNION ALL SELECT i+1, j+i FROM rows WHERE i<10
+ )
+ INSERT INTO t1 SELECT * FROM rows
+ } 10
+
+ # Modify 5 rows.
+ do_changes_test 1.$tn.3 {
+ UPDATE t1 SET b=b+1 WHERE a<5;
+ } 5
+
+ # Delete 4 rows
+ do_changes_test 1.$tn.4 {
+ DELETE FROM t1 WHERE a>6
+ } 4
+
+ # Check the "on the database connecton specified" part of hte
+ # requirement - changes made by other connections do not show up in
+ # the return value of sqlite3_changes().
+ do_test 1.$tn.5 {
+ sqlite3 db2 test.db
+ execsql { INSERT INTO t1 VALUES(-1, -1) } db2
+ db2 changes
+ } 1
+ do_test 1.$tn.6 {
+ db changes
+ } 4
+ db2 close
+
+ # Test that statements that modify no rows because they hit UNIQUE
+ # constraints set the sqlite3_changes() value to 0. Regardless of
+ # whether or not they are executed inside an explicit transaction.
+ #
+ # 1.$tn.8-9: outside of a transaction
+ # 1.$tn.10-12: inside a transaction
+ #
+ do_changes_test 1.$tn.7 {
+ CREATE UNIQUE INDEX i2 ON t1(a);
+ } 4
+ do_catchsql_test 1.$tn.8 {
+ INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11);
+ } {1 {UNIQUE constraint failed: t1.a}}
+ do_test 1.$tn.9 { db changes } 0
+ do_catchsql_test 1.$tn.10 {
+ BEGIN;
+ INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11);
+ } {1 {UNIQUE constraint failed: t1.a}}
+ do_test 1.$tn.11 { db changes } 0
+ do_changes_test 1.$tn.12 COMMIT 0
+
+}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-44877-05564 Executing any other type of SQL statement
+# does not modify the value returned by this function.
+#
+reset_db
+do_changes_test 2.1 { CREATE TABLE t1(x) } 0
+do_changes_test 2.2 {
+ WITH d(y) AS (SELECT 1 UNION ALL SELECT y+1 FROM d WHERE y<47)
+ INSERT INTO t1 SELECT y FROM d;
+} 47
+
+# The statement above set changes() to 47. Check that none of the following
+# modify this.
+do_changes_test 2.3 { SELECT count(x) FROM t1 } {47 47}
+do_changes_test 2.4 { DROP TABLE t1 } 47
+do_changes_test 2.5 { CREATE TABLE t1(x) } 47
+do_changes_test 2.6 { ALTER TABLE t1 ADD COLUMN b } 47
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-53938-27527 Only changes made directly by the INSERT,
+# UPDATE or DELETE statement are considered - auxiliary changes caused
+# by triggers, foreign key actions or REPLACE constraint resolution are
+# not counted.
+#
+# 3.1.*: triggers
+# 3.2.*: foreign key actions
+# 3.3.*: replace constraints
+#
+reset_db
+do_execsql_test 3.1.0 {
+ CREATE TABLE log(x);
+ CREATE TABLE p1(one PRIMARY KEY, two);
+
+ CREATE TRIGGER tr_ai AFTER INSERT ON p1 BEGIN
+ INSERT INTO log VALUES('insert');
+ END;
+ CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN
+ INSERT INTO log VALUES('delete');
+ END;
+ CREATE TRIGGER tr_au AFTER UPDATE ON p1 BEGIN
+ INSERT INTO log VALUES('update');
+ END;
+
+}
+
+do_changes_test 3.1.1 {
+ INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');
+} 3
+do_changes_test 3.1.2 {
+ UPDATE p1 SET two = two||two;
+} 3
+do_changes_test 3.1.3 {
+ DELETE FROM p1 WHERE one IN ('a', 'c');
+} 2
+do_execsql_test 3.1.4 {
+ -- None of the inserts on table log were counted.
+ SELECT count(*) FROM log
+} 8
+
+do_execsql_test 3.2.0 {
+ DELETE FROM p1;
+ INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');
+
+ CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
+ CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
+ CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
+ INSERT INTO c1 VALUES('a', 'aaa');
+ INSERT INTO c2 VALUES('b', 'bbb');
+ INSERT INTO c3 VALUES('c', 'ccc');
+
+ INSERT INTO p1 VALUES('d', 'D'), ('e', 'E'), ('f', 'F');
+ CREATE TABLE c4(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
+ CREATE TABLE c5(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
+ CREATE TABLE c6(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
+ INSERT INTO c4 VALUES('d', 'aaa');
+ INSERT INTO c5 VALUES('e', 'bbb');
+ INSERT INTO c6 VALUES('f', 'ccc');
+
+ PRAGMA foreign_keys = ON;
+}
+
+do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1
+do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1
+do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1
+do_execsql_test 3.2.4 {
+ SELECT * FROM c1;
+ SELECT * FROM c2;
+ SELECT * FROM c3;
+} {{} aaa {} bbb}
+
+do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1
+do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1
+do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1
+do_execsql_test 3.2.8 {
+ SELECT * FROM c4;
+ SELECT * FROM c5;
+ SELECT * FROM c6;
+} {{} aaa {} bbb i ccc}
+
+do_execsql_test 3.3.0 {
+ CREATE TABLE r1(a UNIQUE, b UNIQUE);
+ INSERT INTO r1 VALUES('i', 'i');
+ INSERT INTO r1 VALUES('ii', 'ii');
+ INSERT INTO r1 VALUES('iii', 'iii');
+ INSERT INTO r1 VALUES('iv', 'iv');
+ INSERT INTO r1 VALUES('v', 'v');
+ INSERT INTO r1 VALUES('vi', 'vi');
+ INSERT INTO r1 VALUES('vii', 'vii');
+}
+
+do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1) } 1
+do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') } 1
+do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' } 1
+do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1
+do_execsql_test 3.3.5 {
+ SELECT * FROM r1 ORDER BY a;
+} {i 1 iii v vii vi}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-09813-48563 The value returned by sqlite3_changes()
+# immediately after an INSERT, UPDATE or DELETE statement run on a view
+# is always zero.
+#
+reset_db
+do_execsql_test 4.1 {
+ CREATE TABLE log(log);
+ CREATE TABLE t1(x, y);
+ INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1 VALUES(3, 4);
+ INSERT INTO t1 VALUES(5, 6);
+
+ CREATE VIEW v1 AS SELECT * FROM t1;
+ CREATE TRIGGER v1_i INSTEAD OF INSERT ON v1 BEGIN
+ INSERT INTO log VALUES('insert');
+ END;
+ CREATE TRIGGER v1_u INSTEAD OF UPDATE ON v1 BEGIN
+ INSERT INTO log VALUES('update'), ('update');
+ END;
+ CREATE TRIGGER v1_d INSTEAD OF DELETE ON v1 BEGIN
+ INSERT INTO log VALUES('delete'), ('delete'), ('delete');
+ END;
+}
+
+do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 } 3
+do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) } 0
+
+do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 } 6
+do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 } 0
+
+do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12
+do_changes_test 4.4.2 { DELETE FROM v1 WHERE x=5 } 0
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-32918-61474 Before entering a trigger program the value
+# returned by sqlite3_changes() function is saved. After the trigger
+# program has finished, the original value is restored.
+#
+reset_db
+db func my_changes my_changes
+set ::changes [list]
+proc my_changes {x} {
+ set res [db changes]
+ lappend ::changes $x $res
+ return $res
+}
+
+do_execsql_test 5.1.0 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t2(x);
+ INSERT INTO t1 VALUES(1, NULL);
+ INSERT INTO t1 VALUES(2, NULL);
+ INSERT INTO t1 VALUES(3, NULL);
+ CREATE TRIGGER AFTER UPDATE ON t1 BEGIN
+ INSERT INTO t2 VALUES('a'), ('b'), ('c');
+ SELECT my_changes('trigger');
+ END;
+}
+
+do_execsql_test 5.1.1 {
+ INSERT INTO t2 VALUES('a'), ('b');
+ UPDATE t1 SET b = my_changes('update');
+ SELECT * FROM t1;
+} {1 2 2 2 3 2}
+
+# Value is being restored to "2" when the trigger program exits.
+do_test 5.1.2 {
+ set ::changes
+} {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3}
+
+
+reset_db
+do_execsql_test 5.2.0 {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE log(x);
+ INSERT INTO t1 VALUES(1, 0);
+ INSERT INTO t1 VALUES(2, 0);
+ INSERT INTO t1 VALUES(3, 0);
+ CREATE TRIGGER t1_a_u AFTER UPDATE ON t1 BEGIN
+ INSERT INTO log VALUES(old.b || ' -> ' || new.b || ' c = ' || changes() );
+ END;
+ CREATE TABLE t2(a);
+ INSERT INTO t2 VALUES(1), (2), (3);
+ UPDATE t1 SET b = changes();
+}
+do_execsql_test 5.2.1 {
+ SELECT * FROM t1;
+} {1 3 2 3 3 3}
+do_execsql_test 5.2.2 {
+ SELECT * FROM log;
+} {{0 -> 3 c = 3} {0 -> 3 c = 3} {0 -> 3 c = 3}}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-17146-37073 Within a trigger program each INSERT,
+# UPDATE and DELETE statement sets the value returned by
+# sqlite3_changes() upon completion as normal. Of course, this value
+# will not include any changes performed by sub-triggers, as the
+# sqlite3_changes() value will be saved and restored after each
+# sub-trigger has run.
+reset_db
+do_execsql_test 6.0 {
+
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(a, b);
+ CREATE TABLE t3(a, b);
+ CREATE TABLE log(x);
+
+ CREATE TRIGGER t1_i BEFORE INSERT ON t1 BEGIN
+ INSERT INTO t2 VALUES(new.a, new.b), (new.a, new.b);
+ INSERT INTO log VALUES('t2->' || changes());
+ END;
+
+ CREATE TRIGGER t2_i AFTER INSERT ON t2 BEGIN
+ INSERT INTO t3 VALUES(new.a, new.b), (new.a, new.b), (new.a, new.b);
+ INSERT INTO log VALUES('t3->' || changes());
+ END;
+
+ CREATE TRIGGER t1_u AFTER UPDATE ON t1 BEGIN
+ UPDATE t2 SET b=new.b WHERE a=old.a;
+ INSERT INTO log VALUES('t2->' || changes());
+ END;
+
+ CREATE TRIGGER t2_u BEFORE UPDATE ON t2 BEGIN
+ UPDATE t3 SET b=new.b WHERE a=old.a;
+ INSERT INTO log VALUES('t3->' || changes());
+ END;
+
+ CREATE TRIGGER t1_d AFTER DELETE ON t1 BEGIN
+ DELETE FROM t2 WHERE a=old.a AND b=old.b;
+ INSERT INTO log VALUES('t2->' || changes());
+ END;
+
+ CREATE TRIGGER t2_d BEFORE DELETE ON t2 BEGIN
+ DELETE FROM t3 WHERE a=old.a AND b=old.b;
+ INSERT INTO log VALUES('t3->' || changes());
+ END;
+}
+
+do_changes_test 6.1 {
+ INSERT INTO t1 VALUES('+', 'o');
+ SELECT * FROM log;
+} {t3->3 t3->3 t2->2 1}
+
+do_changes_test 6.2 {
+ DELETE FROM log;
+ UPDATE t1 SET b='*';
+ SELECT * FROM log;
+} {t3->6 t3->6 t2->2 1}
+
+do_changes_test 6.3 {
+ DELETE FROM log;
+ DELETE FROM t1;
+ SELECT * FROM log;
+} {t3->6 t3->0 t2->2 1}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-43399-09409 This means that if the changes() SQL
+# function (or similar) is used by the first INSERT, UPDATE or DELETE
+# statement within a trigger, it returns the value as set when the
+# calling statement began executing.
+#
+# EVIDENCE-OF: R-53215-27584 If it is used by the second or subsequent
+# such statement within a trigger program, the value returned reflects
+# the number of rows modified by the previous INSERT, UPDATE or DELETE
+# statement within the same trigger.
+#
+reset_db
+do_execsql_test 7.1 {
+ CREATE TABLE q1(t);
+ CREATE TABLE q2(u, v);
+ CREATE TABLE q3(w);
+
+ CREATE TRIGGER q2_insert BEFORE INSERT ON q2 BEGIN
+
+ /* changes() returns value from previous I/U/D in callers context */
+ INSERT INTO q1 VALUES('1:' || changes());
+
+ /* changes() returns value of previous I/U/D in this context */
+ INSERT INTO q3 VALUES(changes()), (2), (3);
+ INSERT INTO q1 VALUES('2:' || changes());
+ INSERT INTO q3 VALUES(changes() + 3), (changes()+4);
+ SELECT 'this does not affect things!';
+ INSERT INTO q1 VALUES('3:' || changes());
+ UPDATE q3 SET w = w+10 WHERE w%2;
+ INSERT INTO q1 VALUES('4:' || changes());
+ DELETE FROM q3;
+ INSERT INTO q1 VALUES('5:' || changes());
+ END;
+}
+
+do_execsql_test 7.2 {
+ INSERT INTO q2 VALUES('x', 'y');
+ SELECT * FROM q1;
+} {
+ 1:0 2:3 3:2 4:3 5:5
+}
+
+do_execsql_test 7.3 {
+ DELETE FROM q1;
+ INSERT INTO q2 VALUES('x', 'y');
+ SELECT * FROM q1;
+} {
+ 1:5 2:3 3:2 4:3 5:5
+}
+
+
+
+finish_test
diff --git a/test/e_totalchanges.test b/test/e_totalchanges.test
new file mode 100644
index 000000000..ee163c914
--- /dev/null
+++ b/test/e_totalchanges.test
@@ -0,0 +1,213 @@
+# 2011 May 06
+#
+# 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 e_totalchanges
+
+# Like [do_execsql_test], except it appends the value returned by
+# [db total_changes] to the result of executing the SQL script.
+#
+proc do_tc_test {tn sql res} {
+ uplevel [list \
+ do_test $tn "concat \[execsql {$sql}\] \[db total_changes\]" $res
+ ]
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ CREATE INDEX t1_b ON t1(b);
+ CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
+ CREATE INDEX t2_y ON t2(y);
+}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-65438-26258 This function returns the total number of
+# rows inserted, modified or deleted by all INSERT, UPDATE or DELETE
+# statements completed since the database connection was opened,
+# including those executed as part of trigger programs.
+#
+# 1.1.*: different types of I/U/D statements,
+# 1.2.*: trigger programs.
+#
+do_tc_test 1.1.1 {
+ INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1 VALUES(3, 4);
+ UPDATE t1 SET a = a+1;
+ DELETE FROM t1;
+} {6}
+do_tc_test 1.1.2 {
+ DELETE FROM t1
+} {6}
+
+do_tc_test 1.1.3 {
+ WITH data(a,b) AS (
+ SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<99
+ )
+ INSERT INTO t1 SELECT * FROM data;
+} {106}
+
+do_tc_test 1.1.4 {
+ INSERT INTO t2 SELECT * FROM t1 WHERE a<50;
+ UPDATE t2 SET y=y+1;
+} {206}
+
+do_tc_test 1.1.5 {
+ DELETE FROM t2 WHERE y<=25
+} {231}
+
+do_execsql_test 1.2.1 {
+ DELETE FROM t1;
+ DELETE FROM t2;
+}
+sqlite3 db test.db ; # To reset total_changes
+do_tc_test 1.2.2 {
+ CREATE TABLE log(detail);
+ CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN
+ INSERT INTO log VALUES('inserted into t1');
+ END;
+
+ CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN
+ INSERT INTO log VALUES('deleting from t1');
+ INSERT INTO log VALUES('here we go!');
+ END;
+
+ CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN
+ INSERT INTO log VALUES('update');
+ DELETE FROM log;
+ END;
+
+ INSERT INTO t1 VALUES('a', 'b'); -- 1 + 1
+ UPDATE t1 SET b='c'; -- 1 + 1 + 2
+ DELETE FROM t1; -- 1 + 1 + 1
+} {9}
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement
+# does not affect the value returned by sqlite3_total_changes().
+do_tc_test 2.1 {
+ INSERT INTO t1 VALUES(1, 2), (3, 4);
+ INSERT INTO t2 VALUES(1, 2), (3, 4);
+} {15}
+do_tc_test 2.2 {
+ SELECT count(*) FROM t1;
+} {2 15}
+do_tc_test 2.3 {
+ CREATE TABLE t4(a, b);
+ ALTER TABLE t4 ADD COLUMN c;
+ CREATE INDEX i4 ON t4(c);
+ ALTER TABLE t4 RENAME TO t5;
+ ANALYZE;
+ BEGIN;
+ DROP TABLE t2;
+ ROLLBACK;
+ VACUUM;
+} {15}
+
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key
+# actions are included in the count, but those made as part of REPLACE
+# constraint resolution are not.
+#
+# 3.1.*: foreign key actions
+# 3.2.*: REPLACE constraints.
+#
+sqlite3 db test.db ; # To reset total_changes
+do_tc_test 3.1.1 {
+ CREATE TABLE p1(c PRIMARY KEY, d);
+ CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
+ CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
+ CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
+
+ INSERT INTO p1 VALUES(1, 'one');
+ INSERT INTO p1 VALUES(2, 'two');
+ INSERT INTO p1 VALUES(3, 'three');
+ INSERT INTO p1 VALUES(4, 'four');
+
+ INSERT INTO c1 VALUES(1, 'i');
+ INSERT INTO c2 VALUES(2, 'ii');
+ INSERT INTO c3 VALUES(3, 'iii');
+ PRAGMA foreign_keys = ON;
+} {7}
+
+do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9}
+do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11}
+do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13}
+do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14} ; # only 1 this time.
+
+sqlite3 db test.db ; # To reset total_changes
+do_tc_test 3.1.6 {
+ DROP TABLE c1;
+ DROP TABLE c2;
+ DROP TABLE c3;
+ CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
+ CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
+ CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
+
+ INSERT INTO p1 VALUES(1, 'one');
+ INSERT INTO p1 VALUES(2, 'two');
+ INSERT INTO p1 VALUES(3, 'three');
+ INSERT INTO p1 VALUES(4, 'four');
+
+ INSERT INTO c1 VALUES(1, 'i');
+ INSERT INTO c2 VALUES(2, 'ii');
+ INSERT INTO c3 VALUES(3, 'iii');
+ PRAGMA foreign_keys = ON;
+} {7}
+
+do_tc_test 3.1.7 { UPDATE p1 SET c=c+4 WHERE c=1; } {9}
+do_tc_test 3.1.8 { UPDATE p1 SET c=c+4 WHERE c=2; } {11}
+do_tc_test 3.1.9 { UPDATE p1 SET c=c+4 WHERE c=3; } {13}
+do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14} ; # only 1 this time.
+
+sqlite3 db test.db ; # To reset total_changes
+do_tc_test 3.2.1 {
+ CREATE TABLE t3(a UNIQUE, b UNIQUE);
+ INSERT INTO t3 VALUES('one', 'one');
+ INSERT INTO t3 VALUES('two', 'two');
+ INSERT OR REPLACE INTO t3 VALUES('one', 'two');
+} {3}
+
+do_tc_test 3.2.2 {
+ INSERT INTO t3 VALUES('three', 'one');
+ UPDATE OR REPLACE t3 SET b='two' WHERE b='one';
+ SELECT * FROM t3;
+} {three two 5}
+
+#--------------------------------------------------------------------------
+# EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by
+# INSTEAD OF triggers are not counted.
+#
+sqlite3 db test.db ; # To reset total_changes
+do_tc_test 4.1 {
+ CREATE TABLE t6(x);
+ CREATE VIEW v1 AS SELECT * FROM t6;
+ CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN
+ SELECT 'no-op';
+ END;
+
+ INSERT INTO v1 VALUES('a');
+ INSERT INTO v1 VALUES('b');
+} {0}
+do_tc_test 4.2 {
+ CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN
+ INSERT INTO t6 VALUES(new.x);
+ END;
+
+ INSERT INTO v1 VALUES('c');
+ INSERT INTO v1 VALUES('d');
+} {2}
+
+
+finish_test
diff --git a/test/e_wal.test b/test/e_wal.test
new file mode 100644
index 000000000..a5e074f49
--- /dev/null
+++ b/test/e_wal.test
@@ -0,0 +1,229 @@
+# 2011 May 06
+#
+# 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 e_wal
+
+db close
+testvfs oldvfs -iversion 1
+
+
+# EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and
+# written even if shared memory is unavailable as long as the
+# locking_mode is set to EXCLUSIVE before the first attempted access.
+#
+# EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be
+# created, read, and written by legacy VFSes that lack the "version 2"
+# shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on
+# the sqlite3_io_methods object.
+#
+# 1.1: "create" tests.
+# 1.2: "read" tests.
+# 1.3: "write" tests.
+#
+# All three done with VFS "oldvfs", which has iVersion==1 and so does
+# not support shared memory.
+#
+sqlite3 db test.db -vfs oldvfs
+do_execsql_test 1.1.1 {
+ PRAGMA journal_mode = WAL;
+} {delete}
+do_execsql_test 1.1.2 {
+ PRAGMA locking_mode = EXCLUSIVE;
+ PRAGMA journal_mode = WAL;
+} {exclusive wal}
+do_execsql_test 1.1.3 {
+ CREATE TABLE t1(x, y);
+ INSERT INTO t1 VALUES(1, 2);
+} {}
+do_test 1.1.4 {
+ list [file exists test.db-shm] [file exists test.db-wal]
+} {0 1}
+
+do_test 1.2.1 {
+ db close
+ sqlite3 db test.db -vfs oldvfs
+ catchsql { SELECT * FROM t1 }
+} {1 {unable to open database file}}
+do_test 1.2.2 {
+ execsql { PRAGMA locking_mode = EXCLUSIVE }
+ execsql { SELECT * FROM t1 }
+} {1 2}
+do_test 1.2.3 {
+ list [file exists test.db-shm] [file exists test.db-wal]
+} {0 1}
+
+do_test 1.3.1 {
+ db close
+ sqlite3 db test.db -vfs oldvfs
+ catchsql { INSERT INTO t1 VALUES(3, 4) }
+} {1 {unable to open database file}}
+do_test 1.3.2 {
+ execsql { PRAGMA locking_mode = EXCLUSIVE }
+ execsql { INSERT INTO t1 VALUES(3, 4) }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 4}
+do_test 1.3.3 {
+ list [file exists test.db-shm] [file exists test.db-wal]
+} {0 1}
+
+# EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to
+# the first WAL-mode database access, then SQLite never attempts to call
+# any of the shared-memory methods and hence no shared-memory wal-index
+# is ever created.
+#
+db close
+sqlite3 db test.db
+do_execsql_test 2.1.1 {
+ PRAGMA locking_mode = EXCLUSIVE;
+ SELECT * FROM t1;
+} {exclusive 1 2 3 4}
+do_test 2.1.2 {
+ list [file exists test.db-shm] [file exists test.db-wal]
+} {0 1}
+
+# EVIDENCE-OF: R-36328-16367 In that case, the database connection
+# remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts
+# to change the locking mode using "PRAGMA locking_mode=NORMAL;" are
+# no-ops.
+#
+do_execsql_test 2.2.1 {
+ PRAGMA locking_mode = NORMAL;
+ SELECT * FROM t1;
+} {exclusive 1 2 3 4}
+do_test 2.2.2 {
+ sqlite3 db2 test.db
+ catchsql {SELECT * FROM t1} db2
+} {1 {database is locked}}
+db2 close
+
+# EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE
+# locking mode is to first change out of WAL journal mode.
+#
+do_execsql_test 2.3.1 {
+ PRAGMA journal_mode = DELETE;
+ SELECT * FROM t1;
+} {delete 1 2 3 4}
+do_test 2.3.2 {
+ sqlite3 db2 test.db
+ catchsql {SELECT * FROM t1} db2
+} {1 {database is locked}}
+do_execsql_test 2.3.3 {
+ PRAGMA locking_mode = NORMAL;
+ SELECT * FROM t1;
+} {normal 1 2 3 4}
+do_test 2.3.4 {
+ sqlite3 db2 test.db
+ catchsql {SELECT * FROM t1} db2
+} {0 {1 2 3 4}}
+db2 close
+db close
+
+
+# EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the
+# first WAL-mode database access, then the shared-memory wal-index is
+# created.
+#
+do_test 3.0 {
+ sqlite3 db test.db
+ execsql { PRAGMA journal_mode = WAL }
+ db close
+} {}
+do_test 3.1 {
+ sqlite3 db test.db
+ execsql { SELECT * FROM t1 }
+ list [file exists test.db-shm] [file exists test.db-wal]
+} {1 1}
+
+# EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using
+# a shared-memory wal-index, the locking mode can be changed freely
+# between NORMAL and EXCLUSIVE.
+#
+do_execsql_test 3.2.1 {
+ PRAGMA locking_mode = EXCLUSIVE;
+ PRAGMA locking_mode = NORMAL;
+ PRAGMA locking_mode = EXCLUSIVE;
+ INSERT INTO t1 VALUES(5, 6);
+} {exclusive normal exclusive}
+do_test 3.2.2 {
+ sqlite3 db2 test.db
+ catchsql { SELECT * FROM t1 } db2
+} {1 {database is locked}}
+
+# EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index
+# is omitted, when the locking mode is EXCLUSIVE prior to the first
+# WAL-mode database access, that the locking mode is stuck in EXCLUSIVE.
+#
+do_execsql_test 3.2.3 {
+ PRAGMA locking_mode = NORMAL;
+ SELECT * FROM t1;
+} {normal 1 2 3 4 5 6}
+do_test 3.2.4 {
+ catchsql { SELECT * FROM t1 } db2
+} {0 {1 2 3 4 5 6}}
+
+do_catchsql_test 3.2.5 {
+ PRAGMA locking_mode = EXCLUSIVE;
+ INSERT INTO t1 VALUES(7, 8);
+} {1 {database is locked}}
+
+db2 close
+
+# EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must
+# support the "version 2" shared-memory.
+#
+# EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory
+# methods, then the attempt to open a database that is already in WAL
+# mode, or the attempt convert a database into WAL mode, will fail.
+#
+db close
+do_test 3.4.1 {
+ sqlite3 db test.db -vfs oldvfs
+ catchsql { SELECT * FROM t1 }
+} {1 {unable to open database file}}
+db close
+do_test 3.4.2 {
+ forcedelete test.db2
+ sqlite3 db test.db2 -vfs oldvfs
+ catchsql { PRAGMA journal_mode = WAL }
+} {0 delete}
+db close
+
+
+# EVIDENCE-OF: R-22428-28959 To prevent older versions of SQLite from
+# trying to recover a WAL-mode database (and making matters worse) the
+# database file format version numbers (bytes 18 and 19 in the database
+# header) are increased from 1 to 2 in WAL mode.
+#
+reset_db
+do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) }
+do_test 4.1.2 { hexio_read test.db 18 2 } {0101}
+do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal}
+do_test 4.1.4 { hexio_read test.db 18 2 } {0202}
+
+
+# EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode
+# using a pragma such as this: PRAGMA journal_mode=DELETE;
+#
+do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {}
+do_test 4.2.2 { file exists test.db-wal } {1}
+do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete}
+do_test 4.2.4 { file exists test.db-wal } {0}
+
+# EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode
+# changes the database file format version numbers back to 1 so that
+# older versions of SQLite can once again access the database file.
+#
+do_test 4.3 { hexio_read test.db 18 2 } {0101}
+
+finish_test
diff --git a/test/fkey7.test b/test/fkey7.test
index c2682edbe..6c646a9a7 100644
--- a/test/fkey7.test
+++ b/test/fkey7.test
@@ -50,5 +50,22 @@ do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}
+ifcapable incrblob {
+ do_execsql_test 2.0 {
+ CREATE TABLE pX(x PRIMARY KEY);
+ CREATE TABLE cX(a INTEGER PRIMARY KEY, b REFERENCES pX);
+ }
+
+ do_catchsql_test 2.1 {
+ INSERT INTO cX VALUES(11, zeroblob(40));
+ } {1 {FOREIGN KEY constraint failed}}
+
+ do_test 2.2 {
+ set stmt [sqlite3_prepare_v2 db "INSERT INTO cX VALUES(11, ?)" -1]
+ sqlite3_bind_zeroblob $stmt 1 45
+ sqlite3_step $stmt
+ sqlite3_finalize $stmt
+ } {SQLITE_CONSTRAINT}
+}
finish_test
diff --git a/test/misc8.test b/test/misc8.test
new file mode 100644
index 000000000..3ff52e56f
--- /dev/null
+++ b/test/misc8.test
@@ -0,0 +1,98 @@
+# 2014-11-10
+#
+# 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 script is testing the "eval.c" loadable extension.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+load_static_extension db eval
+do_execsql_test misc8-1.0 {
+ CREATE TABLE t1(a,b,c);
+ INSERT INTO t1 VALUES(1,2,3),(4,5,6);
+ SELECT quote(eval('SELECT * FROM t1 ORDER BY a','-abc-'));
+} {'1-abc-2-abc-3-abc-4-abc-5-abc-6'}
+do_execsql_test misc8-1.1 {
+ SELECT quote(eval('SELECT * FROM t1 ORDER BY a'));
+} {{'1 2 3 4 5 6'}}
+do_catchsql_test misc8-1.2 {
+ SELECT quote(eval('SELECT d FROM t1 ORDER BY a'));
+} {1 {no such column: d}}
+do_execsql_test misc8-1.3 {
+ INSERT INTO t1 VALUES(7,null,9);
+ SELECT eval('SELECT * FROM t1 ORDER BY a',',');
+} {1,2,3,4,5,6,7,,9}
+do_catchsql_test misc8-1.4 {
+ BEGIN;
+ INSERT INTO t1 VALUES(10,11,12);
+ SELECT a, coalesce(b, eval('ROLLBACK; SELECT ''bam'';')), c
+ FROM t1 ORDER BY a;
+} {0 {1 2 3 4 5 6 7 bam 9}}
+do_catchsql_test misc8-1.5 {
+ INSERT INTO t1 VALUES(10,11,12);
+ SELECT a, coalesce(b, eval('SELECT ''bam''')), c
+ FROM t1
+ ORDER BY rowid;
+} {0 {1 2 3 4 5 6 7 bam 9 10 11 12}}
+do_catchsql_test misc8-1.6 {
+ SELECT a, coalesce(b, eval('DELETE FROM t1; SELECT ''bam''')), c
+ FROM t1
+ ORDER BY rowid;
+} {0 {1 2 3 4 5 6 7 bam {}}}
+do_catchsql_test misc8-1.7 {
+ INSERT INTO t1 VALUES(1,2,3),(4,5,6),(7,null,9);
+ BEGIN;
+ CREATE TABLE t2(x);
+ SELECT a, coalesce(b, eval('ROLLBACK; SELECT ''bam''')), c
+ FROM t1
+ ORDER BY rowid;
+} {1 {abort due to ROLLBACK}}
+
+
+reset_db
+
+proc dbeval {sql} { db eval $sql }
+db func eval dbeval
+
+do_execsql_test misc8-2.1 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER) WITHOUT ROWID;
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d INTEGER, x BLOB);
+ INSERT INTO t1 VALUES(0,0);
+ INSERT INTO t1 VALUES(10,10);
+ INSERT INTO t2 VALUES(1,1,zeroblob(200));
+ INSERT INTO t2 VALUES(2,2,zeroblob(200));
+ INSERT INTO t2 VALUES(3,3,zeroblob(200));
+ INSERT INTO t2 VALUES(4,4,zeroblob(200));
+ INSERT INTO t2 VALUES(5,5,zeroblob(200));
+ INSERT INTO t2 VALUES(6,6,zeroblob(200));
+ INSERT INTO t2 VALUES(7,7,zeroblob(200));
+ INSERT INTO t2 VALUES(8,8,zeroblob(200));
+ INSERT INTO t2 VALUES(9,9,zeroblob(200));
+ INSERT INTO t2 VALUES(10,10,zeroblob(200));
+ SELECT a, c, eval(
+ printf('DELETE FROM t2 WHERE c=%d AND %d>5', a+c, a+c)
+ ) FROM t1, t2;
+} {
+ 0 1 {} 10 1 {}
+ 0 2 {} 10 2 {}
+ 0 3 {} 10 3 {}
+ 0 4 {} 10 4 {}
+ 0 5 {} 10 5 {}
+ 0 6 {} 10 {} {}
+ 0 7 {} 10 {} {}
+ 0 8 {} 10 {} {}
+ 0 9 {} 10 {} {}
+ 0 10 {} 10 {} {}
+}
+
+
+finish_test
diff --git a/test/mmap1.test b/test/mmap1.test
index ece3e0201..18aec9f8f 100644
--- a/test/mmap1.test
+++ b/test/mmap1.test
@@ -33,7 +33,7 @@ proc register_rblob_code {dbname seed} {
set ::rcnt $seed
proc rblob {n} {
set ::rcnt [expr (([set ::rcnt] << 3) + [set ::rcnt] + 456) & 0xFFFFFFFF]
- set str [format %.8x [expr [set ::rcnt] ^ 0xbdf20da3]]
+ set str [format %.8x [expr [set ::rcnt] ^ 0xbdf20da3]]
string range [string repeat [set str] [expr [set n]/4]] 1 [set n]
}
$dbname func rblob rblob
@@ -42,7 +42,7 @@ proc register_rblob_code {dbname seed} {
# For cases 1.1 and 1.4, the number of pages read using xRead() is 4 on
# unix and 9 on windows. The difference is that windows only ever maps
-# an integer number of OS pages (i.e. creates mappings that are a multiple
+# an integer number of OS pages (i.e. creates mappings that are a multiple
# of 4KB in size). Whereas on unix any sized mapping may be created.
#
foreach {t mmap_size nRead c2init} {
@@ -106,50 +106,52 @@ foreach {t mmap_size nRead c2init} {
set ::rcnt 0
proc rblob {n} {
set ::rcnt [expr (($::rcnt << 3) + $::rcnt + 456) & 0xFFFFFFFF]
- set str [format %.8x [expr $::rcnt ^ 0xbdf20da3]]
+ set str [format %.8x [expr $::rcnt ^ 0xbdf20da3]]
string range [string repeat $str [expr $n/4]] 1 $n
}
reset_db
db func rblob rblob
-do_execsql_test 2.1 {
- PRAGMA auto_vacuum = 1;
- PRAGMA mmap_size = 67108864;
- PRAGMA journal_mode = wal;
- CREATE TABLE t1(a, b, UNIQUE(a, b));
- INSERT INTO t1 VALUES(rblob(500), rblob(500));
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 2
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 4
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 8
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 16
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 32
- PRAGMA wal_checkpoint;
-} {67108864 wal 0 103 103}
-
-do_execsql_test 2.2 {
- PRAGMA auto_vacuum;
- SELECT count(*) FROM t1;
-} {1 32}
-
-if {[permutation] != "inmemory_journal"} {
- do_test 2.3 {
- sqlite3 db2 test.db
- db2 func rblob rblob
- db2 eval {
- DELETE FROM t1 WHERE (rowid%4);
- PRAGMA wal_checkpoint;
- }
- db2 eval {
- INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 16
- SELECT count(*) FROM t1;
- }
- } {16}
-
- do_execsql_test 2.4 {
+ifcapable wal {
+ do_execsql_test 2.1 {
+ PRAGMA auto_vacuum = 1;
+ PRAGMA mmap_size = 67108864;
+ PRAGMA journal_mode = wal;
+ CREATE TABLE t1(a, b, UNIQUE(a, b));
+ INSERT INTO t1 VALUES(rblob(500), rblob(500));
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 2
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 4
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 8
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 16
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 32
PRAGMA wal_checkpoint;
- } {0 24 24}
- db2 close
+ } {67108864 wal 0 103 103}
+
+ do_execsql_test 2.2 {
+ PRAGMA auto_vacuum;
+ SELECT count(*) FROM t1;
+ } {1 32}
+
+ if {[permutation] != "inmemory_journal"} {
+ do_test 2.3 {
+ sqlite3 db2 test.db
+ db2 func rblob rblob
+ db2 eval {
+ DELETE FROM t1 WHERE (rowid%4);
+ PRAGMA wal_checkpoint;
+ }
+ db2 eval {
+ INSERT INTO t1 SELECT rblob(500), rblob(500) FROM t1; -- 16
+ SELECT count(*) FROM t1;
+ }
+ } {16}
+
+ do_execsql_test 2.4 {
+ PRAGMA wal_checkpoint;
+ } {0 24 24}
+ db2 close
+ }
}
reset_db
@@ -227,7 +229,7 @@ do_test 4.4 {
do_execsql_test 4.5 { COMMIT }
#-------------------------------------------------------------------------
-# Ensure that existing cursors holding xFetch() references are not
+# Ensure that existing cursors holding xFetch() references are not
# confused if those pages are moved to make way for the root page of a
# new table or index.
#
@@ -296,7 +298,7 @@ foreach {tn1 mmap1 mmap2} {
sql1 "PRAGMA mmap_size = $mmap1"
sql2 "PRAGMA mmap_size = $mmap2"
- do_test $tn1.$tn {
+ do_test $tn1.$tn {
for {set i 1} {$i <= 100} {incr i} {
if {$i % 2} {
set c1 sql1
@@ -311,7 +313,7 @@ foreach {tn1 mmap1 mmap2} {
UPDATE t2 SET x = (SELECT md5sum(a) FROM t1);
}
- set res [$c2 {
+ set res [$c2 {
SELECT count(*) FROM t1;
SELECT x == (SELECT md5sum(a) FROM t1) FROM t2;
PRAGMA integrity_check;
diff --git a/test/rollback2.test b/test/rollback2.test
index 9637f0c0e..4d42dda5d 100644
--- a/test/rollback2.test
+++ b/test/rollback2.test
@@ -9,6 +9,9 @@
#
#***********************************************************************
#
+# This file containst tests to verify that ROLLBACK or ROLLBACK TO
+# operations interact correctly with ongoing SELECT statements.
+#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -16,7 +19,6 @@ set ::testprefix rollback2
proc int2hex {i} { format %.2X $i }
db func int2hex int2hex
-
do_execsql_test 1.0 {
SELECT int2hex(0), int2hex(100), int2hex(255)
} {00 64 FF}
@@ -32,6 +34,17 @@ do_execsql_test 1.1 {
} {}
+# do_rollback_test ID SWITCHES
+#
+# where SWITCHES are:
+#
+# -setup SQL script to open transaction and begin writing.
+# -select SELECT to execute after -setup script
+# -result Expected result of -select statement
+# -rollback Use this SQL command ("ROLLBACK" or "ROLLBACK TO ...") to
+# rollback the transaction in the middle of the -select statment
+# execution.
+#
proc do_rollback_test {tn args} {
set A(-setup) ""
set A(-select) ""
@@ -61,7 +74,7 @@ proc do_rollback_test {tn args} {
}
}
-do_rollback_test 2 -setup {
+do_rollback_test 2.1 -setup {
BEGIN;
DELETE FROM t1 WHERE (i%2)==1;
} -select {
@@ -70,5 +83,75 @@ do_rollback_test 2 -setup {
2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
}
+do_rollback_test 2.2 -setup {
+ BEGIN;
+ DELETE FROM t1 WHERE (i%4)==1;
+ SAVEPOINT one;
+ DELETE FROM t1 WHERE (i%2)==1;
+} -rollback {
+ ROLLBACK TO one;
+} -select {
+ SELECT i FROM t1 WHERE (i%2)==0
+} -result {
+ 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
+}
+
+#--------------------------------------------------------------------
+# Try with some index scans
+#
+do_eqp_test 3.1 {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
+} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
+do_rollback_test 3.2 -setup {
+ BEGIN;
+ DELETE FROM t1 WHERE (i%2)==1;
+} -select {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
+} -result {
+ 40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10 8 6 4 2
+}
+do_rollback_test 3.3 -setup {
+ BEGIN;
+ DELETE FROM t1 WHERE (i%4)==1;
+ SAVEPOINT one;
+ DELETE FROM t1 WHERE (i%2)==1;
+} -rollback {
+ ROLLBACK TO one;
+} -select {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
+} -result {
+ 40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10 8 6 4 2
+}
+
+#--------------------------------------------------------------------
+# Now with some index scans that feature overflow keys.
+#
+set leader [string repeat "abcdefghij" 70]
+do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; }
+
+do_eqp_test 4.2 {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
+} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
+do_rollback_test 4.3 -setup {
+ BEGIN;
+ DELETE FROM t1 WHERE (i%2)==1;
+} -select {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
+} -result {
+ 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
+}
+do_rollback_test 4.4 -setup {
+ BEGIN;
+ DELETE FROM t1 WHERE (i%4)==1;
+ SAVEPOINT one;
+ DELETE FROM t1 WHERE (i%2)==1;
+} -rollback {
+ ROLLBACK TO one;
+} -select {
+ SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
+} -result {
+ 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40
+}
+
finish_test
diff --git a/test/rollbackfault.test b/test/rollbackfault.test
new file mode 100644
index 000000000..f248d0758
--- /dev/null
+++ b/test/rollbackfault.test
@@ -0,0 +1,84 @@
+# 2014-11-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.
+#
+#***********************************************************************
+#
+# Test that errors encountered during a ROLLBACK operation correctly
+# affect ongoing SQL statements.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/malloc_common.tcl
+set testprefix rollbackfault
+
+
+proc int2hex {i} { format %.2X $i }
+db func int2hex int2hex
+do_execsql_test 1.0 {
+ SELECT int2hex(0), int2hex(100), int2hex(255)
+} {00 64 FF}
+do_execsql_test 1.1 {
+ CREATE TABLE t1(i, h);
+ CREATE INDEX i1 ON t1(h);
+ WITH data(a, b) AS (
+ SELECT 1, int2hex(1)
+ UNION ALL
+ SELECT a+1, int2hex(a+1) FROM data WHERE a<40
+ )
+ INSERT INTO t1 SELECT * FROM data;
+} {}
+
+foreach f {oom ioerr} {
+ do_faultsim_test 1.2 -faults $f* -prep {
+ set sql1 { SELECT i FROM t1 WHERE (i%2)==0 }
+ set sql2 { SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h }
+ set ::s1 [sqlite3_prepare db $sql1 -1 dummy]
+ set ::s2 [sqlite3_prepare db $sql2 -1 dummy]
+
+ for {set i 0} {$i < 10} {incr i} { sqlite3_step $::s1 }
+ for {set i 0} {$i < 3} {incr i} { sqlite3_step $::s2 }
+
+ execsql {
+ BEGIN; DELETE FROM t1 WHERE (i%2)
+ }
+ } -body {
+ execsql { ROLLBACK }
+ } -test {
+
+ set res1 [list]
+ set res2 [list]
+ while {"SQLITE_ROW" == [sqlite3_step $::s1]} {
+ lappend res1 [sqlite3_column_text $::s1 0]
+ }
+ while {"SQLITE_ROW" == [sqlite3_step $::s2]} {
+ lappend res2 [sqlite3_column_text $::s2 0]
+ }
+ set rc1 [sqlite3_finalize $::s1]
+ set rc2 [sqlite3_finalize $::s2]
+
+ catchsql { ROLLBACK }
+
+ if {$rc1=="SQLITE_OK" && $rc2=="SQLITE_OK"
+ && $res1=="22 24 26 28 30 32 34 36 38 40"
+ && $res2=="8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40"
+ } {
+ # This is Ok.
+ } elseif {$rc1!="SQLITE_OK" && $rc2!="SQLITE_OK" && $res1=="" &&$res2==""} {
+ # Also Ok.
+ } else {
+ error "statements don't look right"
+ }
+ }
+}
+
+
+finish_test
+
+
diff --git a/test/scanstatus.test b/test/scanstatus.test
new file mode 100644
index 000000000..7713bae5f
--- /dev/null
+++ b/test/scanstatus.test
@@ -0,0 +1,398 @@
+# 2014 November 1
+#
+# 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 scanstatus
+
+ifcapable !scanstatus {
+ finish_test
+ return
+}
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(x, y);
+ INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t1 VALUES(3, 4);
+ INSERT INTO t2 VALUES('a', 'b');
+ INSERT INTO t2 VALUES('c', 'd');
+ INSERT INTO t2 VALUES('e', 'f');
+}
+
+proc do_scanstatus_test {tn res} {
+ set stmt [db_last_stmt_ptr db]
+ set idx 0
+ set ret [list]
+ while {1} {
+ set r [sqlite3_stmt_scanstatus $stmt $idx]
+ if {[llength $r]==0} break
+ lappend ret {*}$r
+ incr idx
+ }
+
+ uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
+}
+
+do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6
+do_scanstatus_test 1.2 {
+ nLoop 1 nVisit 2 nEst 1048576.0 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 2 nVisit 6 nEst 1048576.0 zName t2 zExplain {SCAN TABLE t2}
+}
+
+do_execsql_test 1.3 {
+ ANALYZE;
+ SELECT count(*) FROM t1, t2;
+} 6
+do_scanstatus_test 1.4 {
+ nLoop 1 nVisit 2 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 2 nVisit 6 nEst 3.0 zName t2 zExplain {SCAN TABLE t2}
+}
+
+do_execsql_test 1.5 { ANALYZE }
+do_execsql_test 1.6 {
+ SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
+} 4
+do_scanstatus_test 1.7 {
+ nLoop 1 nVisit 2 nEst 2.0 zName t2 zExplain
+ {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
+ nLoop 2 nVisit 4 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
+}
+
+do_execsql_test 1.8 {
+ SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
+} 4
+
+do_scanstatus_test 1.9 {
+ nLoop 2 nVisit 4 nEst 2.0 zName t2 zExplain
+ {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
+ nLoop 4 nVisit 8 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
+}
+
+do_test 1.9 {
+ sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db]
+} {}
+
+do_scanstatus_test 1.10 {
+ nLoop 0 nVisit 0 nEst 2.0 zName t2 zExplain
+ {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
+ nLoop 0 nVisit 0 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
+}
+
+#-------------------------------------------------------------------------
+# Try a few different types of scans.
+#
+reset_db
+do_execsql_test 2.1 {
+ CREATE TABLE x1(i INTEGER PRIMARY KEY, j);
+ INSERT INTO x1 VALUES(1, 'one');
+ INSERT INTO x1 VALUES(2, 'two');
+ INSERT INTO x1 VALUES(3, 'three');
+ INSERT INTO x1 VALUES(4, 'four');
+ CREATE INDEX x1j ON x1(j);
+
+ SELECT * FROM x1 WHERE i=2;
+} {2 two}
+
+do_scanstatus_test 2.2 {
+ nLoop 1 nVisit 1 nEst 1.0 zName x1
+ zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)}
+}
+
+do_execsql_test 2.3.1 {
+ SELECT * FROM x1 WHERE j='two'
+} {2 two}
+do_scanstatus_test 2.3.2 {
+ nLoop 1 nVisit 1 nEst 10.0 zName x1j
+ zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)}
+}
+
+do_execsql_test 2.4.1 {
+ SELECT * FROM x1 WHERE j<'two'
+} {4 four 1 one 3 three}
+do_scanstatus_test 2.4.2 {
+ nLoop 1 nVisit 3 nEst 262144.0 zName x1j
+ zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j<?)}
+}
+
+do_execsql_test 2.5.1 {
+ SELECT * FROM x1 WHERE j>='two'
+} {2 two}
+do_scanstatus_test 2.5.2 {
+ nLoop 1 nVisit 1 nEst 262144.0 zName x1j
+ zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)}
+}
+
+do_execsql_test 2.6.1 {
+ SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two'
+} {3 three 2 two}
+do_scanstatus_test 2.6.2 {
+ nLoop 1 nVisit 2 nEst 16384.0 zName x1j
+ zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j<?)}
+}
+
+do_execsql_test 2.7.1 {
+ CREATE TABLE x2(i INTEGER, j, k);
+ INSERT INTO x2 SELECT i, j, i || ' ' || j FROM x1;
+ CREATE INDEX x2j ON x2(j);
+ CREATE INDEX x2ij ON x2(i, j);
+ SELECT * FROM x2 WHERE j BETWEEN 'three' AND 'two'
+} {3 three {3 three} 2 two {2 two}}
+
+do_scanstatus_test 2.7.2 {
+ nLoop 1 nVisit 2 nEst 16384.0 zName x2j
+ zExplain {SEARCH TABLE x2 USING INDEX x2j (j>? AND j<?)}
+}
+
+do_execsql_test 2.8.1 {
+ SELECT * FROM x2 WHERE i=1 AND j='two'
+}
+do_scanstatus_test 2.8.2 {
+ nLoop 1 nVisit 0 nEst 8.0 zName x2ij
+ zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
+}
+
+do_execsql_test 2.9.1 {
+ SELECT * FROM x2 WHERE i=5 AND j='two'
+}
+do_scanstatus_test 2.9.2 {
+ nLoop 1 nVisit 0 nEst 8.0 zName x2ij
+ zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
+}
+
+do_execsql_test 2.10.1 {
+ SELECT * FROM x2 WHERE i=3 AND j='three'
+} {3 three {3 three}}
+do_scanstatus_test 2.10.2 {
+ nLoop 1 nVisit 1 nEst 8.0 zName x2ij
+ zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
+}
+
+#-------------------------------------------------------------------------
+# Try with queries that use the OR optimization.
+#
+do_execsql_test 3.1 {
+ CREATE TABLE a1(a, b, c, d);
+ CREATE INDEX a1a ON a1(a);
+ CREATE INDEX a1bc ON a1(b, c);
+
+ WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
+ INSERT INTO a1 SELECT x, x, x, x FROM d;
+}
+
+do_execsql_test 3.2.1 {
+ SELECT d FROM a1 WHERE (a=4 OR b=13)
+} {4 13}
+do_scanstatus_test 3.2.2 {
+ nLoop 1 nVisit 1 nEst 10.0 zName a1a
+ zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)}
+ nLoop 1 nVisit 1 nEst 10.0 zName a1bc
+ zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)}
+}
+
+do_execsql_test 3.2.1 {
+ SELECT count(*) FROM a1 WHERE (a BETWEEN 4 AND 12) OR (b BETWEEN 40 AND 60)
+} {30}
+do_scanstatus_test 3.2.2 {
+ nLoop 1 nVisit 9 nEst 16384.0 zName a1a
+ zExplain {SEARCH TABLE a1 USING INDEX a1a (a>? AND a<?)}
+ nLoop 1 nVisit 21 nEst 16384.0 zName a1bc
+ zExplain {SEARCH TABLE a1 USING INDEX a1bc (b>? AND b<?)}
+}
+
+do_execsql_test 3.3.1 {
+ SELECT count(*) FROM a1 AS x, a1 AS y
+ WHERE (x.a BETWEEN 4 AND 12) AND (y.b BETWEEN 1 AND 10)
+} {90}
+do_scanstatus_test 3.2.2 {
+ nLoop 1 nVisit 10 nEst 16384.0 zName a1bc
+ zExplain {SEARCH TABLE a1 AS y USING COVERING INDEX a1bc (b>? AND b<?)}
+ nLoop 10 nVisit 90 nEst 16384.0 zName a1a
+ zExplain {SEARCH TABLE a1 AS x USING COVERING INDEX a1a (a>? AND a<?)}
+}
+
+do_execsql_test 3.4.1 {
+ SELECT count(*) FROM a1 WHERE a IN (1, 5, 10, 15);
+} {4}
+do_scanstatus_test 3.4.2 {
+ nLoop 1 nVisit 4 nEst 40.0 zName a1a
+ zExplain {SEARCH TABLE a1 USING COVERING INDEX a1a (a=?)}
+}
+
+do_execsql_test 3.4.1 {
+ SELECT count(*) FROM a1 WHERE rowid IN (1, 5, 10, 15);
+} {4}
+do_scanstatus_test 3.4.2 {
+ nLoop 1 nVisit 4 nEst 4.0 zName a1
+ zExplain {SEARCH TABLE a1 USING INTEGER PRIMARY KEY (rowid=?)}
+}
+
+#-------------------------------------------------------------------------
+# Test that scanstatus() data is not available for searches performed
+# by triggers.
+#
+# It is available for searches performed as part of FK processing, but
+# not FK action processing.
+#
+do_execsql_test 4.0 {
+ CREATE TABLE t1(a, b, c);
+ CREATE TABLE t2(x PRIMARY KEY, y, z);
+ CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
+ SELECT * FROM t2 WHERE x BETWEEN 20 AND 40;
+ END;
+ WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
+ INSERT INTO t2 SELECT x, x*2, x*3 FROM d;
+}
+
+do_execsql_test 4.1.1 { INSERT INTO t1 VALUES(1, 2, 3); }
+do_scanstatus_test 4.1.2 { }
+
+do_execsql_test 4.2 {
+ CREATE TABLE p1(x PRIMARY KEY);
+ INSERT INTO p1 VALUES(1), (2), (3), (4);
+ CREATE TABLE c1(y REFERENCES p1);
+ INSERT INTO c1 VALUES(1), (2), (3);
+ PRAGMA foreign_keys=on;
+}
+do_execsql_test 4.2.1 { DELETE FROM p1 WHERE x=4 }
+do_scanstatus_test 4.2.2 {
+ nLoop 1 nVisit 1 nEst 1.0 zName sqlite_autoindex_p1_1
+ zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}
+
+ nLoop 1 nVisit 3 nEst 524288.0 zName c1 zExplain {SCAN TABLE c1}
+}
+
+#-------------------------------------------------------------------------
+# Further tests of different scan types.
+#
+reset_db
+proc tochar {i} {
+ set alphabet {a b c d e f g h i j k l m n o p q r s t u v w x y z}
+ return [lindex $alphabet [expr $i % [llength $alphabet]]]
+}
+db func tochar tochar
+do_execsql_test 5.0 {
+ CREATE TABLE t1(a PRIMARY KEY, b, c);
+ INSERT INTO t1 VALUES(0, 1, 'a');
+ INSERT INTO t1 VALUES(1, 0, 'b');
+ INSERT INTO t1 VALUES(2, 1, 'c');
+ INSERT INTO t1 VALUES(3, 0, 'd');
+ INSERT INTO t1 VALUES(4, 1, 'e');
+ INSERT INTO t1 VALUES(5, 0, 'a');
+ INSERT INTO t1 VALUES(6, 1, 'b');
+ INSERT INTO t1 VALUES(7, 0, 'c');
+ INSERT INTO t1 VALUES(8, 1, 'd');
+ INSERT INTO t1 VALUES(9, 0, 'e');
+ CREATE INDEX t1bc ON t1(b, c);
+
+ CREATE TABLE t2(x, y);
+ CREATE INDEX t2xy ON t2(x, y);
+ WITH data(i, x, y) AS (
+ SELECT 0, 0, tochar(0)
+ UNION ALL
+ SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500
+ ) INSERT INTO t2 SELECT x, y FROM data;
+
+ CREATE TABLE t3(x, y);
+ INSERT INTO t3 SELECT * FROM t2;
+
+ ANALYZE;
+}
+
+do_execsql_test 5.1.1 {
+ SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii)
+} {2}
+do_scanstatus_test 5.1.2 {
+ nLoop 1 nVisit 10 nEst 10.0 zName t1bc
+ zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc}
+ nLoop 1 nVisit 2 nEst 8.0 zName sqlite_autoindex_t1_1
+ zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
+}
+
+do_execsql_test 5.2.1 {
+ SELECT count(*) FROM t1 WHERE a IN (0, 1)
+} {2}
+do_scanstatus_test 5.2.2 {
+ nLoop 1 nVisit 2 nEst 2.0 zName sqlite_autoindex_t1_1
+ zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
+}
+
+do_eqp_test 5.3.1 {
+ SELECT count(*) FROM t2 WHERE y = 'j';
+} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
+do_execsql_test 5.3.2 {
+ SELECT count(*) FROM t2 WHERE y = 'j';
+} {19}
+do_scanstatus_test 5.3.3 {
+ nLoop 1 nVisit 19 nEst 56.0 zName t2xy zExplain
+ {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
+}
+
+do_eqp_test 5.4.1 {
+ SELECT count(*) FROM t1, t2 WHERE y = c;
+} {
+ 0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
+ 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
+}
+do_execsql_test 5.4.2 {
+ SELECT count(*) FROM t1, t2 WHERE y = c;
+} {200}
+do_scanstatus_test 5.4.3 {
+ nLoop 1 nVisit 10 nEst 10.0 zName t1bc
+ zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
+ nLoop 10 nVisit 200 nEst 56.0 zName t2xy
+ zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
+}
+
+do_eqp_test 5.5.1 {
+ SELECT count(*) FROM t1, t3 WHERE y = c;
+} {
+ 0 0 1 {SCAN TABLE t3}
+ 0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
+}
+do_execsql_test 5.5.2 {
+ SELECT count(*) FROM t1, t3 WHERE y = c;
+} {200}
+do_scanstatus_test 5.5.3 {
+ nLoop 1 nVisit 501 nEst 480.0 zName t3 zExplain {SCAN TABLE t3}
+ nLoop 501 nVisit 200 nEst 20.0 zName auto-index zExplain
+ {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
+}
+
+#-------------------------------------------------------------------------
+# Virtual table scans
+#
+ifcapable fts3 {
+ do_execsql_test 6.0 {
+ CREATE VIRTUAL TABLE ft1 USING fts4;
+ INSERT INTO ft1 VALUES('a d c f g h e i f c');
+ INSERT INTO ft1 VALUES('g c h b g b f f f g');
+ INSERT INTO ft1 VALUES('h h c c h f a e d d');
+ INSERT INTO ft1 VALUES('e j i j i e b c f g');
+ INSERT INTO ft1 VALUES('g f b g j c h a d f');
+ INSERT INTO ft1 VALUES('j i a e g f a i a c');
+ INSERT INTO ft1 VALUES('f d g g j j c a h g');
+ INSERT INTO ft1 VALUES('b d h a d j j j b i');
+ INSERT INTO ft1 VALUES('j e a b j e c b c i');
+ INSERT INTO ft1 VALUES('a d e f b j j c g d');
+ }
+ do_execsql_test 6.1.1 {
+ SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd'
+ } {6}
+ do_scanstatus_test 6.1.2 {
+ nLoop 1 nVisit 6 nEst 24.0 zName ft1 zExplain
+ {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:}
+ }
+}
+
+
+finish_test
diff --git a/test/skipscan6.test b/test/skipscan6.test
new file mode 100644
index 000000000..026c4d7b0
--- /dev/null
+++ b/test/skipscan6.test
@@ -0,0 +1,200 @@
+# 2014-10-21
+#
+# 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 of the "skip-scan" query strategy. In
+# particular, this file verifies that use of all columns of an index
+# is always preferred over the use of a skip-scan on some columns of
+# the same index. Because of difficulties in scoring a skip-scan,
+# the skip-scan can sometimes come out with a lower raw score when
+# using STAT4. But the query planner should detect this and use the
+# full index rather than the skip-scan.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix skipscan6
+
+ifcapable !stat4 {
+ finish_test
+ return
+}
+
+do_execsql_test 1.1 {
+ CREATE TABLE t1(
+ aa int,
+ bb int,
+ cc int,
+ dd int,
+ ee int
+ );
+ CREATE INDEX ix on t1(aa, bb, cc, dd DESC);
+ ANALYZE sqlite_master;
+ INSERT INTO sqlite_stat1 VALUES('t1','ix','2695116 1347558 264 18 2');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5');
+ INSERT INTO sqlite_stat4 VALUES('t1','ix','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35');
+ ANALYZE sqlite_master;
+} {}
+do_execsql_test 1.2 {
+ EXPLAIN QUERY PLAN
+ SELECT COUNT(*)
+ FROM t1
+ WHERE bb=21
+ AND aa=1
+ AND dd BETWEEN 1413833728 and 1413837331;
+} {/INDEX ix .aa=. AND bb=../}
+
+do_execsql_test 2.1 {
+ DROP INDEX ix;
+ CREATE INDEX good on t1(bb, aa, dd DESC);
+ CREATE INDEX bad on t1(aa, bb, cc, dd DESC);
+ DELETE FROM sqlite_stat1;
+ DELETE FROM sqlite_stat4;
+ INSERT INTO sqlite_stat1 VALUES('t1','good','2695116 299 264 2');
+ INSERT INTO sqlite_stat1 VALUES('t1','bad','2695116 1347558 264 18 2');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','197030 196859 32 1','15086 15086 92511 92536','19 25 81644 92536',X'05010904031552977BD725BD22');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','14972 14687 1 1','289878 289878 299457 299457','199 244 267460 299457',X'050209040301344F7E569402C419');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','19600 19313 22 1','327127 327127 346222 346243','261 319 306884 346243',X'0502090403018A49503BC01EC577');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','25666 25047 15 1','352087 352087 372692 372706','266 327 325601 372706',X'050209040301914C2DD2E91F93CF');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','42392 42327 26 1','378657 378657 382547 382572','268 331 333529 382572',X'05020904030193533B2FE326ED48');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','24619 24513 11 1','457872 457872 461748 461758','286 358 399322 461758',X'050209040301A752B1557825EA7C');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','18969 18730 15 1','482491 482491 501105 501119','287 360 433605 501119',X'050209040301A8494AF3A41EC50C');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','119710 119603 1 1','576500 576500 598915 598915','404 505 519877 598915',X'05020904030247539A7A7912F617');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','11955 11946 1 1','889796 889796 898373 898373','938 1123 794694 898373',X'050209040304EF4DF9C4150BBB28');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','57197 57138 24 1','1129865 1129865 1151492 1151515','1967 2273 1027048 1151515',X'05020904030988533510BC26E20A');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','3609 3543 1 1','1196265 1196265 1197831 1197831','2002 2313 1070108 1197831',X'050209040309B050E95CD718D94D');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','25391 25365 13 1','1309378 1309378 1315567 1315579','2561 2936 1178358 1315579',X'05020904030C5F53DF9E13283570');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','45232 45180 17 1','1334769 1334769 1337946 1337962','2562 2938 1198998 1337962',X'05020904030C60541CACEE28BCAC');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','5496 5493 1 1','1495882 1495882 1497289 1497289','3043 3479 1348695 1497289',X'05020904030E99515C62AD0F0B34');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','26348 26139 17 1','1517381 1517381 1529990 1530006','3074 3519 1378320 1530006',X'05020904030EB95169453423D4EA');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','102927 102894 10 1','1547088 1547088 1649950 1649959','3109 3559 1494260 1649959',X'05020904030EE34D309F671FFA47');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','3602 3576 1 1','1793873 1793873 1796747 1796747','3601 4128 1630783 1796747',X'050209040311294FE88B432219B9');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','154 154 1 1','2096059 2096059 2096205 2096205','5037 5779 1893039 2096205',X'050209040317994EFF05A016DCED');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','68153 66574 60 1','2244039 2244039 2268892 2268951','5899 6749 2027553 2268951',X'05020904031B8A532DBC5A26D2BA');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','321 321 1 1','2395618 2395618 2395663 2395663','6609 7528 2118435 2395663',X'05020904031EFA54078EEE1E2D65');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','19449 19440 22 1','2407769 2407769 2426049 2426070','6718 7651 2146904 2426070',X'05020904031F7450E6118C2336BD');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','18383 18321 56 1','2539949 2539949 2551080 2551135','7838 8897 2245459 2551135',X'050209040324A752EA2E1E2642B2');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','22479 22384 60 1','2558332 2558332 2565233 2565292','7839 8899 2251202 2565292',X'050209040324A853926538279A5F');
+ INSERT INTO sqlite_stat4 VALUES('t1','good','18771 18699 63 1','2580811 2580811 2596914 2596976','7840 8901 2263572 2596976',X'050209040324A9526C1DE9256E72');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5');
+ INSERT INTO sqlite_stat4 VALUES('t1','bad','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35');
+ ANALYZE sqlite_master;
+} {}
+do_execsql_test 2.2 {
+ EXPLAIN QUERY PLAN
+ SELECT COUNT(*)
+ FROM t1
+ WHERE bb=21
+ AND aa=1
+ AND dd BETWEEN 1413833728 and 1413837331;
+} {/INDEX good .bb=. AND aa=. AND dd>. AND dd<../}
+
+
+# Create a table containing 100 rows. Column "a" contains a copy of the
+# rowid value - sequentially increasing integers from 1 to 100. Column
+# "b" contains the value of (a % 5). Columns "c" and "d" both contain
+# constant values (i.e. the same for every row).
+#
+# Then create a second table t2. t2 is the same as t3 except for the
+# order in which the indexes are created.
+#
+do_execsql_test 3.0 {
+ CREATE TABLE t3(a, b, c, d);
+ CREATE INDEX t3_ba ON t3(b, a, c);
+ CREATE INDEX t3_a ON t3(a);
+
+ WITH d(a, b) AS (
+ SELECT 1, 1
+ UNION ALL
+ SELECT a+1, (a+1) % 5 FROM d WHERE a<100
+ )
+ INSERT INTO t3 SELECT a, b, 'c', 'd' FROM d;
+
+ CREATE TABLE t2(a, b, c, d);
+ CREATE INDEX t2_a ON t2(a);
+ CREATE INDEX t2_ba ON t2(b, a, c);
+ INSERT INTO t2 SELECT * FROM t3;
+
+ ANALYZE;
+ SELECT * FROM sqlite_stat1;
+} {
+ t2 t2_ba {100 20 1 1}
+ t2 t2_a {100 1}
+ t3 t3_a {100 1}
+ t3 t3_ba {100 20 1 1}
+}
+
+# Use index "t3_a", as (a=?) is expected to match only a single row.
+#
+do_eqp_test 3.1 {
+ SELECT * FROM t3 WHERE a = ? AND c = ?
+} {
+ 0 0 0 {SEARCH TABLE t3 USING INDEX t3_a (a=?)}
+}
+
+# The same query on table t2. This should use index "t2_a", for the
+# same reason. At one point though, it was mistakenly using a skip-scan.
+#
+do_eqp_test 3.2 {
+ SELECT * FROM t2 WHERE a = ? AND c = ?
+} {
+ 0 0 0 {SEARCH TABLE t2 USING INDEX t2_a (a=?)}
+}
+
+finish_test
+
+
+
+
+finish_test
diff --git a/test/sort2.test b/test/sort2.test
index 29001f009..a4c55c9f2 100644
--- a/test/sort2.test
+++ b/test/sort2.test
@@ -62,7 +62,6 @@ foreach {tn script} {
do_execsql_test $tn.2.4 { PRAGMA integrity_check } {ok}
- breakpoint
do_execsql_test $tn.3 {
PRAGMA cache_size = 5;
WITH r(x,y) AS (
diff --git a/test/tkt-f777251dc7a.test b/test/tkt-f777251dc7a.test
index f814d246b..b91e438da 100644
--- a/test/tkt-f777251dc7a.test
+++ b/test/tkt-f777251dc7a.test
@@ -38,7 +38,6 @@ proc force_rollback {} {
db function force_rollback force_rollback
do_test tkt-f7772-1.2 {
-breakpoint
catchsql {
BEGIN IMMEDIATE;
CREATE TABLE xyzzy(abc);
diff --git a/test/without_rowid5.test b/test/without_rowid5.test
index 45e047bef..d163d9c1b 100644
--- a/test/without_rowid5.test
+++ b/test/without_rowid5.test
@@ -185,8 +185,7 @@ do_execsql_test without_rowid5-5.9 {
# EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
# work for WITHOUT ROWID tables.
#
-# EVIDENCE-OF: R-25760-33257 The sqlite3_blob_open() interface will fail
-# for a WITHOUT ROWID table.
+# EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
#
do_execsql_test without_rowid5-6.1 {
CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-20 07:23:56 +0000