Bug #61329 manual conains a bad paragraph on decimal/numeric precision
Submitted: 27 May 2011 15:17 Modified: 31 May 2011 18:55
Reporter: Stephen Dewey Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1 and maybe others OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: decimal, documentation, numeric, precision

[27 May 2011 15:17] Stephen Dewey
Description:
The MySQL manual states:

"The SQL standard requires that the precision of NUMERIC(M,D) be exactly M digits. For DECIMAL(M,D), the standard requires a precision of at least M digits but permits more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are the same, and both have a precision of exactly M digits. "

on: http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

However, the precision is not "exactly" M, it is rather a maximum of M. This paragraph should be fixed to reduce confusion.

How to repeat:
see above

Suggested fix:
see above
[27 May 2011 17:34] Valeriy Kravchuk
Please, provide some example that illustrates your point. I do not see any problem based on the following example:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table td(c1 decimal(7,6));
Query OK, 0 rows affected (0.56 sec)

mysql> insert into td values(3.1415927);
Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1265
Message: Data truncated for column 'c1' at row 1
1 row in set (0.03 sec)

mysql> select * from td;
+----------+
| c1       |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

mysql> alter table td modify c1 decimal(8,7);
Query OK, 1 row affected (0.26 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from td;
+-----------+
| c1        |
+-----------+
| 3.1415930 |
+-----------+
1 row in set (0.00 sec)

mysql> insert into td values(3.1415927);
Query OK, 1 row affected (0.02 sec)

mysql> select * from td;
+-----------+
| c1        |
+-----------+
| 3.1415930 |
| 3.1415927 |
+-----------+
2 rows in set (0.00 sec)

Am I missing something?
[27 May 2011 17:48] Stephen Dewey
Well what I was thinking of was:

CREATE TABLE td2(c1 DECIMAL(10,6));
INSERT INTO td2 VALUES(3.1415927);

There you get a value with 7 digits, although you have specified 10. So I was thinking that this is not "exactly" 10. However, now that I think about it more, precision is defined as a "maximum number of digits," so in this case the precision may theoretically be "exactly 10" although in practice it is only 7.

Perhaps this is not a bug.
[31 May 2011 18:53] Paul DuBois
The column has a precision of exactly 10. Your particular number, however, does not. Values permitted in the column have up to 6 decimals (the scale) and 10 digits total (the precision), i.e., up to 4 digits to the left of the decimal. Your value has only 1 such digit.

You're right, this is not a bug.
[31 May 2011 18:55] Stephen Dewey
Cool -- thanks for the follow-up.