MySQL Bugs: #98250: INFORMATION_SCHEMA.COLUMNS.COLUMN

Bug #98250	INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE is missing width information
Submitted:	16 Jan 2020 11:00	Modified:	16 Jan 2020 15:29
Reporter:	Ariel Mashraki	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Linux (Docker latest version)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The COLUMN_TYPE in INFORMATION_SCHEMA.COLUMNS table is missing precision information for numerics types in some cases. 
This behavior was introduced in the latest version (8.0.19) and broke backwards compatibility.

How to repeat:
On MySQL 8.0.19:

- Create the table:
create table t1(c1 tinyint(2));

- Read its column information from INFORMATION_SCHEMA:
SELECT `COLUMN_TYPE` FROM INFORMATION_SCHEMA.COLUMNS WHERE `TABLE_SCHEMA` = (SELECT DATABASE()) AND `TABLE_NAME` = "t1";

Result is:
+----------------+
| COLUMN_TYPE |
+----------------+
| tinyint                 |
+----------------+

However, running the same operations on MySQL 8.0.18 (or even 5.6.35)  returns:

+----------------+
| COLUMN_TYPE |
+----------------+
| tinyint(2)             |
+----------------+

Hello Ariel Mashraki,

Thank you for the report and feedback.

regards,
Umesh

- 8.0.19

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t1(c1 tinyint(2));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `COLUMN_TYPE` FROM INFORMATION_SCHEMA.COLUMNS WHERE `TABLE_SCHEMA` = (SELECT DATABASE()) AND `TABLE_NAME` = "t1";
+-------------+
| COLUMN_TYPE |
+-------------+
| tinyint     |
+-------------+
1 row in set (0.00 sec)

- 8.0.18

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table t1(c1 tinyint(2));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` tinyint(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> SELECT `COLUMN_TYPE` FROM INFORMATION_SCHEMA.COLUMNS WHERE `TABLE_SCHEMA` = (SELECT DATABASE()) AND `TABLE_NAME` = "t1";
+-------------+
| COLUMN_TYPE |
+-------------+
| tinyint(2)  |
+-------------+
1 row in set (0.01 sec)

Quoting from docs - As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. Support for ZEROFILL and display widths for integer data types will be removed in a future MySQL version.

If this is intended behavior in 8.0.19 then maybe update the docs confirming this because it still says display widths for integer data types will be removed in a future MySQL version"? Thank you.

Posted by developer:
 
The number 2 in tinyint(2) is not precision information. It is display width as documented here: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html
It is used to left-pad numbers with 0 up to the specified number of digits.
So for example, if 1 is stored in a tinyint(2) column, it would be displayed by the mysql client as 01 so that it is two digits wide.
However, this only happens if the column was also declared as ZEROFILL. So (2) in tinyint(2) has no effect.
This is why integer display width has been deprecated and also removed from information_schema output unless the column is ZEROFILL.

Update synopsis

Thanks for the explanation Ståle Deraas, and sorry for my confusion. I’ve used the wrong word.
Another question if you don’t mind - why tinyint(1) is returned with its width?

Posted by developer:
 
Hi Ariel, the TINYINT(1) still returns the displaywidth, because it turned out that some connectors use this information to deduce a column type that was specified as BOOLEAN. We see this situation as temporary and will eventually remove the displaywidth even for TINYINT(1).

I assumed it because of that. I maintain an entity framework (for MySQL), and need to distinguish between int8 and boolean in some cases.

I understand it's "Not a Bug" (as described in the status of this issue), but I think, it is a breaking-change because it modifies the default behavior, and I suspect it's going to break many tools.

It seems that while the deprecated behaviour was restored for tinyint, it wasn't restored for tinyint unsigned. Not sure if this is worth fixing, but it's certainly worth documenting. Here's a reproducer for various versions of MySQL:

create table t (a tinyint(1), b tinyint(1) unsigned);

select
  version(),
  information_schema.COLUMNS.COLUMN_NAME,
  information_schema.COLUMNS.COLUMN_TYPE,
  information_schema.COLUMNS.DATA_TYPE
from information_schema.COLUMNS
where information_schema.COLUMNS.TABLE_NAME = 't'
order by information_schema.COLUMNS.ORDINAL_POSITION;

|version()|COLUMN_NAME|COLUMN_TYPE        |DATA_TYPE|
|---------|-----------|-------------------|---------|
|8.0.15   |a          |tinyint(1)         |tinyint  |
|8.0.15   |b          |tinyint(1) unsigned|tinyint  |

|version()|COLUMN_NAME|COLUMN_TYPE     |DATA_TYPE|
|---------|-----------|----------------|---------|
|8.0.22   |a          |tinyint(1)      |tinyint  |
|8.0.22   |b          |tinyint unsigned|tinyint  |

|version()|COLUMN_NAME|COLUMN_TYPE     |DATA_TYPE|
|---------|-----------|----------------|---------|
|8.0.27   |a          |tinyint(1)      |tinyint  |
|8.0.27   |b          |tinyint unsigned|tinyint  |