ZEROFILL noop behavior is confusing, weird

[morgo]

Bug Report

TiDB does not support zerofill, see #6688 (this part is fine).

The problem is that the behavior is weird.

Credits to @espresso98 for the bug report.

1. Minimal reproduce step (Required)

CREATE TABLE t1 (a int, b int(10) zerofill, c int(300) zerofill);
SHOW CREATE TABLE t1\G
INSERT INTO t1 VALUES (1,1,1);
SELECT * FROM t1;

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

• MySQL refuses display lengths greater than 255
• Why is TiDB adding the unsigned attribute?
• Why is the zerofill persisted in metadata when it is not supported (nor planned). For other noops, it will warn on table creation and drop the attribute. Being able to read it back in show create table makes it appear like it is working. See fulltext for a better behavior example:

tidb> CREATE TABLE t2 (a varchar(200), fulltext(a));
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1214 | The used table type doesn't support FULLTEXT indexes |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

tidb> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb> CREATE TABLE t1 (a int, b int(10) zerofill, c int(300) zerofill);
Query OK, 0 rows affected (0.00 sec)

tidb> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(10) unsigned zerofill DEFAULT NULL,
  `c` int(300) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

tidb> INSERT INTO t1 VALUES (1,1,1);
Query OK, 1 row affected (0.00 sec)

tidb> SELECT * FROM t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.5.0-alpha-246-g8e273bdac
Edition: Community
Git Commit Hash: 8e273bdac21436aa64898440c01cafbc81bb9d89
Git Branch: master
UTC Build Time: 2022-02-03 17:37:30
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)