MySQL Bugs: #68061: Temporal functions truncate instead of round

Bug #68061	Temporal functions truncate instead of round
Submitted:	9 Jan 2013 20:42	Modified:	22 Feb 2013 4:21
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.6.8-rc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.5, certain temporal functions employed rounding when dealing with data exceeding bounds of the data type involved:

mysql> SELECT SEC_TO_TIME(2378.3422349);
+---------------------------+
| SEC_TO_TIME(2378.3422349) |
+---------------------------+
| 00:39:38                  |
+---------------------------+
1 row in set (0.06 sec)

mysql> SELECT SEC_TO_TIME(2378.6422349);
+---------------------------+
| SEC_TO_TIME(2378.6422349) |
+---------------------------+
| 00:39:39                  |
+---------------------------+
1 row in set (0.00 sec)

That is not the case with fractional seconds in 5.6, which instead simply truncates the digits beyond precision allowed:

mysql> SELECT SEC_TO_TIME(2378.6422349);
+---------------------------+
| SEC_TO_TIME(2378.6422349) |
+---------------------------+
| 00:39:38.642234           |
+---------------------------+
1 row in set (0.00 sec)

This may be intentional, but the manual is unclear on this topic:

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range. 

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_sec-to-time

There is, however, no warning related to loss of precision.

In some cases, truncation (and not rounding) seems entirely appropriate:

mysql> SELECT NOW(), NOW(3), NOW(4);
+---------------------+-------------------------+--------------------------+
| NOW()               | NOW(3)                  | NOW(4)                   |
+---------------------+-------------------------+--------------------------+
| 2013-01-09 12:37:11 | 2013-01-09 12:37:11.640 | 2013-01-09 12:37:11.6406 |
+---------------------+-------------------------+--------------------------+
1 row in set (0.00 sec)

How to repeat:
See above.

Suggested fix:
Clarify when data is truncated and when it is rounded.

Shouldn't this be categorized as a server bug so that a developer can look at it and determine whether it's intentional?

Built-in SQL function SEC_TO_TIME does not handle fractional
seconds correctly. If there are more than six decimals, 
result is truncated instead of rounded.

Solution: In function sec_to_time(), do rounding instead of truncation.

Noted in 5.6.11, 5.7.1 changelogs.

For arguments with fractional seconds greater than six decimals,
SEC_TO_TIME() truncated, rather than rounding as it should have.