matrixorigin / matrixorigin/matrixone

[Bug]: conditional expressions lose temporal fractional-second precision

Open
#28,896 2 comments 0 reactions 1 assignee Assigned to @XuPeng-SH 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

### Problem

MatrixOne loses fractional-second precision when temporal values pass through common conditional expressions. The selected value is changed even when every temporal branch has an explicit fractional precision.

The affected paths include `COALESCE`, `IFNULL`, `IF`, searched/simple `CASE`, prepared parameters, views, and CTAS.

### Reproduction

```sql
drop database if exists conditional_temporal_precision;
create database conditional_temporal_precision;
use conditional_temporal_precision;

create table src(
id int primary key,
d date,
dt6 datetime(6),
ts6 timestamp(6) null,
tm6 time(6)
);
insert into src values
(1, '2024-02-29', '2024-02-29 12:34:56.123456',
'2024-02-29 12:34:56.123456', '12:34:56.123456'),
(2, null, '2024-03-01 01:02:03.999999',
null, '23:59:59.999999');

create view v as
select id,
coalesce(tm6, cast('00:00:00.000001' as time(6))) as c_tm6,
ifnull(dt6, d) as i_dtd,
if(id = 1, ts6, dt6) as f_tsdt,
case when id = 1 then ts6 else dt6 end as k_tsdt,
case id when 1 then ts6 else dt6 end as simple_tsdt
from src;

select * from v order by id;
select column_name, column_type, datetime_precision
from information_schema.columns
where table_schema = database() and table_name = 'v'
order by ordinal_position;

create table ctas as select * from v;
select * from ctas order by id;
```

Prepared execution is also affected:

```sql
prepare p from
'select coalesce(?,cast(''00:00:00.000001'' as time(6))),
ifnull(?,cast(''2000-01-01 00:00:00.000001'' as datetime(6))),
if(1,?,cast(''2000-01-01 00:00:00.000001'' as datetime(6))),
case when 1 then ? else cast(''2000-01-01 00:00:00.000001'' as datetime(6)) end';
set @tm = cast('12:34:56.123456' as time(6));
set @dt = cast('2024-02-29 12:34:56.123456' as datetime(6));
execute p using @tm, @dt, @dt, @dt;
```

### MatrixOne result

- `COALESCE(TIME(6), TIME(6))` is exposed as `TIME(0)` and returns `12:34:56`.
- For input `23:59:59.999999`, the view returns `23:59:59`; CTAS materialization changes that value again to `24:00:00`.
- `IFNULL(DATETIME(6), DATE)`, `IF(TIMESTAMP(6), DATETIME(6))`, and both `CASE` forms are exposed as `DATETIME(0)`.
- `2024-03-01 01:02:03.999999` becomes `2024-03-01 01:02:04` in those expressions.
- Prepared parameters lose the same six fractional digits.

### MySQL 8.0.45 result

The corresponding expressions are `TIME(6)` or `DATETIME(6)` and preserve the original values, including `.123456` and `.999999`, in direct selection, views, CTAS, and prepared execution.

### Additional metadata inconsistency

`COALESCE(TIMESTAMP(6), TIMESTAMP(3))` preserves the value and reports `datetime_precision=6` in MatrixOne, but `column_type` is rendered as `TIMESTAMP(0)` rather than `TIMESTAMP(6)`.

### Scope checked

- `TIME(6)` with `TIME(3)` and constant `TIME(6)` fallbacks;
- `DATE`, `DATETIME(3/6)`, and `TIMESTAMP(3/6)` combinations;
- `COALESCE`, `IFNULL`, `IF`, searched `CASE`, and simple `CASE`;
- direct selection, view, CTAS, and prepared statements;
- ordinary fractions and carry boundary `.999999`;
- three independent runs with byte-identical output on each database.

### Environment

- MatrixOne: official `main` commit `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- MySQL control: 8.0.45
- Local single-node MatrixOne deployment

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.