Bug #6374 Silent conversion of data types in aggregate functions

• Submitted: 1 Nov 2004 23:26
• Modified: 2 Nov 2004 4:26
• Reporter: Chris Tucker
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.x
• OS: Linux (Linux)
• Assigned to: Matthew Lord
• CPU Architecture: Any

[1 Nov 2004 23:26] Chris Tucker

Description:
Using aggregate functions to select values out of a DECIMAL column causes a silent conversion of the data type to DOUBLE, leading to a loss of precision.  For example, the standard deviation of a single value (0.2) computed as an aggregate function on a DECIMAL column returns a non-zero value.

How to repeat:
CREATE TABLE a ( a DECIMAL(40,30) UNSIGNED ZEROFILL NOT NULL );
INSERT INTO a
VALUES
  (0.2);
SELECT MIN(a), MAX(a), STDDEV(a) FROM a;

Suggested fix:
Either update the documentation to indicate the expected behavior of aggregate functions with regards to data types, or preferably correct aggregate functions to work without modifying column data types.

[2 Nov 2004 4:26] Matthew Lord

Hi Chris,

Precision math is one of the flagship features of our upcoming 5.0 release.  Currently all
math is done using doubles so this is the reason for what you see.

You can see information about how things are currently done here:
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html

"All arithmetic is done using signed BIGINT or DOUBLE values"
"FLOAT might give you some unexpected problems because all calculations in MySQL are done 
with double precision"

We do feel that this is important functionality and that is why it will be
in 5.0.

Thanks for your interest and your bug report!

Best Regards,