Bug #19844 time_format in Union truncates values

• Submitted: 16 May 2006 10:17
• Modified: 12 Sep 2006 1:42
• Reporter: Armin Lorenz
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.15, 5.0bk
• OS: 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, [e-mail] +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, [e-mail] +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.