SQLite

The "PRAGMA case_sensitive_like" command changes the behavior of the LIKE operator. But that means that if the LIKE operator is used in the schema, for example in a CHECK constraint or in a partial index or an index on an expression, then a change in behavior of LIKE can cause the database content to disagree with the schema.

This issue was brought to our attention by a mailing list post from Manuel Rigger.

This problem is unusual in that it is a defect in the design of SQLite, not a defect in the implementation.

It is unclear what can or should be done to resolve this issue. Possible resolutions include, but are not limited to, the following:

1. Mark the LIKE operator as non-deterministic so that it cannot be used in the schema. This solution runs into problems of what to do with historical schemas that already use the LIKE operator. Note that (due to a separate coding error) the LIKE operator already is marked as non-deterministic *after* the case_sensitive_like pragma has been run, just not before.

2. Disallow "PRAGMA case_sensitive_like=on" if the schema contains a LIKE operator.

3. Cause LIKE operators in the schema to ignore case (the default behavior) regardless of the case_sensitive_like setting. In other words, change case_sensitive_like to only affect LIKE operators in transient queries.

4. Simply document the fact that "PRAGMA case_sensitive_like" can cause a corrupt database. Perhaps also disable case_sensitive_like when SQLITE_DBCONFIG_DEFENSIVE is enabled.

5. Automatically run REINDEX on indexes that contain a LIKE operator when the case_sensitive_like setting changes. This would only fix problems in indexes, though, not in CHECK constraints.

6. Deprecate or disable the case_sensitive_like pragma. Replace it with a C-language API of some kind. This prevents the database from being corrupted by an SQL script. The database schema can still get out-of-sync with the database content by C code, but that is also true if the C code does something like redefine another built-in function using the sqlite3_create_function() interface.

7. If the schema contains a LIKE operator and the case_sensitive_like pragma is turned on, disable all indexes that use LIKE and mark the whole database as read-only. This prevents index corruption, but does nothing to fix CHECK constraints that are rendered invalid.

For now, this issue is merely documented. No change are made to the code, except for a new compile-time option to complete disable the PRAGMA case_sensitive_like command. We might revisit this choice later.