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

TIME/DATETIME arithmetics does not preserve INTERVAL precision

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.0.3, 5.5.32, 5.3.12
    • 10.0.5, 5.5.33, 5.3.13
    • None
    • 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.

      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

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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