matrixorigin / matrixorigin/matrixone

[Bug]: JSON_KEYS wildcard paths return NULL instead of an error

Open
#28,041 2 comments 0 reactions 1 assignee Claimed by @VioletQwQ-0 View on GitHub
kind/bug severity/s1
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.