MySQL Bugs: #66034: Inconsistent results while comparing time fields with string and number literals

Bug #66034	Inconsistent results while comparing time fields with string and number literals
Submitted:	26 Jul 2012 19:01	Modified:	27 Jul 2012 7:06
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-literals.html) says that both '2:2:2' and 105454 are valid time values. However, in some cases they are not recognized properly.

Below is the output of the test case provided in 'How to repeat' section:

CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('02:02:02'),(105454);
SELECT c1 FROM t WHERE c1 IN ('2:2:2',105454);
c1
10:54:54
SELECT c1 FROM t WHERE c1 IN ('2:2:2');
c1
02:02:02

So, the first SELECT does not find '02:02:02' by '2:2:2' literal, while the second SELECT does.

Please also note that some result files in 'engines' suite contain wrong results caused by this problem. So far I found it in engines/iuds/delete_time, although there might be more. 

How to repeat:
CREATE TABLE t (c1 TIME);
INSERT INTO t VALUES ('02:02:02'),(105454);
SELECT c1 FROM t WHERE c1 IN ('2:2:2',105454);
SELECT c1 FROM t WHERE c1 IN ('2:2:2');

Looks like a problem with the IN operator when the set compared contains both quoted and non-quoted values, compare (using 5.5.23):

SELECT c1 FROM t WHERE c1 IN ('2:2:2',105454);
/*
c1        
----------
10:54:54  
*/

SELECT c1 FROM t WHERE c1 IN ('2:2:2','10:54:54');
/*
c1        
----------
02:02:02  
10:54:54  
*/

SELECT c1 FROM t WHERE c1 IN (020202,105454);
/*
c1        
----------
02:02:02  
10:54:54  
*/

SELECT c1 FROM t WHERE c1 = '2:2:2' OR c1 = 105454;
/*
c1        
----------
02:02:02  
10:54:54 
*/

Peter
(not a MySQL/Oracle person)

Thank you for the bug report. Verified with 5.5.25 on Windows:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE t (c1 TIME);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t VALUES ('02:02:02'),(105454);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT c1 FROM t WHERE c1 IN ('2:2:2',105454);
+----------+
| c1       |
+----------+
| 10:54:54 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT c1 FROM t WHERE c1 IN ('2:2:2');
+----------+
| c1       |
+----------+
| 02:02:02 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT c1 FROM t WHERE c1 IN (105454, '2:2:2');
+----------+
| c1       |
+----------+
| 10:54:54 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT c1 FROM t WHERE c1 IN ('105454', '2:2:2');
+----------+
| c1       |
+----------+
| 02:02:02 |
| 10:54:54 |
+----------+
2 rows in set (0.00 sec)