In the following test case (which depends on the MDEV-13134 ALGORITHM=NOCOPY syntax and the MDEV-15562 instant DROP COLUMN), the ALTER TABLE is being unexpectedly refused on MariaDB 10.4:

--source include/have_innodb.inc

CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;

ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b INT NOT NULL DEFAULT 0,

ALGORITHM=NOCOPY;

DROP TABLE t1;

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:

--source include/have_innodb.inc

CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;

ALTER TABLE t1 DROP COLUMN b, ADD COLUMN c INT NOT NULL DEFAULT 0,

ALGORITHM=NOCOPY;

SHOW CREATE TABLE t1;

DROP TABLE t1;

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):

CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE);

ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b TEXT;

SHOW CREATE TABLE t1;

DROP TABLE t1;

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.

CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE);

ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b TEXT;

SHOW CREATE TABLE t1;

Table	Create Table

t1	CREATE TABLE `t1` (

  `a` int(11) NOT NULL,

  `b` text,

  PRIMARY KEY (`a`),

  UNIQUE KEY `b` (`b`(4))

) ENGINE=MyISAM DEFAULT CHARSET=latin1

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

1170: BLOB/TEXT column 'b' used in key specification without a key length

should be returned, just like for this case:

CREATE TABLE t1(a INT PRIMARY KEY, b TEXT UNIQUE);