summaryrefslogtreecommitdiff
path: root/mysql-test/suite/period
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/period')
-rw-r--r--mysql-test/suite/period/create_triggers.inc38
-rw-r--r--mysql-test/suite/period/engines.combinations6
-rw-r--r--mysql-test/suite/period/engines.inc3
-rw-r--r--mysql-test/suite/period/r/alter.result176
-rw-r--r--mysql-test/suite/period/r/create.result98
-rw-r--r--mysql-test/suite/period/r/delete,myisam.rdiff18
-rw-r--r--mysql-test/suite/period/r/delete.result358
-rw-r--r--mysql-test/suite/period/r/update.result276
-rw-r--r--mysql-test/suite/period/r/versioning.result94
-rw-r--r--mysql-test/suite/period/t/alter.test133
-rw-r--r--mysql-test/suite/period/t/create.test81
-rw-r--r--mysql-test/suite/period/t/delete.test186
-rw-r--r--mysql-test/suite/period/t/update.test165
-rw-r--r--mysql-test/suite/period/t/versioning.test64
14 files changed, 1696 insertions, 0 deletions
diff --git a/mysql-test/suite/period/create_triggers.inc b/mysql-test/suite/period/create_triggers.inc
new file mode 100644
index 00000000000..1126ae0845b
--- /dev/null
+++ b/mysql-test/suite/period/create_triggers.inc
@@ -0,0 +1,38 @@
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+
+create or replace procedure log(s text)
+ insert into log_tbl(log) values(s);
+
+if (!$trig_table)
+{
+ die "No $trig_table specified";
+}
+
+if (!$trig_cols)
+{
+ let $trig_cols= s, e;
+}
+
+let $old_trig_args= `select REGEXP_REPLACE('$trig_cols', '([[:word:]]+)',
+ 'old.\\\\\\\\1')`;
+let $old_trig_args= `select REPLACE('$old_trig_args', ',', ', ", ", ')`;
+let $new_trig_args= `select REGEXP_REPLACE('$trig_cols', '([[:word:]]+)',
+ 'new.\\\\\\\\1')`;
+let $new_trig_args= `select REPLACE('$new_trig_args', ',', ', ", ", ')`;
+
+eval create trigger tr1upd_$trig_table before update on $trig_table
+ for each row call log(CONCAT('>UPD: ', $old_trig_args, ' -> ', $new_trig_args));
+eval create trigger tr2upd_$trig_table after update on $trig_table
+ for each row call log(CONCAT('<UPD: ', $old_trig_args, ' -> ', $new_trig_args));
+eval create trigger tr1del_$trig_table before delete on $trig_table
+ for each row call log(CONCAT('>DEL: ', $old_trig_args));
+eval create trigger tr2del_$trig_table after delete on $trig_table
+ for each row call log(CONCAT('<DEL: ', $old_trig_args));
+eval create trigger tr1ins_$trig_table before insert on $trig_table
+ for each row call log(CONCAT('>INS: ', $new_trig_args));
+eval create trigger tr2ins_$trig_table after insert on $trig_table
+ for each row call log(CONCAT('<INS: ', $new_trig_args));
+
+
+let trig_cols= 0;
+let trig_table= 0;
diff --git a/mysql-test/suite/period/engines.combinations b/mysql-test/suite/period/engines.combinations
new file mode 100644
index 00000000000..b740ec62a42
--- /dev/null
+++ b/mysql-test/suite/period/engines.combinations
@@ -0,0 +1,6 @@
+[innodb]
+innodb
+default-storage-engine=innodb
+
+[myisam]
+default-storage-engine=myisam
diff --git a/mysql-test/suite/period/engines.inc b/mysql-test/suite/period/engines.inc
new file mode 100644
index 00000000000..9a52c7d0640
--- /dev/null
+++ b/mysql-test/suite/period/engines.inc
@@ -0,0 +1,3 @@
+#
+# see engines.combinations
+#
diff --git a/mysql-test/suite/period/r/alter.result b/mysql-test/suite/period/r/alter.result
new file mode 100644
index 00000000000..e202ba2698f
--- /dev/null
+++ b/mysql-test/suite/period/r/alter.result
@@ -0,0 +1,176 @@
+set @s= '1992-01-01';
+set @e= '1999-12-31';
+create table t (s date, e date);
+# period start/end columns are implicit NOT NULL
+alter table t add period for a(s, e);
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t (s date, e date);
+alter table t change s s date, add period for a(s, e);
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t add id int;
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ `id` int(11) DEFAULT NULL,
+ PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t drop id;
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t drop constraint a;
+ERROR HY000: Can't DROP CONSTRAINT `a`. Use DROP PERIOD `a` for this
+# no-op
+alter table t drop period if exists for b;
+Warnings:
+Note 1091 Can't DROP PERIOD `b`; check that it exists
+# no-op
+alter table t add period if not exists for a(e, s);
+Warnings:
+Note 1060 Duplicate column name 'a'
+alter table t drop period if exists for a;
+# no-op
+alter table t drop period if exists for a;
+Warnings:
+Note 1091 Can't DROP PERIOD `a`; check that it exists
+alter table t add period for a(s, e), add period if not exists for a(e, s);
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t drop period for a;
+# Constraint is dropped
+insert t values(@e, @s);
+alter table t drop period for a;
+ERROR 42000: Can't DROP PERIOD `a`; check that it exists
+alter table t add period for a(s, e), drop period for a;
+ERROR 42000: Can't DROP PERIOD `a`; check that it exists
+truncate t;
+alter table t add period for a(s, e);
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t add period for a(s, e), drop period for a;
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t add s1 date not null, add period for b(s1, e), drop period for a;
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ `s1` date NOT NULL,
+ PERIOD FOR `b` (`s1`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t(s, s1, e) values(@e, @s, @e);
+insert t(s, s1, e) values(@e, @e, @s);
+ERROR 23000: CONSTRAINT `b` failed for `test`.`t`
+create table t1 like t;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ `s1` date NOT NULL,
+ PERIOD FOR `b` (`s1`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t2 (period for b(s,e)) select * from t;
+ERROR 23000: CONSTRAINT `b` failed for `test`.`t2`
+create table t2 (period for b(s1,e)) select * from t;
+drop table t2;
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
+# The declared type of BC1 shall be either DATE or a timestamp type
+# and shall be equivalent to the declared type of BC2.
+create or replace table t (s timestamp not null, e timestamp(6) not null);
+alter table t add period for a(s, e);
+ERROR HY000: Fields of PERIOD FOR `a` have different types
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
+# No column of T shall have a column name that is equivalent to ATPN.
+create or replace table t (a int, s date, e date);
+alter table t add period for a(s, e);
+ERROR 42S21: Duplicate column name 'a'
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
+# Neither BC1 nor BC2 shall be an identity column, a generated column,
+# a system-time period start column, or a system-time period end column.
+create or replace table t (id int primary key,
+s date,
+e date generated always as (s+1));
+alter table t add period for a(s, e);
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key,
+s date,
+e date as (s+1) VIRTUAL);
+alter table t add period for a(s, e);
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+st timestamp(6) as row start,
+en timestamp(6) as row end,
+period for system_time (st, en)) with system versioning;
+alter table t add period for a(s, en);
+ERROR HY000: Period field `en` cannot be GENERATED ALWAYS AS
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
+# The table descriptor of T shall not include a period descriptor other
+# than a system-time period descriptor.
+alter table t add period for a(s, e);
+alter table t add period for b(s, e);
+ERROR HY000: Cannot specify more than one application-time period
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
+# Let S be the schema identified by the explicit or implicit
+# <schema name> of TN. Let IDCN be an implementation-dependent
+# <constraint name> that is not equivalent to the <constraint name> of
+# any table constraint descriptor included in S. The following
+# <table constraint definition> is implicit:
+# CONSTRAINT IDCN CHECK ( CN1 < CN2 )
+#
+# Due to the above standard limitation, the constraint name can't always
+# match the period name. So it matches when possible; and when not, it
+# is unique not taken name prefixed with period name.
+create or replace table t (x int, s date, e date,
+period for mytime(s, e));
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `x` int(11) DEFAULT NULL,
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `mytime` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t add constraint mytime check (x > 1);
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `x` int(11) DEFAULT NULL,
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `mytime` (`s`, `e`),
+ CONSTRAINT `mytime` CHECK (`x` > 1)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values (2, @e, @s);
+ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
+alter table t add constraint mytime_1 check (x > 2);
+insert t values (3, @e, @s);
+ERROR 23000: CONSTRAINT `mytime_2` failed for `test`.`t`
+drop table t;
diff --git a/mysql-test/suite/period/r/create.result b/mysql-test/suite/period/r/create.result
new file mode 100644
index 00000000000..8cedb23465d
--- /dev/null
+++ b/mysql-test/suite/period/r/create.result
@@ -0,0 +1,98 @@
+create table t (id int primary key, s date, e date, period for mytime(s,e));
+# CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
+# this is important for correct command-based replication
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `id` int(11) NOT NULL,
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PRIMARY KEY (`id`),
+ PERIOD FOR `mytime` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+period for mytime(s,e));
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `id` int(11) NOT NULL,
+ `s` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+ `e` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+ PRIMARY KEY (`id`),
+ PERIOD FOR `mytime` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)a)
+# 2) If a <table period definition> TPD is specified, then:
+# a) <table scope> shall not be specified.
+create or replace temporary table t (s date, e date, period for mytime(s,e));
+ERROR HY000: Application-time period table cannot be temporary
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)iii)
+# The <data type or domain name> contained in CD1 is either DATE or a
+# timestamp type and it is equivalent to the <data type or domain name>
+# contained in CD2.
+create or replace table t (id int primary key, s datetime, e date,
+period for mytime(s,e));
+ERROR HY000: Fields of PERIOD FOR `mytime` have different types
+create or replace table t (s timestamp(2), e timestamp(6),
+period for mytime(s,e));
+ERROR HY000: Fields of PERIOD FOR `mytime` have different types
+create or replace table t (id int primary key, s int, e date,
+period for mytime(s,e));
+ERROR 42000: Incorrect column specifier for column 's'
+create or replace table t (id int primary key, s time, e time,
+period for mytime(s,e));
+ERROR 42000: Incorrect column specifier for column 's'
+create or replace table t (id int primary key, s date, e date,
+period for mytime(s,x));
+ERROR 42S22: Unknown column 'x' in 'mytime'
+create or replace table t (id int primary key, s date, e date,
+period for mytime(s,e),
+period for mytime2(s,e));
+ERROR HY000: Cannot specify more than one application-time period
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)d)
+# No <column name> in any <column definition> shall be equivalent to PN.
+create or replace table t (mytime int, s date, e date,
+period for mytime(s,e));
+ERROR 42S21: Duplicate column name 'mytime'
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)A)
+# Neither CD1 nor CD2 shall contain an <identity column specification>, a
+# <generation clause>, a <system time period start column specification>,
+# or a <system time period end column specification>.
+create or replace table t (id int primary key,
+s date,
+e date generated always as (s+1),
+period for mytime(s,e));
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key,
+s date,
+e date as (s+1) VIRTUAL,
+period for mytime(s,e));
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+st timestamp(6) as row start,
+en timestamp(6) as row end,
+period for system_time (st, en),
+period for mytime(st,e)) with system versioning;
+ERROR HY000: Period field `st` cannot be GENERATED ALWAYS AS
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)
+# Let IDCN be an implementation-dependent <constraint name> that is not
+# equivalent to the <constraint name> of any table constraint descriptor
+# included in S.
+create or replace table t (x int, s date, e date,
+period for mytime(s, e),
+constraint mytime check (x > 1));
+show create table t;
+Table Create Table
+t CREATE TABLE `t` (
+ `x` int(11) DEFAULT NULL,
+ `s` date NOT NULL,
+ `e` date NOT NULL,
+ PERIOD FOR `mytime` (`s`, `e`),
+ CONSTRAINT `mytime` CHECK (`x` > 1)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values (2, '2001-01-01', '2001-01-01');
+ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
+show status like "Feature_application_time_periods";
+Variable_name Value
+Feature_application_time_periods 6
+drop table t;
diff --git a/mysql-test/suite/period/r/delete,myisam.rdiff b/mysql-test/suite/period/r/delete,myisam.rdiff
new file mode 100644
index 00000000000..78fb972b0bc
--- /dev/null
+++ b/mysql-test/suite/period/r/delete,myisam.rdiff
@@ -0,0 +1,18 @@
+--- suite/period/r/delete.result 2019-02-16 11:14:23.511258191 +0100
++++ suite/period/r/delete.reject 2019-02-16 11:14:32.869258690 +0100
+@@ -250,7 +250,6 @@
+ ERROR 22003: Out of range value for column 'id' at row 1
+ select * from t;
+ id s e
+-127 1999-01-01 2018-12-12
+ # same for trigger case
+ create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+ create or replace procedure log(s text)
+@@ -277,7 +276,6 @@
+ ERROR 22003: Out of range value for column 'id' at row 1
+ select * from t;
+ id s e
+-127 1999-01-01 2018-12-12
+ select * from log_tbl order by id;
+ id log
+ 1 >DEL: 1999-01-01, 2018-12-12
diff --git a/mysql-test/suite/period/r/delete.result b/mysql-test/suite/period/r/delete.result
new file mode 100644
index 00000000000..428200a4564
--- /dev/null
+++ b/mysql-test/suite/period/r/delete.result
@@ -0,0 +1,358 @@
+create table t (id int, s date, e date, period for apptime(s,e));
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+insert into t values(1, '2017-01-01', '2019-01-01');
+insert into t values(2, '1998-01-01', '2018-12-12');
+insert into t values(3, '1997-01-01', '2015-01-01');
+insert into t values(4, '2016-01-01', '2020-01-01');
+insert into t values(5, '2010-01-01', '2015-01-01');
+create or replace table t1 (id int, s date, e date, period for apptime(s,e));
+insert t1 select * from t;
+create or replace table t2 (id int, s date, e date, period for apptime(s,e));
+insert t2 select * from t;
+create or replace table t3 (id int, s date, e date, period for apptime(s,e));
+insert t3 select * from t;
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t1 before update on t1
+for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2upd_t1 after update on t1
+for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr1del_t1 before delete on t1
+for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr2del_t1 after delete on t1
+for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr1ins_t1 before insert on t1
+for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2ins_t1 after insert on t1
+for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+delete from t1 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+select * from t;
+id s e
+1 1999-01-01 2000-01-01
+1 1999-01-01 2000-01-01
+1 2018-01-01 2018-12-12
+1 2018-01-01 2019-01-01
+2 1998-01-01 2000-01-01
+2 2018-01-01 2018-12-12
+3 1997-01-01 2000-01-01
+4 2018-01-01 2020-01-01
+select * from t1;
+id s e
+1 1999-01-01 2000-01-01
+1 1999-01-01 2000-01-01
+1 2018-01-01 2018-12-12
+1 2018-01-01 2019-01-01
+2 1998-01-01 2000-01-01
+2 2018-01-01 2018-12-12
+3 1997-01-01 2000-01-01
+4 2018-01-01 2020-01-01
+select * from log_tbl order by id;
+id log
+1 >DEL: 1, 1999-01-01, 2018-12-12
+2 >INS: 1, 1999-01-01, 2000-01-01
+3 <INS: 1, 1999-01-01, 2000-01-01
+4 >INS: 1, 2018-01-01, 2018-12-12
+5 <INS: 1, 2018-01-01, 2018-12-12
+6 <DEL: 1, 1999-01-01, 2018-12-12
+7 >DEL: 1, 1999-01-01, 2017-01-01
+8 >INS: 1, 1999-01-01, 2000-01-01
+9 <INS: 1, 1999-01-01, 2000-01-01
+10 <DEL: 1, 1999-01-01, 2017-01-01
+11 >DEL: 1, 2017-01-01, 2019-01-01
+12 >INS: 1, 2018-01-01, 2019-01-01
+13 <INS: 1, 2018-01-01, 2019-01-01
+14 <DEL: 1, 2017-01-01, 2019-01-01
+15 >DEL: 2, 1998-01-01, 2018-12-12
+16 >INS: 2, 1998-01-01, 2000-01-01
+17 <INS: 2, 1998-01-01, 2000-01-01
+18 >INS: 2, 2018-01-01, 2018-12-12
+19 <INS: 2, 2018-01-01, 2018-12-12
+20 <DEL: 2, 1998-01-01, 2018-12-12
+21 >DEL: 3, 1997-01-01, 2015-01-01
+22 >INS: 3, 1997-01-01, 2000-01-01
+23 <INS: 3, 1997-01-01, 2000-01-01
+24 <DEL: 3, 1997-01-01, 2015-01-01
+25 >DEL: 4, 2016-01-01, 2020-01-01
+26 >INS: 4, 2018-01-01, 2020-01-01
+27 <INS: 4, 2018-01-01, 2020-01-01
+28 <DEL: 4, 2016-01-01, 2020-01-01
+29 >DEL: 5, 2010-01-01, 2015-01-01
+30 <DEL: 5, 2010-01-01, 2015-01-01
+# INSERT trigger only also works
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t2 before update on t2
+for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2upd_t2 after update on t2
+for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr1del_t2 before delete on t2
+for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr2del_t2 after delete on t2
+for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr1ins_t2 before insert on t2
+for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2ins_t2 after insert on t2
+for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
+drop trigger tr1del_t2;
+drop trigger tr2del_t2;
+delete from t2 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+select * from log_tbl order by id;
+id log
+1 >INS: 1, 1999-01-01, 2000-01-01
+2 <INS: 1, 1999-01-01, 2000-01-01
+3 >INS: 1, 2018-01-01, 2018-12-12
+4 <INS: 1, 2018-01-01, 2018-12-12
+5 >INS: 1, 1999-01-01, 2000-01-01
+6 <INS: 1, 1999-01-01, 2000-01-01
+7 >INS: 1, 2018-01-01, 2019-01-01
+8 <INS: 1, 2018-01-01, 2019-01-01
+9 >INS: 2, 1998-01-01, 2000-01-01
+10 <INS: 2, 1998-01-01, 2000-01-01
+11 >INS: 2, 2018-01-01, 2018-12-12
+12 <INS: 2, 2018-01-01, 2018-12-12
+13 >INS: 3, 1997-01-01, 2000-01-01
+14 <INS: 3, 1997-01-01, 2000-01-01
+15 >INS: 4, 2018-01-01, 2020-01-01
+16 <INS: 4, 2018-01-01, 2020-01-01
+# removing BEFORE INSERT trigger enables internal substitution
+# DELETE+INSERT -> UPDATE, but without any side effects.
+# The optimization is disabled for non-transactional engines
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t3 before update on t3
+for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr2upd_t3 after update on t3
+for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr1del_t3 before delete on t3
+for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
+create trigger tr2del_t3 after delete on t3
+for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
+create trigger tr1ins_t3 before insert on t3
+for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
+create trigger tr2ins_t3 after insert on t3
+for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
+drop trigger tr1ins_t3;
+delete from t3 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+select * from log_tbl order by id;
+id log
+1 >DEL: 1999-01-01, 2018-12-12
+2 <INS: 1999-01-01, 2000-01-01
+3 <INS: 2018-01-01, 2018-12-12
+4 <DEL: 1999-01-01, 2018-12-12
+5 >DEL: 1999-01-01, 2017-01-01
+6 <INS: 1999-01-01, 2000-01-01
+7 <DEL: 1999-01-01, 2017-01-01
+8 >DEL: 2017-01-01, 2019-01-01
+9 <INS: 2018-01-01, 2019-01-01
+10 <DEL: 2017-01-01, 2019-01-01
+11 >DEL: 1998-01-01, 2018-12-12
+12 <INS: 1998-01-01, 2000-01-01
+13 <INS: 2018-01-01, 2018-12-12
+14 <DEL: 1998-01-01, 2018-12-12
+15 >DEL: 1997-01-01, 2015-01-01
+16 <INS: 1997-01-01, 2000-01-01
+17 <DEL: 1997-01-01, 2015-01-01
+18 >DEL: 2016-01-01, 2020-01-01
+19 <INS: 2018-01-01, 2020-01-01
+20 <DEL: 2016-01-01, 2020-01-01
+21 >DEL: 2010-01-01, 2015-01-01
+22 <DEL: 2010-01-01, 2015-01-01
+# multi-table DELETE is not possible
+delete t, t1 from t1, t for portion of apptime from '2000-01-01' to '2018-01-01';
+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 'for portion of apptime from '2000-01-01' to '2018-01-01'' at line 1
+delete t for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1;
+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 'for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1' at line 1
+# Here another check fails before parsing ends
+delete t, t1 from t for portion of apptime from '2000-01-01' to '2018-01-01', t1;
+ERROR 42S02: Unknown table 't1' in MULTI DELETE
+delete history from t2 for portion of apptime from '2000-01-01' to '2018-01-01';
+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 'for portion of apptime from '2000-01-01' to '2018-01-01'' at line 1
+delete from t for portion of othertime from '2000-01-01' to '2018-01-01';
+ERROR HY000: Period `othertime` is not found in table
+delete from t for portion of system_time from '2000-01-01' to '2018-01-01';
+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 'of system_time from '2000-01-01' to '2018-01-01'' at line 1
+create or replace table t (id int, str text, s date, e date,
+period for apptime(s,e));
+insert into t values(1, 'data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'deleted', '2000-01-01', '2018-01-01');
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+show warnings;
+Level Code Message
+select * from t;
+id str s e
+1 data 1999-01-01 2000-01-01
+1 data 2018-01-01 2018-12-12
+1 other data 1999-01-01 2000-01-01
+1 other data 2018-01-01 2018-12-12
+drop table t1;
+# SQL16, Part 2, 15.7 <Effect of deleting rows from base tables>,
+# General rules, 8)b)i)
+# If the column descriptor that corresponds to the i-th field of BR
+# describes an identity column, a generated column, a system-time period
+# start column, or a system-time period end column, then let V i be
+# DEFAULT.
+# auto_increment field is updated
+create or replace table t (id int primary key auto_increment, s date, e date,
+period for apptime(s, e));
+insert into t values (default, '1999-01-01', '2018-12-12');
+select * from t;
+id s e
+1 1999-01-01 2018-12-12
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+id s e
+2 1999-01-01 2000-01-01
+3 2018-01-01 2018-12-12
+truncate t;
+# same for trigger case
+insert into t values (default, '1999-01-01', '2018-12-12');
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t before update on t
+for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr2upd_t after update on t
+for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr1del_t before delete on t
+for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
+create trigger tr2del_t after delete on t
+for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
+create trigger tr1ins_t before insert on t
+for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
+create trigger tr2ins_t after insert on t
+for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+id s e
+2 1999-01-01 2000-01-01
+3 2018-01-01 2018-12-12
+select * from log_tbl order by id;
+id log
+1 >DEL: 1999-01-01, 2018-12-12
+2 >INS: 1999-01-01, 2000-01-01
+3 <INS: 1999-01-01, 2000-01-01
+4 >INS: 2018-01-01, 2018-12-12
+5 <INS: 2018-01-01, 2018-12-12
+6 <DEL: 1999-01-01, 2018-12-12
+# generated columns are updated
+create or replace table t (s date, e date,
+xs date as (s) stored, xe date as (e) stored,
+period for apptime(s, e));
+insert into t values('1999-01-01', '2018-12-12', default, default);
+select * from t;
+s e xs xe
+1999-01-01 2018-12-12 1999-01-01 2018-12-12
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+s e xs xe
+1999-01-01 2000-01-01 1999-01-01 2000-01-01
+2018-01-01 2018-12-12 2018-01-01 2018-12-12
+truncate t;
+# same for trigger case
+insert into t values('1999-01-01', '2018-12-12', default, default);
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t before update on t
+for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr2upd_t after update on t
+for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr1del_t before delete on t
+for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
+create trigger tr2del_t after delete on t
+for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
+create trigger tr1ins_t before insert on t
+for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
+create trigger tr2ins_t after insert on t
+for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+s e xs xe
+1999-01-01 2000-01-01 1999-01-01 2000-01-01
+2018-01-01 2018-12-12 2018-01-01 2018-12-12
+select * from log_tbl order by id;
+id log
+1 >DEL: 1999-01-01, 2018-12-12
+2 >INS: 1999-01-01, 2000-01-01
+3 <INS: 1999-01-01, 2000-01-01
+4 >INS: 2018-01-01, 2018-12-12
+5 <INS: 2018-01-01, 2018-12-12
+6 <DEL: 1999-01-01, 2018-12-12
+# View can't be used
+create or replace view v as select * from t;
+delete from v for portion of p from '2000-01-01' to '2018-01-01';
+ERROR 42S02: 'v' is a view
+# View can't be used
+create or replace view v as select t.* from t, t as t1;
+delete from v for portion of p from '2000-01-01' to '2018-01-01';
+ERROR HY000: Can not delete from join view 'test.v'
+# auto_increment field overflow
+create or replace table t (id tinyint auto_increment primary key,
+s date, e date, period for apptime(s,e));
+insert into t values(127, '1999-01-01', '2018-12-12');
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+ERROR 22003: Out of range value for column 'id' at row 1
+select * from t;
+id s e
+127 1999-01-01 2018-12-12
+# same for trigger case
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t before update on t
+for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr2upd_t after update on t
+for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr1del_t before delete on t
+for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
+create trigger tr2del_t after delete on t
+for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
+create trigger tr1ins_t before insert on t
+for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
+create trigger tr2ins_t after insert on t
+for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
+# negotiate side effects of non-transactional MyISAM engine
+replace into t values(127, '1999-01-01', '2018-12-12');
+select * from t;
+id s e
+127 1999-01-01 2018-12-12
+truncate table log_tbl;
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+ERROR 22003: Out of range value for column 'id' at row 1
+select * from t;
+id s e
+127 1999-01-01 2018-12-12
+select * from log_tbl order by id;
+id log
+1 >DEL: 1999-01-01, 2018-12-12
+# custom constraint for period fields
+create or replace table t(id int, s date, e date, period for apptime(s,e),
+constraint dist2days check (datediff(e, s) >= 2));
+insert into t values(1, '1999-01-01', '2018-12-12'),
+(2, '1999-01-01', '1999-12-12');
+delete from t for portion of apptime from '1999-01-02' to '2018-12-12';
+ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t`
+# negotiate side effects of non-transactional MyISAM engine
+truncate t;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+(2, '1999-01-01', '1999-12-12');
+delete from t for portion of apptime from '1999-01-01' to '2018-12-11';
+ERROR 23000: CONSTRAINT `dist2days` failed for `test`.`t`
+truncate t;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+(2, '1999-01-01', '1999-12-12');
+delete from t for portion of apptime from '1999-01-03' to '2018-12-10';
+select *, datediff(e, s) from t;
+id s e datediff(e, s)
+1 1999-01-01 1999-01-03 2
+1 2018-12-10 2018-12-12 2
+2 1999-01-01 1999-01-03 2
+drop table t,t2,t3,log_tbl;
+drop view v;
+drop procedure log;
diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result
new file mode 100644
index 00000000000..b86537fc9fb
--- /dev/null
+++ b/mysql-test/suite/period/r/update.result
@@ -0,0 +1,276 @@
+create table t (id int, s date, e date, period for apptime(s,e));
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+insert into t values(1, '2017-01-01', '2019-01-01');
+insert into t values(2, '1998-01-01', '2018-12-12');
+insert into t values(3, '1997-01-01', '2015-01-01');
+insert into t values(4, '2016-01-01', '2020-01-01');
+insert into t values(5, '2010-01-01', '2015-01-01');
+create or replace table t1 (id int, s date, e date, period for apptime(s,e));
+insert t1 select * from t;
+create or replace table t2 (id int, s date, e date, period for apptime(s,e));
+insert t2 select * from t;
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+select * from t;
+id s e
+1 1999-01-01 2000-01-01
+1 1999-01-01 2000-01-01
+1 2018-01-01 2018-12-12
+1 2018-01-01 2019-01-01
+10 2016-01-01 2018-01-01
+11 2010-01-01 2015-01-01
+2 1998-01-01 2000-01-01
+2 2018-01-01 2018-12-12
+3 1997-01-01 2000-01-01
+4 2018-01-01 2020-01-01
+7 2000-01-01 2017-01-01
+7 2000-01-01 2018-01-01
+7 2017-01-01 2018-01-01
+8 2000-01-01 2018-01-01
+9 2000-01-01 2015-01-01
+# Check triggers
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t1 before update on t1
+for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2upd_t1 after update on t1
+for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr1del_t1 before delete on t1
+for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr2del_t1 after delete on t1
+for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr1ins_t1 before insert on t1
+for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2ins_t1 after insert on t1
+for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
+update t1 for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+select * from t1;
+id s e
+1 1999-01-01 2000-01-01
+1 1999-01-01 2000-01-01
+1 2018-01-01 2018-12-12
+1 2018-01-01 2019-01-01
+10 2016-01-01 2018-01-01
+11 2010-01-01 2015-01-01
+2 1998-01-01 2000-01-01
+2 2018-01-01 2018-12-12
+3 1997-01-01 2000-01-01
+4 2018-01-01 2020-01-01
+7 2000-01-01 2017-01-01
+7 2000-01-01 2018-01-01
+7 2017-01-01 2018-01-01
+8 2000-01-01 2018-01-01
+9 2000-01-01 2015-01-01
+select * from log_tbl order by id;
+id log
+1 >UPD: 1, 1999-01-01, 2018-12-12 -> 7, 2000-01-01, 2018-01-01
+2 >INS: 1, 1999-01-01, 2000-01-01
+3 <INS: 1, 1999-01-01, 2000-01-01
+4 >INS: 1, 2018-01-01, 2018-12-12
+5 <INS: 1, 2018-01-01, 2018-12-12
+6 <UPD: 1, 1999-01-01, 2018-12-12 -> 7, 2000-01-01, 2018-01-01
+7 >UPD: 1, 1999-01-01, 2017-01-01 -> 7, 2000-01-01, 2017-01-01
+8 >INS: 1, 1999-01-01, 2000-01-01
+9 <INS: 1, 1999-01-01, 2000-01-01
+10 <UPD: 1, 1999-01-01, 2017-01-01 -> 7, 2000-01-01, 2017-01-01
+11 >UPD: 1, 2017-01-01, 2019-01-01 -> 7, 2017-01-01, 2018-01-01
+12 >INS: 1, 2018-01-01, 2019-01-01
+13 <INS: 1, 2018-01-01, 2019-01-01
+14 <UPD: 1, 2017-01-01, 2019-01-01 -> 7, 2017-01-01, 2018-01-01
+15 >UPD: 2, 1998-01-01, 2018-12-12 -> 8, 2000-01-01, 2018-01-01
+16 >INS: 2, 1998-01-01, 2000-01-01
+17 <INS: 2, 1998-01-01, 2000-01-01
+18 >INS: 2, 2018-01-01, 2018-12-12
+19 <INS: 2, 2018-01-01, 2018-12-12
+20 <UPD: 2, 1998-01-01, 2018-12-12 -> 8, 2000-01-01, 2018-01-01
+21 >UPD: 3, 1997-01-01, 2015-01-01 -> 9, 2000-01-01, 2015-01-01
+22 >INS: 3, 1997-01-01, 2000-01-01
+23 <INS: 3, 1997-01-01, 2000-01-01
+24 <UPD: 3, 1997-01-01, 2015-01-01 -> 9, 2000-01-01, 2015-01-01
+25 >UPD: 4, 2016-01-01, 2020-01-01 -> 10, 2016-01-01, 2018-01-01
+26 >INS: 4, 2018-01-01, 2020-01-01
+27 <INS: 4, 2018-01-01, 2020-01-01
+28 <UPD: 4, 2016-01-01, 2020-01-01 -> 10, 2016-01-01, 2018-01-01
+29 >UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01
+30 <UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01
+# INSERT trigger only also works
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t2 before update on t2
+for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2upd_t2 after update on t2
+for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr1del_t2 before delete on t2
+for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr2del_t2 after delete on t2
+for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
+create trigger tr1ins_t2 before insert on t2
+for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
+create trigger tr2ins_t2 after insert on t2
+for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
+drop trigger tr1upd_t2;
+drop trigger tr2upd_t2;
+update t2 for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+select * from t2;
+id s e
+1 1999-01-01 2000-01-01
+1 1999-01-01 2000-01-01
+1 2018-01-01 2018-12-12
+1 2018-01-01 2019-01-01
+10 2016-01-01 2018-01-01
+11 2010-01-01 2015-01-01
+2 1998-01-01 2000-01-01
+2 2018-01-01 2018-12-12
+3 1997-01-01 2000-01-01
+4 2018-01-01 2020-01-01
+7 2000-01-01 2017-01-01
+7 2000-01-01 2018-01-01
+7 2017-01-01 2018-01-01
+8 2000-01-01 2018-01-01
+9 2000-01-01 2015-01-01
+select * from log_tbl order by id;
+id log
+1 >INS: 1, 1999-01-01, 2000-01-01
+2 <INS: 1, 1999-01-01, 2000-01-01
+3 >INS: 1, 2018-01-01, 2018-12-12
+4 <INS: 1, 2018-01-01, 2018-12-12
+5 >INS: 1, 1999-01-01, 2000-01-01
+6 <INS: 1, 1999-01-01, 2000-01-01
+7 >INS: 1, 2018-01-01, 2019-01-01
+8 <INS: 1, 2018-01-01, 2019-01-01
+9 >INS: 2, 1998-01-01, 2000-01-01
+10 <INS: 2, 1998-01-01, 2000-01-01
+11 >INS: 2, 2018-01-01, 2018-12-12
+12 <INS: 2, 2018-01-01, 2018-12-12
+13 >INS: 3, 1997-01-01, 2000-01-01
+14 <INS: 3, 1997-01-01, 2000-01-01
+15 >INS: 4, 2018-01-01, 2020-01-01
+16 <INS: 4, 2018-01-01, 2020-01-01
+select * from t for portion of apptime from 0 to 1 for system_time all;
+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 'portion of apptime from 0 to 1 for system_time all' at line 1
+update t for portion of apptime from 0 to 1 for system_time all set id=1;
+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 'system_time all set id=1' at line 1
+# Modifying period start/end fields is forbidden.
+# SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a)ii)
+# Neither BSTARTCOL nor BENDCOL shall be an explicit <object column>
+# contained in the <set clause list>.
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id= id + 6, s=subdate(s, 5), e=adddate(e, 5);
+ERROR HY000: Column `s` used in period `apptime` specified in update SET list
+# Precision timestamps
+create or replace table t (id int, s timestamp(5), e timestamp(5),
+period for apptime(s,e));
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+update t for portion of apptime from '2000-01-01 00:00:00.00015'
+ to '2018-01-01 12:34:56.31415'
+ set id= id + 5;
+select * from t;
+id s e
+1 1999-01-01 00:00:00.00000 2000-01-01 00:00:00.00015
+1 1999-01-01 00:00:00.00000 2000-01-01 00:00:00.00015
+1 2018-01-01 12:34:56.31415 2018-12-12 00:00:00.00000
+6 2000-01-01 00:00:00.00015 2017-01-01 00:00:00.00000
+6 2000-01-01 00:00:00.00015 2018-01-01 12:34:56.31415
+# Strings
+create or replace table t (id int, str text, s date, e date,
+period for apptime(s,e));
+insert into t values(1, 'data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id= id + 5;
+select * from t;
+id str s e
+1 data 1999-01-01 2000-01-01
+1 data 2018-01-01 2018-12-12
+1 other data 1999-01-01 2000-01-01
+1 other data 2018-01-01 2018-12-12
+6 data 2000-01-01 2018-01-01
+6 other data 2000-01-01 2018-01-01
+# multi-table UPDATE is impossible
+create or replace table t1(x int);
+update t for portion of apptime from '2000-01-01' to '2018-01-01', t1
+set t.id= t.id + 5;
+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 ' t1
+set t.id= t.id + 5' at line 1
+update t1 set x= (select id from t for portion of apptime from '2000-01-01' to '2018-01-01');
+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 'portion of apptime from '2000-01-01' to '2018-01-01')' at line 1
+# single-table views
+create or replace view v1 as select * from t where id<10;
+update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
+ERROR 42S02: 'v1' is a view
+# multi-table views
+create or replace view v1 as select * from t, t1 where x=id;
+update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
+ERROR 42S02: 'v1' is a view
+# SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a) iii-iv)
+# Let FROMVAL be <point in time 1>. FROMVAL shall not generally contain a
+# reference to a column of T or a <routine invocation>
+# whose subject routine is an SQL-invoked routine that
+# is possibly non-deterministic or that possibly modifies SQL-data.
+# ...Same for <point in time 2> (TOVAL)
+update t for portion of apptime from 5*(5+s) to 1 set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
+update t for portion of apptime from 1 to e set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
+set @s= '2000-01-01';
+set @e= '2018-01-01';
+create or replace function f() returns date return @e;
+create or replace function g() returns date not deterministic return @e;
+create or replace function h() returns date deterministic return @e;
+update t for portion of apptime from @s to f() set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
+update t for portion of apptime from @s to g() set t.id= t.id + 5;
+ERROR HY000: Expression in FOR PORTION OF must be constant
+# success
+update t for portion of apptime from @s to h() set t.id= t.id + 5;
+# select value is cached
+update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
+# auto_inrement field is updated
+create or replace table t (id int primary key auto_increment, x int,
+s date, e date, period for apptime(s, e));
+insert into t values (default, 1, '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+select * from t;
+id x s e
+1 6 2000-01-01 2018-01-01
+2 1 1999-01-01 2000-01-01
+3 1 2018-01-01 2018-12-12
+truncate t;
+insert into t values (default, 1, '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1;
+select * from t;
+id x s e
+1 1 2000-01-01 2018-01-01
+2 1 1999-01-01 2000-01-01
+3 1 2018-01-01 2018-12-12
+# generated columns are updated
+create or replace table t (x int, s date, e date,
+xs date as (s) stored, xe date as (e) stored,
+period for apptime(s, e));
+insert into t values(1, '1999-01-01', '2018-12-12', default, default);
+select * from t;
+x s e xs xe
+1 1999-01-01 2018-12-12 1999-01-01 2018-12-12
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+select *, xs=s and xe=e from t;
+x s e xs xe xs=s and xe=e
+1 1999-01-01 2000-01-01 1999-01-01 2000-01-01 1
+1 2018-01-01 2018-12-12 2018-01-01 2018-12-12 1
+6 2000-01-01 2018-01-01 2000-01-01 2018-01-01 1
+# MDEV-18921 Server crashes in bitmap_bits_set or bitmap_is_set upon
+# UPDATE IGNORE .. FOR PORTION with binary logging
+create or replace table t1 (f int, s date, e date, period for app(s,e));
+insert into t1 values (1,'2016-09-21','2019-06-14');
+update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1;
+drop table t,t1,t2,log_tbl;
+drop view v1;
+drop function f;
+drop function g;
+drop function h;
+drop procedure log;
diff --git a/mysql-test/suite/period/r/versioning.result b/mysql-test/suite/period/r/versioning.result
new file mode 100644
index 00000000000..efb7a646e87
--- /dev/null
+++ b/mysql-test/suite/period/r/versioning.result
@@ -0,0 +1,94 @@
+# DELETE
+create table t (
+s date, e date,
+row_start SYS_TYPE as row start invisible,
+row_end SYS_TYPE as row end invisible,
+period for apptime(s, e),
+period for system_time (row_start, row_end)) with system versioning;
+insert into t values('1999-01-01', '2018-12-12'),
+('1999-01-01', '1999-12-12');
+select row_start into @ins_time from t limit 1;
+select * from t order by s, e;
+s e
+1999-01-01 1999-12-12
+1999-01-01 2018-12-12
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+from t for system_time all
+order by s, e, row_start;
+s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
+1999-01-01 1999-12-12 OLD CURRENT ROW
+1999-01-01 2000-01-01 NEW CURRENT ROW
+1999-01-01 2018-12-12 OLD HISTORICAL ROW
+2018-01-01 2018-12-12 NEW CURRENT ROW
+# same for trigger case
+delete from t;
+delete history from t;
+insert into t values('1999-01-01', '2018-12-12'),
+('1999-01-01', '1999-12-12');
+create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
+create or replace procedure log(s text)
+insert into log_tbl(log) values(s);
+create trigger tr1upd_t before update on t
+for each row call log(CONCAT('>UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr2upd_t after update on t
+for each row call log(CONCAT('<UPD: ', old.s, ", ", old.e, ' -> ', new.s, ", ", new.e));
+create trigger tr1del_t before delete on t
+for each row call log(CONCAT('>DEL: ', old.s, ", ", old.e));
+create trigger tr2del_t after delete on t
+for each row call log(CONCAT('<DEL: ', old.s, ", ", old.e));
+create trigger tr1ins_t before insert on t
+for each row call log(CONCAT('>INS: ', new.s, ", ", new.e));
+create trigger tr2ins_t after insert on t
+for each row call log(CONCAT('<INS: ', new.s, ", ", new.e));
+select row_start into @ins_time from t limit 1;
+select * from t order by s, e;
+s e
+1999-01-01 1999-12-12
+1999-01-01 2018-12-12
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+from t for system_time all
+order by s, e, row_start;
+s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
+1999-01-01 1999-12-12 OLD CURRENT ROW
+1999-01-01 2000-01-01 NEW CURRENT ROW
+1999-01-01 2018-12-12 OLD HISTORICAL ROW
+2018-01-01 2018-12-12 NEW CURRENT ROW
+select * from log_tbl order by id;
+id log
+1 >DEL: 1999-01-01, 2018-12-12
+2 >INS: 1999-01-01, 2000-01-01
+3 <INS: 1999-01-01, 2000-01-01
+4 >INS: 2018-01-01, 2018-12-12
+5 <INS: 2018-01-01, 2018-12-12
+6 <DEL: 1999-01-01, 2018-12-12
+# UPDATE
+create or replace table t (x int, s date, e date,
+row_start SYS_TYPE as row start invisible,
+row_end SYS_TYPE as row end invisible,
+period for apptime(s, e),
+period for system_time(row_start, row_end)) with system versioning;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+(2, '1999-01-01', '1999-12-12');
+select row_start into @ins_time from t limit 1;
+select * from t;
+x s e
+1 1999-01-01 2018-12-12
+2 1999-01-01 1999-12-12
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+from t for system_time all
+order by x, s, e, row_start;
+x s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end)
+1 1999-01-01 2000-01-01 NEW CURRENT ROW
+1 1999-01-01 2018-12-12 OLD HISTORICAL ROW
+1 2018-01-01 2018-12-12 NEW CURRENT ROW
+2 1999-01-01 1999-12-12 OLD CURRENT ROW
+6 2000-01-01 2018-01-01 NEW CURRENT ROW
+drop table t,log_tbl;
+drop function check_row;
+drop function current_row;
+drop procedure verify_trt;
+drop procedure verify_trt_dummy;
+drop procedure log;
diff --git a/mysql-test/suite/period/t/alter.test b/mysql-test/suite/period/t/alter.test
new file mode 100644
index 00000000000..3f45d68cd61
--- /dev/null
+++ b/mysql-test/suite/period/t/alter.test
@@ -0,0 +1,133 @@
+set @s= '1992-01-01';
+set @e= '1999-12-31';
+
+create table t (s date, e date);
+
+--echo # period start/end columns are implicit NOT NULL
+alter table t add period for a(s, e);
+show create table t;
+
+create or replace table t (s date, e date);
+alter table t change s s date, add period for a(s, e);
+show create table t;
+
+alter table t add id int;
+show create table t;
+alter table t drop id;
+show create table t;
+
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+
+--error ER_PERIOD_CONSTRAINT_DROP
+alter table t drop constraint a;
+
+--echo # no-op
+alter table t drop period if exists for b;
+--echo # no-op
+alter table t add period if not exists for a(e, s);
+
+alter table t drop period if exists for a;
+--echo # no-op
+alter table t drop period if exists for a;
+
+alter table t add period for a(s, e), add period if not exists for a(e, s);
+show create table t;
+
+alter table t drop period for a;
+--echo # Constraint is dropped
+insert t values(@e, @s);
+
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t drop period for a;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t add period for a(s, e), drop period for a;
+
+truncate t;
+alter table t add period for a(s, e);
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+alter table t add period for a(s, e), drop period for a;
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+alter table t add s1 date not null, add period for b(s1, e), drop period for a;
+show create table t;
+insert t(s, s1, e) values(@e, @s, @e);
+--error ER_CONSTRAINT_FAILED
+insert t(s, s1, e) values(@e, @e, @s);
+
+create table t1 like t;
+show create table t1;
+drop table t1;
+
+--error ER_CONSTRAINT_FAILED
+create table t2 (period for b(s,e)) select * from t;
+
+create table t2 (period for b(s1,e)) select * from t;
+drop table t2;
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
+--echo # The declared type of BC1 shall be either DATE or a timestamp type
+--echo # and shall be equivalent to the declared type of BC2.
+create or replace table t (s timestamp not null, e timestamp(6) not null);
+--error ER_PERIOD_TYPES_MISMATCH
+alter table t add period for a(s, e);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
+--echo # No column of T shall have a column name that is equivalent to ATPN.
+create or replace table t (a int, s date, e date);
+--error ER_DUP_FIELDNAME
+alter table t add period for a(s, e);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
+--echo # Neither BC1 nor BC2 shall be an identity column, a generated column,
+--echo # a system-time period start column, or a system-time period end column.
+create or replace table t (id int primary key,
+ s date,
+ e date generated always as (s+1));
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, e);
+
+create or replace table t (id int primary key,
+ s date,
+ e date as (s+1) VIRTUAL);
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, e);
+
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+ st timestamp(6) as row start,
+ en timestamp(6) as row end,
+ period for system_time (st, en)) with system versioning;
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, en);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
+--echo # The table descriptor of T shall not include a period descriptor other
+--echo # than a system-time period descriptor.
+alter table t add period for a(s, e);
+--error ER_MORE_THAN_ONE_PERIOD
+alter table t add period for b(s, e);
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
+--echo # Let S be the schema identified by the explicit or implicit
+--echo # <schema name> of TN. Let IDCN be an implementation-dependent
+--echo # <constraint name> that is not equivalent to the <constraint name> of
+--echo # any table constraint descriptor included in S. The following
+--echo # <table constraint definition> is implicit:
+--echo # CONSTRAINT IDCN CHECK ( CN1 < CN2 )
+--echo #
+--echo # Due to the above standard limitation, the constraint name can't always
+--echo # match the period name. So it matches when possible; and when not, it
+--echo # is unique not taken name prefixed with period name.
+create or replace table t (x int, s date, e date,
+ period for mytime(s, e));
+show create table t;
+alter table t add constraint mytime check (x > 1);
+show create table t;
+--error ER_CONSTRAINT_FAILED
+insert t values (2, @e, @s);
+alter table t add constraint mytime_1 check (x > 2);
+--error ER_CONSTRAINT_FAILED
+insert t values (3, @e, @s);
+
+drop table t;
diff --git a/mysql-test/suite/period/t/create.test b/mysql-test/suite/period/t/create.test
new file mode 100644
index 00000000000..2e3de795698
--- /dev/null
+++ b/mysql-test/suite/period/t/create.test
@@ -0,0 +1,81 @@
+create table t (id int primary key, s date, e date, period for mytime(s,e));
+--echo # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
+--echo # this is important for correct command-based replication
+show create table t;
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+ period for mytime(s,e));
+show create table t;
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)a)
+--echo # 2) If a <table period definition> TPD is specified, then:
+--echo # a) <table scope> shall not be specified.
+--error ER_PERIOD_TEMPORARY_NOT_ALLOWED
+create or replace temporary table t (s date, e date, period for mytime(s,e));
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)iii)
+--echo # The <data type or domain name> contained in CD1 is either DATE or a
+--echo # timestamp type and it is equivalent to the <data type or domain name>
+--echo # contained in CD2.
+--error ER_PERIOD_TYPES_MISMATCH
+create or replace table t (id int primary key, s datetime, e date,
+ period for mytime(s,e));
+--error ER_PERIOD_TYPES_MISMATCH
+create or replace table t (s timestamp(2), e timestamp(6),
+ period for mytime(s,e));
+--error ER_WRONG_FIELD_SPEC
+create or replace table t (id int primary key, s int, e date,
+ period for mytime(s,e));
+--error ER_WRONG_FIELD_SPEC
+create or replace table t (id int primary key, s time, e time,
+ period for mytime(s,e));
+--error ER_BAD_FIELD_ERROR
+create or replace table t (id int primary key, s date, e date,
+ period for mytime(s,x));
+--error ER_MORE_THAN_ONE_PERIOD
+create or replace table t (id int primary key, s date, e date,
+ period for mytime(s,e),
+ period for mytime2(s,e));
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)d)
+--echo # No <column name> in any <column definition> shall be equivalent to PN.
+--error ER_DUP_FIELDNAME
+create or replace table t (mytime int, s date, e date,
+ period for mytime(s,e));
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)A)
+--echo # Neither CD1 nor CD2 shall contain an <identity column specification>, a
+--echo # <generation clause>, a <system time period start column specification>,
+--echo # or a <system time period end column specification>.
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+create or replace table t (id int primary key,
+ s date,
+ e date generated always as (s+1),
+ period for mytime(s,e));
+
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+create or replace table t (id int primary key,
+ s date,
+ e date as (s+1) VIRTUAL,
+ period for mytime(s,e));
+
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+ st timestamp(6) as row start,
+ en timestamp(6) as row end,
+ period for system_time (st, en),
+ period for mytime(st,e)) with system versioning;
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)
+--echo # Let IDCN be an implementation-dependent <constraint name> that is not
+--echo # equivalent to the <constraint name> of any table constraint descriptor
+--echo # included in S.
+create or replace table t (x int, s date, e date,
+ period for mytime(s, e),
+ constraint mytime check (x > 1));
+show create table t;
+--error ER_CONSTRAINT_FAILED
+insert t values (2, '2001-01-01', '2001-01-01');
+
+show status like "Feature_application_time_periods";
+
+drop table t;
diff --git a/mysql-test/suite/period/t/delete.test b/mysql-test/suite/period/t/delete.test
new file mode 100644
index 00000000000..00bc314160f
--- /dev/null
+++ b/mysql-test/suite/period/t/delete.test
@@ -0,0 +1,186 @@
+source suite/period/engines.inc;
+source include/have_log_bin.inc;
+
+create table t (id int, s date, e date, period for apptime(s,e));
+
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+insert into t values(1, '2017-01-01', '2019-01-01');
+insert into t values(2, '1998-01-01', '2018-12-12');
+insert into t values(3, '1997-01-01', '2015-01-01');
+insert into t values(4, '2016-01-01', '2020-01-01');
+insert into t values(5, '2010-01-01', '2015-01-01');
+
+create or replace table t1 (id int, s date, e date, period for apptime(s,e));
+insert t1 select * from t;
+create or replace table t2 (id int, s date, e date, period for apptime(s,e));
+insert t2 select * from t;
+create or replace table t3 (id int, s date, e date, period for apptime(s,e));
+insert t3 select * from t;
+
+--let $trig_cols=id, s, e
+--let $trig_table=t1
+--source suite/period/create_triggers.inc
+
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+delete from t1 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+--sorted_result
+select * from t;
+--sorted_result
+select * from t1;
+select * from log_tbl order by id;
+
+--echo # INSERT trigger only also works
+--let $trig_cols=id, s, e
+--let $trig_table=t2
+--source suite/period/create_triggers.inc
+drop trigger tr1del_t2;
+drop trigger tr2del_t2;
+delete from t2 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+select * from log_tbl order by id;
+
+--echo # removing BEFORE INSERT trigger enables internal substitution
+--echo # DELETE+INSERT -> UPDATE, but without any side effects.
+--echo # The optimization is disabled for non-transactional engines
+--let $trig_table=t3
+--source suite/period/create_triggers.inc
+drop trigger tr1ins_t3;
+delete from t3 for portion of APPTIME from '2000-01-01' to '2018-01-01';
+select * from log_tbl order by id;
+
+--echo # multi-table DELETE is not possible
+--error ER_PARSE_ERROR
+delete t, t1 from t1, t for portion of apptime from '2000-01-01' to '2018-01-01';
+
+--error ER_PARSE_ERROR
+delete t for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1;
+
+--echo # Here another check fails before parsing ends
+--error ER_UNKNOWN_TABLE
+delete t, t1 from t for portion of apptime from '2000-01-01' to '2018-01-01', t1;
+
+--error ER_PARSE_ERROR
+delete history from t2 for portion of apptime from '2000-01-01' to '2018-01-01';
+
+--error ER_PERIOD_NOT_FOUND
+delete from t for portion of othertime from '2000-01-01' to '2018-01-01';
+--error ER_PARSE_ERROR
+delete from t for portion of system_time from '2000-01-01' to '2018-01-01';
+
+create or replace table t (id int, str text, s date, e date,
+ period for apptime(s,e));
+
+insert into t values(1, 'data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'deleted', '2000-01-01', '2018-01-01');
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+show warnings;
+--sorted_result
+select * from t;
+
+drop table t1;
+
+--echo # SQL16, Part 2, 15.7 <Effect of deleting rows from base tables>,
+--echo # General rules, 8)b)i)
+--echo # If the column descriptor that corresponds to the i-th field of BR
+--echo # describes an identity column, a generated column, a system-time period
+--echo # start column, or a system-time period end column, then let V i be
+--echo # DEFAULT.
+
+--echo # auto_increment field is updated
+create or replace table t (id int primary key auto_increment, s date, e date,
+ period for apptime(s, e));
+insert into t values (default, '1999-01-01', '2018-12-12');
+select * from t;
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+--sorted_result
+select * from t;
+truncate t;
+--echo # same for trigger case
+insert into t values (default, '1999-01-01', '2018-12-12');
+--let $trig_table=t
+--source suite/period/create_triggers.inc
+
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+--sorted_result
+select * from t;
+select * from log_tbl order by id;
+
+--echo # generated columns are updated
+create or replace table t (s date, e date,
+ xs date as (s) stored, xe date as (e) stored,
+ period for apptime(s, e));
+insert into t values('1999-01-01', '2018-12-12', default, default);
+--sorted_result
+select * from t;
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+--sorted_result
+select * from t;
+truncate t;
+--echo # same for trigger case
+insert into t values('1999-01-01', '2018-12-12', default, default);
+--let $trig_table=t
+--source suite/period/create_triggers.inc
+
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+--sorted_result
+select * from t;
+select * from log_tbl order by id;
+
+--echo # View can't be used
+create or replace view v as select * from t;
+--error ER_IT_IS_A_VIEW
+delete from v for portion of p from '2000-01-01' to '2018-01-01';
+
+--echo # View can't be used
+create or replace view v as select t.* from t, t as t1;
+--error ER_VIEW_DELETE_MERGE_VIEW
+delete from v for portion of p from '2000-01-01' to '2018-01-01';
+
+--echo # auto_increment field overflow
+create or replace table t (id tinyint auto_increment primary key,
+ s date, e date, period for apptime(s,e));
+
+insert into t values(127, '1999-01-01', '2018-12-12');
+
+--error HA_ERR_AUTOINC_ERANGE
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+
+--echo # same for trigger case
+--let $trig_table=t
+--source suite/period/create_triggers.inc
+--echo # negotiate side effects of non-transactional MyISAM engine
+replace into t values(127, '1999-01-01', '2018-12-12');
+select * from t;
+truncate table log_tbl;
+
+--error HA_ERR_AUTOINC_ERANGE
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select * from t;
+select * from log_tbl order by id;
+
+--echo # custom constraint for period fields
+create or replace table t(id int, s date, e date, period for apptime(s,e),
+ constraint dist2days check (datediff(e, s) >= 2));
+insert into t values(1, '1999-01-01', '2018-12-12'),
+ (2, '1999-01-01', '1999-12-12');
+--error ER_CONSTRAINT_FAILED
+delete from t for portion of apptime from '1999-01-02' to '2018-12-12';
+--echo # negotiate side effects of non-transactional MyISAM engine
+truncate t;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+ (2, '1999-01-01', '1999-12-12');
+--error ER_CONSTRAINT_FAILED
+delete from t for portion of apptime from '1999-01-01' to '2018-12-11';
+truncate t;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+ (2, '1999-01-01', '1999-12-12');
+
+delete from t for portion of apptime from '1999-01-03' to '2018-12-10';
+--sorted_result
+select *, datediff(e, s) from t;
+
+drop table t,t2,t3,log_tbl;
+drop view v;
+drop procedure log;
diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test
new file mode 100644
index 00000000000..5730387dfda
--- /dev/null
+++ b/mysql-test/suite/period/t/update.test
@@ -0,0 +1,165 @@
+source suite/period/engines.inc;
+source include/have_log_bin.inc;
+
+create table t (id int, s date, e date, period for apptime(s,e));
+
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+insert into t values(1, '2017-01-01', '2019-01-01');
+insert into t values(2, '1998-01-01', '2018-12-12');
+insert into t values(3, '1997-01-01', '2015-01-01');
+insert into t values(4, '2016-01-01', '2020-01-01');
+insert into t values(5, '2010-01-01', '2015-01-01');
+
+create or replace table t1 (id int, s date, e date, period for apptime(s,e));
+insert t1 select * from t;
+create or replace table t2 (id int, s date, e date, period for apptime(s,e));
+insert t2 select * from t;
+
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+--sorted_result
+select * from t;
+
+--echo # Check triggers
+--let $trig_cols=id, s, e
+--let $trig_table=t1
+--source suite/period/create_triggers.inc
+
+update t1 for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+--sorted_result
+select * from t1;
+select * from log_tbl order by id;
+
+--echo # INSERT trigger only also works
+--let $trig_cols=id, s, e
+--let $trig_table=t2
+--source suite/period/create_triggers.inc
+drop trigger tr1upd_t2;
+drop trigger tr2upd_t2;
+update t2 for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id=id + 6;
+--sorted_result
+select * from t2;
+select * from log_tbl order by id;
+
+--error ER_PARSE_ERROR
+select * from t for portion of apptime from 0 to 1 for system_time all;
+--error ER_PARSE_ERROR
+update t for portion of apptime from 0 to 1 for system_time all set id=1;
+
+--echo # Modifying period start/end fields is forbidden.
+--echo # SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a)ii)
+--echo # Neither BSTARTCOL nor BENDCOL shall be an explicit <object column>
+--echo # contained in the <set clause list>.
+--error ER_PERIOD_COLUMNS_UPDATED
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id= id + 6, s=subdate(s, 5), e=adddate(e, 5);
+
+--echo # Precision timestamps
+create or replace table t (id int, s timestamp(5), e timestamp(5),
+ period for apptime(s,e));
+insert into t values(1, '1999-01-01', '2018-12-12');
+insert into t values(1, '1999-01-01', '2017-01-01');
+update t for portion of apptime from '2000-01-01 00:00:00.00015'
+ to '2018-01-01 12:34:56.31415'
+ set id= id + 5;
+--sorted_result
+select * from t;
+
+-- echo # Strings
+create or replace table t (id int, str text, s date, e date,
+ period for apptime(s,e));
+
+insert into t values(1, 'data', '1999-01-01', '2018-12-12');
+insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01'
+ set id= id + 5;
+--sorted_result
+select * from t;
+
+--echo # multi-table UPDATE is impossible
+create or replace table t1(x int);
+--error ER_PARSE_ERROR
+update t for portion of apptime from '2000-01-01' to '2018-01-01', t1
+ set t.id= t.id + 5;
+
+--error ER_PARSE_ERROR
+update t1 set x= (select id from t for portion of apptime from '2000-01-01' to '2018-01-01');
+
+--echo # single-table views
+create or replace view v1 as select * from t where id<10;
+--error ER_IT_IS_A_VIEW
+update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
+
+--echo # multi-table views
+create or replace view v1 as select * from t, t1 where x=id;
+--error ER_IT_IS_A_VIEW
+update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
+
+--echo # SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a) iii-iv)
+--echo # Let FROMVAL be <point in time 1>. FROMVAL shall not generally contain a
+--echo # reference to a column of T or a <routine invocation>
+--echo # whose subject routine is an SQL-invoked routine that
+--echo # is possibly non-deterministic or that possibly modifies SQL-data.
+--echo # ...Same for <point in time 2> (TOVAL)
+--error ER_NOT_CONSTANT_EXPRESSION
+update t for portion of apptime from 5*(5+s) to 1 set t.id= t.id + 5;
+--error ER_NOT_CONSTANT_EXPRESSION
+update t for portion of apptime from 1 to e set t.id= t.id + 5;
+
+set @s= '2000-01-01';
+set @e= '2018-01-01';
+
+create or replace function f() returns date return @e;
+create or replace function g() returns date not deterministic return @e;
+create or replace function h() returns date deterministic return @e;
+
+--error ER_NOT_CONSTANT_EXPRESSION
+update t for portion of apptime from @s to f() set t.id= t.id + 5;
+--error ER_NOT_CONSTANT_EXPRESSION
+update t for portion of apptime from @s to g() set t.id= t.id + 5;
+
+--echo # success
+update t for portion of apptime from @s to h() set t.id= t.id + 5;
+--echo # select value is cached
+update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
+
+--echo # auto_inrement field is updated
+create or replace table t (id int primary key auto_increment, x int,
+ s date, e date, period for apptime(s, e));
+insert into t values (default, 1, '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+--sorted_result
+select * from t;
+
+truncate t;
+insert into t values (default, 1, '1999-01-01', '2018-12-12');
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1;
+--sorted_result
+select * from t;
+
+--echo # generated columns are updated
+create or replace table t (x int, s date, e date,
+ xs date as (s) stored, xe date as (e) stored,
+ period for apptime(s, e));
+insert into t values(1, '1999-01-01', '2018-12-12', default, default);
+--sorted_result
+select * from t;
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+--sorted_result
+select *, xs=s and xe=e from t;
+
+--echo # MDEV-18921 Server crashes in bitmap_bits_set or bitmap_is_set upon
+--echo # UPDATE IGNORE .. FOR PORTION with binary logging
+create or replace table t1 (f int, s date, e date, period for app(s,e));
+insert into t1 values (1,'2016-09-21','2019-06-14');
+update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1;
+
+drop table t,t1,t2,log_tbl;
+drop view v1;
+drop function f;
+drop function g;
+drop function h;
+drop procedure log;
diff --git a/mysql-test/suite/period/t/versioning.test b/mysql-test/suite/period/t/versioning.test
new file mode 100644
index 00000000000..ea20344515d
--- /dev/null
+++ b/mysql-test/suite/period/t/versioning.test
@@ -0,0 +1,64 @@
+source suite/versioning/engines.inc;
+source suite/versioning/common.inc;
+
+--echo # DELETE
+--replace_result $sys_datatype_expl SYS_TYPE
+eval create table t (
+ s date, e date,
+ row_start $sys_datatype_expl as row start invisible,
+ row_end $sys_datatype_expl as row end invisible,
+ period for apptime(s, e),
+ period for system_time (row_start, row_end)) with system versioning;
+insert into t values('1999-01-01', '2018-12-12'),
+ ('1999-01-01', '1999-12-12');
+
+select row_start into @ins_time from t limit 1;
+select * from t order by s, e;
+
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+ from t for system_time all
+ order by s, e, row_start;
+
+--echo # same for trigger case
+delete from t;
+delete history from t;
+insert into t values('1999-01-01', '2018-12-12'),
+ ('1999-01-01', '1999-12-12');
+--let $trig_table=t
+--source suite/period/create_triggers.inc
+
+select row_start into @ins_time from t limit 1;
+select * from t order by s, e;
+
+delete from t for portion of apptime from '2000-01-01' to '2018-01-01';
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+ from t for system_time all
+ order by s, e, row_start;
+select * from log_tbl order by id;
+
+--echo # UPDATE
+--replace_result $sys_datatype_expl SYS_TYPE
+eval create or replace table t (x int, s date, e date,
+ row_start $sys_datatype_expl as row start invisible,
+ row_end $sys_datatype_expl as row end invisible,
+ period for apptime(s, e),
+ period for system_time(row_start, row_end)) with system versioning;
+insert into t values(1, '1999-01-01', '2018-12-12'),
+ (2, '1999-01-01', '1999-12-12');
+
+select row_start into @ins_time from t limit 1;
+--sorted_result
+select * from t;
+
+update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
+select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end)
+ from t for system_time all
+ order by x, s, e, row_start;
+
+drop table t,log_tbl;
+drop function check_row;
+drop function current_row;
+drop procedure verify_trt;
+drop procedure verify_trt_dummy;
+drop procedure log;
View Lorry Controller Status