Bug #4 BETWEEN not working properly with timestamp / datetime
Submitted: 29 Oct 2002 19:45 Modified: 2 Dec 2002 12:01
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.5 OS:Any (all)
Assigned to: CPU Architecture:Any

[29 Oct 2002 19:45] Sinisa Milivojevic
Description:

CREATE TABLE a (
   start datetime default NULL
) TYPE=MyISAM;

INSERT INTO a VALUES ('2002-10-21 00:00:00');
INSERT INTO a VALUES ('2002-10-28 00:00:00');
INSERT INTO a VALUES ('2002-11-04 00:00:00');

CREATE TABLE b (
   ctime timestamp(14) NOT NULL,
   ctime2 timestamp(14) NOT NULL
) TYPE=MyISAM;

INSERT INTO b VALUES (20021029165106,20021105164731);

mysql> select * from a, b where a.start between b.ctime and b.ctime2;
Empty set (0.00 sec)
^^^^^
BUG !!

mysql> select * from a, b where a.start >= b.ctime and a.start <= b.ctime2;
+---------------------+----------------+----------------+
| start               | ctime          | ctime2         |
+---------------------+----------------+----------------+
| 2002-11-04 00:00:00 | 20021029165106 | 20021105164731 |
+---------------------+----------------+----------------+

Correct result.

Both queries must return exactly same result.

Note that:

select ... where a.start+0 between ...; also gives correct result.

mysql> alter table b modify ctime datetime;

mysql> select * from a, b where a.start between b.ctime and b.ctime2;
+---------------------+---------------------+----------------+
| start               | ctime               | ctime2         |
+---------------------+---------------------+----------------+
| 2002-11-04 00:00:00 | 2002-10-29 16:51:06 | 20021105164731 |
+---------------------+---------------------+----------------+

Correct result!!!

How to repeat:
[2 Dec 2002 12:01] MySQL Developer
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/