Bug #48168 serious innodb date comparison bug
Submitted: 19 Oct 2009 19:18 Modified: 19 Oct 2009 19:23
Reporter: Ethan Joffe Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.39 OS:Linux (binary install on debian)
Assigned to: CPU Architecture:Any

[19 Oct 2009 19:18] Ethan Joffe
Description:

exact version: mysql-5.1.39-linux-x86_64-glibc23

basic comparison of constants with date fields is broken if you do not include dashes in the comparison.
it appears to be constrained to indexed innodb tables.
the specific cases appear to be inconsistent, but I found a simple case which should hopefully lead you to the source of the more general bug.
this is a huge bug.. any aggregate report will not include the first day of data in a daterange.

How to repeat:

create table datebug ( 
foo date NOT NULL,
key (foo)
) TYPE = InnoDB;
insert into datebug set foo='2009-10-19';
select * From datebug where foo>='20091019' and foo<='20091019';

returns:
Empty set (0.00 sec)

Different variations of the bug appear in different datasets, for instance in my live databases, the equivalent of the following also return an empty set:
select * From table where date>='20091019';
select * From table where date>='20091019' and date<'20091020';

and the following returns all but the first day (vary xx and yy):
select * From table where date>='200910xx' and date<'200910yy';

again, i am unable to repeat this bug using the above simple table example, but it is consistent when run on my larger real data tables.

this bug does not occur in version mysql-5.1.37-linux-x86_64-glibc23
[19 Oct 2009 19:23] MySQL Verification Team
this is a duplicate of bug #47925