List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 18 2007 3:18pm
Subject:Re: Change in behaviour in version 5.0.41
View as plain text  
At 3:29 PM +0100 6/15/07, Ben Clewett wrote:
>Dear MySql,
>
>I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26 
>with date comparisons.
>
>In 5.0.26:        '2007-06-15' = '2007-06-15 00:00:00' is True.
>In 5.0.41:        '2007-06-15' = '2007-06-15 00:00:00' is False.
>In 5.1.6-alpha:   '2007-06-15' = '2007-06-15 00:00:00' is True.
>
>This has caused us a few problems.  Is this the way things should 
>be, because this change does not seem right?
>
>I am also very worried that this behaviour revert when we role out 5.1?
>
>Does any member know whether this is a bug, or just an anoying feature?
>
>Regards,
>
>Ben
>
>
>To Replicate:
>
>CREATE TABLE t (d DATE);
>INSERT INTO t VALUES ('2007-06-15');
>SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00';
>+----------+
>| COUNT(*) |
>+----------+
>|        0 |
>+----------+
>SELECT COUNT(*) FROM t WHERE d = '2007-06-15';
>+----------+
>| COUNT(*) |
>+----------+
>|        1 |
>+----------+

This might be relevant:

"Prior to MySQL 5.0.42, when DATE values are compared with DATETIME 
values the time portion of the DATETIME value is ignored. Starting 
from MySQL 5.0.42, 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 in the following way: SELECT date_field = 
CAST(NOW() as DATE);."

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

There is a corresponding change in 5.1.18.

See: http://bugs.mysql.com/bug.php?id=28929

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Thread
Change in behaviour in version 5.0.41Ben Clewett15 Jun
  • Re: Change in behaviour in version 5.0.41Baron Schwartz15 Jun
  • Re: Change in behaviour in version 5.0.41Mogens Melander15 Jun
  • Re: Change in behaviour in version 5.0.41Paul DuBois18 Jun
    • Re: Change in behaviour in version 5.0.41Baron Schwartz18 Jun
      • Re: Change in behaviour in version 5.0.41Paul DuBois19 Jun