Error with upsert and trigger WHEN clause?
(1) By miasuji on 2021-04-07 02:59:55 [link] [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?
- I tried a couple of things, curious if they would have any effect, but
- Inserting a row before the upsert
- Adding an
AFTER INSERTtrigger before the
- I tested on versions
- Code to test/replicate is here: https://pastebin.com/raw/9YuAEywU
(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.
(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.
(3) By Keith Medcalf (kmedcalf) on 2021-04-07 03:52:35 in reply to 1 [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
(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
Thanks for the detailed and quick response!