Bug #29739 Incorrect time comparison in BETWEEN
Submitted: 11 Jul 2007 21:15 Modified: 27 Jul 2007 4:35
Reporter: Evgeny Potemkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0-bk OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any

[11 Jul 2007 21:15] Evgeny Potemkin
Description:
TIME values compared as strings. This may lead to wrong comparison result in some
cases.

How to repeat:
mysql> create table t1(f1 time, f2 time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('20:00:00','150:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
Empty set (0.00 sec)
[12 Jul 2007 19:13] 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/30830

ChangeSet@1.2535, 2007-07-12 23:09:55+04:00, evgen@moonbone.local +3 -0
  Bug#29739: Incorrect time comparison in BETWEEN.
  
  Time values were compared by the BETWEEN function as strings. This led to a
  wrong result in cases when some of arguments are less than 100 hours and other
  are greater.
  
  Now if all 3 arguments of the BETWEEN function are of the TIME type then
  they are compared as integers.
[20 Jul 2007 23:46] Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49] Bugs System
Pushed into 5.0.48
[27 Jul 2007 4:35] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

Comparison of TIME values using the BETWEEN operator led to string
comparison, producing incorrect results in some cases. Now the values
are compared as integers.