Bug #103155 Wrong Field Type For Numeric Precision and Numeric Scale
Submitted: 30 Mar 19:17 Modified: 19 Apr 9:52
Reporter: Harshit Gangal Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0.19, 8.0.23 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: regression

[30 Mar 19:17] Harshit Gangal
Description:
NUMERIC_PRECISION and NUMERIC_SCALE is bigint unsigned in information_schema.columns table.

on running the query
select numeric_precision, numeric_scale from information_schema.columns where 1 != 1 
It returns the field type as Uint32 instead of Uint64

on running the query
select numeric_precision, numeric_scale from information_schema.columns where table_name = 'a'
It returns the field type as Uint64

These two types does not match.

Field   1:  `NUMERIC_PRECISION`
Catalog:    `def`
Database:   ``
Table:      `columns`
Org_table:  `COLUMNS`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 2
Decimals:   0
Flags:      UNSIGNED BINARY NUM 

Field   2:  `NUMERIC_SCALE`
Catalog:    `def`
Database:   ``
Table:      `columns`
Org_table:  `COLUMNS`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 2
Decimals:   0
Flags:      UNSIGNED BINARY NUM 

+-------------------+-----------------+-----------+------+-----+---------+-------+------------+---------+
| Field             | Type            | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------------+-----------------+-----------+------+-----+---------+-------+------------+---------+
| NUMERIC_PRECISION | bigint unsigned | NULL      | YES  |     | NULL    |       | select     |         |
| NUMERIC_SCALE     | bigint unsigned | NULL      | YES  |     | NULL    |       | select     |         |
+-------------------+-----------------+-----------+------+-----+---------+-------+------------+---------+

How to repeat:
1. connect using mysql client with --column-type-info as argument.

2. create table 
create table test.a (id bigint primary key);

3. select numeric_precision, numeric_scale from information_schema.columns where table_schema = 'test' and table_name = 'a';

The column info contains Column Type: LONG instead of LONGLONG
[31 Mar 6:19] Harshit Gangal
This is a regression from 5.7.29

mysql> select numeric_precision from information_schema.columns where 1 != 1;
Field   1:  `numeric_precision`
Catalog:    `def`
Database:   `information_schema`
Table:      `columns`
Org_table:  `COLUMNS`
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 0
Decimals:   0
Flags:      UNSIGNED NUM 

0 rows in set (0.06 sec)
[31 Mar 7:59] MySQL Verification Team
Hello Harshit,

Thank you for the report and test case.

regards,
Umesh
[19 Apr 9:09] MySQL Verification Team
Hello Harshit,

Discussed internally with Gopal and we concluded that, there are two issues reported here:
 A) "The column info contains Column Type: LONG instead of LONGLONG"  Analysis:  In MySQL 8.0, the data dictionary design uses 'int' to store NUMERIC_PRECISION and NUMERIC_SCALE metadata. Hence the value seen LONG with --column-type-info in client is correct. This is by design and hence not a bug.
 B) "NUMERIC_PRECISION and NUMERIC_SCALE is bigint unsigned in
    information_schema.columns table."  Analysis:
 We see 'bigint' instead of 'int' due to Bug #97038 and hence marking this report for now as duplicate of Bug #97038.
Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead.
Thank you for your interest in MySQL.

regards,
Umesh