Just to be clear, the statement like the following one
"ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) FROM TABLE p1;"
borrowed from the task description doesn't imply dropping the table 'p1'.
In other words, after the statement be executed the table 'p1' does still exists and should be dropped explicitly if required.

this is a replacement for EXCHANGE PARTITION, so it should not copy the data, but simply rename the table to become a partition. If you think ADD PARTITION is confusing, use IMPORT PARTITION for example.

Yes, IMPORT/EXPORT look much better than ADD/EXTRACT

OTOH classical meaning of IMPORT/EXPORT implies keeping the original data. If we move data instead that is not quite user-intuitive for these terms. We can be straightforward for what we do by using RENAME keyword:

ALTER TABLE archive RENAME PARTITION p1 TO TABLE tp1;
ALTER TABLE archive RENAME TABLE tp1 TO PARTITION p1 VALUES LESS THAN (200);

I believe there is still no fast partition rename in MySQL/MariaDB, so we could make that as a bonus:

ALTER TABLE archive RENAME PARTITION p1 TO p11;

Btw, the last syntax is supported by Oracle.

I support the Aleksey's opinion that meaning of IMPORT/EXPORT implies keeping the original data.

On the other hand, I believe that the new syntax for adding a partition by virtue of moving a separate table to table's partition should be aligned with current syntax of ALTER TABLE in respect of adding partitions. Therefore from my point of view the following syntax would be more clear in expressing the idea of moving data from external table to table's partition:
ALTER TABLE ... ADD PARTITION (PARTITION ... ) MIGRATE FROM TABLE ...

For the sake of uniformity, the keyword MIGRATE could also be used in the ALTER statement for moving a partition to an external table:
ALTER TABLE ... MIGRATE PARTITION ... TO TABLE ...

Using the MIGRATE keyword looks like a nice idea. Keeping ADD PARTITION syntax compatibility is something that I also thought of. The downside of that is kind of longer syntax: two sequential PARTITION keywords. I hope we can make PARTITION inside the brackets optional.

What happens if the two tables are different in one of these aspects?

• Indexes
• Same visible indexes, but different IGNORED indexes
• INVISIBLE columns
• Compressed columns
• CHECK
• Type size (SMALLINT -> INT)
• NULL / NOT NULL
• Storage engine

Indexes differences are not unlikely, in my opinion.

What happens if the original table has triggers?

Somehow I don't see how the original

ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) FROM TABLE p1;

is worse or more confusing than

ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) MIGRATE FROM TABLE p1;

if anything, the former is shorter and only uses familiar keywords, it doesn't introduce a new keyword into the grammar

f_razzoli, this feature introduces a convenience command to replace the following sql script:

ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200));

ALTER TABLE archive EXCHANGE PARTITION p1 WITH TABLE p1;

DROP TABLE p1;

it does not allow to do anything that wasn't possible before and it should generally behave exactly as the script above. Also in regards to indexes, invisible columns, constraints, etc.

Please review bb-10.7-midenok-MDEV-22165

pushed together with MDEV-22166