# [Known Issues] TiDB 和 Mysql 在 `greatest()` 和 `least()` 行为不一致

## Key Words

Data Too Long, greatest, least, datetime

## Description

`greatest()` 和 `least()` 函数在 TiDB 中的行为和 MySQL 不一致，`least(date)` 后取值变成了 `datetime`，可能会引起数据类型不匹配，长度不一致等问题。

## Root Cause

It's a bug, the result of least(date) and greatest(date) will becomes datetime which has more length than date.

## Diagnostic Steps

```sql
mysql> use test;
Database changed
mysql> create table cux_test3(id bigint, rate double, date_1 date, date_2 date);
Query OK, 0 rows affected (0.20 sec)

mysql> create table cux_test4(id bigint, rate double, date_1 date, date_2 date);
Query OK, 0 rows affected (0.16 sec)


mysql> insert into cux_test3 values (1, 2.0, date '2021-01-03', date '2021-02-03'),(2, 4.0, date '2021-05-23', date '2021-02-02');
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into cux_test4 select d1.id, d1.rate, least(d1.date_1, d1.date_2) date1, d1.date_2 from cux_test3 d1 union all select d2.id, d2.rate, max(d2.date_2) over (partition by d2.id) date1, d2.date_2 from cux_test3 d2;
ERROR 1406 (22001): Data Too Long, field len 10, data len 19
```

The error was caused by the result of least(date) becomes to a datetime. 

The default value of date is like "2022-12-31", but the default values of datetime is like "2022-12-31 23:59:59", it need more space to store datetime.

ResolutionUpgrade to the v4.0.15 or later, v5.0.4 or later, v5.1.2 or later versions.

Please refer to this [https://github.com/pingcap/tidb/pull/26533](https://github.com/pingcap/tidb/pull/26533)

## Workaround

 Using cast(least([d1.date](https://kb.pingcap.cn/d1.date)_1, [d1.date](https://kb.pingcap.cn/d1.date)_2) as date) to avoid this error.

## Affected Versions

v4.0.0 ~ v4.0.14

v5.0.0 ~ v5.0.3

v5.1.1