Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3
-
None
Description
As of the moment of writing the report, the documentation says:
one can rotate partitions by time:
<example>
This will initially write the history to p0, in a week it'll switch to p1, and then to p2.
That is, history should first be written to the first historical partition, and after that the interval applies. It makes sense, because the table can be created long before the history actually starts appearing, so it wouldn't make sense to waste first partitions.
Instead, it seems that the interval is counted from partition creation time. The test below demonstrates it.
- create a partitioned table with a short interval, insert some (current) data;
- wait for the first interval to pass;
- delete some data to create historical rows;
=> see that they went to the second partition rather than first.
--source include/have_partition.inc
|
|
|
create or replace table t1 (i int) |
with system versioning |
partition by system_time interval 3 second ( |
partition p0 history,
|
partition p1 history,
|
partition pc current |
);
|
|
|
insert into t1 values (1),(2),(3); |
--echo # Everything is in partition pc for now
|
select * from t1 partition (pc); |
|
|
--echo # Wait for 3+ seconds
|
select sleep(4); |
|
|
delete from t1 where i = 1; |
--echo # The row should have gone to partition p0, but it doesn't:
|
select * from t1 partition (p0); |
|
|
--echo # It went to partition p1 instead:
|
select * from t1 partition (p1); |
|
|
|
|
# Cleanup
|
drop table t1; |
|
10.3 c39f8a80c9fe7f |
MariaDB [test]> --echo # The row should have gone to partition p0, but it doesn't:
|
MariaDB [test]> select * from t1 partition (p0);
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> --echo # It went to partition p1 instead:
|
MariaDB [test]> select * from t1 partition (p1);
|
+------+
|
| i |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
If it is indeed meant to work this way, please adjust documentation.