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

[6 Jun 2006 13:12] Martin Friebe
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
[6 Jun 2006 13:28] Valeriy Kravchuk
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.
[25 Jan 2007 16:55] Alexey Kopytov
This bug is not reproducible on 5.0.27 and up.
[25 Jan 2007 16:58] Bugs System
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"
[8 Mar 2007 22:12] Timothy Smith
pushed to 5.0.38, 5.1.17
[15 Mar 2007 3:10] Paul Dubois
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.