Bug #634 SELECT CAST(datetime_value AS DATE) GROUP by 1
Submitted: 11 Jun 2003 4:24 Modified: 27 Sep 2008 8:09
Reporter: Alexander Barkov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.1 current BK tree OS:Any (FreeBBSD-4.6)
Assigned to: CPU Architecture:Any

[11 Jun 2003 4:24] Alexander Barkov
Description:

I think I found a bug when a CAST(... AS DATE) is used
as a gropping column.

How to repeat:
mysql> select cast(d as date) from a group by 1;
+---------------------+
| cast(d as date)     |
+---------------------+
| 1970-01-12 17:46:40 |
| 1970-01-13 21:33:20 |
+---------------------+
2 rows in set (0.01 sec)
[8 Aug 2003 2:54] Lenz Grimmer
Any news on that one? Does it have to be fixed before 4.1.1 will be released?
[8 Aug 2003 11:06] Sinisa Milivojevic
A patch is prepared for the bug.
[26 Aug 2003 4:22] Michael Widenius
The proposed patch is not usable.

CAST() does in the current 4.0 and 4.1 trees not change the result in
any way; It only marks the return value to be of a certain type.

In 4.1 or 5.0 we will change all casts() to also do conversion to the requested
result type.

This task has Worklog entry #529
[27 Sep 2008 8:09] Konstantin Osipov
mysql> create table t1 select now() as d;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2008-09-27 12:08:50 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select cast(d as date) from t1 group by 1;
+-----------------+
| cast(d as date) |
+-----------------+
| 2008-09-27      | 
+-----------------+
1 row in set (0.00 sec)

Can't repeat in the latest 6.0 tree.
Assuming that it's fixed by some later changeset.