matrixorigin / matrixorigin/matrixone
[Bug]: JSON_OBJECTAGG rejects YEAR and truncates TIME/DATETIME fractional seconds
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
`JSON_OBJECTAGG` does not use the typed temporal conversion used by the other JSON constructors. A `YEAR` value is rejected, and valid `TIME`/`DATETIME` values lose their fractional seconds. The altered values are returned and persisted through normal aggregation, window aggregation, CTAS, views, prepared statements, parallel execution, and aggregate spill.
## Environment
- Branch: `main`
- Commit: `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- Deployment: local standalone MatrixOne, 1 CN / 1 TN / 1 LogService
- Reference: local MySQL `8.0.45`
## Steps to reproduce
```sql
CREATE DATABASE json_objectagg_temporal_repro;
USE json_objectagg_temporal_repro;
CREATE TABLE t (
k VARCHAR(16),
y YEAR,
tm0 TIME,
tm6 TIME(6),
dt0 DATETIME,
dt6 DATETIME(6)
);
INSERT INTO t VALUES
('a', 2024, '12:34:56', '12:34:56.123456',
'2024-02-29 12:34:56', '2024-02-29 12:34:56.123456'),
('b', NULL, NULL, NULL, NULL, NULL);
SELECT JSON_OBJECTAGG(k, y) FROM t;
SELECT JSON_OBJECTAGG(k, tm0), JSON_OBJECTAGG(k, tm6),
JSON_OBJECTAGG(k, dt0), JSON_OBJECTAGG(k, dt6)
FROM t;
```
## MatrixOne result
The YEAR query is rejected:
```text
ERROR 20301 (HY000): invalid input: unsupported type for json aggregate: YEAR
```
The remaining query returns:
```text
{"a":"12:34:56","b":null}
{"a":"12:34:56","b":null}
{"a":"2024-02-29 12:34:56","b":null}
{"a":"2024-02-29 12:34:56","b":null}
```
Both declared scale 0 and scale 6 collapse to the same JSON strings. For scale 6 this silently removes `.123456`.
## MySQL 8.0.45 result
```text
{"a":2024,"b":null}
{"a":"12:34:56.000000","b":null}
{"a":"12:34:56.123456","b":null}
{"a":"2024-02-29 12:34:56.000000","b":null}
{"a":"2024-02-29 12:34:56.123456","b":null}
```
`JSON_OBJECT` and the current `JSON_ARRAYAGG` implementation in MatrixOne also retain the scale-6 value, so the loss is specific to the object aggregate value-conversion path.
## Scope and repeatability
- The scalar aggregate, ordered window aggregate, CTAS result, view result, and prepared execution reproduce the same conversion on every run.
- A 160,000-row / 40,000-group test returns all 40,000 complete JSON objects, but zero objects retain the TIME(6) or DATETIME(6) expected value.
- That scale test reproduces 3/3 with `max_dop=1` and `max_dop=8`, both resident and with `agg_spill_mem=65536`; the low-threshold runs enter multi-level aggregate spill and complete with the same altered values.
- NULL values remain JSON null and object membership is complete; the problem is the non-NULL temporal atom conversion.
## Code analysis
`pkg/sql/plan/function/list_agg.go` admits every non-binary value type for `JSON_OBJECTAGG`. In `pkg/sql/colexec/aggexec/jsonagg2.go`, both the normal `jsonObjectAggExec.BatchFill` path and the allocation-accounted spill path call the generic `buildValueByteJson` / `appendJSONAggregateValue` functions directly. Those functions do not handle `YEAR`, and they format `TIME`/`DATETIME` with `String()`, which drops declared fractional precision.
The recently merged `JSON_ARRAYAGG` fix routes TIME, DATETIME, and YEAR through `jsonvalue.FromVector`; the corresponding object-aggregate route is still absent.
## Expected behavior
`JSON_OBJECTAGG` should accept YEAR values and preserve temporal scale and fractional content consistently with MatrixOne's other JSON constructors and MySQL 8. The corrected conversion must be shared by normal, accounted/spill, merge, window, CTAS/view, and prepared execution paths.
Contributor guide
Assessment
This issue has not been assessed yet.