Bug #69705 Strange fractional seconds

• Submitted: 10 Jul 2013 0:04
• Modified: 10 Jul 2013 13:03
• Reporter: John Duncan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[10 Jul 2013 0:04] John Duncan

Description:
This bug refers to how mysqld interprets values that will be stored in TIME columns.

"01:02:03" refers to 1 hour, 2 minutes, and 3 seconds.

"01:02" refers to 1 hour, 2 minutes, and 0 seconds. This is a special case, and is clearly documented. The manual notes that a time with a single colon is interpreted as a hour:minute.

In MySQL 5.6, a time(1) column stores times with precision to a tenth of a second, i.e. with fsp=1.

"01:02:03.4" refers to 1 hour, 2 minutes, and 3.4 seconds.

Here is what I believe is a bug: 
"01:02.4" refers (beyond any rational understanding) to 1 hour, 2 minutes, 0 seconds, and 4 tenths of a second.

How to repeat:
 CREATE TABLE `mysql56times` (
  `id` int(11) NOT NULL,
  `a` time(1) DEFAULT NULL,
  `b` datetime(2) DEFAULT NULL,
  `c` timestamp(3) NULL DEFAULT NULL,
  `d` time(4) DEFAULT NULL,
  `e` datetime(5) DEFAULT NULL,
  `f` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

insert into mysql56times(id,a) values (1, "01:02.4") ;

select * from mysql56times ;

+----+------------+------+------+------+------+------+
| id | a          | b    | c    | d    | e    | f    |
+----+------------+------+------+------+------+------+
|  1 | 01:02:00.4 | NULL | NULL | NULL | NULL | NULL |
+----+------------+------+------+------+------+------+

Suggested fix:
MySQL's interpretation of "01:02.4" is not a reasonable one.  Therefore, if a time string contains one colon *and also* contains a decimal point, the special case behavior should be disabled.  mysqld should interpret "01:02.4" as "1 minute, 2.4 seconds"

[10 Jul 2013 13:03] MySQL Verification Team

Thank you for the bug report. Verified as described:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.13-debug Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  CREATE TABLE `mysql56times` (
    ->   `id` int(11) NOT NULL,
    ->   `a` time(1) DEFAULT NULL,
    ->   `b` datetime(2) DEFAULT NULL,
    ->   `c` timestamp(3) NULL DEFAULT NULL,
    ->   `d` time(4) DEFAULT NULL,
    ->   `e` datetime(5) DEFAULT NULL,
    ->   `f` timestamp(6) NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ;
Query OK, 0 rows affected (0.50 sec)

mysql>
mysql> insert into mysql56times(id,a) values (1, "01:02.4") ;
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> select * from mysql56times ;
+----+------------+------+------+------+------+------+
| id | a          | b    | c    | d    | e    | f    |
+----+------------+------+------+------+------+------+
|  1 | 01:02:00.4 | NULL | NULL | NULL | NULL | NULL |
+----+------------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql>