SQLite

If "PRAGMA recursive_triggers=ON" is configured, then DELETE triggers should fire for any rows deleted by REPLACE conflict handling. This is broken for WITHOUT ROWID tables in 3.16:

 PRAGMA recursive_triggers = 1;
 CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT ROWID;
 CREATE TABLE del(a, b);
 CREATE TRIGGER t1d AFTER DELETE ON t1 BEGIN
   INSERT INTO del VALUES(old.a, old.b);
 END;
 INSERT INTO t1 VALUES(1, 'one');
 REPLACE INTO t1 VALUES(1, 'two');  -- causes DELETE, should fire trigger but does not
 SELECT * FROM del;

With version 3.15, the final SELECT correctly returns a single row - {1, 'one'}. 3.16 returns zero rows.

This problem was introduced by check-in [925840cf] and first appeared in the 3.16.0 release. The problem was first detected by the Firefox regression tests (see https://bugzilla.mozilla.org/show_bug.cgi?id=1328161) shortly after the 3.16.0 release.

As far as we know at this time, the minimum conditions necessary to invoke this problem are:

1. Recursive triggers enabled by "PRAGMA recursive_triggers=ON".
2. A WITHOUT ROWID table.
3. A DELETE trigger on the WITHOUT ROWID table.
4. Run an INSERT OR REPLACE operation on the WITHOUT ROWID table that overwrites a preexisting row.

The DELETE trigger is suppose to fire in that case, but it does not in version 3.16.0.

Check-in [925840cf] is a performance optimization that helps REPLACE operations run faster by directly overwriting the existing row rather than doing separate delete and insert operations. But fewer b-tree manipulations are required if the row is simply overwritten. Unfortunately, optimizing out the delete operation also seems to have disabled the DELETE trigger.

The same optimization from check-in [925840cf] also causes problems with foreign key constraint enforcements on REPLACE statements for WITHOUT ROWID tables that lack secondary indexes.

The optimization that causes all the trouble is seen here: https://www.sqlite.org/src/artifact/0db6364a6a64e521?ln=1561-1564. That block of code causes the separate OP_Delete operation on REPLACE to be omitted, causing the record to be directly overwritten, for WITHOUT ROWID tables that lack secondary indexes.

The correct optimization is here: https://www.sqlite.org/src/artifact/7af46a3be2656f5e?ln=1552-1569. The problem is that the original change in 3.16.0 and 3.16.1 omitted conditions 4 and 5, and thus omitted the separate OP_Delete operation in cases where it was actually needed.