| Bug #43774 | CAST(CAST(value) as TIME as DATETIME ) returns the wrong value. | ||
|---|---|---|---|
| Submitted: | 21 Mar 0:26 | Modified: | 23 Mar 8:22 |
| Reporter: | Justin Swanhart | ||
| Status: | Verified | ||
| Category: | Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1.30, 4.1, 5.0, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | Target Version: | ||
| Tags: | cast, wrong result, time, datetime | ||
| Triage: | Triaged: D2 (Serious) | ||
[21 Mar 0:57]
Justin Swanhart
Updating the summary to reflect that double cast is being done.
I can understand why CAST('HH:MM:SS' as datetime) returns a bad date, since MySQL will
(in my opinion wrongly) try to extract MM-DD-YY format from the input string.
However, when an explicit CAST to TIME is given, even for column references, MySQL
returns the wrong result.
CREATE TABLE t1 (f1 time, f2 time);
insert into t1 values ('09:00:00','12:00:00');
mysql> select f1, f2, cast(f1 as datetime), cast(cast(f1 as TIME) as datetime) from t1;
+----------+----------+----------------------+------------------------------------+
| f1 | f2 | cast(f1 as datetime) | cast(cast(f1 as TIME) as datetime) |
+----------+----------+----------------------+------------------------------------+
| 09:00:00 | 12:00:00 | 0000-00-00 09:00:00 | 2009-00-00 00:00:00 |
+----------+----------+----------------------+------------------------------------+
1 row in set (0.00 sec)
[23 Mar 8:22]
Sveta Smirnova
Thank you for the report. Verified as described.
Description: Casting of a TIME value to DATETIME treats the time as a string, resulting in a wrong DATETIME without the TIME portion set. How to repeat: mysql> select cast(cast('21:00:00' as time) as datetime); +--------------------------------------------+ | cast(cast('21:00:00' as time) as datetime) | +--------------------------------------------+ | 2021-00-00 00:00:00 | +--------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: CAST to DATETIME from a TIME should yield: 0000-00-00 HH:MI:SS, where 'HH:MI:SS' in the input TIME value in the CAST.