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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.9
    • Component/s: None
    • Labels:
      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

            People

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

              Dates

              • Created:
                Updated:
                Resolved: