| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[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.
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.