On Wed, Nov 25, 2009 at 4:43 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
> Try to look at things not from the point of view of your application > but from the point of view of the SQLite itself. > > > 1. backward compatibility. It worked before upto 3.6.16. so, probably > it > > should work the same now. > > It was undefined behavior up to 3.6.16, it is undefined behavior now. > It's not a problem of SQLite that two undefined behaviors are not the > same and somebody relied on one of them or another. > > > 2. maybe an error should be thrown instead of silently failing (like > > oracle does, as you mentioned..). this will alert the apps using the > > triggers incorrectly but have been working with versions <= 3.6.16. > > It's not "silently failing". It silently works and works correctly, > although "correctly" is not correct from you app's POV. > > > in both before and after triggers, don't allow modifications to the > origin > > tables. (origin table = the table this trigger is on) > > throw error at execution time of the trigger, if this condition occurs. > > This will break a lot of applications (including yours). > So looking at all arguments your suggestions are not viable. It's like > suggesting to change C++ standard to make expressions like j = ++i + > ++i to work in some deterministic way. If you dared not to notice > "undefined behavior" warning in the documentation and relied on > something your particular version of compiler did then compiler is not > at fault when it changed behavior in another version. > > I have to disagree. failing safely is better than failing silently. thanks for the discussion. (except I don't see the c++ analogy :) ) > > Pavel > > On Tue, Nov 24, 2009 at 8:27 PM, Vasu Nori <vn...@google.com> wrote: > > On Tue, Nov 24, 2009 at 12:28 PM, Pavel Ivanov <paiva...@gmail.com> > wrote: > > > >> > I am not what you mean by Oracle's Before triggers have different > >> concept? > >> > care to explain? > >> > >> Sure. When Oracle calls your before update trigger it provides you old > >> values of the row and storage for new values of the row. You can > >> change whatever you like in this storage and be sure that it will make > >> its way into the table. You don't change these values with update > >> statement, you use simple assignment as to some pl/sql variable, don't > >> you? And in this way you can even change values that were provided in > >> update statement which could lead to somewhat "strange" behavior from > >> the point of view of the user not knowing about trigger existence. > >> Also in Oracle you cannot change other rows in the same table trigger > >> was called on at all. > > > > > >> In SQLite on the other hand you can change whatever table and whatever > >> rows you like (except the same rows before update trigger was called > >> on) but you have to issue update statement for that which will work > >> independently from the statement caused trigger to work in the first > >> place. Because of this very concept limitation you've got undefined > >> behavior if you change the same row... > >> > >> > This specific trigger and the conditions under which it operates do > not > >> > cause any such problems. > >> > > >> > but it is not... > >> > >> You're wrong. Just look closer on the statement drh pointed to: your > >> BEFORE UPDATE trigger modifies a row that should have been updated by > >> UPDATE statement which called the trigger (you're updating the row > >> with the same _id). So you're hitting exactly this explained undefined > >> behavior. > >> > >> > > thanks for the email. you are right. it is the same row. but my point is > a > > bit different. > > let me outline 2 different approaches to this. > > > > 1. backward compatibility. It worked before upto 3.6.16. so, probably > it > > should work the same now. > > 2. maybe an error should be thrown instead of silently failing (like > > oracle does, as you mentioned..). this will alert the apps using the > > triggers incorrectly but have been working with versions <= 3.6.16. > > > > in both before and after triggers, don't allow modifications to the > origin > > tables. (origin table = the table this trigger is on) > > throw error at execution time of the trigger, if this condition occurs. > > > > If there is enough encouragement on one or the other, I could do a > patch.. > > > > and thanks once again for your responses. sqlite is great and I would > like > > to help make it better by bringing issues to the forum.. > > > >> > >> Pavel > >> > >> On Tue, Nov 24, 2009 at 3:06 PM, Vasu Nori <vn...@google.com> wrote: > >> > On Tue, Nov 24, 2009 at 11:25 AM, Pavel Ivanov <paiva...@gmail.com> > >> wrote: > >> > > >> >> > yes it is risky, in general. but in this specific case, I expect no > >> >> > problems. > >> >> > just as a reference, this works just fine on mysql and oracle.. > >> >> > and used to work quite well until sqlite 3.6.16. > >> >> > >> >> I don't know anything about MySQL but Oracle has completely different > >> >> concept of BEFORE UPDATE triggers, so you cannot compare it with > >> >> SQLite in here. > >> >> > >> > I am not what you mean by Oracle's Before triggers have different > >> concept? > >> > care to explain? > >> > > >> > > >> >> And I wouldn't look at this issue as on a regression but as on some > >> >> undocumented feature that you relied upon and it was changed without > a > >> >> warning. I guess now SQLite works like this: > >> >> 1) you issue update to change d; > >> >> 2) SQLite finds row that you want to update; > >> >> 3) remembers its old state and new state (including all fields that > >> >> you didn't update); > >> >> 4) executes your trigger; > >> >> 5) writes into database all new values remembered on step 3. > >> >> > >> >> I wouldn't say these steps are wrong but they lead to behavior you > >> caught > >> >> up. > >> >> > >> >> > >> >> Pavel > >> >> > >> >> On Tue, Nov 24, 2009 at 2:04 PM, Vasu Nori <vn...@google.com> wrote: > >> >> > On Tue, Nov 24, 2009 at 10:26 AM, Igor Tandetnik < > itandet...@mvps.org > >> >> >wrote: > >> >> > > >> >> >> Vasu Nori <vn...@google.com> wrote: > >> >> >> > wondering if this is a known issue in 3.6.20. > >> >> >> > > >> >> >> > create table t1(_id integer primary key, v integer, d integer); > >> >> >> > CREATE TRIGGER t1_trig BEFORE UPDATE ON t1 > >> >> >> > BEGIN > >> >> >> > update t1 SET v=OLD.v+1 WHERE NEW._id=OLD._id AND NEW.d!= > OLD.d; > >> >> >> > END; > >> >> >> > >> >> >> Realize that your trigger updates all records in t1, not just the > >> record > >> >> >> where t1._id = OLD._id. You probably don't want that. > >> >> >> > >> >> >> not quite true though.. although I did make a mistake by including > >> NEW > >> >> > infront of "_id" in trigger. > >> >> > > >> >> >> > insert into t1 values(1, 1,0); > >> >> >> > update t1 set d= 2 where _id = 1; <-- expected "v" = 2 > >> >> >> > select * FROM t1; <-- shows "v" = 1 > >> >> >> > >> >> >> I wonder - does a similar AFTER UPDATE trigger work? It sounds > risky > >> to > >> >> try > >> >> >> and modify a table ahead of another update. Though on the face of > it, > >> I > >> >> >> would expect your code to work as written. > >> >> >> > >> >> >> yes it is risky, in general. but in this specific case, I expect > no > >> >> > problems. > >> >> > just as a reference, this works just fine on mysql and oracle.. > >> >> > and used to work quite well until sqlite 3.6.16. > >> >> > > >> >> > thanks for your thoughts on this issue. > >> >> > > >> >> > > >> >> >> Igor Tandetnik > >> >> >> > >> >> >> _______________________________________________ > >> >> >> sqlite-users mailing list > >> >> >> sqlite-users@sqlite.org > >> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> >> >> > >> >> > _______________________________________________ > >> >> > sqlite-users mailing list > >> >> > sqlite-users@sqlite.org > >> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> >> > > >> >> _______________________________________________ > >> >> sqlite-users mailing list > >> >> sqlite-users@sqlite.org > >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> >> > >> > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
