[MDEV-4724] Some temporal functions do not preserve microseconds Created: 2013-06-27  Updated: 2013-09-16  Resolved: 2013-09-13

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.31, 5.3.12
Fix Version/s: 10.0.5, 5.5.33

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-4843 Wrong data type for TIMESTAMP('2001-0... Closed
is blocked by MDEV-4863 COALESCE(time_or_datetime) returns wr... Closed
Duplicate
duplicates MDEV-4862 Wrong result of MAKETIME(0, 0, 59.9) Closed
is duplicated by MDEV-3922 UNIX_TIMESTAMP() reports decimal when... Closed
is duplicated by MDEV-4861 TIME/DATETIME arithmetics does not pr... Closed

 Description   

The function MAKETIME() in MariaDB does not preserve fractional digits:

MariaDB [test]> SELECT MAKETIME(10,10,10.231);
 
+------------------------+
 
| MAKETIME(10,10,10.231) |
 
+------------------------+
 
| 10:10:10               |
 
+------------------------+
 
1 row in set (0.00 sec)

It works as expected in MySQL-5.6:

MySQL [test]> SELECT MAKETIME(10,10,10.231);
 
+------------------------+
 
| MAKETIME(10,10,10.231) |
 
+------------------------+
 
| 10:10:10.231           |
 
+------------------------+
 
1 row in set (0.00 sec)

A possibly related problem:

MariaDB [test]> SELECT MAKETIME(0, 0, 59.9);
 
+----------------------+
 
| MAKETIME(0, 0, 59.9) |
 
+----------------------+
 
| NULL                 |
 
+----------------------+

The above result is wrong.
It seems 59.9 is rounded to 60, which makes a wrong time '00:00:60', hence NULL.
The expected result is '00:00:59.9'.

More examples:

MariaDB [test]> drop table if exists t1; create table t1 as select time('10:00:00'); show create table t1;
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 1 row affected (0.10 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
+-------+------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                         |
 
+-------+------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `time('10:00:00')` time(6) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
+-------+------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

The expected column type is time(0).

 Comments   
Comment by Sergei Golubchik [ 2013-08-09 ]

for maketime it was kind of intentional. it used to take three integers, and it still does. feel free to change it, if you'd like.

the second issue is a duplicate of MDEV-3922

Comment by Alexander Barkov [ 2013-09-13 ]

Fixed in 5.5.31, 5.3.12

Generated at Thu Feb 08 06:58:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.