Bug #95897 DROP COLUMN error on INVISIBLE UNIQUE INDEX that refers to constant expression
Submitted: 20 Jun 13:12 Modified: 26 Jun 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

[20 Jun 13:12] Manuel Rigger
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.
[20 Jun 13:35] Umesh Shastry
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh
[26 Jun 10:21] Nisha Gopalakrishnan
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.
[26 Jun 10:30] Manuel Rigger
Okay, thank you very much for the explanation and sorry for this wrong bug report!