SQLite

View Ticket
Login
Ticket UUID: 30027b613b44f77b7357cf3454510173d23e631d
Title: DELETE trigger on WITHOUT ROWID table not fired for REPLACE op
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-01-05 14:23:37
Version Found In: 3.16
User Comments:
dan added on 2017-01-04 19:12:11: (text/x-fossil-wiki)
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:

<verbatim>
 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;

</verbatim>

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

drh added on 2017-01-04 21:26:20: (text/x-fossil-wiki)
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.

drh added on 2017-01-05 14:23:37: (text/x-fossil-wiki)
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.