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.
- relates to
-
MDEV-15346
Server crashes on Windows in MYSQLparse upon creating a table partitioned by system_time with certain intervals
-
-
Closed
{"report":{"fcp":7291.099999904633,"ttfb":6312.700000047684,"pageVisibility":"visible","entityId":64861,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0,"journeyId":"5351a815-1f03-4333-a461-8dd834b6902f","navigationType":0,"readyForUser":7384.399999856949,"redirectCount":0,"resourceLoadedEnd":7559.899999856949,"resourceLoadedStart":6317.399999856949,"resourceTiming":[{"duration":517.1000001430511,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":6317.399999856949,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":6317.399999856949,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":6834.5,"responseStart":0,"secureConnectionStart":0},{"duration":517.0999999046326,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":6317.700000047684,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":6317.700000047684,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":6834.799999952316,"responseStart":0,"secureConnectionStart":0},{"duration":525.8999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":6318,"connectEnd":6318,"connectStart":6318,"domainLookupEnd":6318,"domainLookupStart":6318,"fetchStart":6318,"redirectEnd":0,"redirectStart":0,"requestStart":6318,"responseEnd":6843.899999856949,"responseStart":6843.899999856949,"secureConnectionStart":6318},{"duration":563.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":6318.099999904633,"connectEnd":6318.099999904633,"connectStart":6318.099999904633,"domainLookupEnd":6318.099999904633,"domainLookupStart":6318.099999904633,"fetchStart":6318.099999904633,"redirectEnd":0,"redirectStart":0,"requestStart":6318.099999904633,"responseEnd":6881.299999952316,"responseStart":6881.299999952316,"secureConnectionStart":6318.099999904633},{"duration":567,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":6318.299999952316,"connectEnd":6318.299999952316,"connectStart":6318.299999952316,"domainLookupEnd":6318.299999952316,"domainLookupStart":6318.299999952316,"fetchStart":6318.299999952316,"redirectEnd":0,"redirectStart":0,"requestStart":6318.299999952316,"responseEnd":6885.299999952316,"responseStart":6885.299999952316,"secureConnectionStart":6318.299999952316},{"duration":567.3000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":6318.399999856949,"connectEnd":6318.399999856949,"connectStart":6318.399999856949,"domainLookupEnd":6318.399999856949,"domainLookupStart":6318.399999856949,"fetchStart":6318.399999856949,"redirectEnd":0,"redirectStart":0,"requestStart":6318.399999856949,"responseEnd":6885.700000047684,"responseStart":6885.700000047684,"secureConnectionStart":6318.399999856949},{"duration":567.3999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":6318.700000047684,"connectEnd":6318.700000047684,"connectStart":6318.700000047684,"domainLookupEnd":6318.700000047684,"domainLookupStart":6318.700000047684,"fetchStart":6318.700000047684,"redirectEnd":0,"redirectStart":0,"requestStart":6318.700000047684,"responseEnd":6886.099999904633,"responseStart":6886.099999904633,"secureConnectionStart":6318.700000047684},{"duration":664.3000001907349,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":6318.899999856949,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":6318.899999856949,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":6983.200000047684,"responseStart":0,"secureConnectionStart":0},{"duration":567.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":6319,"connectEnd":6319,"connectStart":6319,"domainLookupEnd":6319,"domainLookupStart":6319,"fetchStart":6319,"redirectEnd":0,"redirectStart":0,"requestStart":6319,"responseEnd":6886.5,"responseStart":6886.5,"secureConnectionStart":6319},{"duration":664.1999998092651,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":6319.200000047684,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":6319.200000047684,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":6983.399999856949,"responseStart":0,"secureConnectionStart":0},{"duration":562.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":6324.399999856949,"connectEnd":6324.399999856949,"connectStart":6324.399999856949,"domainLookupEnd":6324.399999856949,"domainLookupStart":6324.399999856949,"fetchStart":6324.399999856949,"redirectEnd":0,"redirectStart":0,"requestStart":6324.399999856949,"responseEnd":6887.099999904633,"responseStart":6887.099999904633,"secureConnectionStart":6324.399999856949},{"duration":871.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":6325.299999952316,"connectEnd":6325.299999952316,"connectStart":6325.299999952316,"domainLookupEnd":6325.299999952316,"domainLookupStart":6325.299999952316,"fetchStart":6325.299999952316,"redirectEnd":0,"redirectStart":0,"requestStart":6325.299999952316,"responseEnd":7197,"responseStart":7197,"secureConnectionStart":6325.299999952316},{"duration":1233.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":6325.399999856949,"connectEnd":6325.399999856949,"connectStart":6325.399999856949,"domainLookupEnd":6325.399999856949,"domainLookupStart":6325.399999856949,"fetchStart":6325.399999856949,"redirectEnd":0,"redirectStart":0,"requestStart":6325.399999856949,"responseEnd":7559.099999904633,"responseStart":7559.099999904633,"secureConnectionStart":6325.399999856949},{"duration":203.70000004768372,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":6996.299999952316,"connectEnd":6996.299999952316,"connectStart":6996.299999952316,"domainLookupEnd":6996.299999952316,"domainLookupStart":6996.299999952316,"fetchStart":6996.299999952316,"redirectEnd":0,"redirectStart":0,"requestStart":6996.299999952316,"responseEnd":7200,"responseStart":7200,"secureConnectionStart":6996.299999952316},{"duration":312.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":7247.099999904633,"connectEnd":7247.099999904633,"connectStart":7247.099999904633,"domainLookupEnd":7247.099999904633,"domainLookupStart":7247.099999904633,"fetchStart":7247.099999904633,"redirectEnd":0,"redirectStart":0,"requestStart":7247.099999904633,"responseEnd":7559.899999856949,"responseStart":7559.899999856949,"secureConnectionStart":7247.099999904633},{"duration":293.59999990463257,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":7284.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":7284.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":7578.099999904633,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":32,"domainLookupEnd":6079,"connectStart":6079,"connectEnd":6102,"secureConnectionStart":6089,"requestStart":6103,"responseStart":6312,"responseEnd":6320,"domLoading":6316,"domInteractive":7579,"domContentLoadedEventStart":7579,"domContentLoadedEventEnd":7619,"domComplete":7880,"loadEventStart":7880,"loadEventEnd":7880,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":7563.200000047684},{"name":"bigPipe.sidebar-id.end","time":7564},{"name":"bigPipe.activity-panel-pipe-id.start","time":7564.200000047684},{"name":"bigPipe.activity-panel-pipe-id.end","time":7565.200000047684},{"name":"activityTabFullyLoaded","time":7636.700000047684}],"measures":[],"correlationId":"849a46af6f9199","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":108,"dbReadsTimeInMs":29,"dbConnsTimeInMs":38,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
known bug