Scalar function date_add(signature: ScalarFuncSig_AddDateStringInt) has different behaviour between TiDB and TiFlash.

[LittleFall]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table t;
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)

2. What did you expect to see? (Required)

result is same.

3. What did you see instead (Required)

result is different.

4. What is your TiDB version? (Required)

v5.1.1

[LittleFall]

scalar function date_add is default in blacklist.

[LittleFall]

date_sub has the same issue.

[LittleFall]

/assign @mengxin9014

[mengxin9014]

/assign @mengxin9014

[JaySon-Huang]

Another case from AskTUG: https://asktug.com/t/topic/152922
@mengxin9014 @LittleFall

[windtalker]

This issue is fixed by #28133