Bug #19844 time_format in Union truncates values
Submitted: 16 May 2006 10:17 Modified: 12 Sep 2006 1:42
Reporter: Armin Lorenz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.15, 5.0bk OS:Microsoft Windows (W2K)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[16 May 2006 10:17] Armin Lorenz
Description:
I combine the time difference of 2 fields in one table with the time difference of 2 fields in another table. Everything is fine as long as the result is below 100. After that it truncates the last digit.
Currently i cannot change to a newer version. Maybe anyone can test it on the latest release.

How to repeat:
select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As Stunden

returns

120

(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As Stunden)
union
(select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As Stunden)

returns

12
[16 May 2006 11:17] Hartmut Holzgraefe
Veriefied on latest 4.1 and 5.0 sources using Linux
[16 May 2006 11:23] Hartmut Holzgraefe
mysqltest test case

Attachment: bug19844.tar.gz (application/x-gzip, text), 521 bytes.

[7 Aug 2006 11:02] 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/10111

ChangeSet@1.2537, 2006-08-07 13:02:28+02:00, tnurnberg@salvation.intern.azundris.com +3 -0
  Bug#19844: time_format in Union truncates values
  
  time_format() claimed %H and %k would return at most two digits
  (hours 0-23), but this coincided neither with actual behaviour
  nor with docs.  this is not visible in simple queries; forcing
  a temp-table is probably the easiest way to see this.  adjusted
  the return-length appropriately; the alternative would be to
  adjust the docs to say that behaviour for > 99 hours is undefined.
[10 Aug 2006 13:37] 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/10266

ChangeSet@1.2537, 2006-08-10 15:37:24+02:00, tnurnberg@salvation.intern.azundris.com +3 -0
  Bug#19844: time_format in Union truncates values
  
  time_format() claimed %H and %k would return at most two digits
  (hours 0-23), but this coincided neither with actual behaviour
  nor with docs.  this is not visible in simple queries; forcing
  a temp-table is probably the easiest way to see this.  adjusted
  the return-length appropriately; the alternative would be to
  adjust the docs to say that behaviour for > 99 hours is undefined.
  ---
  Bug#19844: time_format in Union truncates values
  
  time_format() claimed %H and %k would return at most two digits
  (hours 0-23), but this coincided neither with actual behaviour
  nor with docs.  this is not visible in simple queries; forcing
  a temp-table is probably the easiest way to see this.  adjusted
  the return-length appropriately; the alternative would be to
  adjust the docs to say that behaviour for > 99 hours is undefined.
[11 Aug 2006 1:43] Tatiana Azundris Nuernberg
pushed to 4.1-maint for 4.1.22
[17 Aug 2006 2:46] Iggy Galarza
Available in 5.0.25
[12 Sep 2006 1:42] Paul Dubois
Noted in 4.1.22, 5.0.25 changelogs.

For TIME_FORMAT(), the %H and %k format specifiers can return values
larger than two digits (if the hour is greater than 99), but for some
query results that contained three-character hours, column values
were truncated.