MySQL Bugs: #115778: Cast DATE_ADD/SUB result as double get wrong result.

Bug #115778	Cast DATE_ADD/SUB result as double get wrong result.
Submitted:	6 Aug 2024 6:24	Modified:	7 Aug 2024 7:17
Reporter:	Shenghui Wu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.4.0, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-add

According to the documentation, when the first argument to DATE_ADD/SUB is of type string, the output is also of type string. 
However, I try to cast the result string as double, the obtained is inconsistent with that obtained by direct cast literal string as double.

How to repeat:
mysql> SELECT DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND);
+----------------------------------------------------+
| DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) |
+----------------------------------------------------+
| 2018-05-16 23:59:59.900000                         |
+----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT cast('2018-05-16 23:59:59.900000' as double);
+----------------------------------------------+
| cast('2018-05-16 23:59:59.900000' as double) |
+----------------------------------------------+
|                                         2018 |
+----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

But: 
mysql> 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) |
+--------------------------------------------------------------------+
|                                                   20180516235959.9 |
+--------------------------------------------------------------------+
1 row in set (0.01 sec)

Hello Shenghui Wu,

Thank you for the report and test case.
Verified as described. 

regards,
Umesh

Posted by developer:
 
This is not a bug.

Documentation for DATE_SUB() tells that

The return value depends on the arguments:
• If date is NULL, the function returns NULL.
• DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts).
• TIME if the date argument is a TIME value and the calculations involve only HOURS, MINUTES, and SECONDS parts (that is, no date parts).
• DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS, or if the first argument is of type TIME and the unit value uses YEAR, MONTH, or DAY.
• If the first argument is a dynamic parameter (for example, of a prepared statement), its resolved type is DATE if the second argument is an interval that contains some combination of YEAR, MONTH, or DAY values only; otherwise, its type is DATETIME.
• String otherwise (type VARCHAR).

In this case, the argument to DATE_SUB() is a valid DATE literal and is converted to DATE, hence the return value is a DATETIME value.

mysql> create table t(a datetime(6));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t select DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select cast(a as signed) from t;
+-------------------+
| cast(a as signed) |
+-------------------+
|    20180517000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT cast(DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) as signed);
+--------------------------------------------------------------------+
| cast(DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) as signed) |
+--------------------------------------------------------------------+
|                                                               2018 |
+--------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '2018-05-16 23:59:59.900000' |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

>>> In this case, the argument to DATE_SUB() is a valid DATE literal and is converted to DATE, hence the return value is a DATETIME value.

If you understand it that way, why when cast DATE_SUB as signed, the return value is a string value?

I test the DATE_SUB function with --column-type-info. It shows the result type is string.

mysql> SELECT DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND);
Field   1:  `DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     116
Max_length: 26
Decimals:   31
Flags:

+----------------------------------------------------+
| DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND) |
+----------------------------------------------------+
| 2018-05-16 23:59:59.900000                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

Posted by developer:
 
We generate string type in more cases than I first thought.
Thus, the CAST into double for different types of expressions may be regarded as inconsistent.
If in doubt, it is always a good idea to use specific temporal literal values,
such as DATE'2018-05-17' instead of a string literal when the resolver otherwise has to guess
what is meant by the expression.