Inconsistent Results Between MySQL and TiDB in Date Arithmetic Query

[awusan125]

Bug Report

I execute the same statement in tidb and mysql. There may be bugs in there

1. Minimal reproduce step (Required)

drop table if exists t;
create table t (c1 double);
insert into t values (1.01);

SELECT f1 FROM t AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON true; 

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

It returns the following result in mysql:

+-------------------+
| f1                |
+-------------------+
| 20180516235960.91 |
+-------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

It returns the following result in tidb:

+---------+
| f1      |
+---------+
| 2019.01 |
+---------+
1 row in set, 1 warning (0.00 sec)

4. What is your TiDB version? (Required)

+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.01 sec)

[XuHuaiyu]

The behavior of cast(date as double) is unexpected.

tidb:4000 [test]>  SELECT cast(DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) as double);
+--------------------------------------------------------------------+
| cast(DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) as double) |
+--------------------------------------------------------------------+
|                                                               2018 |
+--------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

tidb:4000 [test]> show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2018-05-16 23:59:59.900000' |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

[wshwsh12]

Step 1. The documentation (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add) shows that when the first argument of DATE_ADD/SUB is type string, the return value should be type string. So DATE_SUB('2018-05-17' (type string), INTERVAL 1 DAY_MICROSECOND) -> '2018-05-16 23:59:59.900000' (type string) Step 2. Cast string as double and truncated. Cast('2018-05-16 23:59:59.900000' (type string) as double) -> 2018 (type double)

TiDB DATE_ADD/SUB's behaviour is fully compliant with the mysql documentation.

Ref from mysql doc:

To ensure that the result is DATETIME, you can use CAST() to convert the first argument to DATETIME.

If you want to get the same results as mysql, it is recommended to add the appropriate cast to the first parameter as described in the documentation.

tidb> SELECT f1 FROM t AS t1 JOIN (SELECT (c1+DATE_SUB(cast('2018-05-17' as datetime), INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON true;
+-------------------+
| f1                |
+-------------------+
| 20180516235960.91 |
+-------------------+
1 row in set (0.01 sec)

[wshwsh12]

https://bugs.mysql.com/bug.php?id=115778 Mysql bug verified, close this issue now.