Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.4
-
None
Description
INTERVAL values other than SECOND in PARTITION BY system_time are converted into seconds.
It's fine as long as intervals are such that the number of seconds is deterministic (MINUTE, HOUR, DAY, WEEK), but it becomes strange with MONTH and YEAR.
CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM WITH SYSTEM VERSIONING |
PARTITION BY system_time INTERVAL 1 MONTH ( |
PARTITION p0 VERSIONING,
|
PARTITION pn AS OF CURRENT_TIMESTAMP |
);
|
MariaDB [test]> SHOW CREATE TABLE t1 \G |
*************************** 1. row ***************************
|
Table: t1 |
Create Table: CREATE TABLE `t1` ( |
`i` int(11) DEFAULT NULL, |
`sys_trx_start` timestamp(6) GENERATED ALWAYS AS ROW START, |
`sys_trx_end` timestamp(6) GENERATED ALWAYS AS ROW END, |
PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING |
PARTITION BY SYSTEM_TIME INTERVAL 2592000 SECOND |
(PARTITION `p0` VERSIONING ENGINE = MyISAM,
|
PARTITION `pn` AS OF CURRENT_TIMESTAMP ENGINE = MyISAM) |
1 row in set (0.00 sec) |
That is, 1 MONTH equals 30 days.
Usually the context for INTERVAL allows to keep its natural meaning, e.g. in DATE_ADD interval MONTH actually means a month, not 30 days:
MariaDB [test]> SELECT DATE_ADD('2012-02-20', INTERVAL 1 MONTH); |
+------------------------------------------+ |
| DATE_ADD('2012-02-20', INTERVAL 1 MONTH) | |
+------------------------------------------+ |
| 2012-03-20 |
|
+------------------------------------------+ |
1 row in set (0.00 sec) |
I think the different semantics in the system time partitioning clause can come unexpected for users, but I don't have a good suggestion for how it should work instead, and I couldn't find anything about it in the standard. I'm okay with it being just a documentation issue.
Attachments
Issue Links
- relates to
-
-
- Closed
-