SQLite LIKE Clause Behavior with Percent as Escape Character

SQLite LIKE Clause and Percent Escape Character Ambiguity

The SQLite LIKE clause is a powerful tool for pattern matching in SQL queries, allowing users to search for specific patterns within text data. The % and _ characters are special wildcards in the LIKE pattern, where % matches any sequence of zero or more characters, and _ matches any single character. However, the behavior of the LIKE clause becomes ambiguous when the % character is used as an escape character. This ambiguity arises because the % character has a dual role: it serves as a wildcard and, when used with the ESCAPE clause, it can also be used to escape special characters.

The core issue revolves around the interpretation of the % character when it is designated as the escape character in a LIKE pattern. For example, consider the following query:

SELECT text FROM Test WHERE text LIKE '100%%' ESCAPE '%';

In this query, the % character is used both as a wildcard and as an escape character. The expected behavior is that the %% sequence should be interpreted as a literal % character, while the % at the end of the pattern should act as a wildcard. However, SQLite’s implementation of the LIKE clause does not handle this scenario as expected, leading to unexpected results.

The ambiguity is further compounded when considering the interaction between the escape character and the wildcard characters. If the % character is used as an escape character, then the sequence %% should logically represent a literal % character. However, in SQLite, this sequence is interpreted as a wildcard, leading to a mismatch between the expected and actual behavior.

Interrupted Pattern Interpretation Due to Escape Character Conflict

The root cause of this issue lies in the way SQLite processes the LIKE pattern when an escape character is specified. The SQLite engine first compresses consecutive matchAll characters (i.e., % characters) before testing the matchOther escape character. This compression step is intended to optimize the pattern matching process by reducing redundant wildcard characters. However, this optimization introduces a conflict when the escape character is also a wildcard character, such as %.

When the % character is used as an escape character, the compression step incorrectly interprets the %% sequence as a wildcard rather than a literal % character. This misinterpretation occurs because the compression step does not account for the possibility that the % character could also serve as an escape character. As a result, the pattern matching logic fails to correctly handle the escape sequence, leading to unexpected query results.

The conflict between the escape character and the wildcard character is further exacerbated by the fact that the SQLite documentation does not explicitly state that the escape character cannot be the same as the wildcard characters. This omission creates a gray area in the specification, leaving room for interpretation and leading to inconsistencies in behavior.

Implementing PostgreSQL-Compatible LIKE Clause Behavior

To address this issue, SQLite has adopted a behavior change to align with PostgreSQL’s handling of the LIKE clause when the % character is used as an escape character. This change ensures that the %% sequence is correctly interpreted as a literal % character, even when the % character is designated as the escape character. The updated behavior resolves the ambiguity and provides a consistent and predictable pattern matching experience.

The key to this solution lies in modifying the pattern comparison logic to prioritize the escape character over the wildcard characters. When the % character is used as an escape character, the pattern comparison logic should first check for escape sequences before applying the wildcard interpretation. This ensures that the %% sequence is treated as a literal % character, while the % character at the end of the pattern is correctly interpreted as a wildcard.

The following code snippet illustrates the updated pattern comparison logic:

static int patternCompare(
 const u8 *zPattern,       /* The glob pattern */
 const u8 *zString,        /* The string to compare against the glob */
 const struct compareInfo *pInfo, /* Information about how to do the compare */
 u32 matchOther          /* The escape char (LIKE) or '[' (GLOB) */
){
 u32 c, c2;            /* Next pattern and input string chars */
 u32 matchOne = pInfo->matchOne; /* "?" or "_" */
 u32 matchAll = pInfo->matchAll; /* "*" or "%" */
 u8 noCase = pInfo->noCase;    /* True if uppercase==lowercase */
 const u8 *zEscaped = 0;     /* One past the last escaped input char */
 
 while( (c = Utf8Read(zPattern))!=0 ){
  if( c==matchAll ){ /* Match "*" */
   /* Skip over multiple "*" characters in the pattern. If there
   ** are also "?" characters, skip those as well, but consume a
   ** single character of the input string for each "?" skipped */
   while( (c=Utf8Read(zPattern)) == matchAll || c == matchOne ){
    if( c == matchOne ){
     if( Utf8Read(zString)==0 ) return 0;
    }
   }
   if( c==0 ){
    return 1;
   }
   if( c==matchOther && *zPattern==matchAll ){
    zEscaped = zPattern;
    zPattern++;
    c = Utf8Read(zPattern);
   }
   while( (c2 = Utf8Read(zString))!=0 ){
    if( noCase ){
     c2 = sqlite3Tolower(c2);
     c = sqlite3Tolower(c);
    }
    if( c==c2 && patternCompare(zPattern, zString, pInfo, matchOther) ){
     return 1;
    }
   }
   return 0;
  }
  if( c==matchOther && *zPattern==matchAll ){
   zEscaped = zPattern;
   zPattern++;
   c = Utf8Read(zPattern);
  }
  if( c==matchOther && *zPattern==matchOne ){
   zEscaped = zPattern;
   zPattern++;
   c = Utf8Read(zPattern);
  }
  if( c==matchOther && *zPattern==matchOther ){
   zEscaped = zPattern;
   zPattern++;
   c = Utf8Read(zPattern);
  }
  if( c==0 ){
   return *zString==0;
  }
  if( noCase ){
   c = sqlite3Tolower(c);
   c2 = sqlite3Tolower(*zString);
  } else {
   c2 = *zString;
  }
  if( c!=c2 ){
   return 0;
  }
  zString++;
 }
 return *zString==0;
}

In this updated logic, the escape character is given precedence over the wildcard characters. When the % character is used as an escape character, the %% sequence is correctly interpreted as a literal % character, while the % character at the end of the pattern is treated as a wildcard. This ensures that the LIKE clause behaves consistently and predictably, even when the % character is used as an escape character.

The following table summarizes the behavior of the LIKE clause with different escape characters:

This table illustrates how the LIKE clause interprets patterns with different escape characters. When the % character is used as an escape character, the %% sequence is treated as a literal % character, while the % character at the end of the pattern is treated as a wildcard. This behavior aligns with PostgreSQL’s handling of the LIKE clause, providing a consistent and predictable pattern matching experience.

In conclusion, the ambiguity in SQLite’s LIKE clause behavior when the % character is used as an escape character has been resolved by adopting PostgreSQL-compatible behavior. This change ensures that the %% sequence is correctly interpreted as a literal % character, while the % character at the end of the pattern is treated as a wildcard. This solution provides a consistent and predictable pattern matching experience, eliminating the ambiguity and ensuring that the LIKE clause behaves as expected in all scenarios.