SQLite
View Ticket
Not logged in
Ticket UUID: 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