matrixorigin / matrixorigin/matrixone
[Bug]: JSON_KEYS wildcard paths return NULL instead of an error
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
`JSON_KEYS(json_doc, path)` silently returns SQL `NULL` when `path` contains `*` or `**`. MySQL defines these wildcard paths as invalid for `JSON_KEYS()` and raises an error instead; returning `NULL` makes invalid queries indistinguishable from a valid path that selects a non-object or no value.
## Environment
- Branch: `main`
- Commit: `17c161a31b47a22e71ca222839143f66717ac365`
- Deployment: local single-CN test service
- Test date: 2026-09-03
## Steps to reproduce
```sql
SELECT JSON_KEYS('{"a":{"x":1},"b":{"y":2}}', '$.*');
SELECT JSON_KEYS('{"a":{"x":1},"b":{"y":2}}', '$**.x');
```
## Actual behavior
Both statements return SQL `NULL` without an error.
## Expected behavior
MySQL requires `JSON_KEYS()` to raise an error when its path contains a `*` or `**` wildcard. A non-wildcard control behaves correctly in MatrixOne:
```sql
SELECT JSON_KEYS('{"a":{"x":1},"b":{"y":2}}', '$.a');
-- ["x"]
```
Reference: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-keys
## Stability and controls
- Reproducer: `3/3` for both `$.*` and `*.x`.
- Control: root `JSON_KEYS()` returns `["a", "b"]`; the ordinary object path `$.a` returns `["x"]` in each run.
- No DDL or DML is involved.
## Code analysis
`pkg/sql/plan/function/func_builtin_json.go:jsonKeysWithPath` parses the supplied path and executes `bj.Query(...)`, but does not reject a path containing wildcard legs before querying. A wildcard query returns a non-object aggregate value, and the following `val.Type != bytejson.TpCodeObject` branch appends SQL `NULL`. The function needs a dedicated wildcard-path validation before the query.
## Regression coverage
After the fix, add deterministic coverage for `JSON_KEYS()` with root and ordinary object paths, a path selecting a non-object/no value (SQL `NULL`), and both `*`/`**` paths (error).
Contributor guide
Assessment
This issue has not been assessed yet.