/ View Ticket
Login
2017-01-05
14:23 Fixed ticket [30027b61]: DELETE trigger on WITHOUT ROWID table not fired for REPLACE op plus 4 other changes artifact: 8389ee53 user: drh
13:56
Fix problems in trigger and foreign key handling when doing REPLACE on a WITHOUT ROWID table that has no secondary indexes. Fix for ticket [30027b613b4]. check-in: 8a788594 user: drh tags: branch-3.16
13:50
Fix problems in trigger and foreign key handling when doing REPLACE on a WITHOUT ROWID table that has no secondary indexes. Fix for ticket [30027b613b4]. check-in: 571f166e user: drh tags: trunk
2017-01-04
22:02
Improved the comment on the block of code the provides the performance optimization originally added by check-in [925840cfdb]. The original check-in omitted condition 4, which was the cause of bug [30027b613b]. check-in: c6506b82 user: drh tags: replace-fix
21:26 Ticket [30027b61] DELETE trigger on WITHOUT ROWID table not fired for REPLACE op status still Open with 6 other changes artifact: 53472fbe user: drh
20:13
Possible fix for [30027b61]. There may still be problems surrounding foreign key processing. check-in: 71ccb1f4 user: dan tags: replace-fix
19:12 New ticket [30027b61] DELETE trigger on WITHOUT ROWID table not fired for REPLACE op. artifact: b5b5fd5d user: dan
2016-11-11
19:01
Enhance the OP_IdxInsert opcode so that it can used unpacked key values if they are available. Update the code generator to take advantage of this new capability. The speedtest1.c test is about 2.6% faster as a result. Later: This also introduced bug [30027b613b]. Bummer. check-in: 925840cf user: drh tags: trunk

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:

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.


drh added on 2017-01-04 21:26:20:

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:

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.