Some behaviors about decimal calculation are different from MySQL 5.7

[hanfei1991]

I inserted a max decimal value into tidb and mysql;

mysql> create table t1 (d decimal(65, 0));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(99999999999999999999999999999999999999999999999999999999999999999);
Query OK, 1 row affected (0.01 sec)

If we execute SQL like mysql> select d * 100000000 from t1;
The result is 9999999999999999999999999999999999999999999999999999999999999999900000000 without overflow exception.

Well, that is strange , but it still keep same behaviours with MYSQL.

If we execute mysql> select (d * 100000000) / 1 from t1;

the result of MYSQL is still 9999999999999999999999999999999999999999999999999999999999999999900000000
but TiDB report ERROR 1265 (01000): Data Truncated.

[hanfei1991]

And there is a correctness problem in both TiDB and MySQL:

mysql> select d + 0.1 from t1;
+---------------------------------------------------------------------+
| d + 0.1                                                             |
+---------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999.1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (d + 0.1) * 10000000 from t1;
+----------------------------------------------------------------------------+
| (d + 0.1) * 10000000                                                       |
+----------------------------------------------------------------------------+
| 999999999999999999999999999999999999999999999999999999999999999990000000.0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

For the 2nd query , the correct answer should be 999999999999999999999999999999999999999999999999999999999999999991000000.0

It drives me crazy ...

[erjiaqing]

In mysql document:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Also, here is a issue in mysql: https://bugs.mysql.com/bug.php?id=34692

[ghost]

Including a pastable testcase with the results of MySQL 5.7 and TiDB. It looks like TiDB is compatible with MySQL now, with the exception of one additional warning being generated.

drop table if exists t1;
create table t1 (d decimal(65, 0));
insert into t1 values(99999999999999999999999999999999999999999999999999999999999999999);

select d * 100000000 from t1;
select (d * 100000000) / 1 from t1;
select d + 0.1 from t1;
select (d + 0.1) * 10000000 from t1;

MySQL 5.7:

..
mysql [localhost:5731] {msandbox} (test) > insert into t1 values(99999999999999999999999999999999999999999999999999999999999999999);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > 
mysql [localhost:5731] {msandbox} (test) > select d * 100000000 from t1;
+---------------------------------------------------------------------------+
| d * 100000000                                                             |
+---------------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999999999999999999999999900000000 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > select (d * 100000000) / 1 from t1;
+---------------------------------------------------------------------------+
| (d * 100000000) / 1                                                       |
+---------------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999999999999999999999999900000000 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > select d + 0.1 from t1;
+---------------------------------------------------------------------+
| d + 0.1                                                             |
+---------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999.1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > select (d + 0.1) * 10000000 from t1;
+----------------------------------------------------------------------------+
| (d + 0.1) * 10000000                                                       |
+----------------------------------------------------------------------------+
| 999999999999999999999999999999999999999999999999999999999999999990000000.0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

TiDB:

..
mysql> insert into t1 values(99999999999999999999999999999999999999999999999999999999999999999);
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> select d * 100000000 from t1;
+---------------------------------------------------------------------------+
| d * 100000000                                                             |
+---------------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999999999999999999999999900000000 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (d * 100000000) / 1 from t1;
+---------------------------------------------------------------------------+
| (d * 100000000) / 1                                                       |
+---------------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999999999999999999999999900000000 |
+---------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select d + 0.1 from t1;
+---------------------------------------------------------------------+
| d + 0.1                                                             |
+---------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999.1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (d + 0.1) * 10000000 from t1;
+----------------------------------------------------------------------------+
| (d + 0.1) * 10000000                                                       |
+----------------------------------------------------------------------------+
| 999999999999999999999999999999999999999999999999999999999999999990000000.0 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-893-g4e829aaee
Edition: Community
Git Commit Hash: 4e829aaee7b656aa807814708ae05af5233302af
Git Branch: master
UTC Build Time: 2020-08-04 12:40:52
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)