Bug #103155 Wrong Field Type For Numeric Precision and Numeric Scale

• Submitted: 30 Mar 2021 19:17
• Modified: 19 Apr 2021 9:52
• Reporter: Harshit Gangal
• Status: Duplicate
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0.19, 8.0.23
• OS: Ubuntu
• CPU Architecture: Any
• Tags: regression

[30 Mar 2021 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 2021 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 2021 7:59] MySQL Verification Team

Hello Harshit,

Thank you for the report and test case.

regards,
Umesh

[19 Apr 2021 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