MySQL Bugs: #95897: DROP COLUMN error on INVISIBLE UNIQUE INDEX that refers to constant expression

Bug #95897	DROP COLUMN error on INVISIBLE UNIQUE INDEX that refers to constant expression
Submitted:	20 Jun 2019 13:12	Modified:	26 Jun 2019 10:30
Reporter:	Manuel Rigger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
Dropping the PRIMARY KEY column causes an "ERROR 3522 (HY000): A primary key index cannot be invisible" even though the relevant index does not refer to the PRIMARY KEY.

How to repeat:
CREATE TABLE t0(c0 INT, c1 INT PRIMARY KEY);
CREATE UNIQUE INDEX i0 ON t0((TRUE)) INVISIBLE;
ALTER TABLE t0 DROP c1; -- unexpected: ERROR 3522 (HY000): A primary key index cannot be invisible

Only i0 seems to be INVISIBLE:

SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS;
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i0         | NO         |
| PRIMARY    | YES        |
+------------+------------+

Minor variations cause the bug to not manifest. For example, by replacing (TRUE) in the index by c0, or by removing the UNIQUE in the index creation statement, the error no longer appears.

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

AS mentioned in the documentation:
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functio
nal-key-parts
If a table contains no primary key, InnoDB automatically promotes the first
UNIQUE NOT NULL index to primary key. This is not supported for UNIQUE NOT NULL indexes that have functional key parts. In the example mentioned in the report, an attempt to drop the column having the primary index is attempted. Hence during the ALTER, an attempt to promote the functional index is made resulting an error since it is not supported. As noticed, if the index is on a column i.e
non functional index, the promotion is successful and ALTER operation
succeeds. And for the case where non unique functional index is used, InnoDB does not attempt promotion since there are no UNIQUE index, instead relies of the hidden clustered index generated by itself.

Okay, thank you very much for the explanation and sorry for this wrong bug report!