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)