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

Inconsistent between implicit and explicit conversion from DATETIME to TIME

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 5.3.12, 5.5.35, 10.0.8
    • 5.5
    • None
    • None

    Description

      There is a difference between explicit and implicit DATETIME->TIME conversion when using a datetime with zero YYYYMM and a non-zero day:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES (TIMESTAMP'0000-00-01 10:20:30');
      SELECT  * FROM t1;
      

      +----------+
      | a        |
      +----------+
      | 34:20:30 |
      +----------+
      

      SELECT CAST(TIMESTAMP'0000-00-01 10:20:30' AS TIME);
      

      +----------------------------------------------+
      | CAST(TIMESTAMP'0000-00-01 10:20:30' AS TIME) |
      +----------------------------------------------+
      | 10:20:30                                     |
      +----------------------------------------------+
      

      Notice, implicit conversion adds days to hours, while explicit does not.

      The problem also presents in the upstream.

      The same problem is repeatable when casting a DATETIME column to TIME.

      CREATE OR REPLACE TABLE t1 (a DATETIME);
      INSERT INTO t1 VALUES ('0000-00-01 10:20:30');
      SELECT CAST(a AS TIME) FROM t1;
      

      +-----------------+
      | CAST(a AS TIME) |
      +-----------------+
      | 10:20:30        |
      +-----------------+
      

      Note, if I now ALTER the DATETIME column to TIME, it does mix days to hours:

      ALTER TABLE t1 MODIFY a TIME;
      SELECT * FROM t1;
      

      +----------+
      | a        |
      +----------+
      | 34:20:30 |
      +----------+
      

      This is inconsistent. Conversion should work the same way in all cases:

      • Explicit CAST
      • Implicit conversion on INSERT and SET
      • Explicit conversion on ALTER.

      In 10.3 (as of 2018-02-07) implicit conversion from DATETIME to TIME at INSERT time and explicit conversion at ALTER time work as follows:

      • if the YYYYMM part is zero, then the data represents a "INTERVAL DAY TO SECOND"-alike value, then mix days to hours
      • If the YYYYMM part is not zero, then the data represents a real DATETIME value, then truncate YYYYMMDD and preserve the hhmmss part only.

      There are two options:
      1. Make CAST work like INSERT/ALTER (mix days to hours if YYYYMM is zero)
      2. Make INSERT/ALTER work like CAST (truncate the YYYYMMDD part)

      Sergei is included towards #2.
      Bar would also prefer #2, but is afraid that some users can have upgrade problems.

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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