diff options
Diffstat (limited to 'test/tkt3718.test')
| -rw-r--r-- | test/tkt3718.test | 230 |
1 files changed, 230 insertions, 0 deletions
diff --git a/test/tkt3718.test b/test/tkt3718.test new file mode 100644 index 0000000..e5eb247 --- /dev/null +++ b/test/tkt3718.test @@ -0,0 +1,230 @@ +# 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 execution of SQL statements from +# within callbacks generated by VMs that themselves open statement +# transactions. +# +# $Id: tkt3718.test,v 1.2 2009/06/05 17:09:12 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_test tkt3718-1.1 { + execsql { + CREATE TABLE t1(a PRIMARY KEY, b); + INSERT INTO t1 VALUES(1, 'one'); + INSERT INTO t1 VALUES(2, 'two'); + INSERT INTO t1 VALUES(3, 'three'); + INSERT INTO t1 VALUES(4, 'four'); + INSERT INTO t1 VALUES(5, 'five'); + CREATE TABLE t2(a PRIMARY KEY, b); + } +} {} + +# SQL scalar function: +# +# f1(<arg>) +# +# Uses database handle [db] to execute "SELECT f2(<arg>)". Returns either +# the results or error message from the "SELECT f2(<arg>)" query to the +# caller. +# +proc f1 {args} { + set a [lindex $args 0] + catch { db eval {SELECT f2($a)} } msg + set msg +} + +# SQL scalar function: +# +# f2(<arg>) +# +# Return the value of <arg>. Unless <arg> is "three", in which case throw +# an exception. +# +proc f2 {args} { + set a [lindex $args 0] + if {$a == "three"} { error "Three!!" } + return $a +} + +db func f1 f1 +db func f2 f2 + +# The second INSERT statement below uses the f1 user function such that +# half-way through the INSERT operation f1() will run an SQL statement +# that throws an exception. At one point, before #3718 was fixed, this +# caused the statement transaction belonging to the INSERT statement to +# be rolled back. The result was that some (but not all) of the rows that +# should have been inserted went missing. +# +do_test tkt3718-1.2 { + execsql { + BEGIN; + INSERT INTO t2 SELECT a, b FROM t1; + INSERT INTO t2 SELECT a+5, f1(b) FROM t1; + COMMIT; + } + execsql { + SELECT a FROM t2; + } +} {1 2 3 4 5 6 7 8 9 10} + +# This test turns on the count_changes pragma (causing DML statements to +# return SQLITE_ROW once, with a single integer result value reporting the +# number of rows affected by the statement). It then executes an INSERT +# statement that requires a statement journal. After stepping the statement +# once, so that it returns SQLITE_ROW, a second SQL statement that throws an +# exception is run. At one point, before #3718 was fixed, this caused the +# statement transaction belonging to the INSERT statement to be rolled back. +# The result was that none of the rows were actually inserted. +# +# +do_test tkt3718-1.3 { + execsql { + DELETE FROM t2 WHERE a > 5; + PRAGMA count_changes = 1; + BEGIN; + } + db eval {INSERT INTO t2 SELECT a+5, b||'+5' FROM t1} { + catch { db eval {SELECT f2('three')} } msg + } + execsql { + COMMIT; + SELECT a FROM t2; + } +} {1 2 3 4 5 6 7 8 9 10} + +do_test tkt3718-1.4 { + execsql {pragma count_changes=0} +} {} + +# This SQL function executes the SQL specified as an argument against +# database [db]. +# +proc sql {doit zSql} { + if {$doit} { catchsql $zSql } +} +db func sql [list sql] + +# The following tests, tkt3718-2.*, test that a nested statement +# transaction can be successfully committed or reverted without +# affecting the parent statement transaction. +# +do_test tkt3718-2.1 { + execsql { SELECT sql(1, 'DELETE FROM t2 WHERE a = '||a ) FROM t2 WHERE a>5 } + execsql { SELECT a from t2 } +} {1 2 3 4 5} +do_test tkt3718-2.2 { + execsql { + DELETE FROM t2 WHERE a > 5; + BEGIN; + INSERT INTO t2 SELECT a+5, sql(a==3, + 'INSERT INTO t2 SELECT a+10, f2(b) FROM t1' + ) FROM t1; + } + execsql { + COMMIT; + SELECT a FROM t2; + } +} {1 2 3 4 5 6 7 8 9 10} +do_test tkt3718-2.3 { + execsql { + DELETE FROM t2 WHERE a > 5; + BEGIN; + INSERT INTO t2 SELECT a+5, sql(a==3, + 'INSERT INTO t2 SELECT a+10, b FROM t1' + ) FROM t1; + COMMIT; + } + execsql { SELECT a FROM t2 ORDER BY a+0} +} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} +integrity_check tkt3718.2-4 + +# The next set of tests, tkt3718-3.*, test that a statement transaction +# that has a committed statement transaction nested inside of it can +# be committed or reverted. +# +foreach {tn io ii results} { + 1 0 10 {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} + 2 1 10 {6 7 8 9 10 16 17 18 19 20} + 3 0 11 {1 2 3 4 5 6 7 8 9 10 16 17 18 19 20} + 4 1 11 {6 7 8 9 10 16 17 18 19 20} +} { + do_test tkt3718-3.$tn { + execsql { + DELETE FROM t2; + INSERT INTO t2 SELECT a+5, b FROM t1; + INSERT INTO t2 SELECT a+15, b FROM t1; + } + + catchsql " + BEGIN; + INSERT INTO t2 SELECT a+$io, sql(a==3, + 'INSERT INTO t2 SELECT a+$ii, b FROM t1' + ) FROM t1; + " + + execsql { COMMIT } + + execsql { SELECT a FROM t2 ORDER BY a+0} + } $results + + integrity_check tkt3718-3.$tn.integrity +} + +# This is the same test as tkt3718-3.*, but with 3 levels of nesting. +# +foreach {tn i1 i2 i3 results} { + 1 0 10 20 {5 10 15 20 25 30} + 2 0 10 21 {5 10 15 20 30} + 3 0 11 20 {5 10 20 30} + 4 0 11 21 {5 10 20 30} + 5 1 10 20 {10 20 30} + 6 1 10 21 {10 20 30} + 7 1 11 20 {10 20 30} + 8 1 11 21 {10 20 30} +} { + do_test tkt3718-4.$tn { + execsql { + DELETE FROM t2; + INSERT INTO t2 SELECT a+5, b FROM t1; + INSERT INTO t2 SELECT a+15, b FROM t1; + INSERT INTO t2 SELECT a+25, b FROM t1; + } + + catchsql " + BEGIN; + INSERT INTO t2 SELECT a+$i1, sql(a==3, + 'INSERT INTO t2 SELECT a+$i2, sql(a==3, + ''INSERT INTO t2 SELECT a+$i3, b FROM t1'' + ) FROM t1' + ) FROM t1; + " + + execsql { COMMIT } + + execsql { SELECT a FROM t2 WHERE (a%5)==0 ORDER BY a+0} + } $results + + do_test tkt3718-4.$tn.extra { + execsql { + SELECT + (SELECT sum(a) FROM t2)==(SELECT sum(a*5-10) FROM t2 WHERE (a%5)==0) + } + } {1} + + integrity_check tkt3718-4.$tn.integrity +} + + +finish_test |