Bug #10966 Variance functions return wrong data type
Submitted: 30 May 2005 17:05 Modified: 15 Aug 2006 3:21
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[30 May 2005 17:05] Peter Gulutzan
According to the standard:
""If VAR_POP or VAR_SAMP is specified, then the declared type of the result is an
 implementation-defined approximate numeric type. If DT [the value you're getting
the variance of] is an approximate numeric type,  then the precision of the result is
not less than the precision of DT."
And, since STDDEV_POP and STDDEV_SAMP are derived from VAR_POP and VAR_SAMP,
they should be approximate numeric too.
And, since STDDEV and VARIANCE are just alternate names, they should be approximate
numeric too.
But MySQL decides they're exact numeric if I pass an exact numeric (e.g. DECIMAL) value.

How to repeat:
mysql> create table tv as select stddev(0);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tv;
| Table | Create Table                                                                                       |
| tv    | CREATE TABLE `tv` (
  `stddev(0)` decimal(6,4) default NULL
1 row in set (0.01 sec)
[30 May 2005 17:18] MySQL Verification Team
Thank you for the bug report.
[22 Nov 2005 14:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[10 Apr 2006 13:30] Magnus Blåudd
Looked at the patch and can see that it always return a "field_double", but the comment says "Always return Field_double if no grouping." Wouldn't it be appropriate to add a test showing that it can return some other field type when there is a group by?

Otherwise OK.
[3 Aug 2006 16:45] Magnus Blåudd
Pushed to 5.0.25
[4 Aug 2006 21:47] Timothy Smith
For documentation: the patch ensures that the variance() function always returns a DOUBLE value, never DECIMAL.  It used to depend on the type of the input value, which was incorrect.
[7 Aug 2006 6:40] Jon Stephens
Documented in 5.0.25 changelog.
[14 Aug 2006 20:57] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 3:21] Paul DuBois
Noted in 5.1.12 changelog.