Bug #78659 Stddev_Samp function calculates wrongly when used within Views
Submitted: 1 Oct 2015 8:34 Modified: 1 Oct 2015 9:25
Reporter: Mark Hillier Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6 for Windows,5.1.77, 5.5.47, 5.6.28,5.7.10 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: create view, Sample Standard Deviation Function

[1 Oct 2015 8:34] Mark Hillier
Description:
The Sample Standard Deviation  
(Stddev_Samp) built in function works OK when used as a simple select 
query but when used as part of a view it returns the population 
standard deviation.

I am using Version 5.6 for Windows downloaded about a month ago.
But a colleage has observered this problem on an older  Linux version.

How to repeat:
In this example MyValue contains the numbers 1,2,3,4 & 5. 
The Select query below correctly finds the mean, population
and sample standard deviations. 

mysql> select avg(myvalue), stddev_pop(myvalue), stddev_samp(myvalue) from mytable;
+--------------+---------------------+----------------------+
| avg(myvalue) | stddev_pop(myvalue) | stddev_samp(myvalue) |
+--------------+---------------------+----------------------+
|            3 |  1.4142135623730951 |   1.5811388300841898 |
+--------------+---------------------+----------------------+
1 row in set (0.00 sec)

But when the function is made into a view then it returns the population 
value when executed as shown below.

mysql> Create view myView as 
select avg(myvalue), stddev_pop(myvalue), stddev_samp(myvalue) from mytable;

mysql> select * from myview;
+--------------+---------------------+----------------------+
| avg(myvalue) | stddev_pop(myvalue) | stddev_samp(myvalue) |
+--------------+---------------------+----------------------+
|            3 |  1.4142135623730951 |   1.4142135623730951 |
+--------------+---------------------+----------------------+
1 row in set (0.00 sec)
[1 Oct 2015 9:25] Umesh Shastry
Hello Mark Hillier,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh