MySQL Bugs: #28929: Comparing date field with NOW() broken

Bug #28929	Comparing date field with NOW() broken
Submitted:	6 Jun 2007 15:11	Modified:	15 Jun 2007 13:16
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.42	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Comparing a date with function NOW() (for example) is not working anymore with version 5.0.42.

This is indeed wrong doing it in the SQL code, and instead of NOW() CURDATE() should be used in this case. Though, need to document this important change or something else..

How to repeat:
CREATE TABLE `t1` (
  `today` date default NULL
);

mysql> INSERT INTO t1 VALUES (NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE today = NOW();
Empty set (0.01 sec)

mysql> SELECT * FROM t1 WHERE today = CURDATE();
+------------+
| today      |
+------------+
| 2007-06-04 | 
+------------+

Suggested fix:
Document as an incompatibility or revert (the latter being hard I heard).

Verified with 5.0.42, works fine with 5.0.40 and 5.0.41.

IMHO this bug report is about comparison of DATE and DATETIME entities in general. Here is the current (5.0.44 bk tree @ ChangeSet@1.2513, 2007-06-04 09:26:54+02:00) behaviour:

mysql> create table t1 (c1 date); insert into t1 values (now()); select * from t1 where c1=now(); drop table t1;
Query OK, 0 rows affected (0,02 sec)
Query OK, 1 row affected (0,00 sec)
Empty set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)

where 5.0.40-enterprise gave:

mysql> create table t1 (c1 date); insert into t1 values (now()); select * from t1 where c1=now(); drop table t1;
Query OK, 0 rows affected (0,01 sec)
Query OK, 1 row affected (0,00 sec)
+------------+
| c1         |
+------------+
| 2007-06-11 | 
+------------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)

This is clearly an undocumented behaviour change. But there is more. Both MySQL versions return identical results here:

mysql> set @t=now(); set @d=cast(@t as date);
mysql> select @d, @t, @d<@t, @d<=@t, @d=@t, @d>=@t, @d>@t;
+------------+---------------------+-------+--------+-------+--------+-------+
| @d         | @t                  | @d<@t | @d<=@t | @d=@t | @d>=@t | @d>@t |
+------------+---------------------+-------+--------+-------+--------+-------+
| 2007-06-11 | 2007-06-11 10:58:29 |     1 |      1 |     0 |      0 |     0 | 
+------------+---------------------+-------+--------+-------+--------+-------+

So it seems, a DATE vs. DATETIME comparison in general is done by casting the DATE to DATETIME (using 00:00:00 time part as documented) and then comparing the two DATETIME values.

But: up to 5.0.40 comparison of a DATE /column/ with a DATETIME /literal/ seems to go the other way: the time part is stripped from the DATETIME value and the comparison is done on the DATE part only. IMHO this is the expected behaviour for the = comparison on DATE and DATETIME values (the user asks "Is that (DATETIME) point in time at the same day as this (DATE) one?" rather than "Is that (DATETIME) point in time at midnight of this (DATE) day?"). This behaviour seems to be implemented in 5.0.40 and before in some way.

I just looked up the manual on DATE vs. DATETIME types and how comparison of different types is handled, but it seems not to be documented at all. I checked

11.3. Date and Time Types
11.3.1. The DATETIME, DATE, and TIMESTAMP Types
and
12.2.3. Comparison Functions and Operators

IMHO we need clear and documented rules here.

To avoid further confusion this should be explicitly documented:

Currently when a DATE value is compared to a DATETIME value the former is coerced to the DATETIME type by adding zero time part (i.e. "DATE 00:00:00") and then values are compared. DATE values compared as DATE and DATETIME values as DATETIME.
To get the old behaviour one should use the CAST() function:
SELECT date_field = CAST(NOW() as DATE);

Does this apply to 5.1 as well? If so, which particular version number?

Fix which implements that behavior(#27590) was pushed to 5.0.42, 5.1.18.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Added to the changelogs for 5.0.42, 5.1.18. Also noted in the body of the documentation.

hi there,

i do not want to discuss if this is a bug or not, but you change the behavior, which is there over years (Version 3, 4 and 5) and this is very annoying! now we have to change a lot of applications, because we use NOW() for both DATE and DATETIME fields (i.e. DATE >= NOW() - INTERVAL 6 HOUR is not the same anymore). i think this is not a good practice.

sorry, that i am in a bad mood :)

greetings from germany, thomas

This was a very annoying change to make to a minor point release. Several of our applications have broken due to this change, which is documented hidden away in the detailed release notes.

The last few entries are confusing - is this a bug that will be fixed, or is this a behavior change that is permanent?

I don't see anything in the documentation that clearly explains the change in behavior.

The description of the behavious prior to Version 5.0.42 does not seem to be correct, but restricted to the function NOW() and to values '00:00:00':

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 10:00:00'); select version(); select * from t1 where c1=c2; drop table t1;
Query OK, 0 rows affected (0.06 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 00:00:00'); select version(); select * from t1 where c1=c2; drop table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 00:00:00'); select version(); select * from t1 where c1='2008-05-05 00:00:00'; drop table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.01 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 00:00:00'); select version(); select * from t1 where c2='2008-05-05'; drop table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.00 sec)

+------------+---------------------+
| c1         | c2                  |
+------------+---------------------+
| 2008-05-05 | 2008-05-05 00:00:00 |
+------------+---------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 00:00:00'); select version(); select * from t1 where c2=NOW(); drop table t1;
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (c1 date, c2 datetime); insert into t1 values ('2008-05-05','2008-05-05 00:00:00'); select version(); select * from t1 where c1=NOW(); drop table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| version()  |
+------------+
| 5.0.41-log |
+------------+
1 row in set (0.00 sec)

+------------+---------------------+
| c1         | c2                  |
+------------+---------------------+
| 2008-05-05 | 2008-05-05 00:00:00 |
+------------+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

It would very helpful to change the documentation and the changelog to reflect this, because the current explanation can lead to the expectation of large migration costs.

The change in behavior is mentioned here:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

It turns out that the old behavior it now always "strip the time from the DATETIME value," it could be that the operands would be compared as strings. Updated changelog entry:

Prior to this release, when DATE values were compared with DATETIME
values, the time portion of the DATETIME value was ignored, or the
comparison could be performed as a string compare. Now a DATE value 
is coerced to the DATETIME type by adding the time portion as
00:00:00. To mimic the old behavior, use the CAST() function to cause
the comparison operands to be treated as previously. For example:
SELECT date_col = CAST(NOW() AS DATE);.

I also updated the data-types chapter accordingly.