My expectation would be for a key to be created. That is, if this

alter table b DROP KEY transaction_id, ADD UNIQUE KEY (transaction_id);

succeeds, then this

alter table b DROP KEY transaction_id, ADD UNIQUE KEY IF NOT EXISTS (transaction_id);

must succeed too and the key should be created.

But I think it's not a major issue. And this behavior is pretty difficult to achieve in the current implementation.

So, we consider fixing this bug, but not in the next few releases.

from MDEV-21176:

MariaDB [test]> create table t1 (id int, key (id));

Query OK, 0 rows affected (0.042 sec)

MariaDB [test]> alter table t1 drop key if exists id, add key if not exists (id);

Query OK, 0 rows affected, 1 warning (0.017 sec)

Records: 0  Duplicates: 0  Warnings: 1

Note (Code 1061): Duplicate key name 'id'

MariaDB [test]> show create table t1;

+-------+----------------------------------------------------------------------------------------+

| Table | Create Table                                                                           |

+-------+----------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

Any ETA on this bug getting fixed?

Any progress on this?

I've started to look at this. It seems this can be done during the parsing of the ALTER TABLE statement alone without looking at the table structure..

An ADD KEY/COLUMN/FOREIGN KEY/CONSTRAINT (intentionally leaving off PARTITIONS for now), IF NOT EXISTS id during parsing time needs to search through alter_info.drop_list for an element of the same name/type. When the mirror pair is found, the IF NOT EXISTS ddl_option can be cleared from the parsing structure of the ADD as its known that the condition is true without needing to examine the table structure.

A DROP after an ADD, is syntactical odd (why would you drop something just added), because I think all DROPs are considered before ADDs. So this just needs do the above process is reverse. So for any DROP entry, find an ADD and clear the IF NOT EXISTS flag on it if there is one.

An edge case that needs to be considered is the double ADD or double DROP. e.g. DROP KEY bob, ADD KEY bob(bob) ADD KEY IF NOT EXISTS bob(bob) cannot sanely drop the IF NOT EXISTS flag on the second key add. It could however drop the entire clause and push a note/warning like it currently does and eliminate the element from the parse structures.

For COLUMNS, CHANGE/RENAME COLUMN maybe needs accounting for {{ rename column id to ed, drop column if exists ed}} seems to rename rather than drop. From 10.5 RENAME KEY needs to be accounted for.

So I've started down this path.

danblack, all IF [NOT] EXISTS in ALTER TABLE are processed in one function. Might be not to difficult to add a logic there like "if ADD KEY IF NOT EXISTS and the key exists but it's also present in the keys-to-drop list, then it's ok"