matrixorigin / matrixorigin/matrixone

[Bug]: JSON aggregates serialize TIMESTAMP in UTC instead of the session time zone

Open
#28,890 0 comments 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
area/compatibility kind/bug needs-triage
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

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.