MySQL Bugs: #33059: Loss of precision when selecting a FLOAT

Bug #33059	Loss of precision when selecting a FLOAT
Submitted:	7 Dec 2007 10:43	Modified:	7 Dec 2007 11:54
Reporter:	Robin Corps	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	Ver 14.12 Distrib 5.0.22	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FLOAT, loss, precision, SELECT

Description:
When selecting from a table with a column type defined as FLOAT with no defined precision mysql only returns the first 6 digits in a SELECT.

For example if you store 12345.67 in the column, when you select it will return 12345.7.  For me this is unexpected behaviour, I would expect it to return the number to the precision that it was entered with.  The number is actually being stored ok, if you return the number * 100 you get 1234567.

If you define the column as float(9,2) and perform the select you get the expected result of 12345.67.

I'm not 100% that this is a bug, and not just some strange default behaviour that I can't find documented.  Appologies if this is a duplicate, I did peform a search before hand.

How to repeat:
CREATE TABLE `test` (
  `cash` float NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` VALUES (12345.67);

SELECT `cash` FROM `test`;

Will result in:
+---------+
| cash    |
+---------+
| 12345.7 |
+---------+
1 row in set (0.00 sec)

ALTER TABLE `test` MODIFY `cash` float(9,2);

SELECT `cash` FROM `test`;

Will now result in:
+----------+
| cash     |
+----------+
| 12345.67 |
+----------+
1 row in set (0.00 sec)

Thank you for the bug report. This is an expected behavior, please read:
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html. Thanks in
advance.