matrixorigin / matrixorigin/matrixone
[Bug]: async IVFFLAT INCLUDE shifts TIMESTAMP values by the session timezone offset
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Environment
- MatrixOne commit: `01d60e1c4ded1b0f3fc1a4ecd75ce54e95e23b90`
- Deployment: local shared-TN cluster with two CNs
- Session time zone used for verification: `+08:00`
## Problem
The ISCP maintenance path for an asynchronous IVFFLAT index changes the instant
represented by an included `TIMESTAMP` column. In a `+08:00` session the hidden
entry is eight hours earlier than the source row. `mode=include` and `mode=auto`
can use that hidden value directly in an index-only scan, so they return a
different result from the base table and from `force`, `pre`, and `post` modes.
`DATETIME` and the other tested INCLUDE types retain their values. A synchronous
IVFFLAT full build also retains the `TIMESTAMP` instant, which isolates the
problem to asynchronous ISCP serialization.
## Minimal reproduction
```sql
set time_zone='+08:00';
set experimental_ivf_index=1;
create database ivf_async_include_ts;
use ivf_async_include_ts;
create table t(
id bigint primary key,
ts timestamp,
dtm datetime,
v vecf32(2)
);
insert into t
select result,
cast('2026-01-02 03:04:05' as timestamp),
cast('2026-01-02 03:04:05' as datetime),
cast(concat('[',result,',0]') as vecf32(2))
from generate_series(1,10) g;
create index ix using ivfflat on t(v)
lists=1
op_type 'vector_l2_ops'
quantization 'float16'
include(ts,dtm)
async;
```
After the async entries table reaches 10 rows:
```sql
select unix_timestamp(t.ts),
unix_timestamp(e.__mo_index_include_ts),
t.ts,
e.__mo_index_include_ts,
t.dtm,
e.__mo_index_include_dtm
from t
join e on t.id=e.__mo_index_pri_col
where t.id=1;
```
Result:
```text
source epoch : 1767294245 source ts : 2026-01-02 03:04:05
index epoch : 1767265445 index ts : 2026-01-01 19:04:05
source dtm : 2026-01-02 03:04:05
index dtm : 2026-01-02 03:04:05
```
Changing the query session time zone changes both displays but leaves the epoch
difference at 28,800 seconds, so this is not only display formatting.
## Mode-visible impact
For an index-only projection of `id, ts, dtm`:
| mode | `unix_timestamp(ts)` | `ts` | plan |
|---|---:|---|---|
| `force` | 1767294245 | 2026-01-02 03:04:05 | base table |
| `pre` | 1767294245 | 2026-01-02 03:04:05 | index + base table |
| `post` | 1767294245 | 2026-01-02 03:04:05 | index + base table |
| `include` | 1767265445 | 2026-01-01 19:04:05 | index-only |
| `auto` | 1767265445 | 2026-01-01 19:04:05 | index-only |
Three independently created asynchronous databases reproduced the same shift.
A synchronous IVFFLAT index with the same source rows and INCLUDE columns stored
epoch `1767294245`, matching the source.
## Root cause
`pkg/iscp/util.go` converts `TIMESTAMP` logtail values to SQL strings using UTC:
```go
// TODO:get the right timezone
timeZone := time.UTC
row[i] = timestamp.String2(timeZone, scale)
```
`convertColIntoSql` then emits that string as an unqualified timestamp literal.
The internal SQL session parses the literal in its session time zone, changing
the represented instant. `DATETIME` is unaffected because it has no time-zone
conversion semantics.
## Expected behavior
The included `TIMESTAMP` must represent the same instant as the source column,
independent of the user or internal SQL session time zone. All vector search
modes must return the same projected timestamp for the same row.
## Regression coverage
- async initial replay and later INSERT/UPDATE for `TIMESTAMP` INCLUDE values;
- positive, zero, and negative session offsets;
- `TIMESTAMP` scales with fractional seconds;
- `DATETIME` control;
- direct hidden-table epoch comparison and index-only `include/auto` queries;
- synchronous full-build control.
Contributor guide
Assessment
This issue has not been assessed yet.