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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.