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:
None 
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:05] Alexander Barkov
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.
[18 Sep 2018 10:27] Umesh Shastry
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.