Details
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'.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-5050 Different result set with index_merge=on and index_merge=off on comparing a date column to an incorrect value
-
- Open
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
See also the thread "warning 1292" on [Maria-discuss].