[#MDEV-14675] Questionable semantics of INTERVAL values in PARTITION BY system

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.

[MDEV-14675] Questionable semantics of INTERVAL values in PARTITION BY system_time: converted into seconds Created: 2017-12-16 Updated: 2018-02-22 Resolved: 2018-02-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Partitioning, Versioned Tables
Affects Version/s:	10.3.4
Fix Version/s:	10.3.5

[MDEV-14675] Questionable semantics of INTERVAL values in PARTITION BY system_time: converted into seconds Created: 2017-12-16 Updated: 2018-02-22 Resolved: 2018-02-22