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.
