SQLite REGEXP Issues with Japanese Characters and End-of-String Anchors

Issue Overview: REGEXP Matching Behavior for Multi-Byte Characters and Syntax Constraints

The core issues revolve around two distinct but related problems in SQLite’s REGEXP function:

1. Unexpected Matching Failures with Japanese (Multi-Byte) Characters:

   • Direct matches for strings containing Japanese characters (e.g., 日本語) return no results unless specific conditions are met, such as prefixing the regex with ^ (start-of-string anchor) or using alternations (e.g., 日本語|dummy).
   • Partial matches (e.g., 本語 or 語) fail when combined with ^.* but succeed with .*.
   • Emojis (4-byte Unicode characters) exhibit similar behavior: direct matches fail, while patterns like .*😎 work.

2. Syntax Errors with $ (End-of-String Anchor) in Complex Patterns:

   • Using $ in non-terminal positions within a regex (e.g., ample$|^foo) triggers a "Runtime error: unrecognized character".
   • Attempting to group $ within parentheses (e.g., #(1$|200)) causes "unmatched ‘(‘" errors.

These issues stem from SQLite’s regex engine implementation, which historically had limitations in handling multi-byte Unicode characters and parsing certain regex syntax configurations.

Possible Causes: Multi-Byte Character Processing and Regex Engine Limitations

1. Multi-Byte Character Handling in SQLite’s Regex Engine

SQLite’s default regex engine (a derivative of the public-domain regexp.c library) processes input as a sequence of bytes rather than Unicode code points. This leads to incorrect behavior with multi-byte UTF-8 characters:

• 3-Byte and 4-Byte Characters: Japanese characters like 日 (U+65E5) and emojis like 😎 (U+1F60E) are encoded as 3-byte and 4-byte UTF-8 sequences, respectively. The regex engine’s byte-oriented logic fails to treat these sequences as single logical characters.
  • Example: Matching ^日本語 works because the engine anchors the match to the start of the string, aligning byte offsets. However, 日本語 (without ^) fails because the engine may misinterpret intermediate bytes as standalone characters.
• Greedy Quantifiers and Lookaheads: Patterns like ^.*日本語 fail because .* consumes bytes greedily, potentially "overshooting" valid multi-byte boundaries and leaving an invalid fragment for the subsequent 日本語 match.

2. Syntax Parsing Limitations for $ Anchors

The $ anchor is designed to assert the end of a string. However, SQLite’s regex engine historically enforced strict parsing rules:

• Positional Constraints: In older versions, $ was only recognized as an end-of-string anchor if it appeared at the very end of the regex pattern. Placing $ earlier (e.g., ample$|^foo) confused the parser, leading to syntax errors.
• Parentheses and Grouping: Using $ within groups (e.g., #(1$|200)) triggered parser errors due to incomplete handling of nested syntax elements.

3. Encoding Configuration Missteps

While SQLite supports UTF-8 and UTF-16 via PRAGMA encoding, misconfigurations or incorrect assumptions about encoding conversions can exacerbate regex issues:

• UTF-16 Pitfalls: Setting PRAGMA encoding="UTF-16le"; does not retroactively convert existing data. Tables created before switching encoding retain their original encoding, leading to inconsistent regex behavior.
• Collation Sequences: The default BINARY collation treats strings as raw byte sequences, which can misalign with Unicode-aware regex expectations.

Troubleshooting Steps, Solutions & Fixes

1. Apply Patches or Upgrade SQLite

The root cause of the Japanese character matching issue was addressed in SQLite check-in c94595a6e15490b4, which modifies regexp.c to properly advance through multi-byte UTF-8 characters:

• Steps:
  1. Rebuild SQLite with the patched regexp.c or upgrade to a version including this fix (SQLite 3.39.0+).
  2. Verify the fix by re-running the failing regex queries.

For the $ anchor issue, an enhancement in SQLite trunk (post-3.39.0) improves regex parsing:

• Steps:
  1. Build SQLite from the latest trunk source.
  2. Test complex patterns like ample$|^foo and #(1$|200).

2. Adjust Regex Patterns for Multi-Byte Awareness

When working with multi-byte characters, structure regex patterns to align with SQLite’s byte-oriented engine:

• Explicit Anchors: Use ^ and $ to anchor matches, reducing ambiguity in byte alignment.
  • Example: SELECT * FROM TESTTABLE WHERE regexp('^日本語$', TESTSTRING);
• Avoid Greedy Quantifiers with Partial Matches: Replace ^.*日本語 with .*日本語 to allow flexible backtracking.
• Escape Critical Characters: Ensure $, ^, and | are properly escaped or positioned.

3. Use Unicode-Aware Alternatives

If regex limitations persist, consider SQLite’s built-in string functions or extensions:

• LIKE and GLOB with Wildcards:

  -- Match exact string
  SELECT * FROM TESTTABLE WHERE TESTSTRING = '日本語';
  
  -- Match suffix
  SELECT * FROM TESTTABLE WHERE TESTSTRING GLOB '*語';
  

• User-Defined Functions (UDFs):
  Integrate a Unicode-aware regex library (e.g., PCRE2) via SQLite’s loadable extension mechanism.

4. Validate Encoding and Collation Settings

Ensure consistent encoding across the database lifecycle:

• Set Encoding at Creation:

  PRAGMA encoding="UTF-8";  -- Or UTF-16le/be
  

  Before creating tables.

• Use Unicode Collations:

  CREATE TABLE TESTTABLE(
    ID INTEGER PRIMARY KEY AUTOINCREMENT, 
    TESTSTRING TEXT COLLATE ICU
  );
  

  (Requires SQLite compiled with ICU extension.)

5. Workarounds for $ Anchor Limitations

For older SQLite versions without the trunk fix, redesign regex patterns to avoid $ in non-terminal positions:

• Factorize Alternations:

  -- Original failing pattern: ample$|^foo
  -- Rewrite as:
  SELECT * FROM TESTTABLE WHERE regexp('^foo', TESTSTRING) OR regexp('ample$', TESTSTRING);
  

• Leverlookaheads (Advanced):
  Use UDFs for lookahead support if critical.

6. Comprehensive Testing for Edge Cases

After applying fixes, test extensively with:

• Multi-byte characters across UTF-8/UTF-16.
• Emojis, CJK ideographs, and combining characters.
• Complex regex patterns involving anchors, groups, and alternations.

By addressing SQLite’s regex engine limitations through patches, pattern adjustments, and encoding best practices, developers can achieve consistent Unicode-aware matching and avoid syntax-related runtime errors.