Even Flow..

Recently, Ubuntu 7.10 was released. Since I like new and shiny software, I upgraded some time ago to the beta. Everything looks great, until I ran into some weird behavior while coding. From Rails, I did a standard ActiveRecord find for some objects in my database, but I didn't get any results. Breaking down the query to a minimum didn't give any records back, although I new that there had to be some results.

The following was the case (quite stunned me at first):

mysql> SELECT id,expired_at FROM job_openings
         WHERE CURDATE() <= DATE(expired_at);

Empty set (0.03 sec)

mysql> SELECT id,expired_at FROM job_openings
         WHERE CURDATE() <= DATE(expired_at) AND id=3053;

+------+---------------------+
| id   | expired_at          |
+------+---------------------+
| 3053 | 2007-10-25 14:33:53 |
+------+---------------------+

1 row in set (0.00 sec)

At first I thought that my database (or table) was corrupt, so I got a fresh dump from a production server and loaded it, but the problem persisted. Running the same query on the production server, did gave results. After some more research and talk on #mysql on freenode, I found out that the issue was caused by the fact that the expired_at column (type datetime) has some NULL values and that a bug in MySQL causes to return any results when date() functions are used in the WHEN-clause.

A workaround would be to extend my query with "AND expired_at IS NOT NULL". This worked, but for some reason, adding extra columns to my query, didn't return any results.

At that point I had spent way too much time on the issue and decided to downgrade to 5.0.38 (Ubuntu feisty pacakge), which solved the problem.

Edit: the table was innoDB. I haven't tested this behavior on MyISAM tables.