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:
- 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.
- Disallow "PRAGMA case_sensitive_like=on" if the schema
contains a LIKE operator.
- 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.
- 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.
- 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
- 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()
- 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
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.