Bug #66034 Inconsistent results while comparing time fields with string and number literals

Submitted: 26 Jul 2012 19:01 Modified: 11 Dec 2025 21:56
Reporter: Elena Stepanova Email Updates:
Status: Can't repeat 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

[26 Jul 2012 19:01] Elena Stepanova
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');
[26 Jul 2012 20:01] Peter Laursen
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)
[27 Jul 2012 7:06] Valeriy Kravchuk
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)
[11 Dec 2025 21:56] Roy Lyseng
Posted by developer:
 
Problem cannot be repeated in 8.4.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.