the decimal part of FROM_UNIXTIME is different from MySQL

[SunRunAway]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

select from_unixtime(14516064000/10);
select from_unixtime('14516064000'/10);
select from_unixtime(cast(1451606400 as double));

2. What did you expect to see?

mysql> select from_unixtime(14516064000/10);
+-------------------------------+
| from_unixtime(14516064000/10) |
+-------------------------------+
| 2016-01-01 08:00:00.0000      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('14516064000'/10);
+---------------------------------+
| from_unixtime('14516064000'/10) |
+---------------------------------+
| 2016-01-01 08:00:00.000000      |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(cast(1451606400 as double));
+-------------------------------------------+
| from_unixtime(cast(1451606400 as double)) |
+-------------------------------------------+
| 2016-01-01 08:00:00.000000                |
+-------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> select from_unixtime(14516064000/10);
+-------------------------------+
| from_unixtime(14516064000/10) |
+-------------------------------+
| 2016-01-01 08:00:00.000000    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('14516064000'/10);
+---------------------------------+
| from_unixtime('14516064000'/10) |
+---------------------------------+
| 2016-01-01 08:00:00             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(cast(1451606400 as double));
+-------------------------------------------+
| from_unixtime(cast(1451606400 as double)) |
+-------------------------------------------+
| 2016-01-01 08:00:00                       |
+-------------------------------------------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

98f3420 @ Mon Feb 24 16:28:07 2020 +0800

[ghost]

Confirming this has been fixed in master. Here is a better testcase, since the result is timezone dependent:

set time_zone='+10:00';
select from_unixtime(14516064000/10);
select from_unixtime('14516064000'/10);
select from_unixtime(cast(1451606400 as double));

set time_zone='-8:00';
select from_unixtime(14516064000/10);
select from_unixtime('14516064000'/10);
select from_unixtime(cast(1451606400 as double));

..


mysql> set time_zone='+10:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select from_unixtime(14516064000/10);
+-------------------------------+
| from_unixtime(14516064000/10) |
+-------------------------------+
| 2016-01-01 10:00:00.0000      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('14516064000'/10);
+---------------------------------+
| from_unixtime('14516064000'/10) |
+---------------------------------+
| 2016-01-01 10:00:00.000000      |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(cast(1451606400 as double));
+-------------------------------------------+
| from_unixtime(cast(1451606400 as double)) |
+-------------------------------------------+
| 2016-01-01 10:00:00.000000                |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> set time_zone='-8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select from_unixtime(14516064000/10);
+-------------------------------+
| from_unixtime(14516064000/10) |
+-------------------------------+
| 2015-12-31 16:00:00.0000      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime('14516064000'/10);
+---------------------------------+
| from_unixtime('14516064000'/10) |
+---------------------------------+
| 2015-12-31 16:00:00.000000      |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(cast(1451606400 as double));
+-------------------------------------------+
| from_unixtime(cast(1451606400 as double)) |
+-------------------------------------------+
| 2015-12-31 16:00:00.000000                |
+-------------------------------------------+
1 row in set (0.01 sec)


mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-944-gae779e150
Edition: Community
Git Commit Hash: ae779e1502cf99d65511387a4e1eb171bd9eb9c7
Git Branch: master
UTC Build Time: 2020-08-11 03:55:14
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)

I can confirm that TiDB / MySQL 8.0 produce the same result.