planner: push a substring pre-filter for multi-column MATCH ... AGAINST
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Push a substring pre-filter for a multi-column `MATCH ... AGAINST`, which is currently skipped.
Follow-up to #70497, which added the pre-filter, and ref #70486.
### Problem
#70497 pushes the substring predicates a locally evaluated `MATCH` entails down to the storage layer, so most non-matching rows are discarded before TiDB applies the exact check. It does this only for a single-column `MATCH`.
A multi-column `MATCH` is satisfied by a token in **any** of its columns, but the pre-filter was built from the first column alone, which discarded rows the `MATCH` accepts. On a table where `storage` appears only in `body`, `match(title, body) against('+storage')` returned one row instead of two. The fix in #70497 was to skip the pre-filter entirely for that case.
That is correct but leaves value on the floor: `match(title, body)` is the canonical full-text shape, so the most common real query gets no pushdown at all.
### Proposed change
Build a disjunction per required token instead of one predicate on one column:
```sql
-- match(title, body) against('+storage +engine')
(LOWER(title) LIKE '%storage%' OR LOWER(body) LIKE '%storage%')
AND (LOWER(title) LIKE '%engine%' OR LOWER(body) LIKE '%engine%')
```
This is sound for the same reason the single-column form is: if a document matches, each required token appears in at least one matched column, so at least one disjunct holds. It over-approximates, and the `MATCH` still decides.
`BuildFTSLocalMatchPreFilters` takes one column today and would take the matched set; the OR-building code already exists for optional terms.
### Watch out
- **Binary columns.** If any matched column is binary the disjunction must be abandoned entirely, not merely built from the non-binary columns: `LOWER` is a no-op on binary, so a document matching through that column would fail every remaining disjunct and be discarded. This is the same unsoundness that #70497 already had to fix once.
- **Test by property, not by example.** Three wrong-result bugs have been found in this pre-filter - binary columns, multi-column, and a boolean term collapsing the query - and each was a case where the filter decided rather than narrowed. Hand-picked cases missed the first two. A test that generates column and token combinations and asserts every document the `MATCH` accepts also satisfies the generated predicate would catch this class directly.
Contributor guide
Research direction
Start at BuildFTSLocalMatchPreFilters and trace the existing OR-building code for optional terms. Extend the matched-column handling while preserving the binary-column guard, then add a property-based test showing every document accepted by MATCH also satisfies the generated pre-filter.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100