Bug #21334 using format on whole number yields improper column width
Submitted: 28 Jul 2006 17:32 Modified: 28 Jul 2006 18:19
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.20 OS:Windows (XP)
Assigned to: CPU Architecture:Any
Tags: format numbers

[28 Jul 2006 17:32] [ name withheld ]
Description:
When I use format() with a whole number, the column is set to be too narrow, so it looks like there is no result.

How to repeat:
format(23,2) should return 23.00. The column is formatted so that it looks like a  single space.
[28 Jul 2006 17:43] Hartmut Holzgraefe
works fine for me, could you provide a test case showing
the 'wrong' behavior?

SELECT VERSION(), FORMAT(23,2), LENGTH(FORMAT(23,2));

+--------------+--------------+----------------------+
| VERSION()    | FORMAT(23,2) | LENGTH(FORMAT(23,2)) |
+--------------+--------------+----------------------+
| 4.0.28-debug | 23.00        |                    5 | 
+--------------+--------------+----------------------+

+--------------+--------------+----------------------+
| VERSION()    | FORMAT(23,2) | LENGTH(FORMAT(23,2)) |
+--------------+--------------+----------------------+
| 4.1.22-debug | 23.00        |                    5 | 
+--------------+--------------+----------------------+

+--------------+--------------+----------------------+
| VERSION()    | FORMAT(23,2) | LENGTH(FORMAT(23,2)) |
+--------------+--------------+----------------------+
| 5.0.25-debug | 23.00        |                    5 | 
+--------------+--------------+----------------------+

+-------------------+--------------+----------------------+
| VERSION()         | FORMAT(23,2) | LENGTH(FORMAT(23,2)) |
+-------------------+--------------+----------------------+
| 5.1.12-beta-debug | 23.00        |                    5 | 
+-------------------+--------------+----------------------+
[28 Jul 2006 17:56] [ name withheld ]
Screen show of format(23,2) formatting

Attachment: mysql_bug.jpg (image/jpeg, text), 56.57 KiB.

[28 Jul 2006 17:58] [ name withheld ]
I added a screen shot of the SQL you ran, so you can see it. It's a graphical error in the query browser, the data returned is just fine.
[28 Jul 2006 18:06] MySQL Verification Team
Showing output of format

Attachment: format.PNG (image/png, text), 14.86 KiB.

[28 Jul 2006 18:07] MySQL Verification Team
Thank you for the bug report. I was unable to repeat, please see attached
file.
[28 Jul 2006 18:16] [ name withheld ]
Weird. Another interesting aspect is that ROUND(23,2) works totally fine, it's just FORMAT(23,2) that collapses.

Oh well. I won't worry about it, since it doesn't seem like it's happening to others. Thanks for checking it out.
[28 Jul 2006 18:19] [ name withheld ]
Another tidbit worth noting is that when I run that statement against a different DB here (4.1.7-max-log) the column still compresses, but not as much (I can see the 2).