matrixorigin / matrixorigin/matrixone
[Bug]: JSON aggregates serialize TIMESTAMP in UTC instead of the session time zone
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
`JSON_ARRAYAGG` and `JSON_OBJECTAGG` serialize `TIMESTAMP` from its UTC storage representation instead of the value in the current session time zone. The resulting JSON contains a literal ` UTC` suffix and does not change after `time_zone` changes, while the source TIMESTAMP and scalar JSON constructors do change correctly.
## 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_timestamp_agg_repro;
USE json_timestamp_agg_repro;
SET time_zone = '+00:00';
CREATE TABLE t (k VARCHAR(16), ts TIMESTAMP(6));
INSERT INTO t VALUES ('a', '2024-02-29 04:34:56.123456');
SET time_zone = '+08:00';
SELECT ts,
JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('ts', ts), '$.ts')) AS scalar_json,
JSON_UNQUOTE(JSON_EXTRACT(JSON_ARRAYAGG(ts), '$[0]')) AS array_agg,
JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECTAGG(k, ts), '$.a')) AS object_agg
FROM t
GROUP BY ts;
```
## MatrixOne result
```text
ts 2024-02-29 12:34:56.123456
scalar_json 2024-02-29 12:34:56.123456
array_agg 2024-02-29 04:34:56.123456 UTC
object_agg 2024-02-29 04:34:56.123456 UTC
```
At `time_zone='+00:00'`, both aggregate values are still emitted with the extra ` UTC` suffix. At `+08:00`, they continue to expose the UTC value instead of the session-local value.
## MySQL 8.0.45 result
At `+08:00`, all four values are:
```text
2024-02-29 12:34:56.123456
```
At `+00:00`, all four values are `2024-02-29 04:34:56.123456`. MySQL JSON aggregation follows the same session-time-zone conversion as the TIMESTAMP column and scalar JSON constructor.
## Scope and repeatability
- Both time zones reproduce identically in three independent MatrixOne runs and three MySQL control runs.
- `JSON_ARRAYAGG` and `JSON_OBJECTAGG` are both affected.
- CTAS persists the UTC-suffixed value; changing the time zone before reading cannot recover the intended session-local JSON value.
- Views and prepared statements reproduce the same aggregate value.
- The source TIMESTAMP, scalar `JSON_OBJECT`, NULL handling, table contents, and server availability remain correct.
## Code analysis
`pkg/sql/colexec/aggexec/jsonagg2.go` converts aggregate TIMESTAMP inputs through `types.Timestamp.String()`. That formats the internal UTC representation and bypasses the session-time-zone-aware conversion used by scalar JSON constructors. The special JSON_ARRAYAGG typed-conversion helper currently includes TIME, DATETIME, and YEAR but explicitly omits TIMESTAMP; JSON_OBJECTAGG does not use the helper at all.
## Expected behavior
JSON aggregates should serialize TIMESTAMP values in the current session time zone, without an implementation-specific ` UTC` suffix, consistently with ordinary TIMESTAMP reads, scalar JSON constructors, and MySQL 8. The same conversion must apply to normal, parallel/merge, spill, window, CTAS/view, and prepared paths.
Contributor guide
Assessment
This issue has not been assessed yet.