MySQL Bugs: #29739: Incorrect time comparison in BETWEEN

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

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)

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.

Pushed into 5.1.21-beta

Pushed into 5.0.48

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.