SQLite Forum

how to check update statement really modify a record
> One of these two things must be false.

Both are true:

1) sqlite has to read whole records in order to copy all of the fields (typically only a subset of which are affected by the update). sqlite necessarily does not update records in-place.

2) sqlite compares fields as required by the WHERE clause, but has no reason whatsoever to decode/compare any fields not matched by the WHERE (noting that a memcmp of the undecoded data would (A) require loading the whole field, of arbitrary size (maybe hundreds of MB), into RAM and (B) would not take collation into account). That behavior is reflected in the "changes" results.

> I find it hard to believe, and if it is true, surely there is a huge opportunity for optimization here. 

Regardless, the current behavior is very likely set in stone on compatibility grounds.

Keep in mind that comparing an arbitrary number of fields of arbitrary sizes in an arbitrary number of rows is computationally arbitrarily expensive.