Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.3, 5.5.32, 5.3.12
-
None
-
None
Description
The following comparison throws a warning but returns TRUE:
MariaDB [test]> select cast('00:00:00' as time) = 61; |
+-------------------------------+ |
| cast('00:00:00' as time) = 61 | |
+-------------------------------+ |
| 1 |
|
+-------------------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [test]> show warnings;
|
+---------+------+--------------------------------+ |
| Level | Code | Message | |
+---------+------+--------------------------------+ |
| Warning | 1292 | Incorrect datetime value: '61' | |
+---------+------+--------------------------------+ |
1 row in set (0.00 sec) |
This is strange because
MariaDB [test]> select cast('00:00:00' as time) = cast(61 as time); |
+---------------------------------------------+ |
| cast('00:00:00' as time) = cast(61 as time) | |
+---------------------------------------------+ |
| NULL | |
+---------------------------------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [test]> select cast(61 as time); |
+------------------+ |
| cast(61 as time) | |
+------------------+ |
| NULL | |
+------------------+ |
1 row in set, 1 warning (0.01 sec) |
|
|
MariaDB [test]> show warnings;
|
+---------+------+--------------------------------+ |
| Level | Code | Message | |
+---------+------+--------------------------------+ |
| Warning | 1292 | Incorrect datetime value: '61' | |
+---------+------+--------------------------------+ |
and
MariaDB [test]> select cast('00:00:00' as time) = null; |
+---------------------------------+ |
| cast('00:00:00' as time) = null | |
+---------------------------------+ |
| NULL | |
+---------------------------------+ |
1 row in set (0.00 sec) |
So, the result of the first comparison should have been null or 0, but not 1?
Comparing with an illegal string value demonstrates the same problem:
mysql> select cast('00:00:00' as time) = '61'; |
+---------------------------------+ |
| cast('00:00:00' as time) = '61' | |
+---------------------------------+ |
| 1 |
|
+---------------------------------+ |
1 row in set, 1 warning (1.30 sec) |
A similar problem is observed with the DATETIME data type:
MariaDB [test]> select cast('0000-00-00 00:00:00' as datetime) = 61; |
+----------------------------------------------+ |
| cast('0000-00-00 00:00:00' as datetime) = 61 | |
+----------------------------------------------+ |
| 1 |
|
+----------------------------------------------+ |
1 row in set, 1 warning (0.00 sec) |
More problems:
mysql> drop table if exists t1;
|
mysql> create table t1 (a time, key(a));
|
mysql> insert into t1 values ('00:00:00'),('00:00:01'),('00:00:02');
|
mysql> select * from t1 where a='xxx';
|
Empty set (0.00 sec)
|
|
|
mysql> select * from t1 ignore key(a) where a='xxx';
|
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
1 row in set, 1 warning (0.00 sec)
|
The query with and without index produce different results.
More related problems:
mysql> drop table if exists t1;
|
mysql> create table t1 (a datetime,b time);
|
mysql> insert into t1 values ('xxx','xxx');
|
mysql> select *,cast('xxx' as datetime),cast('xxx' as time) from t1;
|
+---------------------+----------+-------------------------+---------------------+
|
| a | b | cast('xxx' as datetime) | cast('xxx' as time) |
|
+---------------------+----------+-------------------------+---------------------+
|
| 0000-00-00 00:00:00 | 00:00:00 | NULL | 00:00:00 |
|
+---------------------+----------+-------------------------+---------------------+
|
1 row in set, 2 warnings (0.00 sec)
|
Explicit cast to DATETIME returns NULL, while implicit CAST to DATETIME
(when inserting into a DATETIME column) produces '0000-00-00 00:00:00'.
Note: at the same time, explicit and implicit CASTs to TIME return equal results '00:00:00'.
Attachments
Issue Links
- relates to
-
-
- Open
-
-
-
- Open
-
- links to
