summaryrefslogtreecommitdiffstats
path: root/test/delete2.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/delete2.test')
-rw-r--r--test/delete2.test119
1 files changed, 119 insertions, 0 deletions
diff --git a/test/delete2.test b/test/delete2.test
new file mode 100644
index 0000000..1668a3a
--- /dev/null
+++ b/test/delete2.test
@@ -0,0 +1,119 @@
+# 2003 September 6
+#
+# 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 a test to replicate the bug reported by
+# ticket #842.
+#
+# Ticket #842 was a database corruption problem caused by a DELETE that
+# removed an index entry by not the main table entry. To recreate the
+# problem do this:
+#
+# (1) Create a table with an index. Insert some data into that table.
+# (2) Start a query on the table but do not complete the query.
+# (3) Try to delete a single entry from the table.
+#
+# Step 3 will fail because there is still a read cursor on the table.
+# But the database is corrupted by the DELETE. It turns out that the
+# index entry was deleted first, before the table entry. And the index
+# delete worked. Thus an entry was deleted from the index but not from
+# the table.
+#
+# The solution to the problem was to detect that the table is locked
+# before the index entry is deleted.
+#
+# $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Create a table that has an index.
+#
+do_test delete2-1.1 {
+ set DB [sqlite3_connection_pointer db]
+ execsql {
+ CREATE TABLE q(s string, id string, constraint pk_q primary key(id));
+ BEGIN;
+ INSERT INTO q(s,id) VALUES('hello','id.1');
+ INSERT INTO q(s,id) VALUES('goodbye','id.2');
+ INSERT INTO q(s,id) VALUES('again','id.3');
+ END;
+ SELECT * FROM q;
+ }
+} {hello id.1 goodbye id.2 again id.3}
+do_test delete2-1.2 {
+ execsql {
+ SELECT * FROM q WHERE id='id.1';
+ }
+} {hello id.1}
+integrity_check delete2-1.3
+
+# Start a query on the table. The query should not use the index.
+# Do not complete the query, thus leaving the table locked.
+#
+do_test delete2-1.4 {
+ set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
+ sqlite3_step $STMT
+} SQLITE_ROW
+integrity_check delete2-1.5
+
+# Try to delete a row from the table while a read is in process.
+# As of 2006-08-16, this is allowed. (It used to fail with SQLITE_LOCKED.)
+#
+do_test delete2-1.6 {
+ catchsql {
+ DELETE FROM q WHERE rowid=1
+ }
+} {0 {}}
+integrity_check delete2-1.7
+do_test delete2-1.8 {
+ execsql {
+ SELECT * FROM q;
+ }
+} {goodbye id.2 again id.3}
+
+# Finalize the query, thus clearing the lock on the table. Then
+# retry the delete. The delete should work this time.
+#
+do_test delete2-1.9 {
+ sqlite3_finalize $STMT
+ catchsql {
+ DELETE FROM q WHERE rowid=1
+ }
+} {0 {}}
+integrity_check delete2-1.10
+do_test delete2-1.11 {
+ execsql {
+ SELECT * FROM q;
+ }
+} {goodbye id.2 again id.3}
+
+do_test delete2-2.1 {
+ execsql {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(c, d);
+ INSERT INTO t1 VALUES(1, 2);
+ INSERT INTO t2 VALUES(3, 4);
+ INSERT INTO t2 VALUES(5, 6);
+ }
+} {}
+do_test delete2-2.2 {
+ set res [list]
+ db eval {
+ SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2
+ } {
+ db eval {DELETE FROM t1}
+ lappend res $b $c $d
+ }
+ set res
+} {{} 3 4 {} 5 6}
+
+finish_test