|
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'.
|