| Bug #92475 | CAST(AS DATE) truncates microseconds but rounds nanoseconds | ||
|---|---|---|---|
| Submitted: | 18 Sep 2018 10:05 | Modified: | 18 Sep 2018 16:07 |
| Reporter: | Alexander Barkov | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7, 5.7.23, 8.0.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Sep 2018 10:27]
MySQL Verification Team
Hello Alexander, Thank you for the report and test case. regards, Umesh
[18 Sep 2018 12:22]
Roy Lyseng
This is not a bug, it is a natural consequence of rounding excess fractional values. In the case with more than 6 fractional digits, the fraction is first rounded to 6 digits, which causes a wraparound to the next second (and day). After that, the time component is truncated from the datetime entry and we end up with the next day.
[18 Sep 2018 12:43]
Alexander Barkov
Roy, thanks for your comments.
Right, this is caused by the fact that the string literal is first converted to DATETIME, and then to DATE. But these results look surprising.
It could take into account truncation/rounding modes inside str_to_datetime(),
to bring consistent results.
Note, other databases return '2001-01-01' for this query:
SELECT CAST('2001-01-01 23:59:59.9999999' AS DATE);
(checked with PostgreSQL and SQL Server).
Also, suppose at some point MySQL will support more than 6 fractional digits.
The result of this query will change to '2001-01-01' too.
[18 Sep 2018 12:45]
Alexander Barkov
This is confusing that conversion of type A to type B depends on internal implementation of type C. (with A=VARCHAR, B=DATE, C=DATETIME).
[18 Sep 2018 14:05]
Roy Lyseng
Hi Alexander, i'd wish the default mode for fractional datetime parts was truncation, then the behaviour would be more consistent. It could be a mistake to allow conversion from string or integer/decimal value, via datetime, to date. But as long as the two conversions are well defined, it is a fairly logical path. From string to date is fairly simple, we can just strip the time part. But integer/decimal to date is worse: If the integer/decimal is a date value (99991231), it can be transformed directly to a date. If the integer/decimal is a datetime value (99991231235959.999...) it must first be converted to a datetime before it can be converted to a date.
[18 Sep 2018 16:07]
Alexander Barkov
Hi Roy, Right, conversion of a number like 20010101235959.9999999 (with 7 fractional digits) to DATE has to go through DATETIME representation first. However, it should not be hard to convert 20010101235959.9999999 to a DATETIME value of '2001-01-01 23:59:59.999999' (without rounding to the next date) and then truncate it to a DATE value of '2001-01-01'. Should be possibly by passing TIME_FRAC_TRUNCATE down to lldiv_t_to_datetime() from Item_date_typecast::get_date(), independently of the current @@sql_mode settings. This corner case was probably overlooked in the original implementation when fractional seconds were added.
Description: SELECT CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATE), CAST('2001-01-01 23:59:59.9999' AS DATE), CAST(20010101235959.9999 AS DATE), CAST('2001-01-01 23:59:59.9999999' AS DATE), CAST(20010101235959.9999999 AS DATE)\G CAST(TIMESTAMP'2001-01-01 23:59:59.9999' AS DATE): 2001-01-01 CAST('2001-01-01 23:59:59.9999' AS DATE): 2001-01-01 CAST(20010101235959.9999 AS DATE): 2001-01-01 CAST('2001-01-01 23:59:59.9999999' AS DATE): 2001-01-02 CAST(20010101235959.9999999 AS DATE): 2001-01-02 Notice, CAST(AS DATE) usually truncates the hh:mm:ss.ff part. However, if I add the 7th fractional digit into a string or a decimal literal, it rounds to the next day. How to repeat: Run the above query Suggested fix: Please fix to do truncation consistently, so the last two columns return '2001-01-01' like other ones do.