create table for invisible unique key with auto_increment should have same behavior with it's show create tables

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t0(a int auto_increment, unique key (a) invisible);
show create table t0;
CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  UNIQUE KEY `a` (`a`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

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

mysql> create table t0(a int auto_increment, unique key (a) invisible);
ERROR 3522 (HY000): A primary key index cannot be invisible

the sql of create table t0 should have same behavior with show create table result.

3. What did you see instead (Required)

MySQL root@127.0.0.1:test> create table t0(a int auto_increment, unique key (a) invisible);
                       ->  show create table t0;
                       ->  CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  UNIQUE KEY `a` (`a`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected
Time: 0.145s

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (\n  `a` int(11) NOT NULL AUTO_INCREMENT,\n  UNIQUE KEY `a` (`a`) /*!80000 INVISIBLE */\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set
Time: 0.010s
(3522, 'A primary key index cannot be invisible')

4. What is your TiDB version? (Required)

master( 6ebfe8a)

[wjhuang2016]

It's the expected behavior.
MySQL:

mysql> CREATE TABLE `t1` (  `a` int(11) NOT NULL AUTO_INCREMENT,  UNIQUE KEY `a` (`a`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
ERROR 3522 (HY000): A primary key index cannot be invisible

If a has not null, then it would be the implicit primary key in MySQL. To be compatible with MySQL, TiDB also forbids this usage.

[aytrack]

the problem is，

create table t0(a int auto_increment, unique key (a) invisible);

above sql has't the not null and can execute success, but show create table got the not null

MySQL > show create table t0\G
***************************[ 1. row ]***************************
Table        | t0
Create Table | CREATE TABLE `t0` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `a` (`a`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

while use the show create table info to create a new table will got A primary key index cannot be invisible error.

for mysql, an unique index with auto_increment but without not null, it will be return the error.

mysql> create table t0(a int auto_increment, unique key (a) invisible);
ERROR 3522 (HY000): A primary key index cannot be invisible

[aytrack]

If need to be compatible with MySQL, TiDB should forbids create table t0(a int auto_increment, unique key (a) invisible);, otherwise it should have same behavior with the show create table info

[wjhuang2016]

I get it.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.