Bug #34942 AVG() returns wrong result in some cases
Submitted: 28 Feb 2008 21:07 Modified: 28 Feb 2008 21:15
Reporter: Christoph Ziegenberg Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.23-rc-community-log OS:Windows (XP Pro SP2)
Assigned to: CPU Architecture:Any
Tags: AVG calculation average

[28 Feb 2008 21:07] Christoph Ziegenberg 
Description:
I tried to get the average value of two fields - this worked for multiple result rows (as far as I could see). But then I change the WHERE condition so that only on row was returned - and the calculated average was obviously wrong.

Not using the field but entering the same value directly to the query returns the expected result.

How to repeat:
CREATE TABLE avg_test (value_a float NOT NULL, value_b float NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO avg_test VALUES (52.265, 10.523);
SELECT AVG(value_a), AVG(52.265), AVG(value_b), AVG(10.523) FROM avg_test;

Actual result:
52.264999389648, 52.2650000, 10.522999763489, 10.5230000

Expected:
52.2650000, 52.2650000, 10.5230000, 10.5230000
[28 Feb 2008 21:15] 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

Please read about problems with FLOAT at http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html