MDEV-16974 Application-time periods: UPDATE

8 files changed, 579 insertions, 25 deletions

diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result
new file mode 100644
index 00000000000..2ad5f420b41
--- /dev/null
+++ b/mysql-test/suite/period/r/update.result
@@ -0,0 +1,251 @@
+create or replace 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
+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
+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
+# 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
+# system_time columns are updated
+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
+create or replace database test;
diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test
new file mode 100644
index 00000000000..e50060791a7
--- /dev/null
+++ b/mysql-test/suite/period/t/update.test
@@ -0,0 +1,163 @@
+source suite/versioning/engines.inc;
+source suite/versioning/common.inc;
+
+create or replace 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 # 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 # system_time columns are updated
+--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;
+
+create or replace database test;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index b83ed4944a3..d46594d1d37 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -5838,10 +5838,10 @@ ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
         eng "Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed"
         ger "Konstante oder Random-Ausdrücke in (Unter-)Partitionsfunktionen sind nicht erlaubt"
         swe "Konstanta uttryck eller slumpmässiga uttryck är inte tillåtna (sub)partitioneringsfunktioner"
-ER_NO_CONST_EXPR_IN_RANGE_OR_LIST_ERROR
-        eng "Expression in RANGE/LIST VALUES must be constant"
-        ger "Ausdrücke in RANGE/LIST VALUES müssen konstant sein"
-        swe "Uttryck i RANGE/LIST VALUES måste vara ett konstant uttryck"
+ER_NOT_CONSTANT_EXPRESSION
+        eng "Expression in %s must be constant"
+        ger "Ausdrücke in %s müssen konstant sein"
+        swe "Uttryck i %s måste vara ett konstant uttryck"
 ER_FIELD_NOT_FOUND_PART_ERROR
         eng "Field in list of fields for partition function not found in table"
         ger "Felder in der Feldliste der Partitionierungsfunktion wurden in der Tabelle nicht gefunden"
@@ -7946,3 +7946,5 @@ ER_PERIOD_FIELD_WRONG_ATTRIBUTES
         eng "Period field %`s cannot be %s"
 ER_PERIOD_NOT_FOUND
         eng "Period %`s is not found in table"
+ER_PERIOD_COLUMNS_UPDATED
+        eng "Column %`s used in period %`s specified in update SET list"
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index a9109ce67c8..abfcf0865fe 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -842,9 +842,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       {
         error= table->delete_row();
 
+        ha_rows rows_inserted;
         if (likely(!error) && table_list->has_period()
             && !portion_of_time_through_update)
-          error= table->insert_portion_of_time(thd, table_list->period_conditions);
+          error= table->insert_portion_of_time(thd, table_list->period_conditions,
+                                               &rows_inserted);
       }
 
       if (likely(!error))
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d74da408dfc..bb0fbe2a090 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -132,7 +132,8 @@ bool compare_record(const TABLE *table)
     FALSE Items are OK
 */
 
-static bool check_fields(THD *thd, List<Item> &items, bool update_view)
+static bool check_fields(THD *thd, TABLE_LIST *table, List<Item> &items,
+                         bool update_view)
 {
   Item *item;
   if (update_view)
@@ -177,6 +178,22 @@ static bool check_fields(THD *thd, List<Item> &items, bool update_view)
       f->set_has_explicit_value();
     }
   }
+
+  if (table->has_period())
+  {
+    DBUG_ASSERT(thd->lex->sql_command == SQLCOM_UPDATE);
+    for (List_iterator_fast<Item> it(items); (item=it++);)
+    {
+      Field *f= item->field_for_view_update()->field;
+      vers_select_conds_t &period= table->period_conditions;
+      if (period.field_start->field == f || period.field_end->field == f)
+      {
+        my_error(ER_PERIOD_COLUMNS_UPDATED, MYF(0),
+                 item->name.str, period.name.str);
+        return true;
+      }
+    }
+  }
   return FALSE;
 }
 
@@ -267,6 +284,31 @@ static void prepare_record_for_error_message(int error, TABLE *table)
 }
 
 
+static
+int cut_fields_for_portion_of_time(THD *thd, TABLE *table,
+                                   const vers_select_conds_t &period_conds)
+{
+  bool lcond= period_conds.field_start->val_datetime_packed(thd)
+              < period_conds.start.item->val_datetime_packed(thd);
+  bool rcond= period_conds.field_end->val_datetime_packed(thd)
+              > period_conds.end.item->val_datetime_packed(thd);
+
+  Field *start_field= table->field[table->s->period.start_fieldno];
+  Field *end_field= table->field[table->s->period.end_fieldno];
+
+  DBUG_ASSERT(!start_field->has_explicit_value()
+              && !end_field->has_explicit_value());
+
+  int res= 0;
+  if (lcond)
+    res= period_conds.start.item->save_in_field(start_field, true);
+
+  if (likely(!res) && rcond)
+    res= period_conds.end.item->save_in_field(end_field, true);
+
+  return res;
+}
+
 /*
   Process usual UPDATE
 
@@ -330,7 +372,7 @@ int mysql_update(THD *thd,
   query_plan.using_filesort= FALSE;
 
   // For System Versioning (may need to insert new fields to a table).
-  ha_rows updated_sys_ver= 0;
+  ha_rows rows_inserted= 0;
 
   DBUG_ENTER("mysql_update");
 
@@ -398,7 +440,7 @@ int mysql_update(THD *thd,
   if (setup_fields_with_no_wrap(thd, Ref_ptr_array(),
                                 fields, MARK_COLUMNS_WRITE, 0, 0))
     DBUG_RETURN(1);                     /* purecov: inspected */
-  if (check_fields(thd, fields, table_list->view))
+  if (check_fields(thd, table_list, fields, table_list->view))
   {
     DBUG_RETURN(1);
   }
@@ -509,7 +551,10 @@ int mysql_update(THD *thd,
   if (unlikely(init_ftfuncs(thd, select_lex, 1)))
     goto err;
 
-  table->mark_columns_needed_for_update();
+  if (table_list->has_period())
+    table->use_all_columns();
+  else
+    table->mark_columns_needed_for_update();
 
   table->update_const_key_parts(conds);
   order= simple_remove_const(order, conds);
@@ -590,6 +635,14 @@ int mysql_update(THD *thd,
                                                 TRG_ACTION_BEFORE) ||
                  table->triggers->has_triggers(TRG_EVENT_UPDATE,
                                                TRG_ACTION_AFTER)));
+
+  if (table_list->has_period())
+    has_triggers= table->triggers &&
+                  (table->triggers->has_triggers(TRG_EVENT_INSERT,
+                                                 TRG_ACTION_BEFORE)
+                   || table->triggers->has_triggers(TRG_EVENT_INSERT,
+                                                    TRG_ACTION_AFTER)
+                   || has_triggers);
   DBUG_PRINT("info", ("has_triggers: %s", has_triggers ? "TRUE" : "FALSE"));
   binlog_is_row= thd->is_current_stmt_binlog_format_row();
   DBUG_PRINT("info", ("binlog_is_row: %s", binlog_is_row ? "TRUE" : "FALSE"));
@@ -880,14 +933,25 @@ update_begin:
       explain->tracker.on_record_after_where();
       store_record(table,record[1]);
 
+      if (table_list->has_period())
+        cut_fields_for_portion_of_time(thd, table,
+                                       table_list->period_conditions);
+
       if (fill_record_n_invoke_before_triggers(thd, table, fields, values, 0,
                                                TRG_EVENT_UPDATE))
         break; /* purecov: inspected */
 
       found++;
 
-      if (!can_compare_record || compare_record(table))
+      bool record_was_same= false;
+      bool need_update= !can_compare_record || compare_record(table);
+
+      if (need_update)
       {
+        if (table->versioned(VERS_TIMESTAMP) &&
+            thd->lex->sql_command == SQLCOM_DELETE)
+          table->vers_update_end();
+
         if (table->default_field && table->update_default_fields(1, ignore))
         {
           error= 1;
@@ -946,7 +1010,9 @@ update_begin:
           error= table->file->ha_update_row(table->record[1],
                                             table->record[0]);
         }
-        if (unlikely(error == HA_ERR_RECORD_IS_THE_SAME))
+
+        record_was_same= error == HA_ERR_RECORD_IS_THE_SAME;
+        if (unlikely(record_was_same))
         {
           error= 0;
         }
@@ -961,12 +1027,22 @@ update_begin:
               restore_record(table, record[2]);
             }
             if (likely(!error))
-              updated_sys_ver++;
+              rows_inserted++;
           }
           if (likely(!error))
             updated++;
         }
 
+        if (likely(!error) && !record_was_same && table_list->has_period())
+        {
+          store_record(table, record[2]);
+          restore_record(table, record[1]);
+          error= table->insert_portion_of_time(thd,
+                                               table_list->period_conditions,
+                                               &rows_inserted);
+          restore_record(table, record[2]);
+        }
+
         if (unlikely(error) &&
             (!ignore || table->file->is_fatal_error(error, HA_CHECK_ALL)))
         {
@@ -1107,6 +1183,8 @@ update_end:
   delete select;
   select= NULL;
   THD_STAGE_INFO(thd, stage_end);
+  if (table_list->has_period())
+    table->file->ha_release_auto_increment();
   (void) table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
 
   /*
@@ -1169,14 +1247,14 @@ update_end:
   if (likely(error < 0) && likely(!thd->lex->analyze_stmt))
   {
     char buff[MYSQL_ERRMSG_SIZE];
-    if (!table->versioned(VERS_TIMESTAMP))
+    if (!table->versioned(VERS_TIMESTAMP) && !table_list->has_period())
       my_snprintf(buff, sizeof(buff), ER_THD(thd, ER_UPDATE_INFO), (ulong) found,
                   (ulong) updated,
                   (ulong) thd->get_stmt_da()->current_statement_warn_count());
     else
       my_snprintf(buff, sizeof(buff),
                   ER_THD(thd, ER_UPDATE_INFO_WITH_SYSTEM_VERSIONING),
-                  (ulong) found, (ulong) updated, (ulong) updated_sys_ver,
+                  (ulong) found, (ulong) updated, (ulong) rows_inserted,
                   (ulong) thd->get_stmt_da()->current_statement_warn_count());
     my_ok(thd, (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated,
           id, buff);
@@ -1257,6 +1335,19 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
 
   thd->lex->allow_sum_func.clear_all();
 
+  if (table_list->has_period())
+  {
+    if (table_list->is_view_or_derived())
+    {
+      my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str);
+      DBUG_RETURN(true);
+    }
+
+    *conds= select_lex->period_setup_conds(thd, table_list, *conds);
+    if (!*conds)
+      DBUG_RETURN(true);
+  }
+
   /*
     We do not call DT_MERGE_FOR_INSERT because it has no sense for simple
     (not multi-) update
@@ -1276,6 +1367,16 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
       setup_ftfuncs(select_lex))
     DBUG_RETURN(TRUE);
 
+  if (table_list->has_period())
+  {
+    if (!table_list->period_conditions.start.item->const_item()
+        || !table_list->period_conditions.end.item->const_item())
+    {
+      my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF");
+      DBUG_RETURN(true);
+    }
+  }
+
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
@@ -1589,7 +1690,7 @@ int mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  if (check_fields(thd, *fields, update_view))
+  if (check_fields(thd, table_list, *fields, update_view))
   {
     DBUG_RETURN(TRUE);
   }
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 85a0b14f96c..60fbd0e2722 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1929,6 +1929,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         table_primary_ident table_primary_derived
         derived_table_list table_reference_list_parens
         nested_table_reference_list join_table_parens
+        update_table_list
 %type <date_time_type> date_time_type;
 %type <interval> interval
 
@@ -9444,12 +9445,9 @@ history_point:
             $$= Vers_history_point($1, $2);
           }
         ;
-opt_for_portion_of_time_clause:
-          /* empty */
-          {
-            $$= false;
-          }
-        | FOR_SYM PORTION_SYM OF_SYM remember_tok_start ident FROM
+
+for_portion_of_time_clause:
+          FOR_SYM PORTION_SYM OF_SYM remember_tok_start ident FROM
           bit_expr TO_SYM bit_expr
           {
             if (unlikely(0 == strcasecmp($5.str, "SYSTEM_TIME")))
@@ -9461,6 +9459,15 @@ opt_for_portion_of_time_clause:
                                         Vers_history_point(VERS_TIMESTAMP, $7),
                                         Vers_history_point(VERS_TIMESTAMP, $9),
                                         $5);
+          }
+
+opt_for_portion_of_time_clause:
+          /* empty */
+          {
+            $$= false;
+          }
+        | for_portion_of_time_clause
+          {
             $$= true;
           }
         ;
@@ -13576,6 +13583,24 @@ opt_insert_update:
           }
         ;
 
+update_table_list:
+          table_ident opt_use_partition for_portion_of_time_clause
+          opt_table_alias_clause opt_key_definition
+          {
+            SELECT_LEX *sel= Select;
+            sel->table_join_options= 0;
+            if (!($$= Select->add_table_to_list(thd, $1, $4,
+                                                Select->get_table_join_options(),
+                                                YYPS->m_lock_type,
+                                                YYPS->m_mdl_type,
+                                                Select->pop_index_hints(),
+                                                $2)))
+              MYSQL_YYABORT;
+            $$->period_conditions= Lex->period_conditions;
+          }
+        | join_table_list { $$= $1; }
+        ;
+
 /* Update rows in a table */
 
 update:
@@ -13588,7 +13613,7 @@ update:
             lex->sql_command= SQLCOM_UPDATE;
             lex->duplicates= DUP_ERROR; 
           }
-          opt_low_priority opt_ignore join_table_list
+          opt_low_priority opt_ignore update_table_list
           SET update_list
           {
             LEX *lex= Lex;
diff --git a/sql/table.cc b/sql/table.cc
index 863d303efcc..981ca4a1deb 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3427,7 +3427,7 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
   if (prgflag & (READ_ALL + EXTRA_RECORD))
   {
     records++;
-    if (share->versioned)
+    if (share->versioned || share->period.name)
       records++;
   }
 
@@ -8137,7 +8137,8 @@ int TABLE::period_make_insert(Item *src, Field *dst)
 }
 
 int TABLE::insert_portion_of_time(THD *thd,
-                                  const vers_select_conds_t &period_conds)
+                                  const vers_select_conds_t &period_conds,
+                                  ha_rows *rows_inserted)
 {
   bool lcond= period_conds.field_start->val_datetime_packed(thd)
               < period_conds.start.item->val_datetime_packed(thd);
@@ -8146,11 +8147,19 @@ int TABLE::insert_portion_of_time(THD *thd,
 
   int res= 0;
   if (lcond)
+  {
     res= period_make_insert(period_conds.start.item,
                             field[s->period.end_fieldno]);
+    if (likely(!res))
+      ++*rows_inserted;
+  }
   if (likely(!res) && rcond)
+  {
     res= period_make_insert(period_conds.end.item,
                             field[s->period.start_fieldno]);
+    if (likely(!res))
+      ++*rows_inserted;
+  }
 
   return res;
 }
diff --git a/sql/table.h b/sql/table.h
index fb77d6b33c9..b28e865f0b1 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1578,7 +1578,8 @@ public:
 
   int update_generated_fields();
   int period_make_insert(Item *src, Field *dst);
-  int insert_portion_of_time(THD *thd, const vers_select_conds_t &period_conds);
+  int insert_portion_of_time(THD *thd, const vers_select_conds_t &period_conds,
+                             ha_rows *rows_inserted);
   int delete_row();
   void vers_update_fields();
   void vers_update_end();