MySQL Bugs: #59907: Inconstant Averaging Math

Bug #59907	Inconstant Averaging Math
Submitted:	2 Feb 2011 22:46	Modified:	25 Mar 2011 13:28
Reporter:	Glenn Sams	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.77-log, 5.0.91, 5.1.54	OS:	Linux (CentOS 5.5)
Assigned to:		CPU Architecture:	Any
Tags:	average, avg, inconstant

Description:
I was just doing some report creation for our sales system. When I noticed some inconstant results from a AVG() call. I started to look into the data to see if there was a problem with some of the stored information. When running some additional queries i was able to determine it was a problem with MySQL server averaging math.

Here is some of my queries i used.

SELECT avg((total + prepaid) - tax_amount) from avgCheck ;
returned 28.243684

SELECT avg(monthly_due) from avgCheck;
returned 28.252401

SELECT count(monthly_due) from avgCheck;
returned 6480

select count(*) from avgCheck  WHERE monthly_due = ((total + prepaid) - tax_amount);
returned 6480

select count(*) from avgCheck  WHERE monthly_due <> ((total + prepaid) - tax_amount);
returned 0

How to repeat:
Create a Table:
CREATE TABLE `avgCheck` (                                  
              `id` int(11) NOT NULL auto_increment,    
              `total` decimal(11,2) default NULL,
              `prepaid` decimal(11,2) default NULL,
              `monthly_due` decimal(11,2) default NULL,                  
              `tax_amount` decimal(11,2) default NULL,    
              PRIMARY KEY  (`id`),                             
            ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1   

Insert a bunch a couple thousand of empty records:
INSERT INTO avgCheck(`total`, `monthly_due`,`tax_amount` VALUES
((RAND() * 10),0,0,0),((RAND() * 10),0,0,0),((RAND() * 10),0,0,0),......;

Fill in the prepaid column:
UPDATE avgCheck SET `prepaid` = (RAND() * 2) ORDER BY RAND() LIMIT 75 ;

Fill in the tax amount column:
UPDATE avgCheck SET `tax_amount` = (`monthly_due` * .1);

Fill in the total column:
UPDATE avgCheck SET `total` = ((`monthly_due` - `prepaid`) + `tax_amount`);

Then run the queries above and you will see avg() is not returning constant information when doing math in the function call. I've confirmed this on all version's i could get my hands on. 5.5, 5.1, ect

Thank you for the report.

I can not repeat described behavior. Please provide accurate minor version of MySQL server you are seeing the problem with.

OK, the version i first noticed this on was 5.0.77-log, but i then upgraded the server to 5.1.36 and the problem still took place.

Thank you for the feedback.

Both versions are old. Please upgrade to current version 5.0.91 or 5.1.54 and inform us if problem still exists.

The problem is still taking place even with those versions.

Thank you for the feedback.

Please send us your configuration file.

[mysqld]
max_allowed_packet=16M
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
auto_increment_increment=2

old_passwords=1
big-tables
innodb_file_per_table = 1
server-id=1
log-bin
max_connections = 100000
set-variable = net_buffer_length=512M
set-variable = max_allowed_packet=90M
set-variable = myisam_sort_buffer_size=512M
set-variable = sort_buffer=512M
set-variable = key_buffer=512M

master-host = ********
master-user = ********
master-password = ********
master-port = 3306

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Hi Glenn.

I expect some total,prepaid or tax_amount is null in the row where monthly_due is not null. It could explain difference in the avg result.

Could you please run:

 select count(*) from avgCheck  WHERE monthly_due is not NULL;

and

 select count(*) from avgCheck  WHERE  ((total + prepaid) - tax_amount) is not NULL;

Initial posts explains the differences in the results. very slight differences but unless you know the results should match you would not notice it.

SELECT avg((total + prepaid) - tax_amount) from avgCheck ;
returned 28.243684

SELECT avg(monthly_due) from avgCheck;
returned 28.252401

As for the counts with nulls:
Could you please run:

select count(*) from avgCheck  WHERE monthly_due is not NULL;
returned 6480

 select count(*) from avgCheck  WHERE  ((total + prepaid) - tax_amount) is not NULL;
returned 6480

If needed I can provide a dump of the live table. If needed.

Thank you for the feedback.

> If needed I can provide a dump of the live table. If needed.

Yes, please do it: I could not repeat the issue with test data.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".