[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/delete.test

+# 2001 September 15
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing the DELETE FROM statement.
+#
+# $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Try to delete from a non-existant table.
+#
+do_test delete-1.1 {
+  set v [catch {execsql {DELETE FROM test1}} msg]
+  lappend v $msg
+} {1 {no such table: test1}}
+
+# Try to delete from sqlite_master
+#
+do_test delete-2.1 {
+  set v [catch {execsql {DELETE FROM sqlite_master}} msg]
+  lappend v $msg
+} {1 {table sqlite_master may not be modified}}
+
+# Delete selected entries from a table with and without an index.
+#
+do_test delete-3.1.1 {
+  execsql {CREATE TABLE table1(f1 int, f2 int)}
+  execsql {INSERT INTO table1 VALUES(1,2)}
+  execsql {INSERT INTO table1 VALUES(2,4)}
+  execsql {INSERT INTO table1 VALUES(3,8)}
+  execsql {INSERT INTO table1 VALUES(4,16)}
+  execsql {SELECT * FROM table1 ORDER BY f1}
+} {1 2 2 4 3 8 4 16}
+do_test delete-3.1.2 {
+  execsql {DELETE FROM table1 WHERE f1=3}
+} {}
+do_test delete-3.1.3 {
+  execsql {SELECT * FROM table1 ORDER BY f1}
+} {1 2 2 4 4 16}
+do_test delete-3.1.4 {
+  execsql {CREATE INDEX index1 ON table1(f1)}
+  execsql {PRAGMA count_changes=on}
+  ifcapable explain {
+    execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
+  }
+  execsql {DELETE FROM 'table1' WHERE f1=3}
+} {0}
+do_test delete-3.1.5 {
+  execsql {SELECT * FROM table1 ORDER BY f1}
+} {1 2 2 4 4 16}
+do_test delete-3.1.6.1 {
+  execsql {DELETE FROM table1 WHERE f1=2}
+} {1}
+do_test delete-3.1.6.2 {
+  db changes
+} 1
+do_test delete-3.1.7 {
+  execsql {SELECT * FROM table1 ORDER BY f1}
+} {1 2 4 16}
+integrity_check delete-3.2
+
+# Semantic errors in the WHERE clause
+#
+do_test delete-4.1 {
+  execsql {CREATE TABLE table2(f1 int, f2 int)}
+  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
+  lappend v $msg
+} {1 {no such column: f3}}
+
+do_test delete-4.2 {
+  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
+  lappend v $msg
+} {1 {no such function: xyzzy}}
+integrity_check delete-4.3
+
+# Lots of deletes
+#
+do_test delete-5.1.1 {
+  execsql {DELETE FROM table1}
+} {2}
+do_test delete-5.1.2 {
+  execsql {SELECT count(*) FROM table1}
+} {0}
+do_test delete-5.2.1 {
+  execsql {BEGIN TRANSACTION}
+  for {set i 1} {$i<=200} {incr i} {
+     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
+  }
+  execsql {COMMIT}
+  execsql {SELECT count(*) FROM table1}
+} {200}
+do_test delete-5.2.2 {
+  execsql {DELETE FROM table1}
+} {200}
+do_test delete-5.2.3 {
+  execsql {BEGIN TRANSACTION}
+  for {set i 1} {$i<=200} {incr i} {
+     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
+  }
+  execsql {COMMIT}
+  execsql {SELECT count(*) FROM table1}
+} {200}
+do_test delete-5.2.4 {
+  execsql {PRAGMA count_changes=off}
+  execsql {DELETE FROM table1}
+} {}
+do_test delete-5.2.5 {
+  execsql {SELECT count(*) FROM table1}
+} {0}
+do_test delete-5.2.6 {
+  execsql {BEGIN TRANSACTION}
+  for {set i 1} {$i<=200} {incr i} {
+     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
+  }
+  execsql {COMMIT}
+  execsql {SELECT count(*) FROM table1}
+} {200}
+do_test delete-5.3 {
+  for {set i 1} {$i<=200} {incr i 4} {
+     execsql "DELETE FROM table1 WHERE f1==$i"
+  }
+  execsql {SELECT count(*) FROM table1}
+} {150}
+do_test delete-5.4.1 {
+  execsql "DELETE FROM table1 WHERE f1>50"
+  db changes
+} [db one {SELECT count(*) FROM table1 WHERE f1>50}]
+do_test delete-5.4.2 {
+  execsql {SELECT count(*) FROM table1}
+} {37}
+do_test delete-5.5 {
+  for {set i 1} {$i<=70} {incr i 3} {
+     execsql "DELETE FROM table1 WHERE f1==$i"
+  }
+  execsql {SELECT f1 FROM table1 ORDER BY f1}
+} {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50}
+do_test delete-5.6 {
+  for {set i 1} {$i<40} {incr i} {
+     execsql "DELETE FROM table1 WHERE f1==$i"
+  }
+  execsql {SELECT f1 FROM table1 ORDER BY f1}
+} {42 44 47 48 50}
+do_test delete-5.7 {
+  execsql "DELETE FROM table1 WHERE f1!=48"
+  execsql {SELECT f1 FROM table1 ORDER BY f1}
+} {48}
+integrity_check delete-5.8
+
+
+# Delete large quantities of data.  We want to test the List overflow
+# mechanism in the vdbe.
+#
+do_test delete-6.1 {
+  execsql {BEGIN; DELETE FROM table1}
+  for {set i 1} {$i<=3000} {incr i} {
+    execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
+  }
+  execsql {DELETE FROM table2}
+  for {set i 1} {$i<=3000} {incr i} {
+    execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
+  }
+  execsql {COMMIT}
+  execsql {SELECT count(*) FROM table1}
+} {3000}
+do_test delete-6.2 {
+  execsql {SELECT count(*) FROM table2}
+} {3000}
+do_test delete-6.3 {
+  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
+} {1 2 3 4 5 6 7 8 9}
+do_test delete-6.4 {
+  execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
+} {1 2 3 4 5 6 7 8 9}
+do_test delete-6.5.1 {
+  execsql {DELETE FROM table1 WHERE f1>7}
+  db changes
+} {2993}
+do_test delete-6.5.2 {
+  execsql {SELECT f1 FROM table1 ORDER BY f1}
+} {1 2 3 4 5 6 7}
+do_test delete-6.6 {
+  execsql {DELETE FROM table2 WHERE f1>7}
+  execsql {SELECT f1 FROM table2 ORDER BY f1}
+} {1 2 3 4 5 6 7}
+do_test delete-6.7 {
+  execsql {DELETE FROM table1}
+  execsql {SELECT f1 FROM table1}
+} {}
+do_test delete-6.8 {
+  execsql {INSERT INTO table1 VALUES(2,3)}
+  execsql {SELECT f1 FROM table1}
+} {2}
+do_test delete-6.9 {
+  execsql {DELETE FROM table2}
+  execsql {SELECT f1 FROM table2}
+} {}
+do_test delete-6.10 {
+  execsql {INSERT INTO table2 VALUES(2,3)}
+  execsql {SELECT f1 FROM table2}
+} {2}
+integrity_check delete-6.11
+
+do_test delete-7.1 {
+  execsql {
+    CREATE TABLE t3(a);
+    INSERT INTO t3 VALUES(1);
+    INSERT INTO t3 SELECT a+1 FROM t3;
+    INSERT INTO t3 SELECT a+2 FROM t3;
+    SELECT * FROM t3;
+  }
+} {1 2 3 4}
+ifcapable {trigger} {
+  do_test delete-7.2 {
+    execsql {
+      CREATE TABLE cnt(del);
+      INSERT INTO cnt VALUES(0);
+      CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
+        UPDATE cnt SET del=del+1;
+      END;
+      DELETE FROM t3 WHERE a<2;
+      SELECT * FROM t3;
+    }
+  } {2 3 4}
+  do_test delete-7.3 {
+    execsql {
+      SELECT * FROM cnt;
+    }
+  } {1}
+  do_test delete-7.4 {
+    execsql {
+      DELETE FROM t3;
+      SELECT * FROM t3;
+    }
+  } {}
+  do_test delete-7.5 {
+    execsql {
+      SELECT * FROM cnt;
+    }
+  } {4}
+  do_test delete-7.6 {
+    execsql {
+      INSERT INTO t3 VALUES(1);
+      INSERT INTO t3 SELECT a+1 FROM t3;
+      INSERT INTO t3 SELECT a+2 FROM t3;
+      CREATE TABLE t4 AS SELECT * FROM t3;
+      PRAGMA count_changes=ON;
+      DELETE FROM t3;
+      DELETE FROM t4;
+    }
+  } {4 4}
+} ;# endif trigger
+ifcapable {!trigger} {
+  execsql {DELETE FROM t3}
+}
+integrity_check delete-7.7
+
+# Make sure error messages are consistent when attempting to delete
+# from a read-only database.  Ticket #304.
+#
+do_test delete-8.0 {
+  execsql {
+    PRAGMA count_changes=OFF;
+    INSERT INTO t3 VALUES(123);
+    SELECT * FROM t3;
+  }
+} {123}
+db close
+catch {forcedelete test.db-journal}
+catch {file attributes test.db -permissions 0444}
+catch {file attributes test.db -readonly 1}
+sqlite3 db test.db
+set ::DB [sqlite3_connection_pointer db]
+do_test delete-8.1 {
+  catchsql {
+    DELETE FROM t3;
+  }
+} {1 {attempt to write a readonly database}}
+do_test delete-8.2 {
+  execsql {SELECT * FROM t3} 
+} {123}
+do_test delete-8.3 {
+  catchsql {
+    DELETE FROM t3 WHERE 1;
+  }
+} {1 {attempt to write a readonly database}}
+do_test delete-8.4 {
+  execsql {SELECT * FROM t3} 
+} {123}
+
+# Update for v3: In v2 the DELETE statement would succeed because no
+# database writes actually occur. Version 3 refuses to open a transaction
+# on a read-only file, so the statement fails.
+do_test delete-8.5 {
+  catchsql {
+    DELETE FROM t3 WHERE a<100;
+  }
+# v2 result: {0 {}}
+} {1 {attempt to write a readonly database}}
+do_test delete-8.6 {
+  execsql {SELECT * FROM t3}
+} {123}
+integrity_check delete-8.7
+
+# Need to do the following for tcl 8.5 on mac. On that configuration, the
+# -readonly flag is taken so seriously that a subsequent [forcedelete]
+# (required before the next test file can be executed) will fail.
+#
+catch {file attributes test.db -readonly 0}
+db close
+forcedelete test.db test.db-journal
+
+# The following tests verify that SQLite correctly handles the case
+# where an index B-Tree is being scanned, the rowid column being read
+# from each index entry and another statement deletes some rows from
+# the index B-Tree. At one point this (obscure) scenario was causing 
+# SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect
+# query results. 
+#
+do_test delete-9.1 {
+  sqlite3 db test.db
+  execsql {
+    CREATE TABLE t5(a, b);
+    CREATE TABLE t6(c, d);
+    INSERT INTO t5 VALUES(1, 2);
+    INSERT INTO t5 VALUES(3, 4);
+    INSERT INTO t5 VALUES(5, 6);
+    INSERT INTO t6 VALUES('a', 'b');
+    INSERT INTO t6 VALUES('c', 'd');
+    CREATE INDEX i5 ON t5(a);
+    CREATE INDEX i6 ON t6(c);
+  }
+} {}
+do_test delete-9.2 {
+  set res [list]
+  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
+    if {$r==2} { db eval { DELETE FROM t5 } }
+    lappend res $r $c $d
+  }
+  set res
+} {1 a b 1 c d 2 a b {} c d}
+do_test delete-9.3 {
+  execsql {
+    INSERT INTO t5 VALUES(1, 2);
+    INSERT INTO t5 VALUES(3, 4);
+    INSERT INTO t5 VALUES(5, 6);
+  }
+  set res [list]
+  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
+    if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } }
+    lappend res $r $c $d
+  }
+  set res
+} {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}
+do_test delete-9.4 {
+  execsql {
+    DELETE FROM t5;
+    INSERT INTO t5 VALUES(1, 2);
+    INSERT INTO t5 VALUES(3, 4);
+    INSERT INTO t5 VALUES(5, 6);
+  }
+  set res [list]
+  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
+    if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } }
+    lappend res $r $c $d
+  }
+  set res
+} {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}
+do_test delete-9.5 {
+  execsql {
+    DELETE FROM t5;
+    INSERT INTO t5 VALUES(1, 2);
+    INSERT INTO t5 VALUES(3, 4);
+    INSERT INTO t5 VALUES(5, 6);
+  }
+  set res [list]
+  db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
+    if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } }
+    lappend res $r $c $d
+  }
+  set res
+} {1 a b 1 c d 2 a b 2 c d}
+
+do_execsql_test delete-10.0 {
+  CREATE TABLE t1(a INT UNIQUE, b INT);
+  INSERT INTO t1(a,b) VALUES('1','2');
+  SELECT * FROM t1 WHERE a='1' AND b='2';
+} {1 2}
+
+do_execsql_test delete-10.1 {
+  DELETE FROM t1 WHERE a='1' AND b='2';
+}
+
+do_execsql_test delete-10.2 {
+  SELECT * FROM t1 WHERE a='1' AND b='2';
+}
+
+
+finish_test