Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1547,16 +1547,29 @@ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } - if( ix==0 && pPk==pIdx && onError==OE_Replace && pPk->pNext==0 ){ + /* Collision detection may be omitted if all of the following are true: + ** (1) The conflict resolution algorithm is REPLACE + ** (2) The table is a WITHOUT ROWID table + ** (3) There are no secondary indexes on the table + ** (4) No delete triggers need to be fired if there is a conflict + ** (5) No FK constraint counters need to be updated if a conflict occurs. + */ + if( (ix==0 && pIdx->pNext==0) /* Condition 3 */ + && pPk==pIdx /* Condition 2 */ + && onError==OE_Replace /* Condition 1 */ + && ( 0==(db->flags&SQLITE_RecTriggers) || /* Condition 4 */ + 0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0)) + && ( 0==(db->flags&SQLITE_ForeignKeys) || /* Condition 5 */ + (0==pTab->pFKey && 0==sqlite3FkReferences(pTab))) + ){ sqlite3VdbeResolveLabel(v, addrUniqueOk); continue; } - /* Check to see if the new index entry will be unique */ sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, regIdx, pIdx->nKeyCol); VdbeCoverage(v); /* Generate code to handle collisions */ Index: test/fkey8.test ================================================================== --- test/fkey8.test +++ test/fkey8.test @@ -99,7 +99,69 @@ sqlite3_finalize $stmt set ret } $use_stmt } +#------------------------------------------------------------------------- +# The following tests check that foreign key constaint counters are +# correctly updated for any implicit DELETE operations that occur +# when a REPLACE command is executed against a WITHOUT ROWID table +# that has no triggers or auxiliary indexes. +# +reset_db +do_execsql_test 2.1.0 { + PRAGMA foreign_keys = on; + CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID; + CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED); + + INSERT INTO p1 VALUES(1, 'one'); + INSERT INTO p1 VALUES(2, 'two'); + INSERT INTO c1 VALUES(1); + INSERT INTO c1 VALUES(2); +} + +do_catchsql_test 2.1.2 { + BEGIN; + DELETE FROM p1 WHERE a=1; + INSERT OR REPLACE INTO p1 VALUES(2, 'two'); + COMMIT; +} {1 {FOREIGN KEY constraint failed}} + +reset_db +do_execsql_test 2.2.0 { + PRAGMA foreign_keys = on; + CREATE TABLE p2(a PRIMARY KEY, b); + CREATE TABLE c2( + x PRIMARY KEY, + y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED + ) WITHOUT ROWID; +} + +do_catchsql_test 2.2.1 { + BEGIN; + INSERT INTO c2 VALUES(13, 13); + INSERT OR REPLACE INTO c2 VALUES(13, 13); + DELETE FROM c2; + COMMIT; +} {0 {}} + +reset_db +do_execsql_test 2.3.0 { + PRAGMA foreign_keys = on; + CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID; + CREATE TABLE c3(x REFERENCES p3); + + INSERT INTO p3 VALUES(1, 'one'); + INSERT INTO p3 VALUES(2, 'two'); + INSERT INTO c3 VALUES(1); + INSERT INTO c3 VALUES(2); + + CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN + INSERT OR REPLACE INTO p3 VALUES(2, 'three'); + END; +} + +do_catchsql_test 2.3.1 { + DELETE FROM p3 WHERE a=1 +} {1 {FOREIGN KEY constraint failed}} finish_test ADDED test/triggerF.test Index: test/triggerF.test ================================================================== --- /dev/null +++ test/triggerF.test @@ -0,0 +1,72 @@ +# 2017 January 4 +# +# 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 triggerF +ifcapable {!trigger} { + finish_test + return +} + + +foreach {tn sql log} { + 1 { } { } + + 2 { + CREATE TRIGGER trd AFTER DELETE ON t1 BEGIN + INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1)); + END; + } {1one2 2two1 3three1} + + 3 { + CREATE TRIGGER trd BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1)); + END; + } {1one3 2two2 3three2} + + 4 { + CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN + INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1)); + END; + CREATE TRIGGER tr2 BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES(old.a || old.b || (SELECT count(*) FROM t1)); + END; + } {1one3 1one2 2two2 2two1 3three2 3three1} + +} { + reset_db + do_execsql_test 1.$tn.0 { + PRAGMA recursive_triggers = on; + CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID; + CREATE TABLE log(t); + } + + execsql $sql + + do_execsql_test 1.$tn.1 { + INSERT INTO t1 VALUES(1, 'one'); + INSERT INTO t1 VALUES(2, 'two'); + INSERT INTO t1 VALUES(3, 'three'); + + DELETE FROM t1 WHERE a=1; + INSERT OR REPLACE INTO t1 VALUES(2, 'three'); + UPDATE OR REPLACE t1 SET a=3 WHERE a=2; + } + + do_execsql_test 1.$tn.2 { + SELECT * FROM log ORDER BY rowid; + } $log +} + +finish_test +