Bug #28972 Why the precisoion of decimal is 17 when invoking getTypeInfo() method?
Submitted: 8 Jun 2007 10:18 Modified: 31 Aug 2007 14:20
Reporter: xiaojuan zhao Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:MySQL 4.1.18-nt OS:Windows
Assigned to: CPU Architecture:Any
Tags: decimal precision

[8 Jun 2007 10:18] xiaojuan zhao 
Description:
The column PRECISION in the resultSet of getTypeInfo means maximum precision in JDK1.4.2. We gain the decimal precision 17 by invoke this method using mysql jdbc 3.1.12. But in mysql reference manual,it says that:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows: 

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL allowed a range of 1 to 254.) 

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M. 

So I think the jdbc method getTypeInfo should return 254 not 17. 

How to repeat:
mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 4.1.18-nt | 
+-----------+ 
1 row in set (0.00 sec) 
mysql> create table m_dec4(col decimal(20,10)); 
Query OK, 0 rows affected (0.05 sec) 

mysql> insert into m_dec4 values(1234567891.1234567891); 
Query OK, 1 row affected (0.03 sec) 
mysql> insert into m_dec4 values(123456789.1234567890); 
Query OK, 1 row affected (0.05 sec) 

mysql> select *  from m_dec4; 
+-----------------------+ 
| col                  | 
+-----------------------+ 
| 1234567891.1234570000 | 
|  123456789.1234567900 | 
+-----------------------+ 
2 rows in set (0.00 sec) 

The result is so strange I could't explain,maybe MySQL is wrong.  

Suggested fix:
The jdbc method getTypeInfo() should return that the decimal maximum precision is 254,not 17.
[13 Jun 2007 16:30] Tonci Grgin 
Hi Xiaojuan and thanks for your report. This is a known problem, thus, verified as described.
[30 Aug 2007 20:49] Bugs System 
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/commits/33473
[30 Aug 2007 20:54] Bugs System 
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/commits/33474
[30 Aug 2007 20:57] Mark Matthews 
Fix will be available in 5.0.8.
[30 Aug 2007 20:58] Bugs System 
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/commits/33475
[31 Aug 2007 14:20] MC Brown 
A note has been added to the 5.0.8 changelog: 

DatabaseMetaData.getTypeInfo() for the types DECIMAL and NUMERIC will return a precision of 254 for server versions older than 5.0.3, 64 for versions 5.0.3-5.0.5 and 65 for versions newer than 5.0.5.
[6 Sep 2007 15:13] Bugs System 
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/commits/33832
[7 Sep 2007 14:04] Bugs System 
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/commits/33909
[3 Oct 2007 16:41] Bugs System 
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/commits/34839
[3 Oct 2007 16:46] Bugs System 
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/commits/34840
[3 Oct 2007 18:59] Bugs System 
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/commits/34855
[5 Oct 2007 18:53] Bugs System 
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/commits/35011
[11 Oct 2007 20:10] Bugs System 
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/commits/35407
[11 Oct 2007 20:24] Bugs System 
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/commits/35410
[11 Oct 2007 20:52] Bugs System 
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/commits/35414
[19 Nov 2007 0:57] Bugs System 
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/commits/38019
[19 Nov 2007 2:51] Bugs System 
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/commits/38024