Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.4
-
Fix Version/s: 10.0.5
-
Component/s: None
-
Labels:None
Description
I haven't found this one among filed bugs, maybe it was just put in a different way; please feel free to close as a duplicate if so
There was a change in MySQL 5.6.4:
TIME values are converted to DATETIME by adding the time to the current date. (This means that the date part of the result differs from the current date if the time value is outside the range from '00:00:00' to '23:59:59'.) Previously, conversion of TIME values to DATETIME was unreliable.
It works in MySQL 5.6, but not in MariaDB 10.0.4 candidate:
MySQL [test]> select cast(current_time() as datetime); +----------------------------------+ | cast(current_time() as datetime) | +----------------------------------+ | 2013-08-10 14:19:15 | +----------------------------------+ 1 row in set (0.00 sec) MySQL [test]> select @@version; +-----------+ | @@version | +-----------+ | 5.6.10 | +-----------+
MariaDB [test]> select cast(current_time() as datetime); +----------------------------------+ | cast(current_time() as datetime) | +----------------------------------+ | 0000-00-00 14:19:24 | +----------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> select @@version; +----------------------+ | @@version | +----------------------+ | 10.0.3-MariaDB-debug | +----------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Okay, but then I think we need a proper explanation why it was chosen this way, because people will be asking, and a deviation without a reason doesn't look good. I haven't found it in KB in types section, but maybe it's somewhere else?
IIRC, the SQL standard does not have implicit conversion from TIME to TIMESTAMP.
So the SQL standard part that suites best "conversion of TIME to DATETIME"
would be how "CAST( time_expression AS TIMESTAMP)" works.
"SQL:2011 Part 2 Foundation, section <cast specification>" says the following about cast from TIME to TIMESTAMP:
> The fields year, month, and day of TV (the target value) are set to their
> respective values in an execution of CURRENT_DATE and
> the fields hour, minute, and second of TV are set to their respective values in SV (the source value),
> with implementation-defined rounding or truncation if necessary.
I'd vote to apply the same rules to for CAST and for implicit conversion,
like MySQL-5.6 does.
Serg, what was the reason not to use these rules in MariaDB?
Simply because we found it so weird and counter-intuitive that we intentionally diverged from the standard here. The notion that a result of a type cast depends not only on the original value and the desired type, but also on the current date? And produces non-repeatable results? That's a serious gotcha. I believe our approach is more logical, even if non-standard.
The way it's implemented now is make it depend on sql_mode,
not ONLY on the original value. In sql_mode=no_zero_date it always
returns NULL. I think the Standard implementation is more useful.
----------------------------------
| cast(time'10:20:30' as datetime) |
----------------------------------
| 0000-00-00 10:20:30 |
----------------------------------
1 row in set (0.05 sec)
MariaDB [test]> set sql_mode=no_zero_date;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select cast(time'10:20:30' as datetime);
----------------------------------
| cast(time'10:20:30' as datetime) |
----------------------------------
| NULL |
----------------------------------
1 row in set, 1 warning (0.00 sec)
It was intentionally implemented deviation from MySQL behavior