Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4861

TIME/DATETIME arithmetics does not preserve INTERVAL precision

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Duplicate
        • Affects Version/s: 10.0.3, 5.5.32, 5.3.12
        • Fix Version/s: 10.0.5, 5.5.33, 5.3.13
        • Component/s: None
        • Labels:
          None

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                duplicates

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4724 Some temporal functions do not preserve microseconds

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Closed

                  Activity

                  Hide
                  Permalink
                  serg Sergei Golubchik added a comment -

                  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.

                  Show
                  serg Sergei Golubchik added a comment - 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.

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: