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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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