MySQL Bugs: #60990: incorrect date comparison

Bug #60990	incorrect date comparison
Submitted:	28 Apr 2011 10:48	Modified:	28 Apr 2011 13:20
Reporter:	Sergey L	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5, 5.1.56	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
During the update from 5.0.x to 5.1.x we have faced the problem with comparing dates. Attached query returns 0 in 5.0.77-log, and now returns 1 in 5.1.47-log, 5.1.56-log.
Directly comparing  TIMESTAMP('2013-10-13 00:00:00') < DATE('2011-04-28') returns 0 in all versions.

Issue is caused by mixing date and datetime types. Workaround - apply DATE() on test.last_recharge.

How to repeat:
create table test (first_usage date, last_recharge datetime, life_time smallint(4) unsigned) engine=innodb;

insert into test values ('2011-04-27', null, 900);

SELECT VERSION(), 
DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.first_usage)), INTERVAL test.life_time DAY ) as dt, 
  DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.first_usage)), INTERVAL test.life_time DAY ) < DATE('2011-04-28') as exp 
from test;

+------------+---------------------+------+
| VERSION()  | dt                  | exp  |
+------------+---------------------+------+
| 5.1.56-log | 2013-10-13 00:00:00 |    1 |
+------------+---------------------+------+

Verified on Windows XP. 5.0.91 gives:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test (first_usage date, last_recharge datetime, life_time sm
allint(4)
    -> unsigned) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values ('2011-04-27', null, 900);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT VERSION(),
    -> DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.firs
t_usage)),
    -> INTERVAL test.life_time DAY ) as dt,
    ->   DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.fi
rst_usage)),
    -> INTERVAL test.life_time DAY ) < DATE('2011-04-28') as exp
    -> from test;
+---------------------+---------------------+------+
| VERSION()           | dt                  | exp  |
+---------------------+---------------------+------+
| 5.0.91-community-nt | 2013-10-13 00:00:00 |    0 |
+---------------------+---------------------+------+
1 row in set (0.03 sec)

While 5.1.56 (and 5.5.x) produces different result:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.56-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test (first_usage date, last_recharge datetime, life_time sm
allint(4)
    -> unsigned) engine=innodb;
Query OK, 0 rows affected (0.56 sec)

mysql> insert into test values ('2011-04-27', null, 900);
Query OK, 1 row affected (0.13 sec)

mysql> SELECT VERSION(),
    -> DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.firs
t_usage)),
    -> INTERVAL test.life_time DAY ) as dt,
    ->   DATE_ADD( GREATEST(test.first_usage, IFNULL(test.last_recharge, test.fi
rst_usage)),
    -> INTERVAL test.life_time DAY ) < DATE('2011-04-28') as exp
    -> from test;
+------------------+---------------------+------+
| VERSION()        | dt                  | exp  |
+------------------+---------------------+------+
| 5.1.56-community | 2013-10-13 00:00:00 |    1 |
+------------------+---------------------+------+
1 row in set (0.13 sec)

still reproducible with 5.6.10

no, wait ... 5.6.10 returns exp=0 as 5.0 did ... so seems to be fixed in 5.6 ... 5.5.30 still returns exp=1 though ...