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

Bug #20293	group by cuts off value from time_format
Submitted:	6 Jun 2006 15:12	Modified:	15 Mar 2007 4:10
Reporter:	Martin Friebe (Gold Quality Contributor) (SCA)
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1-BK, 5.0-BK, 4.1.20 and 5.0.22	OS:	Any (*)
Assigned to:	Alexey Kopytov	Target Version:

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.