# [Known-Issues]TiFlash returns wrong result when using DATE_SUB() or DATE_ADD()

## Key Words

TiFlash, DATE_SUB(), DATE_ADD(), wrong result, TiFlash 结果不对

## Description

在 SQL 中包含 DATE_SUB(), DATE_ADD() 函数时，通过 TiFlash 和 TiKV 返回的结果不一致。

## Root Cause

This is a bug, the TiFlash returns wrong value when executing DATE_SUB() or DATE_ADD().

Please refer to this pr: [https://github.com/pingcap/tidb/issues/27336](https://github.com/pingcap/tidb/issues/27336)

## Diagnostic Steps

```sql
create table t(a char(20));

alter table t set tiflash replica 1;

set tidb_enforce_mpp=1;

insert into t values('210101');

mysql> explain select DATE_ADD(a, INTERVAL 1 day ) from t;
+---------------------------+---------+--------------+---------------+--------------------------------------+
| id                        | estRows | task         | access object | operator info                        |
+---------------------------+---------+--------------+---------------+--------------------------------------+
| TableReader_11            | 1.00    | root         |               | data:ExchangeSender_10               |
| └─ExchangeSender_10       | 1.00    | cop[tiflash] |               | ExchangeType: PassThrough            |
|   └─Projection_4          | 1.00    | cop[tiflash] |               | date_add(test.t.a, 1, DAY)->Column##3 |
|     └─TableFullScan_9     | 1.00    | cop[tiflash] | table:t       | keep order:false, stats:pseudo       |
+---------------------------+---------+--------------+---------------+--------------------------------------+
4 rows in set (0.01 sec)

mysql> select /*+ read_from_storage(tiflash[t]) */ DATE_ADD(a, INTERVAL 1 day ) from t;
+------------------------------+
| DATE_ADD(a, INTERVAL 1 day ) |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.02 sec)

mysql> select /*+ read_from_storage(tikv[t]) */ DATE_ADD(a, INTERVAL 1 day ) from t;
+------------------------------+
| DATE_ADD(a, INTERVAL 1 day ) |
+------------------------------+
| 2021-01-02                   |
+------------------------------+
1 row in set (0.00 sec)
```

可以看出，当执行 DATE_ADD 函数的时候，在 TiFlash 上返回的结果是错误的。

## Resolution

Upgrade to the v5.4.0

## Workaround

在 where 条件中函数 DATE_SUB() 或 DATE_ADD() 的第一个参数上套一层 cast as date

## Affected Versions

< v5.4.0