MySQL Bugs: #20293: group by cuts off value from time

Bug #20293	group by cuts off value from time_format
Submitted:	6 Jun 2006 13:12	Modified:	15 Mar 2007 3:10
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1-BK, 5.0-BK, 4.1.20 and 5.0.22	OS:	Any (*)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
if you use time_format as field to group on, mysql cuts off the value.

select TIME_FORMAT(sec_to_time(a),"%H:%i:%s") from (select 3020399 as a UNION select 3020398 ) x;
select TIME_FORMAT(sec_to_time(a),"%H:%i:%s") from (select 3020399 as a UNION select 3020398 ) x group by 1;

this is (so far) not related to bug #20292 as both values are in the allowed range frothe time data type.

the cut-off even happens if you enclose the result into something like concat:
 select concat(TIME_FORMAT(sec_to_time(a),"%H:%i:%s"),'') from (select 3020399 as a UNION select 3020398 ) x group by 1;

Currently (and here might be a relation to  bug #20292 ) TIME_FORMAT does also format values outside the range of the time-type:

 select TIME_FORMAT(sec_to_time(a),"%H:%i") from (select 3020399 as a UNION select 3020400 UNION select 4000000 UNION select 4000001) x;

works fine. (with the exception that you can't group on it)

How to repeat:
mysql> select TIME_FORMAT(sec_to_time(a),"%H:%i:%s") from (select 3020399 as a UNION select 3020398 ) x;
+----------------------------------------+
| TIME_FORMAT(sec_to_time(a),"%H:%i:%s") |
+----------------------------------------+
| 838:59:59                              |
| 838:59:58                              |
+----------------------------------------+
2 rows in set (0.00 sec)

mysql> select TIME_FORMAT(sec_to_time(a),"%H:%i:%s") from (select 3020399 as a UNION select 3020398 ) x group by 1;
+----------------------------------------+
| TIME_FORMAT(sec_to_time(a),"%H:%i:%s") |
+----------------------------------------+
| 838:59:5                               |
+----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
as in bug #20292, some currently working queries relate on the fact that the function handle values outside the reange they are intended to handle.

It would be good, if this behaviour could be saved

Thank you for a bug report. Verified just as described with 5.0.23-BK (ChangeSet@1.2171, 2006-06-06 11:25:31+05:00) and 4.1.21-BK (ChangeSet@1.2453.44.8, 2006-06-01 08:24:05+02:00) on Linux.

This bug is not reproducible on 5.0.27 and up.

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/18788

ChangeSet@1.2380, 2007-01-25 19:58:04+03:00, kaa@polly.local +2 -0
  Added a test case for bug #20293 "group by cuts off value from time_format"

pushed to 5.0.38, 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.

When a TIME_FORMAT() expression was used as a column in a GROUP BY
clause, the expression result was truncated.