GLOB Operator Returns Differing Results Across Database Encodings
Understanding GLOB Operator Behavior with Different Text Encodings
The core issue arises when using the GLOB operator to compare values stored as integers containing binary data across databases with different text encodings (UTF-8 vs. UTF-16le). The GLOB operator interprets all inputs as text strings, triggering implicit type conversions that depend on the database’s active encoding. When a binary value stored in an integer column is cast to text during a GLOB comparison, the byte sequence is decoded according to the database encoding, producing different characters and pattern-matching outcomes.
In UTF-16le, the byte sequence x'5BB5AB90' decodes to valid Unicode characters ("땛邫") that match themselves in a GLOB operation. In UTF-8, the same bytes decode to [塐 where the initial [ is a wildcard operator in GLOB syntax, causing the comparison to fail. This discrepancy stems from SQLite’s handling of type affinity and encoding-sensitive conversions between binary data and text. The integer column’s type affinity allows storage of blobs, but operations requiring text force an encoding-dependent reinterpretation of bytes, exposing fundamental differences in how SQLite treats blobs vs. text.
Root Causes of Inconsistent GLOB Results Across Encodings
1. Blob-to-Text Conversion Logic Tied to Database Encoding
SQLite performs automatic type conversion when a blob (stored in an integer column) is used in a text context like GLOB. The cast from blob to text uses the database’s declared encoding (UTF-8/16le/16be) to interpret raw bytes as character sequences. For example:
x'5B'becomes'['in UTF-8 (a wildcard metacharacter)x'5BB5'in UTF-16le becomes Unicode code point0xB55B("땛")
This encoding-sensitive conversion alters the semantic meaning of the data when used in pattern-matching operations. The same binary value yields different text representations depending on the encoding, directly impacting GLOB results.
2. GLOB Metacharacters Introduced via Encoding Artifacts
The GLOB operator treats [, ], *, ?, and \ as special characters. When a blob’s byte sequence decodes to these characters in a specific encoding, unintended pattern-matching logic is invoked. In UTF-8, the byte x'5B' is '[', which opens a character class in GLOB, causing syntax errors or mismatches if not closed properly. In UTF-16le, the same byte is part of a multi-byte character, avoiding metacharacter conflicts. This creates a paradox where the same binary data is both a valid literal and a malformed pattern, depending on encoding.
3. Implicit Type Casting with Integer Columns
Storing binary data in an integer column (via x'...' syntax) circumvents SQLite’s type-strictness. While SQLite allows this for flexibility, it creates ambiguity when the value is used in a text operation. The integer column’s type affinity does not enforce numeric constraints, allowing raw bytes to persist. When the GLOB operator forces a text comparison, SQLite applies encoding rules to reinterpret the bytes as text, leading to unpredictable results. This violates the principle of least astonishment for users expecting binary data to remain inert in non-text contexts.
Resolving Encoding-Dependent GLOB Comparisons and Casting Pitfalls
Step 1: Use Explicit Text Encodings for Binary Data Comparisons
Avoid relying on implicit blob-to-text conversions. When comparing binary data as text, explicitly encode/decode using consistent rules:
-- For UTF-16le comparisons
SELECT c0 FROM t0 WHERE CAST(c0 AS TEXT) GLOB CAST(c0 AS TEXT);
-- For UTF-8 comparisons (with hex encoding)
SELECT c0 FROM t0 WHERE HEX(c0) GLOB HEX(c0);
This forces a consistent interpretation of the binary data. However, note that HEX() converts bytes to ASCII, avoiding encoding ambiguities but increasing storage overhead.
Step 2: Normalize Text Encodings Before Comparison
Ensure all text comparisons occur in a single encoding. Convert blobs to text using application-layer logic before insertion, then store as TEXT with a declared encoding:
PRAGMA encoding = 'UTF-8';
CREATE TABLE t0 (c0 TEXT); -- Enforce text storage
INSERT INTO t0 VALUES (CAST(x'5BB5AB90' AS TEXT)); -- Explicit UTF-8 conversion
This makes GLOB behavior consistent but requires pre-encoding binary data, which may not be feasible for arbitrary blobs.
Step 3: Avoid GLOB for Binary Data Comparisons
Use blob-safe operators like = or IN when comparing raw bytes. Since GLOB is text-centric, convert comparisons to exact matches:
SELECT c0 FROM t0 WHERE c0 = x'5BB5AB90'; -- Exact byte match
For pattern matching on binary data, use LIKE with hex strings or application-defined functions.
Step 4: Leverage Strict Typing with BLOB Columns
Store binary data in BLOB columns to prevent unintended text conversions. SQLite treats BLOB values as inert byte sequences, avoiding encoding reinterpretations:
CREATE TABLE t0 (c0 BLOB); -- Strict blob typing
INSERT INTO t0 VALUES (x'5BB5AB90');
SELECT c0 FROM t0 WHERE c0 GLOB c0; -- Still problematic but less likely
While GLOB will still attempt text conversion, using BLOB columns signals intent and reduces accidental type coercion.
Step 5: Implement Custom Encoding Conversion Functions
Create SQL extension functions to handle explicit encoding conversions. For example, a BLOB_TO_TEXT function with a specified encoding:
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
void blob_to_text(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
const char *encoding = (const char*)sqlite3_value_text(argv[1]);
const void *blob = sqlite3_value_blob(argv[0]);
int len = sqlite3_value_bytes(argv[0]);
// Convert blob to text using specified encoding
// (Implementation omitted for brevity)
sqlite3_result_text(ctx, converted_text, -1, SQLITE_TRANSIENT);
}
// Register function
int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi);
sqlite3_create_function(db, "BLOB_TO_TEXT", 2, SQLITE_UTF8, NULL, blob_to_text, NULL, NULL);
return SQLITE_OK;
}
Usage:
SELECT c0 FROM t0 WHERE BLOB_TO_TEXT(c0, 'UTF-16le') GLOB BLOB_TO_TEXT(c0, 'UTF-16le');
Step 6: Validate and Sanitize Binary Data for Text Contexts
Before using binary data in text operations, validate that the bytes form valid character sequences in the target encoding. Use SQLite’s CAST with error handling:
SELECT c0 FROM t0 WHERE
CASE
WHEN CAST(c0 AS TEXT) IS NOT NULL THEN CAST(c0 AS TEXT) GLOB CAST(c0 AS TEXT)
ELSE FALSE
END;
This excludes invalid text conversions but adds computational overhead.
Step 7: Standardize Database Encoding Early
Set the database encoding pragma immediately after creation and before any schema definitions. Once set, SQLite prohibits encoding changes, ensuring consistent conversions:
PRAGMA encoding = 'UTF-16le'; -- Must be first statement
CREATE TABLE t0 (c0 INTEGER);
-- Subsequent operations use UTF-16le for text conversions
This prevents mixed-encoding artifacts but requires upfront commitment to an encoding.
Step 8: Use Hexadecimal or Base64 for Binary Data Serialization
Encode binary data as hex or base64 strings before storage, ensuring portable text comparisons:
CREATE TABLE t0 (c0 TEXT); -- Stores hex strings
INSERT INTO t0 VALUES (HEX(x'5BB5AB90')); -- '5BB5AB90'
SELECT c0 FROM t0 WHERE c0 GLOB '5BB5AB90'; -- Exact match
This avoids encoding ambiguities but increases storage size and processing overhead.
Step 9: Audit Implicit Casting in Queries
Review queries for implicit type conversions using EXPLAIN or EXPLAIN QUERY PLAN. Look for CAST operations in the compiled bytecode:
EXPLAIN SELECT c0 FROM t0 WHERE c0 GLOB c0;
-- Output may show implicit casts
Rewrite queries to eliminate ambiguous casts, replacing them with explicit HEX(), TEXT, or BLOB annotations.
Step 10: Leverage Type-Safe Function Overloads
Use SQLite’s type-preserving functions like ZEROBLOB() or HEX() to maintain data integrity:
SELECT c0 FROM t0 WHERE HEX(c0) GLOB HEX(c0); -- Type-safe comparison
This enforces a text representation immune to encoding variances, at the cost of increased computational complexity.
By systematically addressing encoding dependencies, type conversions, and operator semantics, users can eliminate inconsistencies in GLOB behavior across SQLite databases. The key is to enforce strict typing, avoid implicit conversions, and handle binary data with encoding-aware serialization.
