bug: StoneDB does not support substring function or year function optimization.

Open haitaoguan opened this issue 3 years ago • 2 comments

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(se) from jrk_all_bak where year(rkrq)=2020;
+------------------+
| sum(se)          |
+------------------+
| 1332849867555.84 |
+------------------+
1 row in set (4.67 sec)

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

###Abnormal
mysql> select substring(a.hy_dm,1,4) hydl,sum(se) from jrk_all_bak a group by substring(a.hy_dm,1,4);
+------+-----------------+
| hydl | sum(se)         |
+------+-----------------+
| 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 hy_dm,sum(se) from jrk_all_bak a group by hy_dm;
+-------+-----------------+
| hy_dm | sum(se)         |
+-------+-----------------+
| 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: [email protected]: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