Bug #13017 NUMERIC/DECIMAL maximum precision,server behaviour contradicts manual
Submitted: 6 Sep 2005 16:54 Modified: 19 Sep 2005 17:10
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[6 Sep 2005 16:54] Matthias Leich
Description:
I assume the documentation is wrong:

The  server accepts a maximum precision(mantissa) of 65 
digits for columns of the data types DECIMAL and NUMERIC.

Example:

CREATE TABLE test.t1 (f1 BIGINT);
ALTER TABLE test.t1 ADD my_decimal_big DECIMAL(66,30);
ERROR 42000: Too big precision 66 specified for column 
                       'my_decimal_big'. Maximum is 65.
ALTER TABLE test.t1 ADD my_decimal_big DECIMAL(65,30);
# --> success

# But the manual says in
# 11.1.1. Overview of Numeric Types
# ....
# DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
#
# For MySQL 5.0.3 and above:
# ....
# The maximum number of digits (M) for DECIMAL is 64. 
#                                                                          ####

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1898.1.59, 2005-09-05

How to repeat:
Please execute the statements above.
[13 Sep 2005 22:37] Mike Hillyer
The Documenting status is for bugs that require changelog entries, not for documentation bugs. Changing to Open status.
[14 Sep 2005 22:29] Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot -pmyuser test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.13-beta-valgrind-max-debug

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

mysql> CREATE TABLE test.t1 (f1 BIGINT);
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test.t1 ADD my_decimal_big DECIMAL(66,30);
ERROR 1426 (42000): Too big precision 66 specified for column 'my_decimal_big'. Maximum is 65.
mysql> ALTER TABLE test.t1 ADD my_decimal_big DECIMAL(65,30);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For MySQL 5.0.3 and above: 

are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 64. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.
[14 Sep 2005 22:40] Paul Dubois
The max precision was indeed 64 when the section
in the manual was written. It appears to have changed
to 64 in MySQL 5.0.6.  I'm trying to find out if this
was deliberate or inadvertent.
[19 Sep 2005 17:10] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The change was deliberate. I've updated the manual
to reflect that the max precision for DECIMAL now is 65.