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: | |
| 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
[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.