ALTER TABLE converts the table atomically from a structure A to a structure B, it does not execute individual sub-commands sequentially. If you want them to be executed sequentially, one after the other, you can do them in separate ALTER TABLE statements, but this will be, of course, slower.

While what you say is correct about the atomicity, the behaviour is still inconsistent and I disagree that this is not a bug.
The behaviour of this alter table is even different depending if the column is there or not (if it's not there, it will work properly).
You should at a minimum treat this as an error and not a warning.

I'm not sure I understand the problem.

alter table testtable

    drop column if exists col1b,

    add column if not exists col1b int not null after col1;

the statement says "drop column if exists" — it exists? yes — so it's scheduled for dropping.
the statement says "add column if not exists" — it exists? yes — so it's not scheduled for adding, instead a warning is issued.

It seems like it works as expected.

Well, you said above "ALTER TABLE does not execute individual sub-commands sequentially" but it's not true.
The subcommands are definitely run sequentially, the column is first dropped then recreated. Isn't that sequential?
I agree that the result is atomic (all or nothing) but the sequence is there nonetheless.

The confusion arises because the user thinks sequentially when running something like "drop column c, add column c int",
while the existence checks in server are all run with table definition that exists before the alter begins, and do not consider the situation at the point in time where the
check is actually encountered in the alter statement.

No, commands aren't executed sequentially. Instead, server applies all DROP operation first, then all ADD operations, it doesn't depend on the order they were specified in the statement. Try

ALTER TABLE testtable ADD COLUMN new_column INT, DROP COLUMN new_column;

If this was executed sequentially, the column would've been added and then immediately dropped. But instead it'll be an error "new_column doesn't exist", because the server will try to drop first.

Still you're right — the user user thinks sequentially. It's not great that the actual execution doesn't follow user's intuitive expectations, it's confusing.

I'm not saying it's ideal, I'm just saying it's not a bug (that is, not a coding mistake) but an intentionally implemented behavior. It would've been better to re-implement ALTER TABLE to apply sub-commands sequentially, as specified in the statement. It will be a rather big task and an incompatible change in behavior that will likely break numerous existing applications. But the end result will be more intuitive indeed.

All DROPs first? Interesting choice. I was not aware of this!

So this behaviour confirms what you say... but doesn't do what it should if you ask me...

MariaDB [test]> show create table rick\G

*************************** 1. row ***************************

       Table: rick

Create Table: CREATE TABLE `rick` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `col1` int(11) DEFAULT NULL,

  `col2` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

1 row in set (0.000 sec)

MariaDB [test]> alter table rick add column col2 int, drop column col2;

Query OK, 0 rows affected (0.005 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table rick\G

*************************** 1. row ***************************

       Table: rick

Create Table: CREATE TABLE `rick` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `col1` int(11) DEFAULT NULL,

  `col2` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

1 row in set (0.000 sec)

I would expect to get an error on "add column" here and I don't, even if column exists.
This is because the DROP is run first. But it is confusing and not what one would expect.