# MySQL Compatible Full-Text Search Profile

Date: 2026-02-27
Status: Draft (Normative)
Owner: Metadata Search / Query Gateway

## 1. Scope

This document defines the MySQL-compatible full-text search (FTS) capability profile supported by Search Fabric in Unified Search Platform.

Normative compatibility boundary:
- TiDB support in this document is a subset of MySQL FULLTEXT SEARCH for `InnoDB` tables only.
- MyISAM-specific behavior is reference baseline only and is out of compatibility commitment unless explicitly marked as an intentional non-parity note (including MyISAM-only tuning paths such as `ft_boolean_syntax`).
- Compatibility in this document means both:
  - the documented MySQL-visible behavior at the SQL/gateway boundary, and
  - the deterministic lowering of that behavior into canonical Search Fabric APIs and schema carriers.
- If a MySQL FULLTEXT behavior cannot be lowered deterministically to canonical APIs, it is outside this profile and MUST be documented as a limitation or unsupported branch rather than approximated.

In scope:
- Compatibility profile definitions and roadmap phase binding
- Commitment-A capability decomposition boundary across MySQL compatibility and TiDB enhancement tracks
- Ownership applicability boundary (`collection_truth_mode`) for MySQL/TiDB path
- `MATCH ... AGAINST` syntax compatibility
- `WITH PARSER` options
- Parser-specific behavior compatibility (`STANDARD` / `NGRAM` / `MECAB`)
- Boolean search operator compatibility
- Relevancy model compatibility
- Default result-ordering compatibility for natural-language and boolean modes
- Global FTS configuration compatibility
- Collation metadata and stopword-configuration compatibility boundary
- Operational compatibility boundaries
- Hard restrictions
- Engine/type/wildcard/ROLLUP restriction compatibility boundaries
- `MATCH()` single-table boundary compatibility
- `doc_id=unsigned int64` integration contract for MySQL/TiDB path
- Profile-scenario-to-error-code mapping (canonical error definitions are in `docs/specs/core/error-codes.md`)

## 2. Compatibility Profiles and Phase Binding

Terminology convention:
- `Profile` defines capability boundary and conformance scope.
- `Milestone` and schedule commitments are defined by `docs/governance/roadmap.md` phases, not by separate numbering in this document.

### 2.1 Profile 1: Boolean Search Compatibility Profile

- Roadmap binding: `docs/governance/roadmap.md` `Phase 1` (`2026-04-01` to `2026-06-30`).
- Governance note: Phase 1 is the integrated architecture, TiDB federated-table, and pre-validation phase of the broader `10^8` logical-table program whose checkpoint closes on `2026-09-30`. This document owns only the MySQL Boolean Search compatibility slice of that wider acceptance package.
- Scope: includes all capabilities currently documented in Sections `3` to `7`.
- Restriction model: inherits and enforces all MySQL FTS restrictions documented in Section `8`.
- Primary syntax target: `MATCH ... AGAINST ... IN BOOLEAN MODE`.

### 2.2 Profile 2: Full Query Syntax Compatibility Profile

- Roadmap binding: `docs/governance/roadmap.md` `Phase 2` (`2026-07-01` to `2026-07-31`).
- Scope: strict superset of Profile 1.
- Adds complete MySQL `MATCH ... AGAINST` syntax compatibility, including:
  - no-modifier natural-language form (`MATCH(col_list) AGAINST(expr)`)
  - `IN NATURAL LANGUAGE MODE`
  - `WITH QUERY EXPANSION`
- Keeps Profile 1 compatibility behavior for `IN BOOLEAN MODE` and `WITH PARSER`.

### 2.3 Profile 3: Full Transaction Semantics Compatibility Profile

- Roadmap binding: `docs/governance/roadmap.md` `Phase 4A` within the broader `Phase 4` window (`2026-11-01` to `2027-01-31`), with the `2026-12-15` Phase-4A foundation checkpoint freezing the certification-critical claim matrix/foundation package before the final `2027-01-31` internal engineering qualification checkpoint closes phase-scope delivery.
- Scope: strict superset of Profile 2.
- Adds MySQL-facing full transaction-semantics compatibility for FTS write/read behavior.
- Adds the Phase-4 MySQL-facing feature deltas that remain externally governed by this document:
  - table-level stopword/tokenizer configuration in canonical applicability mode
  - operational hardening for CJK tokenization through the existing `NGRAM` parser surface
  - partitioned-table support for the MySQL-compatible FTS path in canonical applicability mode
- Clarification:
  - Japanese parser coverage is already part of the active parser surface via `WITH PARSER mecab` in Profile 1/2 when runtime prerequisites are satisfied.
  - Myanmar-specific parser/analyzer support is not part of the current canonical analyzer registry and is therefore outside this document's active compatibility scope; if delivered later, it is introduced by the TiDB enhancement track in `docs/compatibility/tidb-enhancement.md` Phase-4/Profile-3 rather than by changing the baseline Profile-1/2 contract here.
- Adds read-your-write guarantee: data from successful writes MUST be immediately readable from TiDB SQL perspective.
- Transaction semantics in this profile are implemented by TiDB transaction/visibility layer; Search Fabric remains asynchronous projection with potentially stale standalone read model.
- This profile is planned for `Phase 4A` in `docs/governance/roadmap.md` with one mid-window foundation checkpoint on `2026-12-15` and phase-scope completion at development-delivery maturity on the `2027-01-31` internal engineering qualification checkpoint (non-GA Readiness in that phase); same-date Phase-4B post-GA expansion work is separate and not a prerequisite for this profile.

### 2.4 Phase-3 Hardening/GA-Readiness Boundary for Delivered Profile 1/2 Scope

- Roadmap binding: `docs/governance/roadmap.md` `Phase 3` (`2026-09-01` to `2026-10-31`).
- Scope: no new MySQL compatibility profile is introduced in this phase; delivered `Profile 1/2` scope enters GA-Readiness hardening and production-readiness validation.
- Acceptance boundary: phase gate is passed only when delivered `Profile 1/2` behavior remains conformant under phase-declared scale/failure/reliability validation.

Commitment-A capability decomposition note:
- Commitment A includes MySQL-compatible FTS behavior plus ad-hoc multi-field metadata predicates.
- In cross-document decomposition, MySQL FTS syntax/semantic/transaction compatibility is governed by this document.
- Ad-hoc multi-field metadata predicate acceleration capability is governed by `docs/compatibility/tidb-enhancement.md` (`Flexible Filtering Index` track) and integrated into the same canonical applicability mode.
- The `2026-07-31` public-preview milestone closes the MySQL-facing public-preview surface independently; TiDB enhancement private preview is intentionally deferred to the separate `2026-08-31` TiDB private-preview checkpoint and MUST NOT be inferred as part of the MySQL public-preview close.
- Phase-boundary clarification: Phase-1 Commitment-A rollout may include baseline ad-hoc predicate planning via non-profile fallback execution; profile-scoped indexed acceleration for this capability line is governed by `docs/compatibility/tidb-enhancement.md` starting at the `2026-08-31` TiDB private-preview checkpoint (Profile 1).
- Auditable ownership clarification: this Phase-1 baseline capability line is validated only as part of the integrated Phase-1 roadmap acceptance package in `docs/governance/roadmap.md` Section `6`, and it is not counted as Profile-1 conformance scope in this document.

### 2.5 Profile-Level Gate Criteria (Functional / Reliability / Economics)

All gates in this section are normative for canonical applicability mode (`collection_truth_mode=COLLECTION_TRUTH_MODE_EXTERNAL`) and mirror `docs/governance/roadmap.md` Section `6`.

#### 2.5.1 Phase-1 Gate (Profile 1, Integrated Phase-1 Delivery)

Functional gate:
- Profile 1 conformance suite passes for declared boolean-search compatibility scope inside the broader Phase-1 acceptance package.

Reliability gate:
- Snapshot consistency, replay/idempotency, and no-filter-leakage checks pass on the Phase-1 federated/query validation path.

Economics gate:
- Phase-1 profile-scoped metrics stay within the milestone-closing Phase-1 workload bundle and the Phase-1 engineering-validation envelope declared by `docs/governance/roadmap.md` Sections `3.2` and `3.3`.

#### 2.5.2 Phase-2 Gate (Profile 2, Public Preview Expansion)

Functional gate:
- Profile 2 conformance suite passes for declared full-query-syntax compatibility scope.

Reliability gate:
- Replay/failover/chaos checks for expanded preview cohorts pass with no release-blocking defects.

Economics gate:
- MySQL Profile-2 public-preview metrics stay within the profile-scoped subset of the milestone-closing Phase-2 public-preview workload bundle and the Phase-2 public-preview envelope declared by `docs/governance/roadmap.md` Sections `3.2` and `3.3`.

#### 2.5.3 Phase-3 Hardening Gate (Delivered Profile 1/2 Scope, GA Readiness)

Functional gate:
- Delivered Profile 1/2 scope remains conformant under phase-declared hardening and scale-validation campaigns.

Reliability gate:
- Production-readiness validation (SLO/error-budget/runbook/rollback readiness) passes for delivered Profile 1/2 scope.

Economics gate:
- Delivered Profile 1/2 scope stays within the profile-scoped subset of the milestone-closing Phase-3 multi-tenant workload bundle and the Phase-3 GA-Readiness envelope declared by `docs/governance/roadmap.md` Sections `3.2` and `3.3`.

#### 2.5.4 Phase-4A Gate (Profile 3, Internal Engineering Qualification Delivery)

Functional gate:
- Profile 3 conformance covers both feature compatibility and transaction-semantics compatibility, including immediate TiDB SQL read-your-write behavior.

Reliability gate:
- Transactional visibility, failover/replay, and rollback-safety checks pass with no release-blocking defects.

Economics gate:
- Added transaction-semantics overhead stays within the accepted `2026-12-15` Phase-4A foundation package plus the accepted `2027-01-31` internal engineering qualification package for the certification-critical checkpoint, and within the Phase-4 planning / scale-out envelope declared by `docs/governance/roadmap.md` Sections `3.2` and `3.3`.

## 3. Profile 1 Syntax and Parser Options

1. Query form:
- `MATCH(col_list) AGAINST(expr IN BOOLEAN MODE)`

2. Parser options and SQL representation:
- `STANDARD`: canonical compatibility label for MySQL built-in parser behavior; SQL representation is default parser path with no explicit `WITH PARSER` clause.
- `NGRAM`: SQL representation `WITH PARSER ngram`.
- `MECAB`: SQL representation `WITH PARSER mecab` (Japanese parser path; requires parser plugin + dictionary availability in runtime environment).
- Explicit SQL `WITH PARSER standard` is outside MySQL syntax compatibility in this profile and MUST fail closed as unsupported parser option.
- Canonical analyzer binding in this revision is:
  - default parser path -> `builtin_standard_v1`
  - `WITH PARSER ngram` -> `builtin_ngram_v1`
  - `WITH PARSER mecab` -> `builtin_mecab_ja_v1`
- No distinct Myanmar-specific parser/analyzer identifier exists in the current canonical analyzer registry; requests that require such a surface are out of scope in this revision and remain on the TiDB enhancement track until a registry extension is admitted.

3. Parser behavior compatibility boundaries:
- `STANDARD` parser phrase-search behavior uses quoted phrase matching:
  - phrase match requires the same words in the same order;
  - nonword characters in matched text need not be exactly identical to query punctuation.
- `STANDARD` parser word-character rules:
  - words are sequences of letters, digits, and underscores;
  - apostrophes are permitted but not more than one in a row;
  - leading or trailing apostrophes are stripped.
- `STANDARD` parser delimiter and CJK boundary:
  - built-in parser uses delimiter characters (for example space, comma, period) to identify words;
  - languages without delimiters (CJK) require preprocessing or use of `NGRAM`/`MECAB`.
- `STANDARD` phrase queries with no indexed words MUST return an empty result set (for example, when all phrase terms are stopwords or shorter than minimum token length).
- `STANDARD` and `MECAB` parser paths ignore words shorter than `innodb_ft_min_token_size` or longer than `innodb_ft_max_token_size`.
- `NGRAM` mode follows token-size-driven parsing (`ngram_token_size`, range `1..10`), and does not apply InnoDB minimum/maximum word-length token settings (`innodb_ft_min_token_size`, `innodb_ft_max_token_size`).
- `NGRAM` parser eliminates spaces before tokenization.
- `NGRAM` term-search conversion follows mode-specific behavior:
  - Profile 2 natural-language mode term search is converted to a union of ngram terms;
  - Profile 1/2 boolean mode term search is converted to an ngram phrase search.
- `NGRAM` wildcard semantics follow token-size rules:
  - prefix shorter than token size uses prefix token match behavior;
  - prefix longer than token size is converted to ngram phrase behavior and wildcard marker is ignored.
- `NGRAM` stopword semantics follow parser-specific behavior:
  - tokens containing stopwords are excluded from index;
  - stopwords longer than `ngram_token_size` are ignored.
- `NGRAM` phrase search is converted to ngram phrase sequence semantics.
- `MECAB` mode follows tokenized-word behavior for Japanese:
  - natural-language mode term search is token-union behavior;
  - boolean mode term search is tokenized phrase behavior;
  - wildcard search term is prefix-evaluated and not tokenized.
  - query strings use spaces as token separators;
  - phrase searches are tokenized.
- `MECAB` runtime prerequisites:
  - plugin MUST be installed and dictionary MUST be available in the runtime environment;
  - `mecab_rc_file` MUST be configured before plugin installation (via `loose-mecab-rc-file`);
  - `mecab_charset` is informational and MAY be used to validate dictionary character-set support.
- If a requested parser is outside profile scope, or parser runtime prerequisites are unavailable, request MUST fail closed.

### 3.1 Canonical Control-Plane Schema Equivalence for FULLTEXT DDL

For TiDB/MySQL compatibility paths, FULLTEXT DDL semantics MUST be normalized into canonical control-plane schema payload before apply:
- FULLTEXT indexed source columns -> `FieldSchema[]` (type/nullability + semantic/text capabilities)
- parser family (`STANDARD`/`NGRAM`/`MECAB`) and tokenizer/language behavior -> `FieldSchema.config.field_definition.text.analysis.analyzer` plus `FieldSchema.config.field_definition.text.analysis.options`
- BM25/query-expansion/storage tuning semantics -> `FieldSchema.config.field_definition.text.fulltext`
- charset/collation constraints -> supplemental source metadata (`FieldSchema.options` / `CollectionSettings.options`)
- canonical doc-id source constraints -> `DocIDMapping`

Normalization rules:
- parser family and full-text semantics MUST be admitted and versioned through control-plane schema apply (`PutCollectionSchema`); charset/collation are round-trip supplemental metadata only and MUST NOT create a separate typed-schema branch.
- For TiDB-authoritative FULLTEXT DDL, schema apply MUST use the canonical DDL-sync envelope rather than an unscoped schema write:
  - `PutCollectionSchema.scope.collection`
  - `PutCollectionSchema.scope.ddl_commit_ts`
  - authoritative monotonic `PutCollectionSchema.schema_version` sourced from TiDB DDL/catalog lineage
  - `PutCollectionSchema.fence.expected_prev_schema_version`
  - `PutCollectionSchema.fence.schema_generation_token`
  - `PutCollectionSchema.fence.enforce_monotonic_apply=true`
- `PutCollectionSchema.update_policy` MUST be explicit and MUST NOT be `SCHEMA_UPDATE_POLICY_UNSPECIFIED`; the first schema creation for a collection with catalog identity but no prior schema lineage MUST use `SCHEMA_UPDATE_POLICY_INITIAL_CREATION`, while later TiDB-authored DDL MUST classify into additive vs rebuild policy according to the canonical schema-diff rules.
- when TiDB emits the first schema generation for a collection with no prior schema lineage, the request MUST use the full canonical zero-state tuple (`schema_version=1`, `fence.expected_prev_schema_version=0`, `fence.schema_generation_token="__initial__"`, `update_policy=SCHEMA_UPDATE_POLICY_INITIAL_CREATION`, `fence.enforce_monotonic_apply=true`).
- The same TiDB-authored schema apply MUST carry replay-stable non-empty `PutCollectionSchema.meta.idempotency_key` plus explicit adapter `PutCollectionSchema.meta.client_id` and `PutCollectionSchema.meta.client_kind=CLIENT_KIND_TIDB_DDL_ADAPTER`.
- TiDB DDL admission MUST capture replay-critical schema-admission context and persist it into `CollectionSchemaDefinition.replay_context` for the same schema apply; in the current revision this typed carrier holds `sql_mode` and `time_zone`, while parser/tokenizer semantics remain canonical typed schema fields and charset/collation data remains supplemental `FieldSchema.options` / `CollectionSettings.options` metadata.
- readback of the active admitted FULLTEXT schema generation MUST come from canonical catalog read `GetCollectionSchema`; gateway/local planner code MUST NOT reconstruct it from source DDL text heuristics.
- every admitted FULLTEXT query MUST be emitted as one complete canonical `QueryRequest`, not as a detached text clause fragment:
  - `meta=<RequestMeta>`
  - `meta.correlation_id=<non-empty query correlation id stamped by TiDB query executor>`
  - `meta.client_id=<TiDB planner/executor identity>`
  - `meta.client_kind=CLIENT_KIND_TIDB_COMPUTE`
  - `collection=<resolved CollectionRef>`
  - `consistency`
  - `search_payload`
  - `fetch_spec`
  - route legality is validated against the resolved descriptor `collection_truth_mode=COLLECTION_TRUTH_MODE_EXTERNAL`; `QueryRequest` itself does not carry a public truth-mode field
- every admitted `MATCH(col_list)` query lowers to exactly one canonical `TextClause` leaf whose `fields[]` is canonicalized to byte-ascending order before emission, because SQL column-list order is not semantically meaningful in this admitted profile and digest/paging stability depends on deterministic field ordering.
- the gateway MUST NOT rewrite `MATCH(col1, col2, ...)` into independent per-column clauses plus gateway-side score fusion; multi-column token statistics and scoring remain a single engine-native text clause over `TextClause.fields[]`.
- admitted analyzer IDs in this profile are:
  - default parser path -> `builtin_standard_v1`
  - `WITH PARSER ngram` -> `builtin_ngram_v1`
  - `WITH PARSER mecab` -> `builtin_mecab_ja_v1`
- parser-specific tuning MUST be expressed only through the canonical analyzer override registry (`TextAnalysisSpec.options` as `map<string, MetadataValue>`, with active revision values carried through `MetadataValue.atomic_value.string_value`) plus `text.fulltext`; the admitted override keys in this revision are:
  - `stopwords` (`inherit|enabled|disabled`)
  - `stemming` (`inherit|enabled|disabled`)
  - `tokenizer.ngram_token_size` (`1..10`, `NGRAM` analyzer family only)
- compatibility text MUST NOT rely on removed field-default carriers or pre-refactor `FieldSchema.config.text.*` paths.
- query-time parser override is forbidden; parser behavior is determined by admitted schema generation snapshot.
- DDL branches that cannot be represented in canonical schema model MUST fail closed (`SF_UNSUPPORTED_PARSER`, `SF_UNSUPPORTED_OPTION`, or `SF_SCHEMA_VIOLATION` by precedence).

## 4. Profile 1 Boolean Search Semantics

### 4.1 Not Supported

- `++apple`
- `apple+`
- `apple-`
- `+-`
- `+-apple`
- `+*`

### 4.2 Supported

- `+`
- `-`
- no operator
- `@distance` (InnoDB-only proximity operator)
- `>`
- `<`
- `()`
- `~`
- `*`
- `"` (phrase search)

### 4.3 Additional Boolean Behavior Constraints

- Multiple operators on a single search term are not supported; only a single leading operator is permitted (for example, `++term` MUST be rejected).
- `+` marks a required term (logical AND); only leading `+` is supported in this profile.
- `-` marks a prohibited term (logical NOT); only leading `-` is supported in this profile.
- no-operator terms are optional (logical OR) but increase relevance when matched.
- `>` and `<` adjust a term's contribution to relevance (`>` increases, `<` decreases) and do not change match inclusion semantics.
- `()` groups terms into subexpressions; grouped expressions may be nested.
- `~` is a soft negation operator that makes a term's relevance contribution negative; rows are lowered in rank but not excluded (unlike `-`).
- A boolean query containing only `-`-prefixed terms MUST return an empty result set.
- `*` is the truncation operator for `STANDARD`/`MECAB` boolean searches; words match when they begin with the prefix preceding `*` (for `NGRAM`, apply Section `3` token-size wildcard semantics).
- A wildcarded term (`*`) MUST NOT be stripped even when that term is shorter than minimum token size or appears in stopword lists.
- `@` symbol usage is only valid for `@distance`; other `@` usages MUST be rejected.
- `@distance` MUST use quoted-phrase proximity form, where a double-quoted phrase appears immediately before `@distance` (for example `"word1 word2 word3" @8`).
- `@distance` semantics: all words from the quoted phrase must start within the specified distance window, measured in words.
- InnoDB boolean full-text search in this profile MUST NOT apply the MyISAM natural-language `50%` threshold filter.

## 5. Cross-Profile Relevancy and Default Ordering

Supported relevancy models:
- `BM25`

Unsupported relevancy models in this profile:
- `TF/IDF`

Default ordering compatibility:
- Profile 2 natural-language relevance values MUST be nonnegative floating-point numbers; zero means no similarity.
- Profile 2 natural-language search (`MATCH ... AGAINST` with no modifier or `IN NATURAL LANGUAGE MODE`) MUST return rows in descending relevance by default only when all of the following hold:
  - no explicit `ORDER BY` clause is present;
  - execution uses a full-text index scan (not a table scan);
  - if joins are present, the full-text index scan table is the leftmost non-constant table.
- InnoDB natural-language full-text search in Profile 2 MUST NOT apply the MyISAM natural-language `50%` threshold filter.
- Profile 1/2 boolean search (`IN BOOLEAN MODE`) MUST NOT automatically sort rows by decreasing relevance.
- If the Profile-2 natural-language preconditions above are not satisfied, result order is not guaranteed unless caller specifies explicit `ORDER BY`.

## 6. Profile 1 Global Configuration Compatibility

Supported global configuration keys:
- `innodb_ft_min_token_size`
- `innodb_ft_max_token_size`
- `ngram_token_size`
- `innodb_ft_enable_stopword`
- `innodb_ft_server_stopword_table`
- `innodb_ft_user_stopword_table`
- `innodb_ft_aux_table`
- `innodb_optimize_fulltext_only`
- `innodb_ft_num_word_optimize`

Behavioral compatibility notes:
- Indexed-column collation and server collation remain source-side compatibility inputs that gateways MAY validate and preserve as supplemental metadata, but they do not create an extra canonical Search Fabric retrieval-semantics branch in this profile.
- If a requested MySQL path depends on collation-driven or stopword-lookup behavior that cannot be represented by the admitted canonical analyzer/text settings, that path is outside this profile and MUST fail closed rather than creating source-specific retrieval semantics at query time.
- When stopword filtering is enabled, stopwords are omitted from the index and do not match in queries.
- Stopword control is scoped to `innodb_ft_enable_stopword` plus stopword-table options (`innodb_ft_server_stopword_table`, `innodb_ft_user_stopword_table`); no generic `stopword` system variable exists in this compatibility profile.
- For `innodb_ft_server_stopword_table` and `innodb_ft_user_stopword_table`, the referenced stopword table MUST have a single `VARCHAR` column named `value`.
- For `innodb_ft_server_stopword_table` and `innodb_ft_user_stopword_table`, variable values MUST use `db_name/table_name` format.
- For initial index build, stopword-table options (`innodb_ft_server_stopword_table` / `innodb_ft_user_stopword_table`) intended for active use MUST be set before creating the `FULLTEXT` index.
- `innodb_ft_aux_table` SHOULD be used only for INFORMATION_SCHEMA FTS inspection workflows; when set, value MUST use `db_name/table_name` format.
- Default InnoDB token-size boundaries MUST be assumed as `innodb_ft_min_token_size=3` and `innodb_ft_max_token_size=84` when not overridden; applies to `STANDARD`/`MECAB` parser paths (not `NGRAM`).
- Default `ngram_token_size` MUST be assumed as `2` (bigram) when not overridden.
- For `MECAB`, `innodb_ft_min_token_size` SHOULD be set to `1` or `2` to match MySQL guidance; higher values are permitted but may exclude shorter tokens.
- `NGRAM` and `MECAB` default stopword list language is English; for CJK/Japanese workloads, callers SHOULD provide custom stopword lists via stopword-table mechanisms.
- For `NGRAM` parser path, minimum/maximum word-length settings are ignored and token length is controlled by `ngram_token_size`.
- `OPTIMIZE TABLE` on InnoDB tables with `FULLTEXT` indexes is the supported optimization maintenance path.
- `innodb_optimize_fulltext_only` may be used to scope optimization work to full-text index maintenance.
- `innodb_ft_num_word_optimize` may be used to run staged optimization by limiting words processed per `OPTIMIZE TABLE` execution.
- Most full-text variables in this profile are startup-time settings; restart is required for changes to take effect.
- `ngram_token_size` is read-only at runtime and may be set only at startup/configuration-file time.
- After modifying any of `innodb_ft_min_token_size`, `innodb_ft_max_token_size`, `innodb_ft_server_stopword_table`, `innodb_ft_user_stopword_table`, `innodb_ft_enable_stopword`, or `ngram_token_size`, `FULLTEXT` indexes MUST be rebuilt.
- MyISAM-only variable/behavior paths (`ft_min_word_len`, `ft_max_word_len`, `ft_stopword_file`, `ft_boolean_syntax`, and stopword-file latin1 fallback with `ucs2`/`utf16`/`utf16le`/`utf32` server charsets) are out of profile scope and MUST be rejected.

## 7. Profile 1 Operability Compatibility

### 7.1 Supported

- `RENAME TABLE`
- `RENAME COLUMN`
- `ALTER TABLE INDEX`
- `CREATE TABLE` with inline `FULLTEXT` index definition
- `ALTER TABLE ... ADD FULLTEXT INDEX`
- `CREATE FULLTEXT INDEX`
- `OPTIMIZE TABLE` (InnoDB `FULLTEXT` maintenance path)
- `TRUNCATE TABLE`
- `DROP TABLE` and `CREATE TABLE` with the same name

### 7.2 Not Supported

- Modify fulltext index column type
- `TEMPORARY TABLES`

## 8. MySQL FTS Restrictions and Limitations

1. Partitioned tables:
- `Profile 1/2`: partitioned tables are not supported in the MySQL-compatible FTS path.
- `Profile 3`: partitioned `InnoDB` tables are supported in canonical applicability mode (`collection_truth_mode=COLLECTION_TRUTH_MODE_EXTERNAL`) and follow the architecture constraints in `docs/specs/core/search-fabric-architecture.md` Section `11.6`.
- `docs/compatibility/tidb-enhancement.md` Profile 3 tracks delivery/readiness of this capability, but external MySQL-facing semantics remain owned by this document.

2. Engine and indexed-column type boundary:
- FULLTEXT compatibility path is limited to `InnoDB` tables only.
- Requests that require MyISAM-specific full-text behavior are out of profile scope and MUST be rejected.
- FULLTEXT-indexed columns MUST use `CHAR`, `VARCHAR`, or `TEXT` type families.

3. Character set and collation boundary:
- All columns in the same `FULLTEXT` index MUST use the same character set and collation.
- For Unicode in this compatibility profile, `FULLTEXT` index columns MUST use `utf8mb3` or `utf8mb4`; `ucs2` is not supported for indexed full-text path.
- Remarks for `ucs2` in this boundary also apply to `utf16`, `utf16le`, and `utf32`.

4. Index-column matching:
- `MATCH()` column list must match exactly the column list in some `FULLTEXT` index definition for the table.
- Base-MySQL MyISAM exception (boolean search without FULLTEXT index on nonindexed columns) is out of this profile scope and MUST be rejected.

5. `AGAINST()` argument requirement:
- The argument to `AGAINST()` must be a string value that is constant during query evaluation.
- Example that is ruled out: table column references in `AGAINST()` expressions.

6. `MATCH()` + `ROLLUP` query-shape restriction:
- Queries that satisfy all following conditions MUST be rejected:
  - query block contains `GROUP BY ... WITH ROLLUP`;
  - `MATCH()` appears in `SELECT`, `GROUP BY`, `HAVING`, or `ORDER BY` of that block;
  - argument column of `MATCH()` is one of the grouping columns.
- `MATCH()` usage in `WHERE` remains allowed when the forbidden query shape above is not triggered.

7. Unsupported wildcard character:
- `%` is not a supported wildcard character for MySQL-compatible full-text search in this profile.

8. Index hints:
- Index hints are more limited for `FULLTEXT` searches than for non-`FULLTEXT` searches.

9. InnoDB commit-time visibility boundary:
- For InnoDB, DML operations (`INSERT`/`UPDATE`/`DELETE`) involving `FULLTEXT`-indexed columns are processed into full-text index state at transaction commit time.
- Full-text search results in this profile MUST include only committed data.

10. `MATCH()` single-table boundary:
- For index-backed full-text search, `MATCH()` arguments MUST reference columns from exactly one table; multi-table `MATCH()` argument sets MUST be rejected.

11. User-defined collations for full-text indexing:
- Deprecated MySQL user-defined collation paths are out of profile scope and MUST be rejected.

12. Canonical lowering limitations:
- Canonical doc-id schema lowering admits only `DocIDMapping.source_field_type=SCALAR_FIELD_TYPE_UINT64`.
- `DocIDMapping.source_field_type=SCALAR_FIELD_TYPE_STRING` and `DocIDMapping.source_field_type=SCALAR_FIELD_TYPE_BYTES` are intentionally unsupported in this profile, even if source SQL identifiers are lexically string-shaped.
- admitted `MATCH(col_list)` semantics rely on canonical `TextClause.fields[]`; query plans that would require a second independent multi-field text carrier beyond that canonical leaf are outside the active profile until the API evolves again.
- Any FULLTEXT DDL/query behavior that would require canonical schema/query carriers beyond `PutCollectionSchema` / `GetCollectionSchema` / `ExecuteQuery` is outside the active profile until the canonical API surface is expanded.

## 9. Profile 2 Additional Syntax Compatibility Scope

Profile 2 extends Section `3` syntax support to full MySQL `MATCH ... AGAINST` syntax compatibility, including:
- `MATCH(col_list) AGAINST(expr)` (no modifier; default natural-language behavior)
- `MATCH(col_list) AGAINST(expr IN NATURAL LANGUAGE MODE)`
- `MATCH(col_list) AGAINST(expr IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)`
- `MATCH(col_list) AGAINST(expr WITH QUERY EXPANSION)`

Canonical lowering contract for admitted forms:
- every admitted source query in this section still emits one complete canonical `QueryRequest` with non-empty `meta.correlation_id`, `meta.client_id`, `meta.client_kind=CLIENT_KIND_TIDB_COMPUTE`, resolved `collection`, `consistency`, `search_payload`, and `fetch_spec`; route legality is validated against the resolved descriptor `collection_truth_mode=COLLECTION_TRUTH_MODE_EXTERNAL`, and the bullets below only define the `search_payload` text-clause lowering inside that envelope.
- each admitted source `MATCH(col_list)` lowers to exactly one canonical `TextClause` leaf with byte-ascending `fields[]`; gateway MUST NOT split one source clause into multiple canonical clauses.
- `MATCH(col_list) AGAINST(expr)` and `... IN NATURAL LANGUAGE MODE` lower to `TextClause.mode_override.natural_language` with `query_expansion=false`.
- `... IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION` and `... WITH QUERY EXPANSION` lower to `TextClause.mode_override.natural_language.query_expansion=true`.
- `... IN BOOLEAN MODE` lowers to `TextClause.mode_override.boolean_match`.
- when no explicit mode modifier is present, compatibility behavior is still explicit after lowering: canonical request emission MUST choose the natural-language branch rather than relying on an undocumented downstream default.

Profile 2 query-expansion behavior compatibility:
- Query expansion is blind query expansion and executes in two passes:
  - pass 1 executes natural-language search using the original query string;
  - pass 2 executes using the original query string expanded with terms from the few most relevant pass-1 rows.
- Result rows MUST be returned from pass 2 (expanded-query pass), not pass 1.
- Query expansion may increase nonrelevant/noise results and is intended primarily for short search phrases.

## 10. Profile 3 Transaction Semantics Compatibility Scope

Profile 3 extends Profile 2 with TiDB-coordinated transaction semantics coverage for MySQL-compatible FTS behavior.

1. Read-your-write guarantee:
- Any write acknowledged as success MUST be immediately visible to subsequent reads from TiDB SQL perspective.
- This guarantee is achieved by TiDB actively merging:
  - search-system latest visible data version, and
  - incremental changes between that version and the transaction-visible latest version.
- Retrieval system itself remains asynchronous projection and may serve stale standalone read model snapshots.

2. Transactional read/write semantics:
- Multi-statement transaction lifecycle (`BEGIN`, `COMMIT`, `ROLLBACK`) semantics are provided by TiDB.
- Retrieval system does not become a standalone transactional engine; it remains an asynchronous projection target.
- Uncommitted writes are not visible outside their transaction boundary (TiDB-enforced).
- `ROLLBACK` MUST leave no visible write effect (TiDB-enforced).
- `COMMIT` MUST make transaction writes visible atomically at TiDB SQL interface.

3. Snapshot and isolation expectations:
- Reads in a transaction follow TiDB deterministic transaction snapshot plus in-transaction writes.
- Post-commit reads MUST observe committed writes without requiring retrieval-side refresh/wait steps; TiDB resolves visibility through merged-version evaluation.

4. Conformance requirement:
- Profile 3 conformance requires both feature coverage and transaction-semantic coverage; passing syntax-only compatibility is not sufficient.
- Phase-4A completion for this profile means the declared internal-engineering-qualification gate passes at development-delivery maturity; GA Readiness promotion requires separate milestone gating.

5. Profile-3 feature-scope ownership:
- table-level stopword/tokenizer configuration, CJK tokenization hardening on the existing parser surface, and partitioned-table semantics are part of this document's external compatibility contract in canonical applicability mode.
- Myanmar-specific parser/analyzer coverage is not part of this document's active compatibility promise; if added later, it is staged only by `docs/compatibility/tidb-enhancement.md` Profile 3 final tokenizer tranche after analyzer-registry expansion.
- Myanmar support, if admitted in a later revision, is layered in through the TiDB enhancement Phase-4/Profile-3 post-GA expansion tail and does not silently expand the baseline parser registry described in this document.
- Japanese `MECAB` parser semantics remain part of the already-declared parser surface in Sections `3` to `7`; Profile 3 does not reassign that baseline capability.
- `docs/compatibility/tidb-enhancement.md` owns delivery/readiness tracking for the enabling implementation work; `docs/specs/core/search-fabric-architecture.md` owns the internal partitioned-table architecture contract.

## 11. Document ID Contract for MySQL/TiDB Integration Path

1. Canonical ID type:
- `doc_id` MUST lower to canonical `DocumentID.id_value.u64`.
- Control-plane schema MUST declare `DocIDMapping.source_field_type=SCALAR_FIELD_TYPE_UINT64`.
- For the MySQL/TiDB external-truth compatibility path, control-plane schema MUST declare `DocIDMapping.generation_policy=DOC_ID_GENERATION_POLICY_CALLER_REQUIRED`; upstream MySQL/TiDB may auto-generate the source column value (for example through `AUTO_INCREMENT`), but Search Fabric consumes that realized identity rather than allocating a second one.

2. Table schema requirement:
- For MySQL/TiDB integrated FTS path, table MUST expose canonical `doc_id` source as:
  - unsigned `int64` primary key, or
  - unsigned `int64` non-null unique index, or
  - unsigned `int64` `AUTO_INCREMENT` column (`NOT NULL` and uniqueness-preserving).

3. Conformance behavior:
- Tables not satisfying this contract are out of compatibility profile scope and MUST be rejected at onboarding/configuration validation stage.
- For TiCDC-integrated canonical mode, canonical `doc_id` source is immutable after insert; updates attempting to mutate the canonical `doc_id` source MUST be rejected as contract violations.
- Search Fabric allocator-backed omission is out of scope for this external-truth compatibility path; any route that depends on omitted source identity plus `IdAllocatorService` is not part of the MySQL/TiDB canonical compatibility contract defined here.

## 12. Ownership Applicability Boundary

1. Canonical applicability mode:
- Compatibility commitments in this document are normative for tables whose resolved collection descriptor declares:
  - `collection_truth_mode=COLLECTION_TRUTH_MODE_EXTERNAL`
  - table engine=`InnoDB`

2. Profile 3 transaction-semantics scope:
- Profile 3 (`BEGIN`/`COMMIT`/`ROLLBACK`, immediate read-your-write from TiDB SQL perspective) applies only to the canonical applicability mode above.
- Direct native-search-compatible writes to these tables are outside profile scope and MUST be rejected via ownership guardrails.

3. Non-canonical modes:
- `collection_truth_mode=COLLECTION_TRUTH_MODE_SEARCH_FABRIC` may exist in architecture (`docs/specs/core/search-fabric-architecture.md`), but is out of MySQL compatibility profile commitment unless explicitly declared by a phase-scoped release note and conformance addendum that follows `docs/governance/roadmap.md` Section `3.1`.

## 13. Deterministic Error Mapping

Determinism rule:
- For any unsupported or out-of-scope MySQL compatibility behavior, the system MUST fail closed with deterministic error code mapping; no silent fallback is allowed.
- Canonical `SF_*` code definitions (status class, retryability, semantic contract) are centralized in `docs/specs/core/error-codes.md` as the source of truth.
- This section only defines MySQL compatibility scenario-to-code mapping and MUST reuse canonical codes from `docs/specs/core/error-codes.md`.
- At the internal Search Fabric API boundary reached after SQL lowering, canonical/native error projection uses the canonical envelope fields from `docs/specs/core/error-codes.md` Section `2`: `code`, `message`, `retryable`, `correlation_id`, optional typed `context`, and optional string `diagnostics`; this sentence does not redefine TiDB/MySQL SQL-layer wire diagnostics.
- If multiple violations apply to the same request, code selection MUST follow deterministic precedence defined in `docs/specs/core/error-codes.md` Section `2.1`.

### 13.1 Profile 1/2 Syntax and Restriction Errors

- Unsupported `MATCH ... AGAINST` syntax form: `400 SF_UNSUPPORTED_SYNTAX`
- Unsupported parser option (`WITH PARSER`) or parser runtime prerequisites unavailable (for example `MECAB` plugin/dictionary path missing): `400 SF_UNSUPPORTED_PARSER`
- Unsupported boolean operator/token pattern: `400 SF_UNSUPPORTED_BOOLEAN_OPERATOR`
- Unsupported table-level operation in compatibility scope: `400 SF_UNSUPPORTED_TABLE_OPERATION`
- Partitioned table used before Profile-3 support is active in the MySQL-compatible FTS path: `400 SF_PARTITIONED_TABLE_UNSUPPORTED`
- Unsupported storage-engine boundary for FULLTEXT path (non-`InnoDB`): `400 SF_UNSUPPORTED_TABLE_OPERATION`
- FULLTEXT indexed-column type outside `CHAR`/`VARCHAR`/`TEXT`: `422 SF_SCHEMA_VIOLATION`
- FULLTEXT index character-set/collation contract violation: `422 SF_SCHEMA_VIOLATION`
- FULLTEXT indexed Unicode charset boundary violation (`ucs2`/`utf16`/`utf16le`/`utf32` indexed path): `422 SF_SCHEMA_VIOLATION`
- InnoDB stopword table contract violation (`innodb_ft_server_stopword_table` / `innodb_ft_user_stopword_table` not shaped as single `VARCHAR value`, not using `db_name/table_name` value format, or lifecycle ordering incompatible with index-build contract): `422 SF_SCHEMA_VIOLATION`
- `MATCH()` columns span multiple tables, or do not match a declared `FULLTEXT` index definition: `400 SF_MATCH_COLUMN_MISMATCH`
- Requested MyISAM boolean nonindexed-column exception path (outside compatibility profile scope): `400 SF_UNSUPPORTED_QUERY_FEATURE`
- Requested MyISAM-only full-text configuration/behavior path (`ft_*` variables including `ft_boolean_syntax`, or stopword-file charset fallback): `400 SF_UNSUPPORTED_QUERY_FEATURE`
- User-defined full-text collation usage (deprecated in MySQL): `400 SF_UNSUPPORTED_QUERY_FEATURE`
- Unsupported `@` usage outside `@distance` proximity form: `400 SF_UNSUPPORTED_BOOLEAN_OPERATOR`
- Malformed `@distance` proximity form (for example, missing quoted phrase or invalid/missing distance token): `400 SF_UNSUPPORTED_BOOLEAN_OPERATOR`
- `AGAINST()` argument is not constant during query evaluation: `400 SF_INVALID_AGAINST_EXPRESSION`
- Unsupported `MATCH()` + `ROLLUP` query shape: `400 SF_UNSUPPORTED_SYNTAX`
- Unsupported index-hint usage for active `FULLTEXT` path: `400 SF_UNSUPPORTED_QUERY_FEATURE`
- `%` wildcard usage in full-text query: `400 SF_UNSUPPORTED_SYNTAX`

### 13.2 Integration Contract and Ownership Errors

- Source-row `doc_id` value not losslessly normalizable as canonical `SCALAR_FIELD_TYPE_UINT64` (including non-`unsigned int64` or out-of-range values): `422 SF_SCHEMA_VIOLATION`
- Canonical doc-id schema branch requesting `DocIDMapping.source_field_type=SCALAR_FIELD_TYPE_STRING` or `SCALAR_FIELD_TYPE_BYTES`: `400 SF_DOC_ID_CONTRACT_VIOLATION`
- Canonical doc-id schema branch requesting malformed source-field enum outside the admitted set: `400 SF_INVALID_SCHEMA`
- Missing canonical `SCALAR_FIELD_TYPE_UINT64` `unsigned int64 NOT NULL` unique key source for `doc_id`: `400 SF_DOC_ID_CONTRACT_VIOLATION`
- Attempted mutation of canonical `doc_id` source after insert (including CDC events that modify canonical `doc_id` source): `400 SF_DOC_ID_CONTRACT_VIOLATION`
- Write path violates the authoritative `collection_truth_mode` policy: `409 SF_OWNERSHIP_CONFLICT`
- Catalog/descriptor service unavailable for ownership/schema validation: `503 SF_CATALOG_UNAVAILABLE`

### 13.3 Runtime Timeout Errors

- Query execution timeout: `504 SF_QUERY_TIMEOUT`
- Wait-for-visible timeout (only when caller explicitly requests retrieval-side wait-for-visible acknowledgment, i.e., `visibility_policy=wait_for_visible`): `504 SF_VISIBILITY_TIMEOUT`
- `SF_VISIBILITY_TIMEOUT` in this section MUST NOT weaken TiDB SQL immediate read-your-write contract defined in Section `10`; it applies to retrieval-side visibility wait flows only.

### 13.4 Common Access, Admission, and Backend Errors

- Authentication failure or missing credentials: `401 SF_UNAUTHORIZED`
- Authenticated caller lacks required namespace/collection/API permission: `403 SF_FORBIDDEN`
- Target table/index does not exist in active compatibility scope: `404 SF_RESOURCE_NOT_FOUND`
- Document version/create-only conflict, or TiDB transaction/configuration state conflict surfaced in Profile-3 path: `409 SF_VERSION_CONFLICT`
- Tenant/API throttling or admission quota rejection: `429 SF_RATE_LIMITED`
- Payload/schema/type violation: `422 SF_SCHEMA_VIOLATION`
- Vector payload dimension mismatch on hybrid payload path: `422 SF_VECTOR_DIMENSION_MISMATCH`
- Required backend dependency temporarily unavailable: `503 SF_BACKEND_UNAVAILABLE`

### 13.5 Generic and Internal Fallback Errors

- Malformed request envelope/body that does not match any narrower deterministic mapping: `400 SF_BAD_REQUEST`
- Query structure invalid for active MySQL compatibility profile (after syntax-specific checks): `400 SF_INVALID_QUERY`
- Unclassified internal server failure after deterministic mapping rules are exhausted: `500 SF_INTERNAL`