TIMESTAMPDIFF(YEAR, dbirth, CURDATE()) is very slow if dbirth is of decimal(10,0)

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (id int, dbirth decimal(10,0), birth date);
alter table t set tiflash replica 1;
insert into t values (0, 20220501,'20120501'),(3, 20210503,'20220503'),(4, 20220504,'20220504'),(5, 20220505,'20220505'),(200214, 20020514,'20020514'),(200114, 20010514,'20010514'),(201114, 20110514,'20110514');
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;
insert into t (id, dbirth,birth) select * from t;

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

the function does not cost too much time compared to executing birthday of data type.

3. What did you see instead (Required)

evaluating on birthday of decimal is very slower than on birthday of date.

mysql> explain analyze SELECT  max(TIMESTAMPDIFF(YEAR, dbirth, CURDATE())) from t group by id limit 10;
+----------------------------------------+-----------+---------+--------------+---------------+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| id                                     | estRows   | actRows | task         | access object | execution info                                                                             | operator info                                                                               | memory | disk |
+----------------------------------------+-----------+---------+--------------+---------------+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
| Limit_10                               | 10.00     | 10      | root         |               | time:9.85s, loops:2                                                                        | offset:0, count:10                                                                          | N/A    | N/A  |
| └─TableReader_38                       | 10.00     | 17      | root         |               | time:9.85s, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_37                                                                      | N/A    | N/A  |
|   └─ExchangeSender_37                  | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:1, threads:8}                                              | ExchangeType: PassThrough                                                                   | N/A    | N/A  |
|     └─Projection_33                    | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:1, threads:8}                                              | Column#5                                                                                    | N/A    | N/A  |
|       └─HashAgg_34                     | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:1, threads:1}                                              | group by:test.t.id, funcs:max(Column#7)->Column#5                                           | N/A    | N/A  |
|         └─ExchangeReceiver_36          | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:8, threads:8}                                              |                                                                                             | N/A    | N/A  |
|           └─ExchangeSender_35          | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:256, threads:6}                                            | ExchangeType: HashPartition, Hash Cols: [name: test.t.id, collate: binary]                  | N/A    | N/A  |
|             └─HashAgg_16               | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:9.85s, loops:256, threads:1}                                            | group by:Column#11, funcs:max(Column#10)->Column#7                                          | N/A    | N/A  |
|               └─Projection_52          | 983040.00 | 3932172 | mpp[tiflash] |               | tiflash_task:{time:9.83s, loops:64, threads:6}                                             | timestampdiff(YEAR, cast(test.t.dbirth, datetime BINARY), 2022-06-06)->Column#10, test.t.id | N/A    | N/A  |
|                 └─TableFullScan_32     | 983040.00 | 3932172 | mpp[tiflash] | table:t       | tiflash_task:{time:9.36s, loops:64, threads:6}                                             | keep order:false                                                                            | N/A    | N/A  |
+----------------------------------------+-----------+---------+--------------+---------------+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------+------+
10 rows in set (9.85 sec)

mysql> explain analyze SELECT  max(TIMESTAMPDIFF(YEAR, birth, CURDATE())) from t group by id limit 10;
+----------------------------------------+-----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+------+
| id                                     | estRows   | actRows | task         | access object | execution info                                                                              | operator info                                                              | memory | disk |
+----------------------------------------+-----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+------+
| Limit_10                               | 10.00     | 10      | root         |               | time:92.1ms, loops:2                                                                        | offset:0, count:10                                                         | N/A    | N/A  |
| └─TableReader_38                       | 10.00     | 17      | root         |               | time:92.1ms, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_37                                                     | N/A    | N/A  |
|   └─ExchangeSender_37                  | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:91.2ms, loops:1, threads:8}                                              | ExchangeType: PassThrough                                                  | N/A    | N/A  |
|     └─Projection_33                    | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:91.2ms, loops:1, threads:8}                                              | Column#5                                                                   | N/A    | N/A  |
|       └─HashAgg_34                     | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:91.1ms, loops:1, threads:1}                                              | group by:test.t.id, funcs:max(Column#7)->Column#5                          | N/A    | N/A  |
|         └─ExchangeReceiver_36          | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:90.9ms, loops:7, threads:8}                                              |                                                                            | N/A    | N/A  |
|           └─ExchangeSender_35          | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:90.6ms, loops:256, threads:7}                                            | ExchangeType: HashPartition, Hash Cols: [name: test.t.id, collate: binary] | N/A    | N/A  |
|             └─HashAgg_16               | 10.00     | 17      | mpp[tiflash] |               | tiflash_task:{time:88.4ms, loops:256, threads:1}                                            | group by:Column#11, funcs:max(Column#10)->Column#7                         | N/A    | N/A  |
|               └─Projection_52          | 983040.00 | 3932172 | mpp[tiflash] |               | tiflash_task:{time:63.4ms, loops:64, threads:7}                                             | timestampdiff(YEAR, test.t.birth, 2022-06-06)->Column#10, test.t.id        | N/A    | N/A  |
|                 └─TableFullScan_32     | 983040.00 | 3932172 | mpp[tiflash] | table:t       | tiflash_task:{time:36.9ms, loops:64, threads:7}                                             | keep order:false                                                           | N/A    | N/A  |
+----------------------------------------+-----------+---------+--------------+---------------+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------+--------+------+
10 rows in set (0.10 sec)

4. What is your TiFlash version? (Required)

master

Start by running the provided TiFlash reproduction and compare the two EXPLAIN ANALYZE queries, focusing on the decimal-to-datetime path versus the date path. Done means TIMESTAMPDIFF(YEAR, dbirth, CURDATE()) no longer shows the reported severe slowdown relative to the equivalent date column, with regression coverage added where appropriate.