feature: Support field function substring optimization

[haitaoguan]

Describe the problem

When you use a function on a field, performance is affected.The trace.log displays the message "Unoptimized expression near 'substr'".

###Abnormal
mysql> select sum(ce) from all_bak where year(xxxx)=2020;
+------------------+
| sum(ce)          |
+------------------+
| 1332849867555.84 |
+------------------+
1 row in set (4.67 sec)

###Normal
mysql> select sum(ce) from all_bak where xxxx between '2020-01-01' and '2020-12-31';
+------------------+
| sum(ce)          |
+------------------+
| 1332849867555.84 |
+------------------+
1 row in set (0.00 sec)

###Abnormal
mysql> select substring(a.h_m,1,4) hrd,sum(ce) from all_bak a group by substring(a.h_m,1,4);
+------+-----------------+
| hrd | sum(ce)         |
+------+-----------------+
| 7499 |  63816644743.20 |
| 7040 |  47191046107.84 |
| 8341 | 191566748484.48 |
| 7299 | 112803464512.32 |
| 8391 |  73260665137.28 |
| 6129 | 205524450576.96 |
| 7289 | 318319076069.28 |
| 7511 | 320367771924.48 |
+------+-----------------+
8 rows in set (22.44 sec)

###Normal
mysql> select h_m,sum(ce) from all_bak a group by h_m;
+-------+-----------------+
| h_m | sum(ce)         |
+-------+-----------------+
| 7499  |  63816644743.20 |
| 7040  |  47191046107.84 |
| 8341  | 191566748484.48 |
| 7299  | 112803464512.32 |
| 8391  |  73260665137.28 |
| 6129  | 205524450576.96 |
| 7289  | 318319076069.28 |
| 7511  | 320367771924.48 |
+-------+-----------------+
8 rows in set (5.45 sec)

Expected behavior

No response

How To Reproduce

No response

Environment

./mysqld --version

./mysqld Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
Repository address: git@github.com:stoneatom/stonedb.git:stonedb-5.7
Branch name: stonedb-5.7
Last commit ID: 05eb397
Last commit time: Date: Mon Aug 15 14:05:47 2022 +0800
Build time: Date: 2022年 08月 15日 星期一 15:38:53 CST

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[adofsauron]

What is the table structure? What is the statement for the amount of data inserted? What is the amount of data

[isredstar]

So far，Tianmu doesn‘t support optimize function expression yet.

[adofsauron]

ACK