Basically, he reported, this query doesn't return any results, despite there being plenty of valid records:
select * from injections where DATE(created_at) = DATE('2008-03-11');
And this query returns the expected records:
select * from injections where DATE(created_at) = DATE('2008-02-18');
The only difference between the two queries is the date provided. Crazy.
But wait, it's gets worse!
This query yields 293 rows (distinct days):
select distinct DATE(created_at) from injections;
While this one only returns 277 rows (days):
select distinct DATE(created_at) from injections where DATE(created_at) = DATE(created_at);
Josh provided some interesting links to MySQL documentation with tidbits like:
"MySQL automatically converts a date to a number if the date is used in a numeric context"and
"However, this conversion is subject to the following exceptions: ... For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison."and this one, which I suspect is the culprit:
"A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions."I emphasized the use of the word "some" in that quote. When an API is sometimes consistent - an oxymoron if I've ever heard one - it should set off the klaxons in your noggin.
We didn't solve the puzzle, but Jack did find a clever resolution:
...it turns out that "created_at LIKE '2008-03-11%'" is about 10% faster.