SQLite Forum

Error with upsert and trigger WHEN clause?
Login

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?

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 the AFTER UPDATE
  • I tested on versions 3.34.1 and 3.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.

(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 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!