MySQL Bugs: #53814: NUMERIC_PRECISION for unsigned bigint field is 19, should be 20

Bug #53814	NUMERIC_PRECISION for unsigned bigint field is 19, should be 20
Submitted:	19 May 2010 16:28	Modified:	14 Oct 2010 15:26
Reporter:	Lawrenty Novitsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.48-bzr	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	NUMERIC_PRECISION bigint unsigned

Description:
NUMERIC_PRECISION in COLUMNS is 19 for both signed and unsigned bigint fields. However for unsigned bigint it should be 20.

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.42-community |
+------------------+

p.s. for COLUMNS shows COLUMN_TYPE bigint(21) unsigned for few own fields.

mysql> select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, NUMERIC_PRECISION from infor
mation_schema.columns where table_name='COLUMNS' and DATA_TYPE='bigint';
+------------+--------------------------+---------------------+-----------------
--+
| TABLE_NAME | COLUMN_NAME              | COLUMN_TYPE         | NUMERIC_PRECISIO
N |
+------------+--------------------------+---------------------+-----------------
--+
| COLUMNS    | ORDINAL_POSITION         | bigint(21) unsigned |                1
9 |
| COLUMNS    | CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned |                1
9 |
| COLUMNS    | CHARACTER_OCTET_LENGTH   | bigint(21) unsigned |                1
9 |
| COLUMNS    | NUMERIC_PRECISION        | bigint(21) unsigned |                1
9 |
| COLUMNS    | NUMERIC_SCALE            | bigint(21) unsigned |                1
9 |
+------------+--------------------------+---------------------+-----------------
--+
That 21 looks very suspicious for me :) I would appreciate if somebody tell me it's not a bug.

How to repeat:
mysql> create table ubig (b bigint unsigned not null);

mysql> select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, NUMERIC_PRECISION from infor
mation_schema.columns where table_name='ubig';
+------------+-------------+---------------------+-------------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE         | NUMERIC_PRECISION |
+------------+-------------+---------------------+-------------------+
| ubig       | b           | bigint(20) unsigned |                19 |
+------------+-------------+---------------------+-------------------+

0xFFFFFFFFFFFFFFFF= 18 446 744 073 709 551 615

Suggested fix:
return NUMERIC_PRECISION 20 for unsigned bigint fields

Thank you for the problem report. Verified just as described:

valeriy-kravchuks-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 2
Server version: 5.1.48-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 ubig(c1 bigint, c2 bigint unsigned);
Query OK, 0 rows affected (0.40 sec)

mysql> select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, NUMERIC_PRECISION from information_schema.columns where table_name='ubig';
+------------+-------------+---------------------+-------------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE         | NUMERIC_PRECISION |
+------------+-------------+---------------------+-------------------+
| ubig       | c1          | bigint(20)          |                19 |
| ubig       | c2          | bigint(20) unsigned |                19 |
+------------+-------------+---------------------+-------------------+
2 rows in set (0.05 sec)

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/111950

3438 Georgi Kodinov	2010-06-23
      Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 
      should be 20
      
      Fixed the numeric precision of the unsigned BIGINT column to 
      be 20 instead of 19.

The fix is correct.  Subtract 1 from the max precision only if the sign bit is used.  Patch approved

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/113690

3487 Georgi Kodinov	2010-06-23
      Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 
      should be 20
      
      Fixed the numeric precision of the unsigned BIGINT column to 
      be 20 instead of 19.

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/113764

3489 Georgi Kodinov	2010-07-16
      Addendum to bug #53814 : test results updates

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Noted in 5.5.6 changelog.

INFORMATION_SCHEMA.COLUMNS reported incorrect precision for BIGINT
UNSIGNED columns.

Setting report to Need Merge pending push to 5.1.x.

Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Added 5.1.51 changelog entry; no new changelog entries required. Closed.

Hi, I still see this bug  in version 5.5.13-log. 
Could anyone take a look?