|17:29||• Closed ticket [a340eef4]: PRAGMA case_sensitive_like can corrupt some databases. plus 2 other changes (artifact: c318aa85 user: drh)|
|17:28||• Deferred ticket [a340eef4]. (artifact: 09375ae5 user: drh)|
|17:27||Provide the SQLITE_OMIT_CASE_SENSITIVE_LIKE_PRAGMA compile-time option to omit the case_sensitive_like pragma. This change, in combination with documentation changes, is the current solution to ticket [a340eef47b0cad5]. (check-in: eabe7f2d user: drh tags: trunk)|
|13:09||• New ticket [a340eef4] PRAGMA case_sensitive_like can corrupt some databases.. (artifact: 42a32680 user: drh)|
|Title:||PRAGMA case_sensitive_like can corrupt some databases.|
|Last Modified:||2019-05-08 17:29:15|
|Version Found In:||3.28.0|
drh added on 2019-05-01 13:09:08:
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:
drh added on 2019-05-08 17:28:56:
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.