| Bug #60990 | incorrect date comparison | ||
|---|---|---|---|
| Submitted: | 28 Apr 2011 10:48 | Modified: | 14 Dec 17:47 |
| Reporter: | Sergey L | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5, 5.1.56 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[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 ...
[14 Dec 17:47]
Roy Lyseng
Posted by developer: Not reproducible in version 8.0 and later.
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 | +------------+---------------------+------+