Bug #30326 UNION returns incorrect results to 14 decimals
Submitted: 9 Aug 2007 1:24 Modified: 9 Aug 2007 7:49
Reporter: Jared S (Silver Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.46 OS:Windows (Vista)
Assigned to: CPU Architecture:Any
Tags: decimal places, FLOAT, GROUP BY, qc, UNION

[9 Aug 2007 1:24] Jared S 
Description:
Hi,

Have an issues where InnoDB engine is returning unexpected decimal places.  At a guess I would have to say there is a major rounding issue present in Win32 builds.

The problem is '7.70' becomes '7.69999980926514'

How to repeat:
-Download my small DB
-Run the following query

(SELECT 'U1' AS Ex0, id AS Ex1, SUM(price) AS Ex2
FROM `new table`
WHERE id = 1
group by id)
UNION ALL
(SELECT 'U2' AS Ex0, id AS Ex1, SUM(amount) AS Ex2
FROM `new table2`
WHERE id = 1
group by id)
[9 Aug 2007 1:26] Jared S 
Simple DB with Sales and Payments tables

Attachment: TestDB.sql (application/octet-stream, text), 2.00 KiB.
[9 Aug 2007 7:49] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

price and amount in your example are defined as float (`amount` float(9,2) NOT NULL default '0.00'). SUM in this case returns DOUBLE value. See also http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html and about rounding errors at http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html