Repository navigation
[Compatibility]: MySQL functional indexes on JSON expressions are unsupported #28053
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
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:
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'selectedidx_kind. - MatrixOne workaround control: a stored generated column
kind AS (doc ->> '$.kind')plusINDEX(kind)works and producesIndex 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.
Activity
VioletQwQ-0 commentedon Sep 4, 2026
已完成 triage。该问题具有稳定复现、MySQL 8 对照和明确代码入口，并已进入当前里程碑，因此从
needs-triage提升为severity/s1。首期范围如下：
CREATE TABLE内联定义、CREATE INDEX和ALTER TABLE ADD INDEX。实现采用“一条功能索引对应一个内部隐藏 generated column”的方式，复用现有 generated-column DML 计算和普通二级索引维护；隐藏列及其索引必须原子创建、回填和删除，且
SHOW CREATE TABLE、SHOW INDEX、clone、checkpoint/restore 不得暴露内部列名。合入依赖为：
JSON_UNQUOTE/->>的文本类型和 charset 语义。实现 PR 使用
issue #28053关联，不自动关闭 Issue。合入后转入phase/testing，由 QA 在单 CN、多 CN、重启及备份恢复环境给出明确结论后再决定关闭。[RFC] functional indexes on deterministic expressions (#28156)
feat: support ordinary functional indexes (matrixorigin#28053)
feat: support ordinary functional indexes (matrixorigin#28053)
[RFC] functional indexes on deterministic expressions (matrixorigin#2…
[RFC] functional indexes on deterministic expressions (matrixorigin#2…
[RFC] functional indexes on deterministic expressions (matrixorigin#2…
feat(sql): support composite functional indexes (#29472)
feat(sql): support bounded JSON text functional indexes (matrixorigin…
Ariznawlll commentedon Oct 22, 2026
Current-main data point (dev
3a1e42b430, 2026-10-22): this rejection now has a narrower, misleading surface worth knowing for the fix.Non-JSON functional indexes work end-to-end on the same build —
CREATE INDEX idx_lower ON fi((lower(name)))creates, maintains, and is used both by point lookups and by EXPLAIN (dual filter + Index Table Scan), with UPDATE consistency and SHOW CREATE round-trip preserved.The JSON expression form specifically dies at DDL time with:
Two observations:
CAST(d->'$.k' AS UNSIGNED)is fully session-independent (pure function of the row); whatever check produces this error is misclassifying the JSON cast/extract expression — likely a whitelist of permitted functional-index expressions that includes string functions but not JSON arrow/cast nodes.WHERE CAST(d->'$.k' AS UNSIGNED) = 12returns the right row; UPDATE-consistency checks pass), so this is purely "cannot create the acceleration index", consistent with this issue's compatibility scope.Regression checks for the eventual fix: (a) DDL acceptance of the CAST form, (b) index actually used by the
CAST(...)=Npredicate per EXPLAIN, (c) post-UPDATE index consistency — same three probes that currently pass forlower()on this build.fix(sql): allow bounded JSON text in existing functional indexes (#28274
XuPeng-SH commentedon Oct 25, 2026
QA handoff: bounded JSON functional-index behavior on pinned main
Tested the original issue DDL against the isolated local MatrixOne service:
aeaa273daf588a9988ba423915d3ae6b8c82124a; runninggit_version()returnedaeaa273daf.450363d5ec5e693bdd7ded44565b3b0ab1addcb807d3f9569c5d5eedeb1486e4.CREATE INDEX idx_kind ON docs ((CAST(doc ->> '$.kind' AS CHAR(20))))succeeded and persisted in SHOW CREATE TABLE.Index Table Scan on docs.idx_kind; indexed and IGNORE INDEX result sets matched the independent row oracle.The temporary database was dropped and the owned service was shut down cleanly. This verifies the issue's bounded
CHAR(20)compatibility case on one local CN; it does not cover multi-CN, restart, backup/restore, or release deployments. Keeping the issue open for tester disposition.slyunnn commentedon Oct 26, 2026
Regression and extended verification for #28053 (2026-10-26)
Verification Environment
main:861a2c4cb68961d8b6b01f18e0aa6ac52a3a505f—Fix decimal rounding and MySQL text-to-float casts (#31191). The running binary reportedgit_version() = 861a2c4cb6.2 CN / 1 TN / 1 LogService / Proxy; built from the pinned source with Go1.27.1.16833, CN126151, and CN226152were reachable. The original DDL and plan checks ran through Proxy; the indexed lookup was also run directly through Proxy, CN1, and CN2.What Was Covered
CREATE INDEX idx_kind ON docs ((CAST(doc ->> '$.kind' AS CHAR(20))))succeeded.SHOW INDEXexposed the expression, andEXPLAINselectedIndex Table Scan on docs.idx_kind.UPDATE, the match moved to the new JSON value; afterDELETE, it no longer matched.ERROR 20105.Findings
The original bounded JSON-text functional-index scenario is fixed on current
main. The narrow fix is merged as PR #28274, commit8f9c50468ea06cd52554f2a5ef94cfb137d930f6— it permits bounded JSON-to-text expressions while retaining rejection of unsupported/unbounded forms.MOTR / BVT Coverage
No new MOTR case was added: this run supplied direct multi-CN SQL evidence, and the implementation PR already added the focused distributed SQL regression. The full BVT suite was not rerun.
Final Conclusion
Fixed for the issue's bounded JSON-text functional-index case on the pinned latest
main. Large-data regression is not needed: this is an expression-eligibility, index-plan, and DML-maintenance behavior, not a row-count or resource-boundary issue.