# 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() # # Uses database handle [db] to execute "SELECT f2()". Returns either # the results or error message from the "SELECT f2()" 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() # # Return the value of . Unless 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