Error with upsert and trigger WHEN clause?
(1) By miasuji on 2021-04-07 02:59:55 [source]
I'm hitting some unexpected behavior where an AFTER UPDATE
trigger's
WHEN
clause is being evaluated by an upsert, even though there is no
conflict (and hence, the trigger shouldn't be evaluated).
As an example, let's say we want to ensure that an item's name is always uppercase.
CREATE TABLE Items (name TEXT PRIMARY KEY);
CREATE TRIGGER uppercase_name AFTER UPDATE ON Items WHEN lower(name) != upper(name) BEGIN UPDATE Items SET name = upper(name); END;
(AFTER INSERT
trigger omitted for brevity)
Now we upsert an item:
INSERT INTO Items VALUES ('Apple') ON CONFLICT (name) DO UPDATE SET name = excluded.name;
This will produce Error: no such column: name
. It seems like the trigger shouldn't be evaluated at all since there is no conflict, and thus there's no UPDATE
happening. Is this expected behavior or a bug?
Notes:
- I tried a couple of things, curious if they would have any effect, but
they didn't:
- Inserting a row before the upsert
- Adding an
AFTER INSERT
trigger before theAFTER UPDATE
- I tested on versions
3.34.1
and3.35.4
. - Code to test/replicate is here: https://pastebin.com/raw/9YuAEywU
Thanks!
(2) By Keith Medcalf (kmedcalf) on 2021-04-07 03:43:22 in reply to 1 [link] [source]
Your trigger is in error. It refers to a column name
that does not exist. One presumes that you meant:
CREATE TRIGGER uppercase_name
AFTER UPDATE ON Items
WHEN lower(new.name) != upper(new.name)
BEGIN
UPDATE Items SET name = upper(new.name);
END;
However this is an ill-conceived trigger.
Firstly, it fires after any update of the Items
table whenever the lowercase version is not equal to the uppercase version of the new name. Since this condition is ALWAYS True unless the new name is NULL, it does not really do anything at all to restrict when the trigger fires -- it will always fire on all updates of anything in the Items
table except when the name
is updated to NULL.
Secondly, it updates every row of the Items
table and sets every name
to the uppercased name.
If recursive triggers are in effect, this will cause an endless loop which will be terminated only by the heat death of the multiverse.
What you probably meant is:
CREATE TRIGGER uppercase_name
AFTER UPDATE OF name ON Items
WHEN new.name IS NOT upper(new.name)
BEGIN
UPDATE Items SET name = upper(new.name) where rowid == new.rowid;
END;
which will only fire when the column name
of the table Items
has been updated, and then only when the new name is not the uppercase of the new name (which means it will not fire if the new name is the uppercase of the new name or if the new name is NULL) and then it will ONLY update the column name
of the Items
table to be the uppercase of the new name for only the row which was updated and caused the trigger to fire.
It will work even if recursive triggers are enabled without causing the heat death of the multiverse.
(3) By Keith Medcalf (kmedcalf) on 2021-04-07 03:52:35 in reply to 1 [link] [source]
The error was produced when parsing/preparing the statement, not at execution time (the statement never got that far) because the WHEN clause referred to an unknown/ambiguous column name.
(4.1) By Keith Medcalf (kmedcalf) on 2021-04-07 03:58:08 edited from 4.0 in reply to 1 [link] [source]
Note also that this will explode if you attempt to operate on the same name with different cases. You would fix this deficiency by adding a COLLATE NOCASE
to the table definition for the column Items.name
.
(5) By miasuji on 2021-04-07 15:47:01 in reply to 4.1 [link] [source]
D'oh! This was just an all around ill-conceived example. I was clearly too sleepy to be coding ;-)
The core issue I was hitting was checking against name
instead of new.name
- a silly oversight.
The rest of the things you point out are also totally correct, but in this case they are just artifacts of my (poorly) contriving a non-project-specific example to demonstrate the behavior and leaving out important IRL details like COLLATE NOCASE
, using a different primary key (or rowid
) etc...
Thanks for the detailed and quick response!
(6) By Igor Tandetnik (itandetnik) on 2021-04-07 23:03:25 in reply to 2 [link] [source]
the lowercase version is not equal to the uppercase version of the new name. Since this condition is ALWAYS True unless the new name is NULL
Nit: it is quite possible for a non-null, non-empty string s
to have lower(s) = upper(s)
. E.g. s='42'
. It just means that s
consists entirely of characters that don't have lowercase or uppercase counterparts, characters that case-fold to themselves.