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
Triage: Needs Triage: D2 (Serious)

[28 Apr 2011 10:48] Sergey L
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 |
+------------+---------------------+------+
[28 Apr 2011 13:20] Valeriy Kravchuk
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)
[28 Apr 2013 9:29] Hartmut Holzgraefe
still reproducible with 5.6.10
[28 Apr 2013 9:34] Hartmut Holzgraefe
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 ...