Bug #115778 | Cast DATE_ADD/SUB result as double get wrong result. | ||
---|---|---|---|
Submitted: | 6 Aug 6:24 | Modified: | 7 Aug 7:17 |
Reporter: | Shenghui Wu | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 8.4.0, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[6 Aug 6:24]
Shenghui Wu
[6 Aug 6:34]
MySQL Verification Team
Hello Shenghui Wu, Thank you for the report and test case. Verified as described. regards, Umesh
[6 Aug 8:59]
Roy Lyseng
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.
[6 Aug 9:18]
Shenghui Wu
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?
[7 Aug 6:18]
Shenghui Wu
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)
[7 Aug 7:17]
Roy Lyseng
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.