[MDEV-4861] TIME/DATETIME arithmetics does not preserve INTERVAL precision Created: 2013-08-08  Updated: 2013-09-16  Resolved: 2013-09-16

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3, 5.5.32, 5.3.12
Fix Version/s: 10.0.5, 5.5.33, 5.3.13

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

Issue Links:
Duplicate
duplicates MDEV-4724 Some temporal functions do not preser... Closed

 Description    

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.

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

This was intentional. The documented way to add microseconds is to use

+ INTERVAL '1.1' SECOND_MICROSECOND

If desired, we can allow 1.1 SECOND too, like MySQL 5.6 does.

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