The reason for this is that while the DROP COLUMN will correctly imply DROP INDEX, the ADD COLUMN will unexpectedly imply ADD INDEX. The new column b should not inherit anything from the old column b!
If we split the ADD COLUMN clause into a separate ALTER TABLE statement, then the table will be altered as I would expect, with no index on the new column b. I can also work around this by adding a (redundant) clause DROP INDEX b. Likewise, if I use a different column name c for the new column, the operation will succeed:
If I change the column type to TEXT, it gets even more weird. The following test produces the same weird results already on 10.1 (the first still-supported version of MariaDB Server that uses the ALTER TABLE API that was introduced in MySQL 5.6):
This will create a UNIQUE INDEX (b(4)), instead of (also wrongly) returning an error that only a prefix index on TEXT columns may be defined. We never specified any column prefix for the index; it was b INT UNIQUE.
Even if we assumed that it is correct to inherit the attributes from an old column that had the same name, I think that an error
should be returned, just like for this case: