Unfortunately, this is where the logic "exactly the same as setting the timestamp" breaks. We cannot create new partitions (DDL) in the middle of an INSERT (DML). Note that a multi-row insert might need to create new partitions for every row,

The row must go into the correct partition, if it exists, that's clear.
But when it doesn't — should it be a warning (as it there was no AUTO at all), an error, or nothing?

The main use case — mysqldump — creates tables with the correct number of partitions, so it won't be affected by that.

FWIW, mysqldump isn't great at it either. Yes, it will create the correct number of partitions, but for partitioning by LIMIT (and if mysqldump runs with default extended insert), it will distribute the records between the partitions with rather little regard to the actual LIMIT condition. It will put into each partition at least as many records as a single INSERT contains (based on net-buffer-length), which can be a lot more than LIMIT, depending on circumstances; or, if LIMIT is greater than INSERT number, up to <LIMIT + INSERT> records.

And since historical records in the dump aren't sorted by ROW END, it will put them into partitions pretty much randomly comparing to the order they were originally added.

But this is of course to be expected.

Another consequence:

--source include/have_partition.inc

CREATE TABLE t (a INT) WITH SYSTEM VERSIONING PARTITION BY system_time INTERVAL 1 YEAR (PARTITION p1 HISTORY, PARTITION ver_pn CURRENT);

SET SYSTEM_VERSIONING_INSERT_HISTORY= ON;

INSERT INTO t (a, row_start, row_end) VALUES (NULL, '2000-01-01', '2036-01-01');

ALTER TABLE t ADD PARTITION (PARTITION p2 HISTORY);

CHECK TABLE t;

# Cleanup

DROP TABLE t;

CHECK TABLE t;

Table	Op	Msg_type	Msg_text

test.t	check	error	Found a misplaced row

test.t	check	error	Partition p1 returned error

test.t	check	error	Upgrade required. Please do "REPAIR TABLE `t`" or dump/reload to fix it!

or, if LIMIT is greater than INSERT number, up to <LIMIT + INSERT> records.

I was too optimistic in this part. In fact, all inserted historical records are put into the first LIMIT partition, regardless its size and the amount/size of inserts. For mysqldump it means that even if it's run with --skip-extended-insert, it still puts all records into the first partition.

Even if partition to insert into is specified explicitly, it still refuses to use any other than the first partition:

MariaDB [db3]> create or replace table t (a int) with system versioning partition by system_time limit 10 partitions 3;

Query OK, 0 rows affected (0.182 sec)

MariaDB [db3]> insert into t partition (p1) (a,row_start,row_end) select seq,'2022-02-02','2023-03-03' from seq_1_to_20;

ERROR 1748 (HY000): Found a row not matching the given partition set

MariaDB [db3]> insert into t partition (p0) (a,row_start,row_end) select seq,'2022-02-02','2023-03-03' from seq_1_to_20;

Query OK, 20 rows affected (0.026 sec)

Records: 20  Duplicates: 0  Warnings: 0

MariaDB [db3]> insert into t partition (p1) (a,row_start,row_end) select seq,'2022-02-02','2023-03-03' from seq_1_to_20;

ERROR 1748 (HY000): Found a row not matching the given partition set