expression, planner: evaluate MATCH ... AGAINST correctly without a full-text index
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Evaluate `MATCH ... AGAINST` correctly on the classic kernel, where no full-text index is available.
Related: ref #61185 (indexed full-text execution, which this does not cover), ref #1793.
### Problem
TiDB's shipped full-text search — `FTS_MATCH_WORD()` backed by a columnar FULLTEXT index — is gated to next-gen starter deployments:
```go
// pkg/config/deploymode/mode.go
func IsStarter() bool {
return kerneltype.IsNextGen() && Get() == Starter
}
```
`CREATE FULLTEXT INDEX` is rejected outside that mode (`pkg/ddl/index.go`, `checkFullTextSupportedInStarter`), and the planner rules that bind `FTS_MATCH_WORD()` to an index are gated on the same condition (`pkg/planner/core/optimizer.go`).
So on the classic kernel the only way to serve `MATCH ... AGAINST` is the ILIKE fallback (`pkg/expression/fts_to_like.go`), which approximates the search with substring predicates. Its own doc comment enumerates the gaps; the ones users hit are:
| Behaviour | MySQL | ILIKE fallback |
| --- | --- | --- |
| Word boundaries | `cat` does not match `concatenate` | matches, because `%cat%` is a substring test |
| Stop words | dropped from the search | still filter rows (see note below) |
| `innodb_ft_min_token_size` / `max` | terms outside the range dropped | still filter rows |
| Exact phrase `"a b"` | adjacency required | rejected at rewrite time |
| Prefix `term*` | word-start match | rejected at rewrite time |
Concretely, on master today `SELECT ... WHERE MATCH(title) AGAINST('+vs' IN BOOLEAN MODE)` returns rows whose title contains `vs`, even though MySQL drops `vs` for being shorter than `innodb_ft_min_token_size` and would return nothing.
### Proposed change
Evaluate the predicate in TiDB using a real analyzer and boolean query matcher, rather than rewriting it to ILIKE. Scope is deliberately **no-score**: the result is a 0/1 filter flag, so only direct-boolean predicate positions are eligible. Relevance-score positions (`SELECT` field list, `ORDER BY`, threshold comparisons) keep the native builtin and are unaffected.
Delivered in two steps:
1. **Analyzer and query matcher library.** Port the engine-independent parts of the local `MATCH ... AGAINST` work from the `feature/fts` branch to master: boolean-mode query parsers, standard/ngram tokenizers with stop-word and token-length filters, analyzed row documents carrying token positions and frequencies, and a compiled query matcher. Neither package references TiCI, TiFlash or any columnar index type.
2. **Wire it into the planner and expression layers.** A new `FTSLocalEvalInfo` on the `MATCH ... AGAINST` builtin authorises local evaluation and carries the analyzer configuration, resolved from session variables once at plan time. Gated behind a new `tidb_enable_local_match_against` system variable, off by default.
### Out of scope
- Relevance scoring and natural-language mode. Both are defined in terms of ranking, which the no-score path cannot produce; natural-language mode continues to use the existing fallback.
- Query expansion, which needs a second retrieval pass over an index.
- An index-backed access path. Local evaluation is a full scan with a residual filter — correct, but not fast. Making it fast needs an inverted access path underneath the matcher, which is separate work.
- Parser selection. The STANDARD parser is used, matching MySQL's default for a FULLTEXT index declared without `WITH PARSER`. There is no index on this path to carry a parser snapshot, so ngram is not reachable yet.
- Cost modelling for the non-pushed-down filter. A locally evaluated `MATCH` currently costs the same as a pushed-down one.
- Stop-word filtering. `stopwordSetFromConfig` in the ported analyzer returns an empty set unless an explicit word list is supplied, and no code path populates one, so `innodb_ft_enable_stopword` removes no terms today. The flag is still threaded through and recorded, so turning it into real filtering later cannot silently reinterpret data written before the change. Word boundaries and the token-size limits in the table above do take effect.
### Teachability and adoption
The variable is off by default, so existing behaviour is unchanged until a user opts in:
```sql
SET @@tidb_enable_local_match_against = ON;
-- Word boundaries are respected: matches nothing, where the ILIKE
-- fallback would match 'Optimizing MySQL'.
SELECT id, title FROM articles
WHERE MATCH(title) AGAINST('+Optimiz' IN BOOLEAN MODE);
-- Exact phrases and prefixes are evaluated rather than rejected.
SELECT id, title FROM articles
WHERE MATCH(title, body) AGAINST('"distributed sql"' IN BOOLEAN MODE);
SELECT id, title FROM articles
WHERE MATCH(title) AGAINST('Optim*' IN BOOLEAN MODE);
```
Analyzer behaviour is controlled by the existing MySQL-compatible sysvars `innodb_ft_min_token_size`, `innodb_ft_max_token_size` and `innodb_ft_enable_stopword`, which become real variables instead of noops as part of step 1.
Contributor guide
Research direction
Start with pkg/expression/fts_to_like.go and the gates in pkg/config/deploymode/mode.go, pkg/ddl/index.go, and pkg/planner/core/optimizer.go to understand the current fallback and planner behavior. Then trace the MATCH ... AGAINST builtin and the feature/fts branch's analyzer and matcher work before wiring the opt-in local evaluation path. Done means boolean word boundaries, phrases, prefixes, and token-size limits work for direct predicates without changing score positions or default behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100