Behavior change around triggers between 3.19 and 3.30

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Behavior change around triggers between 3.19 and 3.30

Dominique Devienne
BEFORE 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
AFTER 3.30.1 2019-10-10 20:19:45
18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2

Every 18 to 24 months we upgrade SQLite in a large commercial software suite.
Such a recent upgrade surfaced a major backward-compatibility issue in
the custom
upgrade mechanism of that software suite, when restoring old projects.

The problem was traced back to a bad trigger definition, using a WHEN
clause on an invalid column (that does NOT exist). It's of course
trivial to get rid of that trigger in the latest version of the
schema, but the fact the upgrade fails when processed using 3.30.1 is
a major issue and new.

The same upgrade, processed with 3.19.3, goes through without errors.

The upgrade executes an ordered linear series of +1 version upgrade
scripts (pretty common),
and the first few pure-DDL scripts execute OK, but a subsequent
upgrade script with DML fails, with an error about the non-existing
column referenced by the trigger. That one error of course fails the
whole custom upgrade process.

My first question would be to ask whether there's a pragma or
compile-time option to get back to the old behavior?

Second, any idea when this was introduced?

My guess would be that the first DML "triggers" the parsing of the
schema, which discovers the invalid trigger definition, which starts
failing in 3.30.1, while was "OK" in 3.19.1 (as in no errors
reported).

Is there anything we can do, short of reverting back to 3.19.3?

Note that all the SQL is processed by the official shell, not via custom code.
Also note we build from the amalgamation, mostly with default options,
adding JSON1.

Thanks for any help on the above. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior change around triggers between 3.19 and 3.30

Richard Hipp-3
On 1/30/20, Dominique Devienne <[hidden email]> wrote:
>
> My first question would be to ask whether there's a pragma or
> compile-time option to get back to the old behavior?

Did you try "PRAGMA legacy_alter_table=ON;"?

>
> Second, any idea when this was introduced?
>

People have been requesting enhanced ALTER TABLE support.  In order to
provide that, we had to change ALTER TABLE to do a full parse of the
entire schema, so that it can find all of the bits and pieces that
need altering.  This means that ALTER TABLE now also finds latent
syntax errors in the schema.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior change around triggers between 3.19 and 3.30

Dominique Devienne
On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp <[hidden email]> wrote:
> On 1/30/20, Dominique Devienne <[hidden email]> wrote:
> > My first question would be to ask whether there's a pragma or
> > compile-time option to get back to the old behavior?
>
> Did you try "PRAGMA legacy_alter_table=ON;"?

BINGO!!! Thanks a bunch Richard.

> > Second, any idea when this was introduced?
> People have been requesting enhanced ALTER TABLE support.  In order to
> provide that, we had to change ALTER TABLE to do a full parse of the
> entire schema, so that it can find all of the bits and pieces that
> need altering.  This means that ALTER TABLE now also finds latent
> syntax errors in the schema.

The strange thing though, is that I can't repro on a small example.
Despite using not_there in the trigger, and doing DML and ALTER TABLE,
still doesn't fail the same way as in production. What could be the cause? --DD

------------ Console #1 ------------
c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> create table t1(v);
sqlite> create table t2(id INTEGER PRIMARY KEY, v);
sqlite> create table t2bis(id INTEGER PRIMARY KEY, v);
sqlite> create trigger t2_on_insert_ok after insert on t2 begin insert
into t2bis(id,v) values(NEW.id, NEW.v); END;
sqlite> create trigger t2_on_update_ko after update of not_there on t2
when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v
where id=OLD.id; END;
sqlite> .exit

c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite>
sqlite> insert into t1(v) values (1);
sqlite> insert into t2(v) values (1, 'one');
Error: 2 values for 1 columns
sqlite> insert into t2(id, v) values (1, 'one');
sqlite> update t2 set v = 'uno' where id = 1;
sqlite> alter table t1 add column v2;
sqlite> alter table t2 add column v2;
sqlite> pragma legacy_alter_table;
sqlite>

------------ Console #2 ------------
C:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> select name, type from sqlite_master;
t1|table
t2|table
t2bis|table
t2_on_insert_ok|trigger
t2_on_update_ko|trigger
sqlite> insert into t1(v) values (2);
sqlite> insert into t2(id, v) values (2, 'two');
sqlite> update t2 set v = 'dos' where id = 2;
sqlite> alter table t1 add column v3;
sqlite> alter table t2 add column v3;
sqlite> pragma legacy_alter_table;
0
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior change around triggers between 3.19 and 3.30

Graham Holden
Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne <[hidden email]> wrote:

> The strange thing though, is that I can't repro on a small example.
> Despite using not_there in the trigger, and doing DML and ALTER TABLE,
> still doesn't fail the same way as in production. What could be the cause? --DD

I suspect it may only kick-in if you use ALTER TABLE to rename either
a table or column. As I understand it, the old behaviour was to JUST
rename the table/column; the new behaviour also scan triggers etc. and
renames any references to the table/column as well. Since a new column
cannot (shouldn't?) be referenced by a trigger, there's no real need
to check them.

Graham


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior change around triggers between 3.19 and 3.30

Dominique Devienne
On Thu, Jan 30, 2020 at 3:38 PM Graham Holden <[hidden email]> wrote:

> Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne <[hidden email]> wrote:
> > The strange thing though, is that I can't repro on a small example.
> > Despite using not_there in the trigger, and doing DML and ALTER TABLE,
> > still doesn't fail the same way as in production. What could be the cause? --DD
>
> I suspect it may only kick-in if you use ALTER TABLE to rename either
> a table or column. As I understand it, the old behaviour was to JUST
> rename the table/column; the new behaviour also scan triggers etc. and
> renames any references to the table/column as well. Since a new column
> cannot (shouldn't?) be referenced by a trigger, there's no real need
> to check them.

Good point. Confirmed below it seems. Thanks Graham. --DD

3.19.3 happily renamed the table, and the trigger is still invalid.
3.30.1 error'd out with: Error: error in trigger t2_on_update_ko: no
such column: OLD.not_there

------------ Console#1 on 3.19.3 ------------
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on t2
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on t2 when OLD.not_there != NEW.not_there begin update t2bis
set v=NEW.v where id=OLD.id; END

sqlite> alter table t2 rename to t2a;

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>

------------ Console#2 on 3.30.1 ------------
sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END

sqlite> alter table t2a rename to t2b;
Error: error in trigger t2_on_update_ko: no such column: OLD.not_there

sqlite> select name, sql from sqlite_master where type = 'trigger';
t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a"
begin insert into t2bis(id,v) values(NEW.id, NEW.v); END
t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of
not_there on "t2a" when OLD.not_there != NEW.not_there begin update
t2bis set v=NEW.v where id=OLD.id; END
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users