Bug #41886 max() and min() functions truncate result to 14 places on double columns
Submitted: 5 Jan 2009 20:53 Modified: 6 Jan 2009 11:54
Reporter: Eric George Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:4.1.22, 5.0.67 OS:Linux
Assigned to: CPU Architecture:Any
Tags: aggregate, GROUP BY, MAX, min, precision

[5 Jan 2009 20:53] Eric George
Description:
I've noticed that the min() and max() aggregate functions appear to truncate results at 14 places.

How to repeat:
For example:

CREATE TABLE junk (x double);
INSERT INTO junk VALUES(0.123456789012345);
SELECT x FROM junk;
+-------------------+
| x                 |
+-------------------+
| 0.123456789012345 |
+-------------------+

***** Notice 15 places displayed. *****

SELECT min(x) FROM junk;
+------------------+
| min(x)           |
+------------------+
| 0.12345678901234 |
+------------------+

***** Result incorrectly truncated to 14 places. *****

SELECT max(x) FROM junk1;
+------------------+
| max(x)           |
+------------------+
| 0.12345678901234 |
+------------------+

***** Result incorrectly truncated to 14 places. *****

It does seem to be doing things correctly internally:
INSERT INTO junk SELECT max(x) FROM junk;
SELECT x FROM junk1;
+-------------------+
| x                 |
+-------------------+
| 0.123456789012345 |
| 0.123456789012345 |
+-------------------+

***** all 15 places present *****

This is not a display issue in the command-line client.  I have done similar tests via Python with the same results.

Suggested fix:
max() and min() functions should return the full precision of the value.
[5 Jan 2009 21:02] Eric George
This is actually a rounding, not a truncation.  Taking the previous example...

INSERT INTO junk VALUES(0.123456789012349);

SELECT max(x) FROM junk1;
+------------------+
| max(x)           |
+------------------+
| 0.12345678901235 |
+------------------+
[6 Jan 2009 11:54] MySQL Verification Team
Thank you for the bug report. Please read the Manual at:

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
<cut>
M is the total number of digits and D is the number of digits following the decimal point. If M  and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.76-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE junk (x double);
Query OK, 0 rows affected (0.05 sec)

mysql 5.0 > INSERT INTO junk VALUES(0.123456789012345);
Query OK, 1 row affected (0.03 sec)

mysql 5.0 > SELECT x FROM junk;
+-------------------+
| x                 |
+-------------------+
| 0.123456789012345 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT min(x) FROM junk;
+------------------+
| min(x)           |
+------------------+
| 0.12345678901235 |
+------------------+
1 row in set (0.00 sec)

mysql 5.0 > alter table junk modify x double(16,15);
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0 > SELECT min(x) FROM junk;
+-------------------+
| min(x)            |
+-------------------+
| 0.123456789012345 |
+-------------------+
1 row in set (0.00 sec)

mysql 5.0 >