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

XML

Word

Printable

DROP TABLE IF EXISTS t1,t2;

CREATE TABLE t1 (t0 TIME);

INSERT INTO t1 VALUES ('00:00:00');

SELECT t0 + INTERVAL 1.1 SECOND FROM t1;

CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;

SHOW COLUMNS FROM t2;

+--------------------------+

| t0 + INTERVAL 1.1 SECOND |

+--------------------------+

| 00:00:01                 |

+--------------------------+

+--------------------------+------+------+-----+---------+-------+

| Field                    | Type | Null | Key | Default | Extra |

+--------------------------+------+------+-----+---------+-------+

| t0 + INTERVAL 1.1 SECOND | time | YES  |     | NULL    |       |

+--------------------------+------+------+-----+---------+-------+

The above output does not look correct.
The expected value is '00:00:01.1'.
The expected data type is time(1).

The same problem is observed with a DATETIME column:

DROP TABLE IF EXISTS t1,t2;

CREATE TABLE t1 (t0 DATETIME);

INSERT INTO t1 VALUES ('2001-01-01 00:00:00');

SELECT t0 + INTERVAL 1.1 SECOND FROM t1;

CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1;

SHOW COLUMNS FROM t2;

+--------------------------+

| t0 + INTERVAL 1.1 SECOND |

+--------------------------+

| 2001-01-01 00:00:01      |

+--------------------------+

+--------------------------+----------+------+-----+---------+-------+

| Field                    | Type     | Null | Key | Default | Extra |

+--------------------------+----------+------+-----+---------+-------+

| t0 + INTERVAL 1.1 SECOND | datetime | YES  |     | NULL    |       |

+--------------------------+----------+------+-----+---------+-------+

The same problem is observed with DATE_ADD:

 SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);

+------------------------------------------------------+

| DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND) |

+------------------------------------------------------+

| 2001-01-01 00:00:01                                  |

+------------------------------------------------------+

Note: MySQL-5.6 is not affected.

duplicates

MDEV-4724 Some temporal functions do not preserve microseconds

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