matrixorigin / matrixorigin/matrixone

[Compatibility]: numeric aggregate functions reject JSON operands accepted by MySQL

Open
#28,868 0 comments 0 reactions 1 assignee Claimed by @VioletQwQ-0 View on GitHub
area/compatibility kind/bug severity/s1
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## Description

MatrixOne rejects JSON operands for MySQL numeric aggregate functions. MySQL converts non-NULL JSON values to numeric form for aggregation; MatrixOne rejects the expression during binding even when every JSON value is a numeric scalar.

## Environment

- MatrixOne: latest official `main`, commit `07fdd4ae0f80f287b93fc4b525fd296d5617abc7`
- Deployment: local standalone launch (isolated Log/TN/CN ports and data directory)
- MySQL JSON aggregation contract: https://dev.mysql.com/doc/refman/8.0/en/json.html#json-aggregation

## Reproduction

```sql
create table t(id int, j json);
insert into t values (1,'1'),(2,'2.5'),(3,'3'),(4,null);

select sum(j), avg(j), var_pop(j), var_samp(j),
stddev_pop(j), stddev_samp(j)
from t;
```

## MatrixOne behavior

```text
invalid argument aggregate function sum, bad value [JSON]
```

Executing each function separately produces its corresponding binding error. Ordinary and window forms reproduce in 3/3 fresh runs.

## Control

The equivalent explicit numeric conversion succeeds in MatrixOne:

```sql
select sum(cast(j as double)), avg(cast(j as double)),
var_pop(cast(j as double)), stddev_pop(cast(j as double))
from t;
```

```text
6.5, 2.1666666666666665, 0.7222222222222223, 0.8498365855987975
```

## Expected behavior

For MySQL compatibility, numeric JSON scalars should enter the same numeric-conversion path as other numeric-context operands. Ordinary, DISTINCT, grouped/window, prepared-statement, View, and CTAS forms should resolve consistently, including documented NULL and nonnumeric JSON behavior.

## Code-path analysis

`sumAvgTypeCheck` accepts only `SumSupportedTypes`; `mysqlNumericAggTypeCheck` used by variance/stddev adds string and temporal coercion but still excludes `T_json`. MatrixOne already has JSON-to-number CAST execution, as demonstrated by the explicit-cast control, so the missing piece is aggregate overload/type resolution.

## Suggested regression coverage

- integer, decimal, exponent, JSON null, SQL NULL, boolean/string/composite JSON;
- SUM/AVG/VAR/STDDEV and aliases, DISTINCT, grouped/window, prepared statements, View/CTAS;
- strict-mode warnings/errors and explicit-cast controls.

## Duplicate search

Open and closed issues were searched for JSON numeric aggregation, SUM/AVG/VAR/STDDEV JSON operands, and the binding error. Existing JSON aggregate reports concern JSON_ARRAYAGG/OBJECTAGG, not standard numeric aggregation.

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.