Bug #22317 BETWEEN fails when a datetime is compared to two dates
Submitted: 13 Sep 2006 16:38 Modified: 13 Sep 2006 16:48
Reporter: Pete Harlan (Candidate Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.21 OS:Linux (Linux x86_64)
Assigned to: CPU Architecture:Any

[13 Sep 2006 16:38] Pete Harlan 
Description:
A BETWEEN test fails when a datetime is compared to two dates.

The how-to-repeat script works on 4.1.18 and 4.1.20, but returns no rows on 4.1.21.  I did not test with 5.x or on platforms other than x86_64.  I used the MySQL binaries.

If you replace "datetimeval between..." with "date(datetimeval) between...", 4.1.21 does match the row.

How to repeat:
drop table if exists test1;
create table test1
(
  datetimeval datetime,
  dateval1 date,
  dateval2 date
);

insert into test1
  (datetimeval, dateval1, dateval2)
values
  ('2006-09-13 08:47:32', '2006-01-01', '2007-01-01');

select datetimeval
from test1 
where datetimeval between dateval1 and dateval2;
[13 Sep 2006 16:42] Evgeny Potemkin 
Duplicate of bug#21677
[13 Sep 2006 16:48] Pete Harlan 
So it is.  Sorry about that.  I did search the bug database for active and closed bugs with "between" and 4.1.21 and it didn't find any matches.  When I submitted my bug, it suggested five possible duplicate bugs but none of them were relevant.

Thank you for looking into this.