| 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: drh added on 2009-08-24 01:37:18: | ||||