MySQL Bugs: #32002: Strnge behavior for NOW() function

Bug #32002	Strnge behavior for NOW() function
Submitted:	31 Oct 2007 19:12	Modified:	15 Dec 2007 6:13
Reporter:	Eber M. Duarte	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Function now() is not working properly in MySQL 5.0. The same query issued on MySQL 4.1.21-log and MySQL 5.0.45-log return different result sets.

How to repeat:
Data set to test:

CREATE TABLE `phpads_adstats` (
  `views` int(11) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `day` date NOT NULL default '0000-00-00',
  `hour` tinyint(4) NOT NULL default '0',
  `bannerid` smallint(6) NOT NULL default '0',
  `zoneid` smallint(6) NOT NULL default '0',
  `source` varchar(50) NOT NULL,
  PRIMARY KEY  (`day`,`hour`,`bannerid`,`zoneid`,`source`),
  KEY `bannerid_day` (`bannerid`,`day`),
  KEY `zoneid` (`zoneid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `phpads_adstats` VALUES (1,1,'2007-10-31',15,817,76,'');
INSERT INTO `phpads_adstats` VALUES (1,1,'2007-10-31',16,817,76,'');
INSERT INTO `phpads_adstats` VALUES (1,1,'2007-10-31',17,817,76,'');

=> MySQL 4.1.21-log

eber@cacimba:test> select * from phpads_adstats;
+-------+--------+------------+------+----------+--------+--------+
| views | clicks | day        | hour | bannerid | zoneid | source |
+-------+--------+------------+------+----------+--------+--------+
|     1 |      1 | 2007-10-31 |   15 |      817 |     76 |        | 
|     1 |      1 | 2007-10-31 |   16 |      817 |     76 |        | 
|     1 |      1 | 2007-10-31 |   17 |      817 |     76 |        | 
+-------+--------+------------+------+----------+--------+--------+
3 rows in set (0.01 sec)

eber@cacimba:test> select now();
+---------------------+
| now()               |
+---------------------+
| 2007-10-31 17:10:10 | 
+---------------------+
1 row in set (0.00 sec)

eber@cacimba:test> select * from phpads_adstats WHERE day = NOW() AND hour = HOUR(NOW()) AND bannerid = '817' AND zoneid = '76' AND source = '';
+-------+--------+------------+------+----------+--------+--------+
| views | clicks | day        | hour | bannerid | zoneid | source |
+-------+--------+------------+------+----------+--------+--------+
|     1 |      1 | 2007-10-31 |   17 |      817 |     76 |        | 
+-------+--------+------------+------+----------+--------+--------+
1 row in set (0.02 sec)

=> 5.0.45-log

eber@acegua:temporario> select * from phpads_adstats;
+-------+--------+------------+------+----------+--------+--------+
| views | clicks | day        | hour | bannerid | zoneid | source |
+-------+--------+------------+------+----------+--------+--------+
|     1 |      1 | 2007-10-31 |   15 |      817 |     76 |        | 
|     1 |      1 | 2007-10-31 |   16 |      817 |     76 |        | 
|     1 |      1 | 2007-10-31 |   17 |      817 |     76 |        | 
+-------+--------+------------+------+----------+--------+--------+
3 rows in set (0.01 sec)

eber@acegua:temporario> select now();
+---------------------+
| now()               |
+---------------------+
| 2007-10-31 17:11:09 | 
+---------------------+
1 row in set (0.00 sec)

eber@acegua:temporario> select * from phpads_adstats WHERE day = NOW() AND hour = HOUR(NOW()) AND bannerid = '817' AND zoneid = '76' AND source = '';
Empty set (0.00 sec)

Thank you for a problem report. I think, this problem has nothing to do with NOW(). The problem is in comparing NOW() to a `day` date column. 

Please, check if comparing to DATE(NOW()) will solve your problem. If it will, then this is a duplicate of numberous bug reports with similar situation.

Ok, it works with DATE(NOW()).

Thanks a bunch, I'll keep waiting for related bugs to be solved.

Duplicate of Bug #32198.

The behavior was changed by this cset:

# ChangeSet
#   2007/04/27 00:12:09+04:00 evgen@moonbone.local 
#   Bug#27590: Wrong DATE/DATETIME comparison.
#   
#   DATE and DATETIME can be compared either as strings or as int. Both
#   methods have their disadvantages. Strings can contain valid DATETIME value
#   but have insignificant zeros omitted thus became non-comparable with
#   other DATETIME strings. The comparison as int usually will require conversion
#   from the string representation and the automatic conversion in most cases is
#   carried out in a wrong way thus producing wrong comparison result. Another
#   problem occurs when one tries to compare DATE field with a DATETIME constant.
#   The constant is converted to DATE losing its precision i.e. losing time part.
#   
#   This fix addresses the problems described above by adding a special
#   DATE/DATETIME comparator. The comparator correctly converts DATE/DATETIME
#   string values to int when it's necessary, adds zero time part (00:00:00)
#   to DATE values to compare them correctly to DATETIME values. Due to correct
#   conversion malformed DATETIME string values are correctly compared to other
#   DATE/DATETIME values.
#   
#   As of this patch a DATE value equals to DATETIME value with zero time part.
#   For example '2001-01-01' equals to '2001-01-01 00:00:00'.
#   
#   The compare_datetime() function is added to the Arg_comparator class.
#   It implements the correct comparator for DATE/DATETIME values.
#   Two supplementary functions called get_date_from_str() and get_datetime_value()
#   are added. The first one extracts DATE/DATETIME value from a string and the
#   second one retrieves the correct DATE/DATETIME value from an item.
#   The new Arg_comparator::can_compare_as_dates() function is added and used
#   to check whether two given items can be compared by the compare_datetime()
#   comparator.
#   Two caching variables were added to the Arg_comparator class to speedup the
#   DATE/DATETIME comparison.
#   One more store() method was added to the Item_cache_int class to cache int
#   values.
#   The new is_datetime() function was added to the Item class. It indicates
#   whether the item returns a DATE/DATETIME value.

This shouldn't be considered a duplicate of BUG#32198 because BUG#32198 is about performance and this bug is about query results.

The described behavior is how MySQL now works, it is documented here: 

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

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 to perform the comparison in the following way:

date_col = CAST(NOW() as DATE) 

or 

date_col=CURDATE();