SQLite Forum

Database Schema Changed Error
There are other cases in which a re-prepare will happen on calling sqlite3_exec even if the database schema has not changed if you are using SQLITE_ENABLE_STAT4.  

Consider the following simple example:

create table x(data text collate nocase unique);
select data from x where data like ?;

When you `sqlite3_prepare_v2` the select statement, the query planner has absolutely no idea whether to do a full-scan of data, or a partial-index scan of data because this decision depends on the value of the parameter.  So it generates a query plan which will always work, which is a full-scan of data.

However, when SQLITE_ENABLE_STAT4 is in effect, then the statement will be re-prepared when sqlite3_exec is called in order that the "value" of the parameter may be examined, in this case to isolate whether these is a fixed prefix to the like parameter that can be used to limit the scan and if so, will regenerate the statement plan to now use a partial-index scan of data rather than a full-scan of data.

Since SQLITE_ENABLE_STAT4 collects additional histogram (selectivity) statistics, an additional re-prepare may occur at sqlite3_exec time for any statement in which an "indexed" relation to a parameter is used because the planner might not be able to determine the most efficient plan until after the parameters to the query are known, and this does not happen until sqlite3_exec is executed for the first time after the parameters are bound.

This behaviour is why the use of SQLITE_ENABLE_STAT4 may violate the Query Plan Stability guarantee since it enables re-evaluation of the plan after the bindings are set based on the values of the parameters, and not merely based on the query itself, the schema, and the statistics.