MySQL Bugs: #10966: Variance functions return wrong data type

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:	None
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

Description:
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
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Thank you for the bug report.

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:

  http://lists.mysql.com/internals/32533

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.

Pushed to 5.0.25

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.

Documented in 5.0.25 changelog.

Merged into 5.1.12

Noted in 5.1.12 changelog.