matrixorigin / matrixorigin/matrixone
[Bug]: conditional expressions lose temporal fractional-second precision
- 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
Assessment
This issue has not been assessed yet.