MySQL Bugs: #3693: STDDEV() returns non-zero for set of identical values

Bug #3693	STDDEV() returns non-zero for set of identical values
Submitted:	8 May 2004 3:34	Modified:	8 May 2004 18:09
Reporter:	Matthias Fripp	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.17-nt	OS:	Windows (Windows XP)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
The standard deviation of a set of identical numbers is 0. However, STDDEV() can return a small positive value when evaluated on a set of identical values. I suspect this is a rounding error, due to taking sums of x and x^2 during aggregation.

How to repeat:
# Create a table with a good number of records
# it doesn't matter what's in it; we just need some rows
create temporary table t1 (t int);
create temporary table t2 (t int);
insert into t1 values (1);
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
insert into t2 select * from t1;
insert into t1 select * from t2;
# Now calculate the STDDEV() with something that is guaranteed to be constant.
select stddev(7.424000) from t1;
# returns 0.0000004142
# should return 0

Suggested fix:
Correct the function to return 0 in this instance. It may also need to be fixed to give more accurate results for real distributions. I haven't checked that.

I suspect this is a rounding error. You may be able to fix the problem by rounding the stddev output to a shorter, more reliable number of digits before returning it. Or you may be able to change the stddev algorithm so it doesn't do so much summing of floating point numbers (I'm not sure how to do that). Or maybe there is some minor bug in the STDDEV code.

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

rounding error, indeed.
Nothing we can do here - fixed precision numbers are intrinsically inexact.