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

Make "CAST(time_expr AS DATETIME)" compatible with MySQL-5.6 (and the SQL Standard)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • None
    • 10.0.9
    • None
    • None

    Description

      CAST(time_expr AS DATETIME) and CAST(time_expr AS DATE)
      work differently in MariaDB-10.0 and MySQL-5.6.

      MySQL-5.6:

      mysql> SELECT CURRENT_DATE, CAST(TIME'48:10:10' AS DATETIME), CAST(TIME'48:10:10' AS DATE);
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      | CURRENT_DATE | CAST(TIME'48:10:10' AS DATETIME) | CAST(TIME'48:10:10' AS DATE) |
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      | 2013-12-02   | 2013-12-04 00:10:10              | 2013-12-04                   |
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      1 row in set (0.00 sec)

      MariaDB-10.0:

      mysql> SELECT CURRENT_DATE, CAST(TIME'48:10:10' AS DATETIME), CAST(TIME'48:10:10' AS DATE);
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      | CURRENT_DATE | CAST(TIME'48:10:10' AS DATETIME) | CAST(TIME'48:10:10' AS DATE) |
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      | 2013-12-02   | 0000-00-02 00:10:10              | 0000-00-00                   |
      		 
      +--------------+----------------------------------+------------------------------+
      		 
      1 row in set (0.01 sec)

      Starting from the version 5.6, MySQL switched to the SQL Standard
      behaviour when casting TIME to DATETIME, i.e. by adding CURRENT_DATE
      to the time value.

      MySQL-5.6 also uses CURRENT_DATE when casting from TIME to DATE for consistency
      (this is a non-standard extension, the standard disallows CAST from TIME to DATE).

      Pre-5.6 versions of MySQL did not use CURRENT_DATE when doing such casts.
      MariaDB-10.0 still demonstrates the pre-5.6 behaviour.

      Note, Oracle 11g also uses the standard behaviour:

      SQL> SELECT CURRENT_DATE, CAST(TIME'10:10:10' AS TIMESTAMP) FROM DUAL;
      		 
       
      		 
      CURRENT_DATE
      		 
      ------------------
      		 
      CAST(TIME'10:10:10'ASTIMESTAMP)
      		 
      ---------------------------------------------------------------------------
      		 
      02-DEC-13
      		 
      02-DEC-13 10.10.10.000000 AM

      An excerpt from the SQL standard, Section 6.12 <cast specification>:

      <cast specification> ::= CAST <left paren> <cast operand> AS <cast target> <right paren>
      		 
      <cast operand> ::= <value expression> | <null specification> | <empty specification>
      		 
       
      		 
      .. let TD be the data type identified by <data type>
      		 
      .. let SD be the declared type of the <value expression>
      		 
      .. SV is the source value
      		 
      .. TV is the target value
      		 
       
      		 
      17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE...
      		 
      c) If SD is TIME WITHOUT TIME ZONE, then the <primary datetime field>s
      		 
      year, month, and day of TV are set to their respective values in an execution
      		 
      of CURRENT_DATE and the <primary datetime field>s hour, minute, and second
      		 
      of TV are set to their respective values in SV, with implementation-
      		 
      defined rounding or truncation if necessary.

      Attachments

        Activity

          monty Michael Widenius added a comment -

          Personally don't like that one adds current date on cast as it's not obvious and time zones could cause confusion.
          However, I agree that it's good to follow standards.

          So lets do this, but if one starts MariaDB 10.0 with the --old option, then one should get the old behavior

          monty Michael Widenius added a comment - Personally don't like that one adds current date on cast as it's not obvious and time zones could cause confusion. However, I agree that it's good to follow standards. So lets do this, but if one starts MariaDB 10.0 with the --old option, then one should get the old behavior

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.