[#MDEV-25529] Auto-create: Pre-existing historical data is not partitioned as specified by ALTER

I have a system-versioned table with some historical data, e.g.

MariaDB [test]> show create table t \G

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

       Table: t

Create Table: CREATE TABLE `t` (

  `pk` int(11) NOT NULL,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`pk`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

1 row in set (0.000 sec)

MariaDB [test]> select year(row_end) y, count(*) from t for system_time all group by y;

+------+----------+

| y    | count(*) |

+------+----------+

| 2019 |        8 |

| 2020 |       12 |

| 2021 |        4 |

| 2038 |        2 |

+------+----------+

4 rows in set (0.001 sec)

Now I want to make it auto-partitioned, so I run ALTER TABLE:

MariaDB [test]> alter table t partition by system_time interval 1 year auto;

Query OK, 26 rows affected (0.017 sec)

Records: 26  Duplicates: 0  Warnings: 0

My expectation is that it will auto-create the partition structure suitable for the already existing historical data – that is, 2 or 3 historical partitions starting from the lowest row_end timestamp with 1 year interval. It doesn't happen; instead, the table gets 1 historical partition starting from the current time, and all historical data is put in there:

bb-10.6-midenok-MDEV-17554 b96b96f9f
MariaDB [test]> show create table t \G
************************* 1. row *************************
Table: t
Create Table: CREATE TABLE `t` (
`pk` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'2021-04-26 00:00:00' AUTO
PARTITIONS 2
1 row in set (0.000 sec)

MariaDB [test]> select count(*) from t partition (p0);
+----------+
\| count(*) \|
+----------+
\| 24 \|
+----------+
1 row in set (0.000 sec)

It is even semantically wrong, 2019- and 2020-year data shouldn't be in a partition with start time in 2021.

Admittedly non-auto partitioning would do it too, but it is somewhat natural to expect more intelligent action from "auto".

With LIMIT instead of INTERVAL, sufficient partitions aren't created either, but at least with LIMIT there is no "semantically wrong" aspect, the partition simply gets overflowed, which can happen anyway.

If the behavior is intentional, please document it (and in general, please make sure that the documentation says what happens upon ALTER TABLE .. PARTITION, as currently ~~MDEV-17554~~ description only mentions CREATE TABLE and DML).

[MDEV-25529] Auto-create: Pre-existing historical data is not partitioned as specified by ALTER Created: 2021-04-26 Updated: 2023-11-28
Status:	Stalled
Project:	MariaDB Server
Component/s:	Partitioning, Versioned Tables
Affects Version/s:	10.9
Fix Version/s:	10.11

[MDEV-25529] Auto-create: Pre-existing historical data is not partitioned as specified by ALTER Created: 2021-04-26 Updated: 2023-11-28