MySQL Bugs: #29729: datetime compared with date failing to add zero time on group by query

Bug #29729	datetime compared with date failing to add zero time on group by query
Submitted:	11 Jul 2007 18:06	Modified:	23 Jul 2007 0:44
Reporter:	austin woz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.44, 5.0.45	OS:	Linux
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	backport_050044SP1, date, datetime, GROUP BY, regression

Description:
if i have a table with date column, when i query this with datetime i get results with normal select query, but if i change query to a 'group by' then it gives me no results. This was a wider issue from version 5.0.41 and fix was put in place (Bug# 28929), but i do not think the fix was done properly. 
Version 5.0.37 worked perfectly, what changed betweeen 5.0.37 and 5.0.41 that meant a fix had to be done and now further issues?

How to repeat:
1. create the table:
CREATE TABLE  `live`.`CategoryHits` (
  `categoryId` int(11) unsigned NOT NULL,
  `hitMonth` date NOT NULL,
  `count` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`categoryId`,`hitMonth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Add some data with the date as 2007-07-01. 
3. The query "SELECT * FROM CategoryHits C where hitMonth='2007-07-01 00:00:00';" will return rows as expected. 
4. Run the query "SELECT sum(C.count) FROM CategoryHits C where hitMonth='2007-07-01 00:00:00' group by hitMonth ;" and no results are given when they are expected.
5. worse still i dont think the query "SELECT sum(C.count) FROM CategoryHits C where hitMonth='2007-07-01' group by hitMonth ;" works either.
6. the query "SELECT sum(C.count) FROM CategoryHits C where hitMonth='20070701' group by hitMonth ;" does return results as expected. 

Suggested fix:
look at changes between version 5.0.37 and 5.0.41

Thank you for a bug report. Verified just as described:

mysql> CREATE TABLE  `CategoryHits` (
    ->   `categoryId` int(11) unsigned NOT NULL,
    ->   `hitMonth` date NOT NULL,
    ->   `count` int(11) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`categoryId`,`hitMonth`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into `CategoryHits` values(1, '2007-07-01', 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into `CategoryHits` values(2, '2007-07-01', 2);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM CategoryHits C where hitMonth='2007-07-01 00:00:00';
+------------+------------+-------+
| categoryId | hitMonth   | count |
+------------+------------+-------+
|          1 | 2007-07-01 |     1 |
|          2 | 2007-07-01 |     2 |
+------------+------------+-------+
2 rows in set (0.02 sec)

mysql> SELECT sum(C.count) FROM CategoryHits C where hitMonth='2007-07-01 00:00:
00' group by hitMonth;
Empty set (0.00 sec)

But:

mysql> SELECT sum(C.count) FROM CategoryHits C where hitMonth='2007-07-01' group
 by hitMonth;
+--------------+
| sum(C.count) |
+--------------+
|            3 |
+--------------+
1 row in set (0.02 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30935

ChangeSet@1.2527, 2007-07-14 21:57:23+04:00, evgen@moonbone.local +3 -0
  Bug#29729: Wrong conversion error led to an empty result set.
  
  The Field_newdate::store when storing a DATETIME value was returning the
  'value was cut' error even if the thd->count_cuted_fields flag is set to
  CHECK_FIELD_IGNORE. This made range optimizr think that there is no
  appropriate data in the table and thus to return an empty set.
  
  Now the Field_newdate::store function returns conversion error only if the
  thd->count_cuted_fields flag isn't set to CHECK_FIELD_IGNORE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30937

ChangeSet@1.2527, 2007-07-14 22:43:58+04:00, evgen@moonbone.local +3 -0
  Bug#29729: Wrong conversion error led to an empty result set.
  
  The Field_newdate::store when storing a DATETIME value was returning the
  'value was cut' error even if the thd->count_cuted_fields flag is set to
  CHECK_FIELD_IGNORE. This made range optimizr think that there is no
  appropriate data in the table and thus to return an empty set.
  
  Now the Field_newdate::store function returns conversion error only if the
  thd->count_cuted_fields flag isn't set to CHECK_FIELD_IGNORE.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

For a table with a DATE column date_col such that selecting rows with
WHERE date_col = 'date_val 00:00:00' yielded a non-empty result,
adding GROUP BY date_col caused the result to be empty.