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: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 8.0.16 | OS: | Ubuntu |
Assigned to: | CPU Architecture: | x86 |
[20 Jun 2019 13:12]
Manuel Rigger
[20 Jun 2019 13:35]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
[26 Jun 2019 10:21]
Nisha Padmini 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 2019 10:30]
Manuel Rigger
Okay, thank you very much for the explanation and sorry for this wrong bug report!