MariaDB Development
  1. MariaDB Development
  2. MDEV-4861

TIME/DATETIME arithmetics does not preserve INTERVAL precision

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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
    • Labels:
      None
    • Global Rank:
      3426

      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.

        Issue Links

          Activity

          Hide
          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
          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:
              Alexander Barkov
              Reporter:
              Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: