MDEV-17553 Enable setting start datetime for interval partitioned history of system versioned tables

* Explicit STARTS syntax
* SHOW CREATE
* Default STARTS rounding depending on INTERVAL type
* Warn when STARTS timestamp is later than query time
* Fix uninitialized Lex->create_last_non_select_table under
  mysql_unpack_partition()

Default STARTS rounding depending on INTERVAL type

If STARTS clause is omitted, default one is assigned with value
derived from query timestamp. The rounding is done on STARTS value
depending on INTERVAL type:

SECOND: no rounding is done;
MINUTE: timestamp seconds is set to 0;
HOUR: timestamp seconds and minutes are set to 0;
DAY, WEEK, MONTH and YEAR: timestamp seconds, minutes and hours are
set to 0 (the date of rotation is kept as current date).

11 files changed, 563 insertions, 87 deletions

diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result
index 690617551de..77f7d067a53 100644
--- a/mysql-test/suite/versioning/r/partition.result
+++ b/mysql-test/suite/versioning/r/partition.result
@@ -1,3 +1,4 @@
+call mtr.add_suppression("need more HISTORY partitions");
 set system_versioning_alter_history=keep;
 # Check conventional partitioning on temporal tables
 create or replace table t1 (
@@ -266,11 +267,11 @@ x
 6
 insert into t1 values (7), (8);
 Warnings:
-Warning	4114	Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
 ### warn about full partition
 delete from t1;
 Warnings:
-Warning	4114	Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
 select * from t1 partition (p1) order by x;
 x
 4
@@ -293,16 +294,6 @@ partition p0 history,
 partition p1 history,
 partition pn current);
 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
-create or replace table t1 (x int)
-with system versioning
-partition by system_time interval 1 second starts 12345 (
-partition p0 history,
-partition p1 history,
-partition pn current);
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'starts 12345 (
-partition p0 history,
-partition p1 history,
-partition pn current)' at line 3
 create table t1 (i int) with system versioning
 partition by system_time interval 6 day limit 98
 (partition p0 history, partition ver_pn current);
@@ -546,7 +537,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `pk` int(11) DEFAULT NULL
 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
- PARTITION BY SYSTEM_TIME INTERVAL 7 SECOND
+ PARTITION BY SYSTEM_TIME INTERVAL 7 SECOND STARTS TIMESTAMP'2018-04-11 17:00:04'
 (PARTITION `ver_p1` HISTORY ENGINE = DEFAULT_ENGINE,
  PARTITION `ver_pn` CURRENT ENGINE = DEFAULT_ENGINE)
 #
@@ -564,7 +555,7 @@ insert into t1 values (0), (1), (2), (3);
 delete from t1 where x < 3;
 delete from t1;
 Warnings:
-Warning	4114	Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
 unlock tables;
 #
 # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table
diff --git a/mysql-test/suite/versioning/r/partition_rotation.result b/mysql-test/suite/versioning/r/partition_rotation.result
index 82558085c02..d0540b8d716 100644
--- a/mysql-test/suite/versioning/r/partition_rotation.result
+++ b/mysql-test/suite/versioning/r/partition_rotation.result
@@ -1,3 +1,5 @@
+set time_zone= "+00:00";
+call mtr.add_suppression("need more HISTORY partitions");
 set timestamp=unix_timestamp('2001-02-03 10:20:30');
 create or replace table t1 (i int) with system versioning
 partition by system_time interval 1 day
@@ -9,14 +11,14 @@ delete from t1;
 set timestamp=unix_timestamp('2001-02-04 10:20:50');
 insert t1 values (2);
 Warnings:
-Warning	4114	Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
 delete from t1;
 Warnings:
-Warning	4114	Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
 select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
 subpartition_name	partition_description	table_rows
-p1sp0	2001-02-04 10:20:30	1
-p1sp1	2001-02-04 10:20:30	1
+p1sp0	2001-02-04 00:00:00	1
+p1sp1	2001-02-04 00:00:00	1
 pnsp0	CURRENT	0
 pnsp1	CURRENT	0
 set timestamp=unix_timestamp('2001-02-04 10:20:55');
@@ -27,12 +29,12 @@ set timestamp=unix_timestamp('2001-02-04 10:30:10');
 update t1 set i=6 where i=5;
 select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
 subpartition_name	partition_description	table_rows
-p1sp0	2001-02-04 10:20:30	1
-p1sp1	2001-02-04 10:20:30	0
-p0sp0	2001-02-05 10:20:30	1
-p0sp1	2001-02-05 10:20:30	1
-p2sp0	2001-02-06 10:20:30	0
-p2sp1	2001-02-06 10:20:30	0
+p1sp0	2001-02-04 00:00:00	1
+p1sp1	2001-02-04 00:00:00	0
+p0sp0	2001-02-05 00:00:00	1
+p0sp1	2001-02-05 00:00:00	1
+p2sp0	2001-02-06 00:00:00	0
+p2sp1	2001-02-06 00:00:00	0
 pnsp0	CURRENT	0
 pnsp1	CURRENT	2
 ## pruning check
@@ -55,4 +57,253 @@ i
 explain partitions select * from t1 for system_time all where row_end = @ts;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1_p1sp0,p1_p1sp1	#	NULL	NULL	NULL	NULL	#	#
-drop table t1;
+## INTERVAL ... STARTS
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts 'a'
+(partition p0 history, partition pn current);
+ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '00:00:00'
+(partition p0 history, partition pn current);
+ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-00-01 00:00:00'
+(partition p0 history, partition pn current);
+ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts 946684800
+(partition p0 history, partition pn current);
+ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+# Test STARTS warning
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:01'
+(partition p0 history, partition pn current);
+Warnings:
+Warning	4164	`t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
+# Test default STARTS rounding
+set timestamp= unix_timestamp('1999-12-15 13:33:33');
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 second
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 SECOND STARTS TIMESTAMP'1999-12-15 13:33:33'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 minute
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 MINUTE STARTS TIMESTAMP'1999-12-15 13:33:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 hour
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'1999-12-15 13:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-15 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 month
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS TIMESTAMP'1999-12-15 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 year
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'1999-12-15 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+# seconds equivalent of 1 day does not round:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 86400 second
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 86400 SECOND STARTS TIMESTAMP'1999-12-15 13:33:33'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+# STARTS value is in local time_zone:
+set time_zone="+03:00";
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition pn current);
+Warnings:
+Warning	4164	`t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+create or replace table t2 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+set time_zone="+00:00";
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-31 21:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-31 21:00:00'
+(PARTITION `p0` HISTORY ENGINE = MyISAM,
+ PARTITION `pn` CURRENT ENGINE = MyISAM)
+# Test rotation
+set timestamp= unix_timestamp('2001-01-01 00:00:00');
+# it's ok to add partitions for past:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition p1 history, partition pn current);
+# we are warned when we push to present:
+insert into t1 values (0);
+Warnings:
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
+set timestamp= unix_timestamp('2001-01-01 00:00:01');
+update t1 set i= i + 1;
+Warnings:
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
+set timestamp= unix_timestamp('2001-01-01 00:00:02');
+update t1 set i= i + 1;
+Warnings:
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
+select *, row_end from t1 partition (p0);
+i	row_end
+select *, row_end from t1 partition (p1);
+i	row_end
+0	2001-01-01 00:00:01.000000
+1	2001-01-01 00:00:02.000000
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+# now we "overflow" first partition a bit:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-03 00:00:00'
+(partition p0 history, partition p1 history, partition pn current);
+Warnings:
+Warning	4164	`t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
+insert into t1 values (0);
+set timestamp= unix_timestamp('2000-01-01 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-02 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-03 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-04 00:00:01');
+update t1 set i= i + 1;
+select *, row_end from t1 partition (p0);
+i	row_end
+0	2000-01-01 00:00:01.000000
+1	2000-01-02 00:00:01.000000
+2	2000-01-03 00:00:01.000000
+select *, row_end from t1 partition (p1);
+i	row_end
+3	2000-01-04 00:00:01.000000
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+# and this is how it usually goes:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition p1 history, partition pn current);
+insert into t1 values (0);
+set timestamp= unix_timestamp('2000-01-01 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-02 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-03 00:00:01');
+update t1 set i= i + 1;
+Warnings:
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
+set timestamp= unix_timestamp('2000-01-04 00:00:01');
+update t1 set i= i + 1;
+Warnings:
+Warning	4114	Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
+alter table t1 add partition (partition p2 history, partition p3 history);
+select *, row_end from t1 partition (p0);
+i	row_end
+0	2000-01-01 00:00:01.000000
+select *, row_end from t1 partition (p1);
+i	row_end
+1	2000-01-02 00:00:01.000000
+select *, row_end from t1 partition (p2);
+i	row_end
+2	2000-01-03 00:00:01.000000
+select *, row_end from t1 partition (p3);
+i	row_end
+3	2000-01-04 00:00:01.000000
+drop tables t1, t2;
diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test
index e3acf53686e..7a4868e4785 100644
--- a/mysql-test/suite/versioning/t/partition.test
+++ b/mysql-test/suite/versioning/t/partition.test
@@ -2,6 +2,8 @@
 -- source suite/versioning/common.inc
 -- source suite/versioning/engines.inc
 
+call mtr.add_suppression("need more HISTORY partitions");
+
 set system_versioning_alter_history=keep;
 --echo # Check conventional partitioning on temporal tables
 
@@ -250,14 +252,6 @@ partition by system_time interval 0 second (
     partition pn current);
 
 --error ER_PARSE_ERROR
-create or replace table t1 (x int)
-with system versioning
-partition by system_time interval 1 second starts 12345 (
-    partition p0 history,
-    partition p1 history,
-    partition pn current);
-
---error ER_PARSE_ERROR
 create table t1 (i int) with system versioning
   partition by system_time interval 6 day limit 98
   (partition p0 history, partition ver_pn current);
diff --git a/mysql-test/suite/versioning/t/partition_rotation.test b/mysql-test/suite/versioning/t/partition_rotation.test
index 4937a2a069b..bc585cd88d7 100644
--- a/mysql-test/suite/versioning/t/partition_rotation.test
+++ b/mysql-test/suite/versioning/t/partition_rotation.test
@@ -1,5 +1,8 @@
 --source include/have_partition.inc
 
+set time_zone= "+00:00";
+call mtr.add_suppression("need more HISTORY partitions");
+
 #
 # partition rotation
 #
@@ -37,4 +40,155 @@ select * from t1 for system_time all where row_end = @ts;
 --replace_column 5 # 10 # 11 #
 explain partitions select * from t1 for system_time all where row_end = @ts;
 
-drop table t1;
+--echo ## INTERVAL ... STARTS
+--error ER_PART_WRONG_VALUE
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts 'a'
+(partition p0 history, partition pn current);
+
+--error ER_PART_WRONG_VALUE
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '00:00:00'
+(partition p0 history, partition pn current);
+
+--error ER_PART_WRONG_VALUE
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-00-01 00:00:00'
+(partition p0 history, partition pn current);
+
+--error ER_PART_WRONG_VALUE
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts 946684800
+(partition p0 history, partition pn current);
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition pn current);
+show create table t1;
+
+--echo # Test STARTS warning
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:01'
+(partition p0 history, partition pn current);
+
+--echo # Test default STARTS rounding
+set timestamp= unix_timestamp('1999-12-15 13:33:33');
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 second
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 minute
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 hour
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 month
+(partition p0 history, partition pn current);
+show create table t1;
+
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 year
+(partition p0 history, partition pn current);
+show create table t1;
+
+--echo # seconds equivalent of 1 day does not round:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 86400 second
+(partition p0 history, partition pn current);
+show create table t1;
+
+--echo # STARTS value is in local time_zone:
+set time_zone="+03:00";
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition pn current);
+
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+create or replace table t2 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition pn current);
+
+show create table t1;
+show create table t2;
+set time_zone="+00:00";
+show create table t1;
+show create table t2;
+
+--echo # Test rotation
+set timestamp= unix_timestamp('2001-01-01 00:00:00');
+--echo # it's ok to add partitions for past:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-01 00:00:00'
+(partition p0 history, partition p1 history, partition pn current);
+
+--echo # we are warned when we push to present:
+insert into t1 values (0);
+set timestamp= unix_timestamp('2001-01-01 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2001-01-01 00:00:02');
+update t1 set i= i + 1;
+
+select *, row_end from t1 partition (p0);
+select *, row_end from t1 partition (p1);
+
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+--echo # now we "overflow" first partition a bit:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day starts '2000-01-03 00:00:00'
+(partition p0 history, partition p1 history, partition pn current);
+
+insert into t1 values (0);
+set timestamp= unix_timestamp('2000-01-01 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-02 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-03 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-04 00:00:01');
+update t1 set i= i + 1;
+
+select *, row_end from t1 partition (p0);
+select *, row_end from t1 partition (p1);
+
+set timestamp= unix_timestamp('2000-01-01 00:00:00');
+--echo # and this is how it usually goes:
+create or replace table t1 (i int) with system versioning
+partition by system_time interval 1 day
+(partition p0 history, partition p1 history, partition pn current);
+
+insert into t1 values (0);
+set timestamp= unix_timestamp('2000-01-01 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-02 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-03 00:00:01');
+update t1 set i= i + 1;
+set timestamp= unix_timestamp('2000-01-04 00:00:01');
+update t1 set i= i + 1;
+
+alter table t1 add partition (partition p2 history, partition p3 history);
+
+select *, row_end from t1 partition (p0);
+select *, row_end from t1 partition (p1);
+select *, row_end from t1 partition (p2);
+select *, row_end from t1 partition (p3);
+
+drop tables t1, t2;
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index 38e085b3be9..f1c4fb52e83 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -851,8 +851,13 @@ void partition_info::vers_set_hist_part(THD *thd)
     if (records > vers_info->limit)
     {
       if (next == vers_info->now_part)
-        goto warn;
-      vers_info->hist_part= next;
+      {
+        my_error(WARN_VERS_PART_FULL, MYF(ME_WARNING|ME_ERROR_LOG),
+                table->s->db.str, table->s->table_name.str,
+                vers_info->hist_part->partition_name, "LIMIT");
+      }
+      else
+        vers_info->hist_part= next;
     }
     return;
   }
@@ -873,13 +878,10 @@ void partition_info::vers_set_hist_part(THD *thd)
       if (next->range_value > thd->query_start())
         return;
     }
-    goto warn;
+    my_error(WARN_VERS_PART_FULL, MYF(ME_WARNING|ME_ERROR_LOG),
+            table->s->db.str, table->s->table_name.str,
+            vers_info->hist_part->partition_name, "INTERVAL");
   }
-  return;
-warn:
-  my_error(WARN_VERS_PART_FULL, MYF(ME_WARNING|ME_ERROR_LOG),
-           table->s->db.str, table->s->table_name.str,
-           vers_info->hist_part->partition_name);
 }
 
 
@@ -2371,6 +2373,101 @@ static bool strcmp_null(const char *a, const char *b)
   return true;
 }
 
+/**
+  Assign INTERVAL and STARTS for SYSTEM_TIME partitions.
+
+  @return true on error
+*/
+
+bool partition_info::vers_set_interval(THD* thd, Item* interval,
+                                       interval_type int_type, Item* starts,
+                                       const char *table_name)
+{
+  DBUG_ASSERT(part_type == VERSIONING_PARTITION);
+
+  MYSQL_TIME ltime;
+  uint err;
+  vers_info->interval.type= int_type;
+
+  /* 1. assign INTERVAL to interval.step */
+  if (interval->fix_fields_if_needed_for_scalar(thd, &interval))
+    return true;
+  bool error= get_interval_value(thd, interval, int_type, &vers_info->interval.step) ||
+          vers_info->interval.step.neg || vers_info->interval.step.second_part ||
+        !(vers_info->interval.step.year || vers_info->interval.step.month ||
+          vers_info->interval.step.day || vers_info->interval.step.hour ||
+          vers_info->interval.step.minute || vers_info->interval.step.second);
+  if (error)
+  {
+    my_error(ER_PART_WRONG_VALUE, MYF(0), table_name, "INTERVAL");
+    return true;
+  }
+
+  /* 2. assign STARTS to interval.start */
+  if (starts)
+  {
+    if (starts->fix_fields_if_needed_for_scalar(thd, &starts))
+      return true;
+    switch (starts->result_type())
+    {
+      case INT_RESULT:
+      case DECIMAL_RESULT:
+      case REAL_RESULT:
+        /* When table member is defined, we are inside mysql_unpack_partition(). */
+        if (!table || starts->val_int() > TIMESTAMP_MAX_VALUE)
+          goto interval_starts_error;
+        vers_info->interval.start= (my_time_t) starts->val_int();
+        break;
+      case STRING_RESULT:
+      case TIME_RESULT:
+      {
+        Datetime::Options opt(TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE, thd);
+        starts->get_date(thd, &ltime, opt);
+        vers_info->interval.start= TIME_to_timestamp(thd, &ltime, &err);
+        if (err)
+          goto interval_starts_error;
+        break;
+      }
+      case ROW_RESULT:
+      default:
+        goto interval_starts_error;
+    }
+    if (!table)
+    {
+      if (thd->query_start() < vers_info->interval.start) {
+        push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                            ER_PART_STARTS_BEYOND_INTERVAL,
+                            ER_THD(thd, ER_PART_STARTS_BEYOND_INTERVAL),
+                            table_name);
+      }
+    }
+  }
+  else // calculate default STARTS depending on INTERVAL
+  {
+    thd->variables.time_zone->gmt_sec_to_TIME(&ltime, thd->query_start());
+    if (vers_info->interval.step.second)
+      goto interval_set_starts;
+    ltime.second= 0;
+    if (vers_info->interval.step.minute)
+      goto interval_set_starts;
+    ltime.minute= 0;
+    if (vers_info->interval.step.hour)
+      goto interval_set_starts;
+    ltime.hour= 0;
+
+interval_set_starts:
+    vers_info->interval.start= TIME_to_timestamp(thd, &ltime, &err);
+    if (err)
+      goto interval_starts_error;
+  }
+
+  return false;
+
+interval_starts_error:
+  my_error(ER_PART_WRONG_VALUE, MYF(0), table_name, "STARTS");
+  return true;
+}
+
 
 /**
   Check if the new part_info has the same partitioning.
diff --git a/sql/partition_info.h b/sql/partition_info.h
index 00ef815ce09..3a34ad88d28 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -394,27 +394,9 @@ public:
   bool field_in_partition_expr(Field *field) const;
 
   bool vers_init_info(THD *thd);
-  bool vers_set_interval(THD *thd, Item *item,
-                         interval_type int_type, my_time_t start)
-  {
-    DBUG_ASSERT(part_type == VERSIONING_PARTITION);
-    vers_info->interval.type= int_type;
-    vers_info->interval.start= start;
-    if (item->fix_fields_if_needed_for_scalar(thd, &item))
-      return true;
-    bool error= get_interval_value(thd, item, int_type, &vers_info->interval.step) ||
-           vers_info->interval.step.neg || vers_info->interval.step.second_part ||
-          !(vers_info->interval.step.year || vers_info->interval.step.month ||
-            vers_info->interval.step.day || vers_info->interval.step.hour ||
-            vers_info->interval.step.minute || vers_info->interval.step.second);
-    if (error)
-    {
-      my_error(ER_PART_WRONG_VALUE, MYF(0),
-               thd->lex->create_last_non_select_table->table_name.str,
-               "INTERVAL");
-    }
-    return error;
-  }
+  bool vers_set_interval(THD *thd, Item *interval,
+                         interval_type int_type, Item *starts,
+                         const char *table_name);
   bool vers_set_limit(ulonglong limit)
   {
     DBUG_ASSERT(part_type == VERSIONING_PARTITION);
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index dfbacccc169..36188e58624 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7817,7 +7817,7 @@ ER_PARTITION_WRONG_TYPE
         eng "Wrong partitioning type, expected type: %`s"
 
 WARN_VERS_PART_FULL
-        eng "Versioned table %`s.%`s: partition %`s is full, add more HISTORY partitions"
+        eng "Versioned table %`s.%`s: last HISTORY partition (%`s) is out of %s, need more HISTORY partitions"
 
 WARN_VERS_PARAMETERS
         eng "Maybe missing parameters: %s"
@@ -7939,3 +7939,7 @@ ER_UNKNOWN_DATA_TYPE
         eng "Unknown data type: '%-.64s'"
 ER_UNKNOWN_OPERATOR
         eng "Operator does not exists: '%-.128s'"
+ER_WARN_HISTORY_ROW_START_TIME
+        eng "Table `%s.%s` history row start '%s' is later than row end '%s'"
+ER_PART_STARTS_BEYOND_INTERVAL
+        eng "%`s: STARTS is later than query time, first history partition may exceed INTERVAL value"
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e5e6b1f3afd..257a36e94c5 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -206,6 +206,7 @@ init_lex_with_single_table(THD *thd, TABLE *table, LEX *lex)
   table->map= 1; //To ensure correct calculation of const item
   table_list->table= table;
   table_list->cacheable_table= false;
+  lex->create_last_non_select_table= table_list;
   return FALSE;
 }
 
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 9957a3b064c..9e31ce3b31c 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -2569,11 +2569,21 @@ char *generate_partition_syntax(THD *thd, partition_info *part_info,
       err+= str.append(STRING_WITH_LEN("INTERVAL "));
       err+= append_interval(&str, vers_info->interval.type,
                                   vers_info->interval.step);
+      err+= str.append(STRING_WITH_LEN(" STARTS "));
       if (create_info) // not SHOW CREATE
       {
-        err+= str.append(STRING_WITH_LEN(" STARTS "));
         err+= str.append_ulonglong(vers_info->interval.start);
       }
+      else
+      {
+        MYSQL_TIME ltime;
+        char ctime[MAX_DATETIME_WIDTH + 1];
+        thd->variables.time_zone->gmt_sec_to_TIME(&ltime, vers_info->interval.start);
+        uint ctime_len= my_datetime_to_str(&ltime, ctime, 0);
+        err+= str.append(STRING_WITH_LEN("TIMESTAMP'"));
+        err+= str.append(ctime, ctime_len);
+        err+= str.append('\'');
+      }
     }
     if (vers_info->limit)
     {
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b06d2f5a80c..05e68f3b752 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1887,7 +1887,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %type <ulong_num>
         ulong_num real_ulong_num merge_insert_types
-        ws_nweights opt_versioning_interval_start
+        ws_nweights
         ws_level_flag_desc ws_level_flag_reverse ws_level_flags
         opt_ws_levels ws_level_list ws_level_list_item ws_level_number
         ws_level_range ws_level_list_or_range bool
@@ -1936,6 +1936,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         simple_target_specification
         condition_number
         reset_lex_expr
+        opt_versioning_interval_start
 
 %type <item_param> param_marker
 
@@ -6021,7 +6022,8 @@ opt_versioning_rotation:
        | INTERVAL_SYM expr interval opt_versioning_interval_start
          {
            partition_info *part_info= Lex->part_info;
-           if (unlikely(part_info->vers_set_interval(thd, $2, $3, $4)))
+           const char *table_name= Lex->create_last_non_select_table->table_name.str;
+           if (unlikely(part_info->vers_set_interval(thd, $2, $3, $4, table_name)))
              MYSQL_YYABORT;
          }
        | LIMIT ulonglong_num
@@ -6041,17 +6043,11 @@ opt_versioning_rotation:
 opt_versioning_interval_start:
          /* empty */
          {
-           $$= thd->query_start();
+           $$= NULL;
          }
-       | STARTS_SYM ulong_num
+       | STARTS_SYM literal
          {
-           /* only allowed from mysql_unpack_partition() */
-           if (unlikely(!Lex->part_info->table))
-           {
-             thd->parse_error(ER_SYNTAX_ERROR, $1.pos());
-             MYSQL_YYABORT;
-           }
-           $$= (ulong)$2;
+           $$= $2;
          }
        ;
 
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index c18acd3ef96..20312ab7e37 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -1359,7 +1359,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
 
 %type <ulong_num>
         ulong_num real_ulong_num merge_insert_types
-        ws_nweights opt_versioning_interval_start
+        ws_nweights
         ws_level_flag_desc ws_level_flag_reverse ws_level_flags
         opt_ws_levels ws_level_list ws_level_list_item ws_level_number
         ws_level_range ws_level_list_or_range bool
@@ -1408,6 +1408,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         simple_target_specification
         condition_number
         reset_lex_expr
+        opt_versioning_interval_start
 
 %type <item_param> param_marker
 
@@ -5984,7 +5985,8 @@ opt_versioning_rotation:
        | INTERVAL_SYM expr interval opt_versioning_interval_start
          {
            partition_info *part_info= Lex->part_info;
-           if (unlikely(part_info->vers_set_interval(thd, $2, $3, $4)))
+           const char *table_name= Lex->create_last_non_select_table->table_name.str;
+           if (unlikely(part_info->vers_set_interval(thd, $2, $3, $4, table_name)))
              MYSQL_YYABORT;
          }
        | LIMIT ulonglong_num
@@ -6004,17 +6006,11 @@ opt_versioning_rotation:
 opt_versioning_interval_start:
          /* empty */
          {
-           $$= thd->query_start();
+           $$= NULL;
          }
-       | STARTS_SYM ulong_num
+       | STARTS_SYM literal
          {
-           /* only allowed from mysql_unpack_partition() */
-           if (unlikely(!Lex->part_info->table))
-           {
-             thd->parse_error(ER_SYNTAX_ERROR, $1.pos());
-             MYSQL_YYABORT;
-           }
-           $$= (ulong)$2;
+           $$= $2;
          }
        ;