The decimal type calculation result is not compatbile

[cfzjywxk]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The sum result of two decimal type is not decimal, it's compatible with MySQL 5.7 but not with TiDB 4.0 and MySQL 8.0. Return the decimal result type seems better.

create table t(c1 int, ld decimal(32,4), rd decimal(32,4));
insert into t values(1, -100.0000, 100.0000);
select sum(ld + rd) + 1 from t;

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

mysql> select sum(ld + rd) + 1 from t;
+------------------+
| sum(ld + rd) + 1 |
+------------------+
|           1.0000 |
+------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

TiDB v3.0.12
mysql> select sum(ld + rd) + 1 from t;
+------------------+
| sum(ld + rd) + 1 |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

MySQL 8.0.20

``mysql> select sum(ld + rd) + 1 from t;
+------------------+
| sum(ld + rd) + 1 |
+------------------+
|           1.0000 |
+------------------+
1 row in set (0.00 sec)


### 4. Affected version (Required)

v3.0.12

### 5. Root Cause Analysis

<!-- should be filled by the investigator before it's closed -->

[mmyj]

v3.0.12 can not reproduce it.

[jebter]

4.0.16

TiDB root@10.2.12.57:test> create table t(c1 int, ld decimal(32,4), rd decimal(32,4));
Query OK, 0 rows affected
Time: 0.076s
TiDB root@10.2.12.57:test> insert into t values(1, -100.0000, 100.0000);
Query OK, 1 row affected
Time: 0.015s
TiDB root@10.2.12.57:test> select sum(ld + rd) + 1 from t;
+------------------+
| sum(ld + rd) + 1 |
+------------------+
| 1.0000           |
+------------------+

1 row in set
Time: 0.017s
TiDB root@10.2.12.57:test> select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v4.0.16
Edition: Community
Git Commit Hash: 75f81d2d7faae72575c4e471ec0b96756796284a
Git Branch: heads/refs/tags/v4.0.16
UTC Build Time: 2021-12-02 04:17:32
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

1 row in set
Time: 0.007s

8.1.0

TiDB root@127.0.0.1:test> create table t(c1 int, ld decimal(32,4), rd decimal(32,4));
Query OK, 0 rows affected
Time: 0.076s
TiDB root@127.0.0.1:test> insert into t values(1, -100.0000, 100.0000);
Query OK, 1 row affected
Time: 0.004s
TiDB root@127.0.0.1:test> select sum(ld + rd) + 1 from t;
+------------------+
| sum(ld + rd) + 1 |
+------------------+
| 1.0000           |
+------------------+

1 row in set
Time: 0.005s
TiDB root@127.0.0.1:test> select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:52:40
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

1 row in set
Time: 0.001s

Please try the latest version. I will close it, if there are any updates, you can reopen it.