Getting bit by MySQL Dates

Yesterday morning, Jack sent out a rather perplexing problem to Josh and myself.

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.