View Ticket
Ticket Hash: a340eef47b0cad59f01889a2d92cb75a7a1cb1a3
Title: PRAGMA case_sensitive_like can corrupt some databases.
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Wont_Fix
Last Modified: 2019-05-08 17:29:15
Version Found In: 3.28.0
User Comments:
drh added on 2019-05-01 13:09:08: (text/x-fossil-wiki)
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

  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()

  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

drh added on 2019-05-08 17:28:56: (text/x-fossil-wiki)
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.