Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
None
-
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.
|