matrixorigin / matrixorigin/matrixone
[Compatibility]: MySQL functional indexes on JSON expressions are unsupported
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
MatrixOne accepts the `CREATE INDEX` syntax up to the expression key part, then rejects a MySQL-supported functional index over a JSON extraction expression. This prevents indexing JSON predicates without adding a separate generated column to the schema.
## Environment
- Branch: `main`
- Commit: `95d04586eb80864499d0fde27aeb912b463ae4cd`
- Deployment: local single-CN MatrixOne
- MySQL control: isolated MySQL 8.0 socket instance
## Steps to reproduce
```sql
CREATE DATABASE json_functional_index;
USE json_functional_index;
CREATE TABLE docs(id INT PRIMARY KEY, doc JSON);
CREATE INDEX idx_kind ON docs ((CAST(doc ->> '$.kind' AS CHAR(20))));
```
## Actual behavior
MatrixOne returns:
```text
ERROR 20101 (HY000): internal error: unsupported index which using expression as keypart
```
The table remains unchanged.
## Expected behavior
The same DDL is accepted as in MySQL 8.0. After inserting JSON documents, an equality predicate on the identical expression can use the functional index.
## Stability and controls
- Reproducer: 3/3 MatrixOne runs returned the same error.
- MySQL 8.0 control: created `idx_kind`; `EXPLAIN SELECT id FROM docs WHERE CAST(doc ->> '$.kind' AS CHAR(20)) = 'alpha'` selected `idx_kind`.
- MatrixOne workaround control: a stored generated column `kind AS (doc ->> '$.kind')` plus `INDEX(kind)` works and produces `Index Table Scan`; it requires a schema change and is not syntax-compatible with MySQL functional indexes.
## Code analysis
`pkg/sql/plan/build_index_util.go:159-163` explicitly rejects every expression key part in `checkIndexKeypartSupportability` with the reported error.
## Regression coverage
After implementation, add deterministic DDL/DML/EXPLAIN coverage for a JSON expression key part to the index BVT suite.
Contributor guide
Assessment
This issue has not been assessed yet.