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:
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: