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 Hash: | 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:
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. |