| Bug #92474 | STR_TO_DATE truncates bad microseconds unexpectedly | ||
|---|---|---|---|
| Submitted: | 18 Sep 2018 9:54 | Modified: | 19 Sep 2018 5:37 |
| Reporter: | Alexander Barkov | Email Updates: | |
| Status: | Verified | 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:23]
MySQL Verification Team
Hello Alexander, Thank you for the report and test case. regards, Umesh
[18 Sep 2018 12:10]
Roy Lyseng
Hi Alexander, a fractional decimal value can never be out of range. In this case there is excess precision that should be rounded or truncated to a microsecond value according to the SQL mode time_truncate_fractional.
[18 Sep 2018 12:17]
Alexander Barkov
The manual says that %f stands for Microseconds (000000..999999). It does not say it stands for the arbitrary length fractional part.
[18 Sep 2018 13:19]
Roy Lyseng
Strictly the manual is correct, as the format specifier indicates a microsecond value in the formatted value. It does not say explicitly what to do if the input value has more than 6 fractional digits, however it does follow the general pattern of truncation and rounding. We can be more explicit about it in the documentation, though.
[19 Sep 2018 5:33]
Alexander Barkov
Roy,
SELECT STR_TO_DATE('2017-12-31 23:59:59 and 10 microseconds', '%Y-%m-%d %H:%i:%s and %f microseconds') AS c;
+----------------------------+
| c |
+----------------------------+
| 2017-12-31 23:59:59.100000 |
+----------------------------+
A microsecond value of 10 would generate fractional part .000010 (instead of .100000).
So %f is not "microseconds" in STR_TO_DATE().
It's a fractioal part with an arbitrary length.
Btw, is there a way to scan microseconds,
so "10" in the format string gives ".000010"?
[19 Sep 2018 5:37]
Alexander Barkov
Hi Roy,
In case if %f is a fractional part with an arbitrary length
(which would be nice to say explicitly in the manual),
should not this expression honor sql_mode:
STR_TO_DATE('2017-12-31 23:59:59.99999999', '%Y-%m-%d %H:%i:%s.%f')
and return
'2018-01-01 00:00:00.000000'
instead of
'2017-12-31 23:59:59.999999'
like it does in all other cases of string-to-datetime conversion?
[19 Sep 2018 7:11]
Roy Lyseng
So %f is not "microseconds" in STR_TO_DATE().
It's a fractional part with an arbitrary length.
Correct.
Btw, is there a way to scan microseconds,
so "10" in the format string gives ".000010"?
Not that I am aware of.
In case if %f is a fractional part with an arbitrary length
(which would be nice to say explicitly in the manual),
should not this expression honor sql_mode:
STR_TO_DATE('2017-12-31 23:59:59.99999999', '%Y-%m-%d %H:%i:%s.%f')
and return
'2018-01-01 00:00:00.000000'
instead of
'2017-12-31 23:59:59.999999'
like it does in all other cases of string-to-datetime conversion?
I agree that the fraction should be rounded or truncated according to current SQL mode. In this case, the fraction should be rounded to 1.000000 seconds and used to increment the seconds value (etc).
Description: SELECT STR_TO_DATE('99999-12-31 23:59:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_year, STR_TO_DATE('2017-13-31 23:59:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_month, STR_TO_DATE('2017-12-32 23:59:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_day, STR_TO_DATE('2017-12-31 24:59:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_hour, STR_TO_DATE('2017-12-31 23:60:59.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_minute, STR_TO_DATE('2017-12-31 23:59:60.9999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_second, STR_TO_DATE('2017-12-31 23:59:59.99999999', '%Y-%m-%d %H:%i:%s.%f') AS bad_microsecond\G bad_year: NULL bad_month: NULL bad_day: NULL bad_hour: NULL bad_minute: NULL bad_second: NULL bad_microsecond: 2017-12-31 23:59:59.999999 Notice, a bad value in all fields (but microsecond) makes STR_TO_DATE() return NULL. For some reasons, microsecond is an exception. A bad value in microsecond is truncated to the left 6 digits. The relevant manual articles: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format The manual says %f stands for microseconds in the range 0..999999. Nothing in the manual tells that the fractional part is truncated to the left 6 digits. Note, even if this is an expected behavior, then *truncation* is questionable. Shouldn't it do rounding, for consistency with all other functions? How to repeat: Run the above query. Suggested fix: - Either fix to return NULL if microseconds value is out of the documented range - Or fix to round rather than truncate