MySQL inconsistent results with Date() select.

Question

What's going on here? BTW, MySQL Server version: 5.0.45-log Source distribution.

mysql> select count(*) 
       from notes 
      where date(updated_at) > date('2010-03-25');
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.59 sec)

mysql> select count(*) 
         from notes 
        where message like'%***%' 
          and date(updated_at) > date('2010-03-25');
+----------+
| count(*) |
+----------+
|       26 | 
+----------+
1 row in set (1.30 sec)

mysql> explain select count(*) 
      from notes 
      where date(updated_at) > date('2010-03-25');
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | notes | ALL  | NULL          | NULL | NULL    | NULL | 588106 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.07 sec)

mysql> explain select updated_at 
         from notes 
        where message like'%***%' 
          and date(updated_at) > date('2010-03-25');
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | notes | ALL  | NULL          | NULL | NULL    | NULL | 588106 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.09 sec)

mysql> 

Here's the table schema.

CREATE TABLE `notes` (
 `id` int(11) NOT NULL auto_increment,
 `status` varchar(255) default NULL,
 `message` text,
 `noteable_id` int(11) default NULL,
 `noteable_type` varchar(255) default NULL,
 `deleted_at` datetime default NULL,
 `creator_id` int(11) default NULL,
 `updater_id` int(11) default NULL,
 `deleter_id` int(11) default NULL,
 `created_at` datetime default NULL,
 `updated_at` datetime default NULL,
 `public` tinyint(1) default '0',
 `forced` tinyint(1) default '0',
 `agent_created_at` datetime default NULL,
 PRIMARY KEY  (`id`),
 KEY `noteable_id` (`noteable_id`),
 KEY `deleted_at` (`deleted_at`),
 KEY `noteable_type` (`noteable_type`(10)),
 KEY `creator_id` (`creator_id`),
 KEY `status` (`status`),
 KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=613168 DEFAULT CHARSET=latin1 

Answer 1

If the table is small, try dumping & reloading on a fresh server on another box (with the same version). If the problem goes away, there is some internal corruption and you will need to either reload the table on the existing server, or reinit the entire database from a dump.

If the behaviour is reproducible on a clean database and nobody can explain it (after you post the schema etc), then raise a bug.

Answer 2

It turns out, this particular instance was not caused by database corruption, but a bug in the Date function for MySQL version 5.0.45 (+?).

http://bugs.mysql.com/bug.php?id=32159

We don't see a need to react to this situation immediately, but we will be migrating to a higher version of MySQL (either 5.0.77+ or 5.1)