Bug #28133 Wrong DATE/DATETIME comparison in IN() function
Submitted: 27 Apr 2007 12:05 Modified: 10 May 2007 18:02
Reporter: Evgeny Potemkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-bk OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any

[27 Apr 2007 12:05] Evgeny Potemkin
Description:
mysql> create table t1 (f1 date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('01-01-01'),('01-01-02'),('01-01-03');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------------+
| f1         |
+------------+
| 2001-01-01 | 
| 2001-01-02 | 
| 2001-01-03 | 
+------------+
3 rows in set (0.00 sec)

mysql> select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00');
Empty set (0.00 sec)

The last select should return all records.

How to repeat:
See description.

Suggested fix:
Implement correct DATE/DATETIME comparison for the IN function.
[28 Apr 2007 21:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25682

ChangeSet@1.2445, 2007-04-29 01:07:28+04:00, evgen@moonbone.local +5 -0
  Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
  
  The IN function was comparing DATE/DATETIME values either as ints or as
  strings. Both methods have their disadvantages and may lead to a wrong
  result.
  
  Now IN function checks whether all of its arguments has the STRING result
  types and at least one of them is a DATE/DATETIME item. If so it uses either
  an object of the in_datetime class or an object of the cmp_item_datetime
  class to perform its work.
  The in_datetime class is used to find occurence of the item to be checked
  in the vector of the constant DATE/DATETIME values. The cmp_item_datetime
  class is used to compare items one by one in the DATE/DATETIME context.
  Both classes obtain values from items with help of the get_datetime_value()
  function and cache the left item if it is a constant one.
[28 Apr 2007 21:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25683

ChangeSet@1.2445, 2007-04-29 01:20:31+04:00, evgen@moonbone.local +5 -0
  Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
  
  The IN function was comparing DATE/DATETIME values either as ints or as
  strings. Both methods have their disadvantages and may lead to a wrong
  result.
  
  Now IN function checks whether all of its arguments has the STRING result
  types and at least one of them is a DATE/DATETIME item. If so it uses either
  an object of the in_datetime class or an object of the cmp_item_datetime
  class to perform its work.
  The in_datetime class is used to find occurence of the item to be checked
  in the vector of the constant DATE/DATETIME values. The cmp_item_datetime
  class is used to compare items one by one in the DATE/DATETIME context.
  Both classes obtain values from items with help of the get_datetime_value()
  function and cache the left item if it is a constant one.
[2 May 2007 18:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25932

ChangeSet@1.2445, 2007-05-02 22:32:38+04:00, evgen@moonbone.local +5 -0
  Bug#28133: Wrong DATE/DATETIME comparison in IN() function.
  
  The IN function was comparing DATE/DATETIME values either as ints or as
  strings. Both methods have their disadvantages and may lead to a wrong
  result.
  
  Now IN function checks whether all of its arguments has the STRING result
  types and at least one of them is a DATE/DATETIME item. If so it uses either
  an object of the in_datetime class or an object of the cmp_item_datetime
  class to perform its work. If the IN() function arguments are rows then
  row columns are checked whether the DATE/DATETIME comparator should be used
  to compare them.
  The in_datetime class is used to find occurence of the item to be checked
  in the vector of the constant DATE/DATETIME values. The cmp_item_datetime
  class is used to compare items one by one in the DATE/DATETIME context.
  Both classes obtain values from items with help of the get_datetime_value()
  function and cache the left item if it is a constant one.
[10 May 2007 6:38] Bugs System
Pushed into 5.1.19-beta
[10 May 2007 6:51] Bugs System
Pushed into 5.0.42
[10 May 2007 18:02] Paul Dubois
Noted in 5.1.19, 5.0.42 changelogs.

Comparisons of DATE or DATETIME values for the IN() function could
yield incorrect results.
[25 Oct 2007 9:44] Sveta Smirnova
Bug #31835 was marked as duplicate of this one.