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

Inserting a TIME with hour>24 into a DATETIME column produces a wrong value

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.4, 5.5.33, 5.3.13
    • 5.5.38, 10.0.11, 5.3.13
    • None
    • None

    Description

      mysql> drop table if exists t1;
      		 
      mysql> create table t1 (a datetime);
      		 
      mysql> insert into t1 values (time('58:00:00'));
      		 
      mysql> select * from t1;
      		 
      +---------------------+
      		 
      | a                   |
      		 
      +---------------------+
      		 
      | 0000-00-00 58:00:00 |
      		 
      +---------------------+
      		 
      1 row in set (0.00 sec)

      This is a wrong result.
      The expected result is ' 0000-00-02 10:00:00',
      which is what the explicit CAST returns:

      mysql> select cast(time('58:00:00') as datetime);
      		 
      +------------------------------------+
      		 
      | cast(time('58:00:00') as datetime) |
      		 
      +------------------------------------+
      		 
      | 0000-00-02 10:00:00                |
      		 
      +------------------------------------+
      		 
      1 row in set (0.00 sec)

      There is also a difference in 5.3 and 5.5 in how invariants of the same TIME value
      are inserted into a DATE column:

      mysql> drop table if exists t1; create table t1 (a date);  insert into t1 values (time('2 10:00:00')),(time('58:20:30')); select * from t1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      Query OK, 0 rows affected (0.07 sec)
      		 
       
      		 
      Query OK, 2 rows affected, 2 warnings (0.00 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 2
      		 
       
      		 
      +------------+
      		 
      | a          |
      		 
      +------------+
      		 
      | 0000-00-02 |
      		 
      | 0000-00-00 |
      		 
      +------------+
      		 
      2 rows in set (0.00 sec)

      The expected result is to have the same values in the table in the two records.
      MariaDB-10.0 returns '0000-00-00' for both records.
      The expected result should probably be '0000-00-02'.

      Attachments

        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.