matrixorigin / matrixorigin/matrixone

[Compatibility]: session timestamp override is unavailable for current-time expressions and automatic TIMESTAMP columns

Open
#28,599 1 comment 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

## Environment

- MatrixOne: `main` at `fc621e3616d229c7a29d0c80e73ed8eb1997459c`
- MySQL reference: 8.0.46
- Session time zone: `+00:00`

## Problem

MatrixOne does not define MySQL's session `timestamp` system variable. Reading or setting `@@session.timestamp` returns `internal error: the system variable does not exist`.

MySQL uses this variable to override the session's current time for deterministic execution. A nonzero value controls `NOW`, `CURRENT_TIMESTAMP`, `LOCALTIME`, `LOCALTIMESTAMP`, `UTC_TIMESTAMP`, `UNIX_TIMESTAMP()`, and automatic `DEFAULT/ON UPDATE CURRENT_TIMESTAMP` evaluation. Setting it to zero restores the real clock. `SYSDATE()` intentionally remains tied to the real clock.

This behavior is documented by MySQL under the [`timestamp` system variable](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_timestamp).

## Reproduction

```sql
SET SESSION time_zone = '+00:00';
SET SESSION timestamp = 1704067200.123456;
SELECT @@session.timestamp;

SELECT NOW(6), CURRENT_TIMESTAMP(6), LOCALTIME(6),
LOCALTIMESTAMP(6), UTC_TIMESTAMP(6), UNIX_TIMESTAMP(), SYSDATE(6);

PREPARE p FROM 'SELECT NOW(6), CURRENT_TIMESTAMP(6), UTC_TIMESTAMP(6), UNIX_TIMESTAMP()';
EXECUTE p;

CREATE TABLE t (
id INT PRIMARY KEY,
created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
ON UPDATE CURRENT_TIMESTAMP(6)
);
INSERT INTO t(id) VALUES (1);

SET SESSION timestamp = 1704067260.5;
UPDATE t SET id = 2 WHERE id = 1;
SELECT id, created_at, updated_at FROM t;
EXECUTE p;

SET SESSION timestamp = 0;
EXECUTE p;
```

## MatrixOne behavior

```text
SET SESSION timestamp = 1704067200.123456
ERROR 20101: internal error: the system variable does not exist

SELECT @@session.timestamp
ERROR 20101: internal error: the system variable does not exist
```

All affected functions and automatic timestamp columns continue to use wall-clock time.

## MySQL 8.0.46 behavior

- `@@session.timestamp` returns `1704067200.123456`.
- The affected functions return `2024-01-01 00:00:00.123456`; repeated executions of the prepared statement return the same frozen value.
- The inserted row receives `2024-01-01 00:00:00.123456` for both automatic columns.
- After changing the variable to `1704067260.5`, `updated_at` becomes `2024-01-01 00:01:00.500000`, while `created_at` remains unchanged.
- `SYSDATE(6)` continues to return wall-clock time.
- Setting the variable to zero restores real-time evaluation.

## Impact

Applications and test tools that rely on MySQL's session clock override cannot obtain deterministic current-time expressions or deterministic automatic timestamp values in MatrixOne. The same SQL either errors while configuring the session or silently uses wall-clock time if the configuration step is omitted.

## Code analysis

`pkg/frontend/variables.go` registers several MySQL-compatible temporal variables, including `default_week_format`, `explicit_defaults_for_timestamp`, and `lc_time_names`, but has no `timestamp` system-variable definition. Current-time expressions therefore only receive the statement execution time and have no session override to consume.

The behavior above was reproduced three times with fresh databases and connections. Direct execution, prepared-statement reuse, automatic column defaults, automatic `ON UPDATE`, and the zero-reset control were covered.

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.