MySQL Bugs: #30669: Matching date() to curdate() does not work in 5.0.45

Bug #30669	Matching date() to curdate() does not work in 5.0.45
Submitted:	28 Aug 2007 13:13	Modified:	23 Oct 2008 18:40
Reporter:	John Butterfield	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (RHEL 4)
Assigned to:		CPU Architecture:	Any

Description:
When running the following query on a table, 5.0.45 does not match any rows.  When the same query is run on 5.0.41 it matches the correct rows.

select date(close_datetime) from events where date(close_datetime) = date(date_sub(now(), interval 1 day));

How to repeat:
Create the following table called "events":

mysql> describe events;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| open_datetime    | datetime     | YES  |     | NULL    |                |
| source_reporting | varchar(30)  | YES  |     | NULL    |                |
| severity         | int(1)       | YES  |     | NULL    |                |
| close_datetime   | datetime     | YES  |     | NULL    |                |
| close_reason     | text         | YES  |     | NULL    |                |
| db               | varchar(30)  | YES  | MUL | NULL    |                |
| description      | varchar(500) | YES  | MUL | NULL    |                |
| incident_id      | int(11)      | YES  |     | NULL    |                |
| district         | char(2)      | YES  |     | NULL    |                |
| group_id         | varchar(4)   | YES  |     | NULL    |                |
| close_id         | varchar(10)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
 Run the query under 5.0.45 and then 5.0.41

Suggested fix:
No idea...

Version that I am using:
mysql-5.0.41-linux-i686-glibc23
and 
mysql-5.0.45-linux-i686-glibc23

Thank you for the report.

Please provide output of query `select close_datetime from events where date(close_datetime) = date(date_sub(now(), interval 1 day));` if run it on 5.0.41 server.

mysql> select date(close_datetime) from events where date(close_datetime) = date(date_sub(now(), interval 1 day))
    -> ;
+----------------------+
| date(close_datetime) |
+----------------------+
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
| 2007-08-27           |
...snip...
| 2007-08-27           |
| 2007-08-27           |
+----------------------+
45 rows in set (0.25 sec)

Thank you for the feedback.

Please orovide output of query select close_datetime from events where
date(close_datetime) = date(date_sub(now(), interval 1 day)), not select date(close_datetime) from events where date(close_datetime) = date(date_sub(now(), interval 1 day)). We nead raw values which present in the table.

Sorry about that.  New data:
mysql> select close_datetime from events where
    -> date(close_datetime) = date(date_sub(now(), interval 1 day))
    -> ;
+---------------------+
| close_datetime      |
+---------------------+
| 2007-08-27 07:50:46 |
| 2007-08-27 07:50:34 |
| 2007-08-27 07:50:23 |
| 2007-08-27 07:50:13 |
| 2007-08-27 07:50:08 |
| 2007-08-27 07:50:04 |
| 2007-08-27 07:50:00 |
...snip...
| 2007-08-27 12:20:48 |
| 2007-08-27 12:22:42 |
| 2007-08-27 13:58:39 |
| 2007-08-27 13:58:46 |
| 2007-08-27 14:32:28 |
| 2007-08-27 16:21:16 |
| 2007-08-27 16:21:26 |
+---------------------+
45 rows in set (0.21 sec)

Thank you for the feedback.

I can not repeat described behaviour in myself environment.

Please also provide output of SHOW CREATE TABLE events: we want to check if there are some indexes. Also provide output of SELECT now() on 5.0.45 server.

mysql> show create table events\G
*************************** 1. row ***************************
       Table: events
Create Table: CREATE TABLE `events` (
  `id` int(11) NOT NULL auto_increment,
  `open_datetime` datetime default NULL,
  `source_reporting` varchar(30) default NULL,
  `severity` int(1) default NULL,
  `close_datetime` datetime default NULL,
  `close_reason` text,
  `db` varchar(30) default NULL,
  `description` varchar(500) default NULL,
  `incident_id` int(11) default NULL,
  `district` char(2) default NULL,
  `group_id` varchar(4) default NULL,
  `close_id` varchar(10) default NULL,
  PRIMARY KEY  (`id`),
  KEY `db_idx` (`db`),
  FULLTEXT KEY `desc_idx` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=56654 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2007-08-28 14:24:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> show index from events\G
*************************** 1. row ***************************
       Table: events
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 56574
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: events
  Non_unique: 1
    Key_name: db_idx
Seq_in_index: 1
 Column_name: db
   Collation: A
 Cardinality: 12
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: events
  Non_unique: 1
    Key_name: desc_idx
Seq_in_index: 1
 Column_name: description
   Collation: NULL
 Cardinality: 2
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment:
3 rows in set (0.00 sec)

Thank you for the feedback.

I still can not repeat described behaviour.

Please provide your configuration file and output of `uname -a`

[root@g1dssapp01 Lumigent]# uname -a
Linux g1dssapp01 2.6.9-55.0.2.ELsmp #1 SMP Tue Jun 12 17:59:08 EDT 2007 i686 i686 i386 GNU/Linux
[root@g1dssapp01 Lumigent]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql/mysql1
socket=/tmp/mysql.sock
log_slow_queries=/var/log/slow-queries.log
long_query_time=1
max_allowed_packet=16M

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/tmp/mysql.sock

Thank you for the feedback.

I still can not repeat described behaviour. So I'll close the report as "Can't repeat" If you have any idea how we can repeat it feel free to provide this information and reopen the report.