Bug #22006 Simple math using floats yielding bad precision results.

Description:
The server appears to handle precision poorly when performing math in a SQL statement. Consider this statement:

SELECT Item.ItemSize, Inventory.Units, Item.ItemSize * Inventory.Units FROM Inventory, Item WHERE Item.ItemKey = Inventory.ItemKey AND Inventory.ContInvKey='327';

Which returns these results:

1, 125, 125
1, 100, 100
1, 100, 100
10, 1, 10
2, 2, 4
2, 4, 8
2, 2, 4
0.001, 900000, 900.00004274771

Note the last row and the garbage decimal places. Should be just '900.0'.

This statement also yields the same error.

SELECT SUM( Item.ItemSize * Inventory.Units ) FROM Inventory, Item WHERE Item.ItemKey = Inventory.ItemKey AND Inventory.ContInvKey='327';

Returns: 1251.0000427477

**************
* The tables *
**************

CREATE TABLE `item` (
  `ItemKey` bigint(20) NOT NULL default '0',
  `ItemName` varchar(64) NOT NULL,
  `ItemDesc` varchar(1024) NOT NULL,
  `ItemCategoryKey` bigint(20) NOT NULL,
  `ItemCategorySubKey` bigint(20) NOT NULL,
  `ObjectTypeKey` bigint(20) NOT NULL,
  `ItemMass` float NOT NULL default '1',
  `ItemSize` float NOT NULL default '1',
  `IsContainer` tinyint(1) NOT NULL default '0',
  `IsEnabled` tinyint(1) NOT NULL default '1',
  `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ItemKey`),
  KEY `Item_IDX1` (`ItemName`),
  KEY `Item_IDX2` (`ItemCategoryKey`,`ItemCategorySubKey`),
  KEY `Item_IDX3` (`ObjectTypeKey`),
  CONSTRAINT `item_ibfk_1` FOREIGN KEY (`ItemCategoryKey`, `ItemCategorySubKey`) REFERENCES `itemcategorysub` (`ItemCategoryKey`, `ItemCategorySubKey`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `item_ibfk_2` FOREIGN KEY (`ObjectTypeKey`) REFERENCES `objecttype` (`ObjectTypeKey`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `inventory` (
  `InventoryKey` bigint(20) NOT NULL default '0',
  `ItemKey` bigint(20) NOT NULL default '0',
  `ObjectTypeKey` bigint(20) NOT NULL default '0',
  `ContInvKey` bigint(20) NOT NULL default '0',
  `ContAs` tinyint(4) NOT NULL default '0',
  `OwnerType` tinyint(4) NOT NULL default '0',
  `OwnerKey` bigint(20) NOT NULL default '0',
  `Units` float NOT NULL default '1',
  `QuadrantX` double NOT NULL default '0',
  `QuadrantY` double NOT NULL default '0',
  `QuadrantZ` double NOT NULL default '0',
  `SectorX` double NOT NULL default '0',
  `SectorY` double NOT NULL default '0',
  `SectorZ` double NOT NULL default '0',
  `PositionX` double NOT NULL default '0',
  `PositionY` double NOT NULL default '0',
  `PositionZ` double NOT NULL default '0',
  `OrientationW` double NOT NULL default '1',
  `OrientationX` double NOT NULL default '0',
  `OrientationY` double NOT NULL default '0',
  `OrientationZ` double NOT NULL default '0',
  `DateTimeCreated` datetime default NULL,
  `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`InventoryKey`),
  KEY `Inventory_IDX1` (`ItemKey`),
  KEY `Inventory_IDX2` (`ObjectTypeKey`),
  KEY `Inventory_IDX3` (`ContInvKey`),
  KEY `Inventory_IDX4` (`OwnerType`,`OwnerKey`),
  KEY `Inventory_IDX5` (`QuadrantX`,`QuadrantY`,`QuadrantZ`,`SectorX`,`SectorY`,`SectorZ`),
  CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`ItemKey`) REFERENCES `item` (`ItemKey`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `inventory_ibfk_2` FOREIGN KEY (`ObjectTypeKey`) REFERENCES `objecttype` (`ObjectTypeKey`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

How to repeat:
Create the tables (or similar tables) and use the select statement provided.

Suggested fix:
Check your C/C++ compiler 'Foating Point Model" settings and make sure "Precise (/fp:precise)" or maybe even "Strict (/fp:strict)" is being used.

Thank you for the bug report. Sorry but it is expected behavior and explained
in the Manual:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Use decimal data type instead.