FTS5 Auxiliary Functions Functioning with LIKE Operator Despite Documentation Restrictions

Documentation Discrepancy: FTS5 Auxiliary Functions Invoked via LIKE Operator

The FTS5 auxiliary function documentation explicitly states that auxiliary functions such as snippet(), highlight(), and bm25() may only be used within full-text queries employing the MATCH operator on FTS5 tables. However, empirical observations reveal that these functions can execute successfully in queries using the LIKE operator under specific conditions, particularly when applied to trigram-tokenized FTS5 tables. This contradiction between documented behavior and practical implementation raises questions about the scope of auxiliary functions, the role of tokenizers in query processing, and potential undocumented edge cases in SQLite’s FTS5 engine.

The core anomaly centers on the snippet() function, which generates formatted text excerpts highlighting search terms. According to the official documentation, snippet() should only operate within MATCH-based queries. Yet, developers report its functionality persisting in LIKE-driven queries when the FTS5 table uses a trigram tokenizer. This inconsistency suggests either a documentation oversight, a hidden dependency on tokenizer configuration, or an undocumented extension of FTS5 auxiliary function capabilities. Resolving this requires dissecting the interplay between FTS5’s query parsing logic, tokenizer implementations, and the auxiliary function invocation mechanism.

Root Causes: Tokenizer Behavior, Parser Ambiguities, and Version-Specific Quirks

Tokenizer-Driven Query Parsing Overrides
FTS5 auxiliary functions are intrinsically tied to the virtual table’s tokenizer. The trigram tokenizer, which splits text into contiguous three-character sequences, operates differently from the default Unicode-aware tokenizer. When a trigram tokenizer is active, the FTS5 engine may reinterpret LIKE patterns as token-based queries, inadvertently enabling auxiliary functions. This occurs because trigram tokenization converts LIKE wildcards (% and _) into token range queries, bridging the gap between pattern matching and full-text search. Consequently, auxiliary functions designed for MATCH may leak into LIKE contexts due to tokenizer-specific query rewriting.

Ambiguous Function Binding in Virtual Tables
FTS5 auxiliary functions are registered as part of the virtual table module. SQLite’s function resolution logic may bind these functions to any query targeting the FTS5 table, regardless of the operator used, if the query structure resembles a full-text search. For example, a LIKE condition with a wildcard pattern on a trigram-indexed column could trigger FTS5’s query planner to optimize the pattern into a token lookup, thereby invoking auxiliary functions. This blurring of boundaries between LIKE and MATCH operations stems from how FTS5 virtual tables override standard SQLite query parsing.

Version-Specific Undocumented Behaviors
SQLite’s FTS5 module has evolved across versions, with subtle changes in function eligibility checks. Prior to version 3.35.0 (2021-03-12), auxiliary functions lacked rigorous context validation, allowing their execution in non-MATCH queries if the FTS5 table’s internal query optimizer intervened. Developers using older SQLite builds, or builds with specific compile-time options, might observe auxiliary functions working with LIKE due to incomplete guardrails in earlier FTS5 implementations. This creates version-dependent discrepancies between documentation and behavior.

Resolution: Validation, Configuration Audits, and Safe Query Practices

Step 1: Validate Auxiliary Function Contextual Eligibility
To determine whether an FTS5 auxiliary function is being improperly invoked, execute an EXPLAIN query on the suspect SQL statement. For instance:

EXPLAIN SELECT snippet(fts_table) FROM fts_table WHERE column LIKE '%term%';

Inspect the output for references to the Fts5Vocab virtual table or the MATCH operator in the query plan. If the plan shows Fts5Vocab usage despite the absence of MATCH, this indicates that the trigram tokenizer or another component is coercing the LIKE query into a full-text search, thereby enabling auxiliary functions. This hybrid behavior confirms tokenizer-driven parser interference.

Step 2: Audit Tokenizer Configuration and Its Impact
Recreate the FTS5 table with explicit tokenizer directives and test auxiliary function availability:

-- Default tokenizer (unicode61)
CREATE VIRTUAL TABLE fts_standard USING fts5(column);
-- Trigram tokenizer
CREATE VIRTUAL TABLE fts_trigram USING fts5(column, tokenize='trigram');

Execute LIKE queries with snippet() on both tables. If snippet() only works on fts_trigram, the tokenizer is the culprit. The trigram tokenizer’s pattern expansion converts LIKE into a token search compatible with auxiliary functions, whereas standard tokenizers reject this. Modify tokenizer usage if strict adherence to documented behavior is required.

Step 3: Cross-Version Testing and Documentation Alignment
Compare SQLite versions to identify behavioral changes. For example, in SQLite 3.34.0, run:

SELECT sqlite_version();
SELECT snippet(fts_trigram) FROM fts_trigram WHERE column LIKE '%abc%';

Then repeat on SQLite 3.36.0+. If the latter rejects the query with an error ("fts5: syntax error near "LIKE"") while the former succeeds, version-specific guards are in play. Update SQLite to align with documented behavior or lock the version if relying on the anomaly. Consult the SQLite changelog for FTS5-related updates, particularly entries around function visibility and operator restrictions.

Step 4: Query Rewriting and Operator Isolation
Enforce a strict separation between MATCH and LIKE queries by refactoring application code. For instance, replace:

SELECT snippet(fts_table) FROM fts_table WHERE column LIKE '%term%';

with:

SELECT snippet(fts_table) FROM fts_table WHERE column MATCH 'term';

If LIKE is unavoidable due to legacy code or specific wildcard requirements, use a subquery to isolate the auxiliary function:

SELECT snippet(results) FROM (
  SELECT * FROM fts_table WHERE column LIKE '%term%'
) AS results;

This may fail on newer SQLite versions but can work on older builds where auxiliary functions tolerate indirect invocation.

Step 5: Compile-Time Configuration and Defense Mechanisms
If maintaining the anomalous behavior is critical, compile SQLite with custom flags to disable auxiliary function guards. Locate the sqlite3Fts5ExprCheckTokenizers() function in fts5_expr.c and modify its logic to skip context checks. Conversely, for strict compliance, enable SQLITE_DENY hooks to block auxiliary functions in LIKE queries via sqlite3_set_authorizer(), raising errors when snippet(), highlight(), or bm25() are detected outside MATCH contexts.

Final Recommendation:
While the trigram tokenizer’s unique query processing can enable auxiliary functions in LIKE clauses, this is an undocumented side effect. Relying on it risks breakage across SQLite updates. Reserve auxiliary functions for MATCH queries and use LIKE only for non-FTS5 tables or columns. For hybrid use cases, employ SQLite’s MATCH operator with NEAR or phrase queries to replicate LIKE-like behavior within documented constraints.