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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.33, 5.3.13
    • Fix Version/s: 5.5.38, 10.0.11, 5.3.13
    • Component/s: None
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: