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
