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
Tags: NUMERIC_PRECISION bigint unsigned
Triage: Triaged: D2 (Serious)

[19 May 2010 16:28] Lawrenty Novitsky
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
[20 May 2010 4:02] Valerii Kravchuk
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)
[23 Jun 2010 16:25] 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/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.
[14 Jul 2010 13:04] Kevin Lewis
The fix is correct.  Subtract 1 from the max precision only if the sign bit is used.  Patch approved
[15 Jul 2010 15:22] 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/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.
[16 Jul 2010 13: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/113764

3489 Georgi Kodinov	2010-07-16
      Addendum to bug #53814 : test results updates
[23 Jul 2010 12:22] Bugs System
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)
[23 Jul 2010 12:29] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[29 Jul 2010 19:28] Paul Dubois
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.
[19 Aug 2010 15:41] Bugs System
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)
[14 Oct 2010 8:27] Bugs System
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)
[14 Oct 2010 8:42] Bugs System
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)
[14 Oct 2010 8:57] Bugs System
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)
[14 Oct 2010 15:26] Jon Stephens
Added 5.1.51 changelog entry; no new changelog entries required. Closed.
[27 Feb 2013 6:21] Tracy Lu
Hi, I still see this bug  in version 5.5.13-log. 
Could anyone take a look?