[MDEV-4724] Some temporal functions do not preserve microseconds - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.0.4, 5.5.31, 5.3.12
Fix Version/s: 10.0.5, 5.5.33
Component/s: None
Labels:
None

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).

Attachments

Issue Links

blocks

MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10')

Closed

duplicates

MDEV-4862 Wrong result of MAKETIME(0, 0, 59.9)

Closed

is blocked by

MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

Closed

is duplicated by

MDEV-3922 UNIX_TIMESTAMP() reports decimal when date specified as string

Closed

MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2013-06-27 18:48

Alexander Barkov made changes - 2013-06-27 18:49

Field	Original Value	New Value
Assignee		Alexander Barkov [ bar ]

Alexander Barkov made changes - 2013-06-28 09:01

Summary

MAKETIME does not preserve microseconds

Some temporal functions do not preserve microseconds

Alexander Barkov made changes - 2013-06-28 09:01

Description

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

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

It works as expected in MySQL-5.6:

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

More examples:
{code:sql}
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)
{code}
The expected column type is time(0).

Sergei Golubchik made changes - 2013-08-09 00:52

Link

This issue duplicates ~~MDEV-4862~~ [ ~~MDEV-4862~~ ]

Sergei Golubchik made changes - 2013-08-09 00:53

Fix Version/s		10.0.5 [ 13201 ]
Fix Version/s		5.5.33 [ 13300 ]

Alexander Barkov made changes - 2013-08-09 07:58

Description

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

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

It works as expected in MySQL-5.6:

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

{code}
MariaDB [test]> SELECT MAKETIME(0, 0, 59.9);
+----------------------+
| MAKETIME(0, 0, 59.9) |
+----------------------+
| NULL |
+----------------------+
{code}
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:
{code:sql}
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)
{code}
The expected column type is time(0).

Alexander Barkov made changes - 2013-08-09 07:59

Description

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

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

It works as expected in MySQL-5.6:

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

{code}
MariaDB [test]> SELECT MAKETIME(0, 0, 59.9);
+----------------------+
| MAKETIME(0, 0, 59.9) |
+----------------------+
| NULL |
+----------------------+
{code}
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:
{code:sql}
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)
{code}
The expected column type is time(0).

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

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

It works as expected in MySQL-5.6:

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

A possibly related problem:
{code}
MariaDB [test]> SELECT MAKETIME(0, 0, 59.9);
+----------------------+
| MAKETIME(0, 0, 59.9) |
+----------------------+
| NULL |
+----------------------+
{code}
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:
{code:sql}
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)
{code}
The expected column type is time(0).

Sergei Golubchik added a comment - 2013-08-09 12:11

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~~

Sergei Golubchik added a comment - 2013-08-09 12:11 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

Sergei Golubchik made changes - 2013-08-09 12:12

Link

This issue is duplicated by ~~MDEV-3922~~ [ ~~MDEV-3922~~ ]

Sergei Golubchik made changes - 2013-08-30 12:09

Link

This issue blocks ~~MDEV-4843~~ [ ~~MDEV-4843~~ ]

Sergei Golubchik made changes - 2013-08-30 12:09

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik made changes - 2013-08-30 12:12

Link

This issue is blocked by ~~MDEV-4863~~ [ ~~MDEV-4863~~ ]

Sergei Golubchik made changes - 2013-09-12 13:25

Priority

Major [ 3 ]

Minor [ 4 ]

Alexander Barkov added a comment - 2013-09-13 15:58

Fixed in 5.5.31, 5.3.12

Alexander Barkov added a comment - 2013-09-13 15:58 Fixed in 5.5.31, 5.3.12

Alexander Barkov made changes - 2013-09-13 15:58

Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2013-09-16 13:09

Link

This issue is duplicated by ~~MDEV-4861~~ [ ~~MDEV-4861~~ ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 27768 ]

MariaDB v2 [ 44612 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 44612 ]

MariaDB v3 [ 61757 ]

Sergei Golubchik made changes - 2021-12-06 21:38

Workflow

MariaDB v3 [ 61757 ]

MariaDB v4 [ 146818 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2013-06-27 18:48

Updated:: 2013-09-16 13:09

Resolved:: 2013-09-13 15:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration