matrixorigin / matrixorigin/matrixone

[Bug]: VIRTUAL generated DATE(TIMESTAMP) column ignores session time zone

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

Description

## 现象

在 MatrixOne main `99ed717b769e261b842c2f17345bd65865fc1470`,基于 `TIMESTAMP` 的 `VIRTUAL` 生成列没有按当前会话时区重新计算:

```sql
CREATE TABLE t (
id INT PRIMARY KEY,
ts TIMESTAMP(6) NOT NULL,
d DATE AS (DATE(ts)) VIRTUAL,
KEY kd(d)
);
SET time_zone = '+00:00';
INSERT INTO t VALUES (1, '2024-02-29 23:30:00.123456');
SET time_zone = '+08:00';
SELECT ts, DATE(ts), d FROM t;
```

结果为:`ts=2024-03-01 07:30:00.123456`、`DATE(ts)=2024-03-01`,但虚拟列 `d=2024-02-29`。同一查询中的直接表达式与生成列值不一致。

对 `d` 的索引查询 `WHERE d='2024-03-01'` 返回空集,而 `WHERE DATE(ts)='2024-03-01'` 能命中该行。

## 期望

`VIRTUAL` 生成列应在读取/谓词计算时按当前会话语义重新计算,至少应与其生成表达式在同一查询中保持一致;其索引查找也应与表达式结果一致。

## 影响

按时区查询 `TIMESTAMP` 的日期/小时虚拟列会得到错误结果,且索引可能静默漏行。

## 复现信息

- MatrixOne main: `99ed717b769e261b842c2f17345bd65865fc1470`
- 重复执行 3 次,结果一致

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.