The SQLite SET-AND Gotcha

In SQLite, suppose that we have the following table:

CREATE TABLE IF NOT EXISTS external_data (
    name     TEXT PRIMARY KEY NOT NULL,
    atime    TEXT NOT NULL,
    content  TEXT NOT NULL
);

And we have inserted a row:

  INSERT INTO external_data
       VALUES ("name", "october", "a");

Plus, we have executed an 'upsert' statement:

  INSERT INTO external_data
       VALUES ("name", "december", "b")
  ON CONFLICT (name)
DO UPDATE SET content = "b"
          AND atime = "december";

Without conflicts, it does what you expect. But we do have a conflict, and we end up with the following in the table:

sqlite> SELECT * FROM external_data ;
name|october|0

Instead of what we wanted to accomplish with the query, content received the value of "0" while the atime field remained with the old value "october" instead of being set with "december".

What the hell is going on

Well, we misplaced the SET separator , with AND because having AND in WHERE makes sense and we casually did cut&paste from a WHERE statement to a SET statement... well, this SET statement is in fact equivalent to the following:

SET content = ("b" AND atime = "december")

Where ("b" AND atime = "december") evaluates to 0 because the second AND operand is not true.

Wait, but SQLite has 'StrictMode'

Yes, there's StrictMode, but it doesn't help here as of SQLite 3.37.2. A String is a valid operand to AND.

Wait again, but there's STRICT for CREATE TABLE

Nope, doesn't help either. Sorry.

sqlime playground.