SQLite changes() counts non-changing UPDATEs

Question

I have question regarding SQLite's changes() function, which, according to the documentation, "returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement" (also see the documentation of the underlying C/C++ function).

I was hoping to use this function to check whether the execution of an UPDATE statement pertaining to a single row has really caused that row to be changed or not. By changed I do not just mean that the row matched the statement's WHERE clause. No, instead what I mean is that, for the row in question, the value of at least 1 column is actually different after the execution compared to before. If you ask me this is the only proper definition of a change in this context.

So I was hoping to detect such changes by checking whether changes() returns 1 (row changed) or 0 (row unchanged) when called right after the execution of the UPDATE statement. But much to my despair this does not seem to work as expected.

Allow me to illustrate:

CREATE TABLE People (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL);
INSERT INTO People (Name) VALUES ("Astrid");
SELECT changes();

Here changes() returns 1, as expected because we just INSERTed 1 row.

UPDATE People SET Name = "Emma" WHERE Id = 1;
SELECT changes();

Here changes() returns 1, as expected because 1 row was UPDATEd (i.e. actually changed: the Name of the Person with Id = 1 was "Astrid" but is now "Emma").

UPDATE People SET Name = "John" WHERE Id = 200;
SELECT changes();

Here changes() returns 0, as expected because there is no row with Id = 200.

So far so good. But now have a look at the following UPDATE statement, which does indeed match an existing row, but does not actually change it at all (Name remains set to "Emma")...

UPDATE People SET Name = "Emma" WHERE Id = 1;
SELECT changes();

Here changes() returns 1, while I was of course hoping for 0 :-(.

Perhaps this would have made sense if the function was called something like matched_rows() or affected_rows(). But for a function called changes(), and documented as it is, this behaviour strikes me as illogical, or confusing at best.

So anyway, can somebody explain why this happens, or, even better, suggest an alternative strategy to achieve my goal in a reliable (and efficient) way?

All I can think of is to actually do something like SELECT * FROM People WHERE Id = x, compare all returned column values with the values I'm about to set in the UPDATE statement and thereby decide whether I need to execute the UPDATE at all. But that can't be very efficient, right? Of course in this toy example it might not matter much, but in my actual application I'm dealing with tables with many more columns, some of which are (potentially big) BLOBs.

Answer 1

The database does not compare old and new values; any UPDATEd row always counts as "changed" even if the values happen to be the same. The documentation says that

the UPDATE affects … those rows for which the result of evaluating the WHERE clause expression as a boolean expression is true.

If you want to check the old value, you have to do it explicitly:

UPDATE People SET Name = 'Emma' WHERE Id = 1 AND Name IS NOT 'Emma';