/ View Ticket
Login
2009-08-24
01:37 Fixed ticket [efc02f97]: Trigger creation order affects query correctness plus 2 other changes (artifact: b6005089 user: drh)
01:35
Run the column cache in a new context when generating code for trigger programs. Fix for ticket [efc02f9779]. (check-in: dee1b8eb user: drh tags: trunk)
00:24 Ticket [efc02f97] Trigger creation order affects query correctness status still Open with 1 other change (artifact: 9c473ca6 user: drh)
00:21 Ticket [efc02f97]: 1 change (artifact: ab46aeb2 user: drh)
2009-08-23
21:36 Ticket [efc02f97]: 3 changes (artifact: 58e20de1 user: drh)
20:36 New ticket [efc02f97]. (artifact: a0f77139 user: anonymous)

Ticket Hash: efc02f9779194ed96405d5e42f569d71d576591c
Title: Trigger creation order affects query correctness
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2009-08-24 01:37:18
Version Found In: 3.6.17
Description:
I was able to boil down to a simple, reproducible test case. For intuition: we create a table to hold all values (named "p") and then two tables ("four" and "five") to hold filtered subsets of of the data, automatically populated via trigger on insert.
CREATE TABLE p (i INTEGER);
CREATE TABLE four (val INTEGER);
CREATE TABLE five (val INTEGER);
CREATE TRIGGER five_trigger AFTER INSERT ON p WHEN NEW.i=5 BEGIN INSERT INTO five (val) VALUES (NEW.i); END;
CREATE TRIGGER four_trigger AFTER INSERT ON p WHEN NEW.i=4 BEGIN INSERT INTO four (val) VALUES (NEW.i); END;
INSERT INTO p (i) VALUES (5);

SELECT * FROM five;

On version SQLite 3.6.17 (downloaded today from sqlite.org, compiled on OS X 10.5.8) the final SELECT statement returns no rows (incorrect). On version 3.4.0 (apparently pre-installed on OS X) the result is a single row with value of column "val" as the number 5 (correct).

If the order of the CREATE TRIGGER statements is reversed, I get the same (correct) behavior on both versions. Sorry, but I haven't tried any version in between, nor variation on column data types or trigger operations.


drh added on 2009-08-24 00:21:21:
Problem introduced by check-in [f099d6773a837dbe4ba85a8fda818e2d9466e743] on 2008-07-04 09:15:11.


drh added on 2009-08-24 01:37:18:
Fixed by check-in [dee1b8eb402f47]