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
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1 current BK tree
• OS: Any (FreeBBSD-4.6)
• 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] MySQL Verification Team

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.