Bug #84867 Incorrect math that use FLOAT datatype

• Submitted: 7 Feb 2017 17:23
• Modified: 7 Feb 2017 22:29
• Reporter: Andrey Trubin
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.5.8
• OS: Any
• CPU Architecture: Any

[7 Feb 2017 17:23] Andrey Trubin

Description:
There is a table 'Lead' that has 'AnnualRevenue' column. When we calculate it's value like 
AnnualRevenue + 100 we get inconsistent result: it gives the result as it was AnnualRevenue + 104.

How to repeat:
-- DDL
CREATE TABLE `lead` (
  `Name` varchar(250) DEFAULT NULL,
  `AnnualRevenue` float DEFAULT NULL,
  `NumberOfEmployees` int(11) DEFAULT NULL,
  `CreatedDate` datetime DEFAULT NULL,
  `ConvertedDate` datetime DEFAULT NULL,
  `Country` varchar(250) DEFAULT NULL,
  `PostalCode` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- DATA
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Jeremy Jackson', 129233000, 200, '2012-08-17 18:54:28.000', NULL, 'Canada', 'B3K 4X8');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('David Wolfe', 1822700000, 8000, '2012-11-06 21:35:06.000', NULL, 'United States', '19897-2910');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('John Gallagher', 295749000, 2416, '2012-11-30 23:40:25.000', NULL, 'United Kingdom', 'LA1 4YW');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Dave Jarrett', 0, 0, '2012-08-17 18:57:54.000', NULL, 'Canada', 'M9C 5L5');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Michael Gerber', 5025120, 500, '2012-12-04 20:40:02.000', NULL, 'United States', '44636-0206');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Carl Yates', 76290400, 180, '2012-08-17 18:49:02.000', NULL, 'Canada', 'B3K 5M1');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Fred Morley', 2552160, 21, '2012-11-14 16:38:46.000', NULL, 'Canada', 'B3J 3R7');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Michele Peveril', 30316900, 70, '2012-11-14 16:40:27.000', NULL, 'Canada', 'B3H 4P8');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Greg Condon', 5, 500, '2012-08-17 19:03:37.000', NULL, 'Canada', 'B3M 2J6');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Dick MacDonald', 40834600, 300, '2012-08-17 18:50:10.000', NULL, 'Canada', 'C1A 4Z1');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Christine Bishop', 1225030, 13, '2012-11-14 17:10:47.000', NULL, 'Canada', 'B3J 3Z3');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Debra Wong', 39685100, 250, '2012-07-31 17:07:07.000', NULL, 'United States', '20817');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Roselie Bright', 350000, 4, '2012-11-06 16:33:32.000', NULL, 'United States', '20857-0002');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Alex McComber', 0, 0, '2012-10-22 17:01:16.000', NULL, 'United States', '27515-3300');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Alex McComber', 0, 0, '2012-10-22 17:01:16.000', NULL, 'United States', '27514');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Alex McComber', 0, 0, '2012-10-22 17:01:18.000', NULL, 'United States', '27515-3300');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Tony England', 519477, 3, '2012-11-27 09:22:49.000', NULL, 'United Kingdom', 'RG7 4GA');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Jamie Reid', 1623200, 13, '2012-11-14 15:15:49.000', NULL, 'Canada', 'B2X 1R5');
INSERT INTO lead
(Name, AnnualRevenue, NumberOfEmployees, CreatedDate, ConvertedDate, Country, PostalCode)
VALUES('Darren Freeman', 60680100, 800, '2012-11-06 17:14:30.000', NULL, 'United States', '35243-2358');

SELECT `Lead`.`Name` AS `Name`, `Lead`.`AnnualRevenue` AS `AnnualRevenue`, `Lead`.`AnnualRevenue`+100 AS `intFunc` FROM `Lead` AS `Lead` LIMIT 20,10

Notice that the expression `Lead`.`AnnualRevenue`+100 gives incorrect numbers: generally value + 4.

[7 Feb 2017 22:29] MySQL Verification Team

Thank you for the bug report. Please read the below Manual item about float problems:

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html.

Use decimal type instead.