Integer zerofill size attribute should not prevent type modification

[kolbe]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (t tinyint(4));
alter table t1 modify t tinyint(1);

2. What did you expect to see? (Required)

No data loss can occur with this statement, and it does not cause any change to data storage format. The number in parentheses after an integer data type is just an advisory number used for "zerofill" in the client.

This issue can cause problems during migration if a customer exploits the equivalence between TINYINT and BOOLEAN. When a customer creates a BOOLEAN column, it's implicitly converted to TINYINT(1). Because the "arguments" to integer types are irrelevant, they may be omitted during certain operations, in which case TINYINT(1) can be converted to TINYINT(4). If a customer tries to execute an ALTER TABLE that modifies this column that was originally BOOLEAN (but is now TINYINT(4)) and treats it again as a BOOLEAN, they'll get an error.

3. What did you see instead (Required)

mysql> create table t1 (t tinyint(4));
Query OK, 0 rows affected (0.07 sec)

mysql> alter table t1 modify t tinyint(1);
ERROR 8200 (HY000): Unsupported modify column: length 1 is less than origin 4

4. What is your TiDB version? (Required)

*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.7
Edition: Community
Git Commit Hash: ed939f3f11599b5a38352c5c160c917df3ebf3eb
Git Branch: heads/v4.0.7
UTC Build Time: 2020-09-29 08:02:09
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ghost]

Here is the test case to show that the display length has no impact on storage range:

DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (t tinyint(1));
CREATE TABLE t2 (t tinyint(4));
INSERT INTO t1 VALUES (100);
INSERT INTO t2 VALUES (100);
SELECT * FROM t1;
SELECT * FROM t2;

...

mysql> SELECT * FROM t1;
+------+
| t    |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+
| t    |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1395-g8fdd49ccc-dirty
Edition: Community
Git Commit Hash: 8fdd49cccd58f6c521f9b783e955dc8f94b7dfd2
Git Branch: debug-rejected-plans
UTC Build Time: 2020-10-20 04:15:15
GoVersion: go1.13.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

This is compatible with MySQL 5.7:

mysql [localhost:5731] {msandbox} (test) > SELECT * FROM t1;
+------+
| t    |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > SELECT * FROM t2;
+------+
| t    |
+------+
|  100 |
+------+
1 row in set (0.01 sec)

[ti-srebot]

Please edit this comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions