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