SQL, Parser: system_time logic and syntax fixes [closes #237]

25 files changed, 263 insertions, 434 deletions

diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result
index 4e58ac8e5f8..14cb242d09e 100644
--- a/mysql-test/suite/versioning/r/alter.result
+++ b/mysql-test/suite/versioning/r/alter.result
@@ -8,8 +8,6 @@ t	CREATE TABLE `t` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 alter table t without system versioning;
 ERROR HY000: Wrong parameters for `t`: table is not versioned
-alter table t with system versioning without system versioning;
-ERROR HY000: Wrong parameters for `t`: multiple 'WITH/WITHOUT SYSTEM VERSIONING'
 alter table t with system versioning;
 show create table t;
 Table	Create Table
@@ -226,10 +224,8 @@ t	CREATE TABLE `t` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 alter table t modify a int with system versioning;
 ERROR HY000: Wrong parameters for `t`: table is not versioned
-alter table t modify a int with system versioning with system versioning;
-ERROR HY000: Wrong parameters for `t`: multiple 'WITH/WITHOUT SYSTEM VERSIONING' for `a`
-alter table t modify a int with system versioning without system versioning;
-ERROR HY000: Wrong parameters for `t`: multiple 'WITH/WITHOUT SYSTEM VERSIONING' for `a`
+alter table t modify a int without system versioning;
+ERROR HY000: Wrong parameters for `t`: table is not versioned
 alter table t with system versioning;
 alter table t modify a int without system versioning;
 show create table t;
diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result
index 32b8d1c97a7..b71e84ccc08 100644
--- a/mysql-test/suite/versioning/r/create.result
+++ b/mysql-test/suite/versioning/r/create.result
@@ -103,7 +103,7 @@ Sys_start2 SYS_TRX_TYPE generated always as row start,
 Sys_end SYS_TRX_TYPE generated always as row end,
 period for system_time (Sys_start, Sys_end)
 ) with system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'AS ROW START' (`Sys_start2`, `Sys_start`)
+ERROR HY000: Wrong parameters for `t1`: mismatch 'PERIOD FOR SYSTEM_TIME' and 'AS ROW START'
 create or replace table t1 (
 x4 int unsigned,
 Sys_start SYS_TRX_TYPE generated always as row start,
@@ -118,7 +118,7 @@ Sys_end SYS_TRX_TYPE generated always as row end,
 Sys_end2 SYS_TRX_TYPE generated always as row end,
 period for system_time (Sys_start, Sys_end)
 ) with system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'AS ROW END' (`Sys_end2`, `Sys_end`)
+ERROR HY000: Wrong parameters for `t1`: mismatch 'PERIOD FOR SYSTEM_TIME' and 'AS ROW END'
 create or replace table t1 (
 x6 int unsigned,
 period for system_time (Sys_start, Sys_end)
@@ -131,7 +131,7 @@ Sys_end SYS_TRX_TYPE generated always as row end,
 Sys_end2 SYS_TRX_TYPE generated always as row end,
 period for system_time (Sys_start, Sys_end)
 );
-ERROR HY000: Wrong parameters for `t1`: multiple 'AS ROW END' (`Sys_end2`, `Sys_end`)
+ERROR HY000: Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
 create or replace table t1 (
 x8 int unsigned,
 Sys_start SYS_TRX_TYPE generated always as row start,
@@ -152,7 +152,7 @@ Sys_start SYS_TRX_TYPE generated always as row start,
 Sys_end SYS_TRX_TYPE generated always as row end,
 period for system_time (Sys_start, Sys_start)
 );
-ERROR HY000: Wrong parameters for `t1`: multiple `Sys_start` for 'PERIOD FOR SYSTEM_TIME'
+ERROR HY000: Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
 create or replace table t1 (
 x11 int unsigned,
 Sys_start bigint unsigned generated always as row start,
@@ -257,34 +257,10 @@ create or replace table t1 (
 A8 int without system versioning
 ) with system versioning;
 ERROR HY000: Wrong parameters for `t1`: no columns defined 'WITH SYSTEM VERSIONING'
-create or replace table t1 (
-A9 int without system versioning with system versioning
-);
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING' for `A9`
-create or replace table t1 (
-A10 int with system versioning without system versioning
-);
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING' for `A10`
 create table t(
 a11 int
 ) without system versioning;
 ERROR HY000: Wrong parameters for `t`: not allowed 'WITHOUT SYSTEM VERSIONING'
-create or replace table t1 (
-A12 int
-) without system versioning with system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING'
-create or replace table t1 (
-A13 int
-) with system versioning without system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING'
-create or replace table t1 (
-A14 int
-) with system versioning with system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING'
-create or replace table t1 (
-A15 int
-) without system versioning without system versioning;
-ERROR HY000: Wrong parameters for `t1`: multiple 'WITH/WITHOUT SYSTEM VERSIONING'
 create or replace table t1 (a int) with system versioning;
 create temporary table tmp with system versioning select * from t1;
 create or replace table t1 (a int) with system versioning;
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index 8599cb92b52..eabeaf0d85a 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -102,7 +102,7 @@ ASOF_x	y
 7	107
 8	108
 9	109
-select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
+select x as FROMTO_x, y from t1 for system_time from '0-0-0 0:0:0' to timestamp @t1;
 FROMTO_x	y
 0	100
 1	101
@@ -114,7 +114,7 @@ FROMTO_x	y
 7	107
 8	108
 9	109
-select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+select x as BETWAND_x, y from t1 for system_time between '0-0-0 0:0:0' and timestamp @t1;
 BETWAND_x	y
 0	100
 1	101
@@ -127,31 +127,6 @@ BETWAND_x	y
 8	108
 9	109
 3	33
-select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-FROMTO_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-BETWAND_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-3	33
 select x as ALL_x, y from t1 for system_time all;
 ALL_x	y
 0	100
@@ -199,29 +174,6 @@ BETWAND2_x	y
 8	108
 9	109
 3	33
-FROMTO2_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-BETWAND2_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-3	33
 create or replace table t1 (
 x int unsigned,
 y int unsigned
diff --git a/mysql-test/suite/versioning/r/select_sp.result b/mysql-test/suite/versioning/r/select_sp.result
index 9ced07f03e1..69fbc1255f1 100644
--- a/mysql-test/suite/versioning/r/select_sp.result
+++ b/mysql-test/suite/versioning/r/select_sp.result
@@ -100,23 +100,17 @@ select vtq_commit_ts(@x1) into @t1;
 end if;
 select x, y from t1;
 select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
-select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+select x as FROMTO_x, y from t1 for system_time from '0-0-0 0:0:0' to timestamp @t1;
+select x as BETWAND_x, y from t1 for system_time between '0-0-0 0:0:0' and timestamp @t1;
 select x as ALL_x, y from t1 for system_time all;
 if engine = 'innodb' then
-select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
-select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
+select x as ASOF2_x, y from t1 for system_time as of @x0;
+select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
 select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
-select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1;
-select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1;
 else
-select x as ASOF2_x, y from t1 for system_time as of timestamp @t0;
-select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
+select x as ASOF2_x, y from t1 for system_time as of @t0;
+select x as FROMTO2_x, y from t1 for system_time from '0-0-0 0:0:0' to @t1;
 select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;  
 end if;
 drop table t1;
 end~~
@@ -198,29 +192,6 @@ BETWAND_x	y
 8	108
 9	109
 3	33
-FROMTO_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-BETWAND_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-3	33
 ALL_x	y
 0	100
 1	101
@@ -267,29 +238,6 @@ BETWAND2_x	y
 8	108
 9	109
 3	33
-FROMTO2_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-BETWAND2_ext_x	y
-0	100
-1	101
-2	102
-3	103
-4	104
-5	105
-6	106
-7	107
-8	108
-9	109
-3	33
 call test_02();
 IJ1_x1	y1	x2	y2
 1	1	1	2
diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result
index 56122432e6e..a9cc7d3aec3 100644
--- a/mysql-test/suite/versioning/r/sysvars.result
+++ b/mysql-test/suite/versioning/r/sysvars.result
@@ -115,11 +115,11 @@ select * from t for system_time all;
 a	sys_trx_start	sys_trx_end
 2	TIMESTAMP	TIMESTAMP
 1	TIMESTAMP	TIMESTAMP
-select * from t for system_time timestamp from '0-0-0' to current_timestamp(6);
+select * from t for system_time from '0-0-0' to current_timestamp(6);
 a	sys_trx_start	sys_trx_end
 2	TIMESTAMP	TIMESTAMP
 1	TIMESTAMP	TIMESTAMP
-select * from t for system_time timestamp between '0-0-0' and current_timestamp(6);
+select * from t for system_time between '0-0-0' and current_timestamp(6);
 a	sys_trx_start	sys_trx_end
 2	TIMESTAMP	TIMESTAMP
 1	TIMESTAMP	TIMESTAMP
diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result
index 5651cffb179..957163286c3 100644
--- a/mysql-test/suite/versioning/r/truncate.result
+++ b/mysql-test/suite/versioning/r/truncate.result
@@ -1,9 +1,9 @@
 create table t (a int);
 truncate t for system_time all;
-ERROR HY000: Unused clause: 'SYSTEM_TIME'
+ERROR HY000: System Versioning required: t
 create procedure truncate_history_of_t()
 begin
-prepare stmt from 'truncate t for system_time timestamp between \'1-1-1\' and now(6)';
+prepare stmt from 'truncate t for system_time between \'1-1-1\' and now(6)';
 execute stmt;
 drop prepare stmt;
 end~~
@@ -34,12 +34,12 @@ a
 4
 2
 3
-truncate t for system_time timestamp between '1-1-1' and now(6);
+truncate t for system_time between '1-1-1' and now(6);
 select * from t for system_time all;
 a
 4
 update t set a=5;
-truncate t for system_time timestamp from '1-1-1' to now(6);
+truncate t for system_time from '1-1-1' to now(6);
 select * from t for system_time all;
 a
 5
@@ -52,13 +52,13 @@ set @ts1 = now(6);
 update t set a=7;
 set @ts2 = now(6);
 update t set a=8;
-truncate t for system_time timestamp from '1-1-1' to @ts1;
+truncate t for system_time from '1-1-1' to @ts1;
 select * from t for system_time all;
 a
 8
 7
 update t set a=9;
-truncate t for system_time timestamp between '1-1-1' and @ts2;
+truncate t for system_time between '1-1-1' and @ts2;
 select * from t for system_time all;
 a
 9
@@ -82,12 +82,12 @@ a
 4
 2
 3
-truncate t for system_time timestamp between '1-1-1' and now(6);
+truncate t for system_time between '1-1-1' and now(6);
 select * from t for system_time all;
 a
 4
 update t set a=5;
-truncate t for system_time timestamp from '1-1-1' to now(6);
+truncate t for system_time from '1-1-1' to now(6);
 select * from t for system_time all;
 a
 5
@@ -100,13 +100,13 @@ set @ts1 = now(6);
 update t set a=7;
 set @ts2 = now(6);
 update t set a=8;
-truncate t for system_time timestamp from '1-1-1' to @ts1;
+truncate t for system_time from '1-1-1' to timestamp @ts1;
 select * from t for system_time all;
 a
 8
 7
 update t set a=9;
-truncate t for system_time timestamp between '1-1-1' and @ts2;
+truncate t for system_time between '1-1-1' and timestamp @ts2;
 select * from t for system_time all;
 a
 9
diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test
index 30fee87f3fe..6ceac987a5e 100644
--- a/mysql-test/suite/versioning/t/alter.test
+++ b/mysql-test/suite/versioning/t/alter.test
@@ -4,8 +4,6 @@ create table t(
 show create table t;
 --error ER_VERS_WRONG_PARAMS
 alter table t without system versioning;
---error ER_VERS_WRONG_PARAMS
-alter table t with system versioning without system versioning;
 
 alter table t with system versioning;
 show create table t;
@@ -118,9 +116,7 @@ show create table t;
 --error ER_VERS_WRONG_PARAMS
 alter table t modify a int with system versioning;
 --error ER_VERS_WRONG_PARAMS
-alter table t modify a int with system versioning with system versioning;
---error ER_VERS_WRONG_PARAMS
-alter table t modify a int with system versioning without system versioning;
+alter table t modify a int without system versioning;
 
 alter table t with system versioning;
 
diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test
index 9c92cc30e2c..9de6a5157b3 100644
--- a/mysql-test/suite/versioning/t/create.test
+++ b/mysql-test/suite/versioning/t/create.test
@@ -192,16 +192,6 @@ create or replace table t1 (
   A8 int without system versioning
 ) with system versioning;
 
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A9 int without system versioning with system versioning
-);
-
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A10 int with system versioning without system versioning
-);
-
 # table with/without system versioning
 
 --error ER_VERS_WRONG_PARAMS
@@ -209,26 +199,6 @@ create table t(
   a11 int
 ) without system versioning;
 
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A12 int
-) without system versioning with system versioning;
-
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A13 int
-) with system versioning without system versioning;
-
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A14 int
-) with system versioning with system versioning;
-
---error ER_VERS_WRONG_PARAMS
-create or replace table t1 (
-  A15 int
-) without system versioning without system versioning;
-
 create or replace table t1 (a int) with system versioning;
 create temporary table tmp with system versioning select * from t1;
 
diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test
index 5dc5f603d54..345e28f610c 100644
--- a/mysql-test/suite/versioning/t/select.test
+++ b/mysql-test/suite/versioning/t/select.test
@@ -42,28 +42,22 @@ if ($default_engine == 'innodb')
 
 select x, y from t1;
 select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
-select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+select x as FROMTO_x, y from t1 for system_time from '0-0-0 0:0:0' to timestamp @t1;
+select x as BETWAND_x, y from t1 for system_time between '0-0-0 0:0:0' and timestamp @t1;
 select x as ALL_x, y from t1 for system_time all;
 
 --disable_query_log
 if ($default_engine == 'innodb')
 {
-  select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
-  select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
+  select x as ASOF2_x, y from t1 for system_time as of @x0;
+  select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
   select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
-  select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1;
-  select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1;
 }
 if ($default_engine != 'innodb')
 {
-  select x as ASOF2_x, y from t1 for system_time as of timestamp @t0;
-  select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
+  select x as ASOF2_x, y from t1 for system_time as of @t0;
+  select x as FROMTO2_x, y from t1 for system_time from '0-0-0 0:0:0' to @t1;
   select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-  select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-  select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
 }
 --enable_query_log
 
diff --git a/mysql-test/suite/versioning/t/select_sp.test b/mysql-test/suite/versioning/t/select_sp.test
index 23921e9580d..d261e1bc537 100644
--- a/mysql-test/suite/versioning/t/select_sp.test
+++ b/mysql-test/suite/versioning/t/select_sp.test
@@ -45,24 +45,18 @@ begin
 
   select x, y from t1;
   select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
-  select x as FROMTO_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-  select x as BETWAND_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-  select x as FROMTO_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-  select x as BETWAND_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+  select x as FROMTO_x, y from t1 for system_time from '0-0-0 0:0:0' to timestamp @t1;
+  select x as BETWAND_x, y from t1 for system_time between '0-0-0 0:0:0' and timestamp @t1;
   select x as ALL_x, y from t1 for system_time all;
 
   if engine = 'innodb' then
-    select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
-    select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
+    select x as ASOF2_x, y from t1 for system_time as of @x0;
+    select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
     select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
-    select x as FROMTO2_ext_x, y from t1 for system_time transaction from @x0 to @x1;
-    select x as BETWAND2_ext_x, y from t1 for system_time transaction between @x0 and @x1;
   else
-    select x as ASOF2_x, y from t1 for system_time as of timestamp @t0;
-    select x as FROMTO2_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
+    select x as ASOF2_x, y from t1 for system_time as of @t0;
+    select x as FROMTO2_x, y from t1 for system_time from '0-0-0 0:0:0' to @t1;
     select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
-    select x as FROMTO2_ext_x, y from t1 for system_time from timestamp '0-0-0 0:0:0' to timestamp @t1;
-    select x as BETWAND2_ext_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;  
   end if;
 
   drop table t1;
diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test
index 4516833d58d..6b713c29239 100644
--- a/mysql-test/suite/versioning/t/sysvars.test
+++ b/mysql-test/suite/versioning/t/sysvars.test
@@ -84,9 +84,9 @@ select * from t for system_time as of timestamp current_timestamp(6);
 --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/
 select * from t for system_time all;
 --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/
-select * from t for system_time timestamp from '0-0-0' to current_timestamp(6);
+select * from t for system_time from '0-0-0' to current_timestamp(6);
 --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/
-select * from t for system_time timestamp between '0-0-0' and current_timestamp(6);
+select * from t for system_time between '0-0-0' and current_timestamp(6);
 
 set versioning_hide= NEVER;
 --replace_regex /\d{4}-\d\d-\d\d \d\d:\d\d:\d\d\.\d{6}/TIMESTAMP/
diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test
index 97362c7423b..ddd0db5856d 100644
--- a/mysql-test/suite/versioning/t/truncate.test
+++ b/mysql-test/suite/versioning/t/truncate.test
@@ -1,13 +1,13 @@
 -- source include/have_innodb.inc
 
 create table t (a int);
---error ER_VERS_UNUSED_CLAUSE
+--error ER_VERSIONING_REQUIRED
 truncate t for system_time all;
 
 delimiter ~~;
 create procedure truncate_history_of_t()
 begin
-  prepare stmt from 'truncate t for system_time timestamp between \'1-1-1\' and now(6)';
+  prepare stmt from 'truncate t for system_time between \'1-1-1\' and now(6)';
   execute stmt;
   drop prepare stmt;
 end~~
@@ -34,11 +34,11 @@ update t set a=4;
 truncate t for system_time as of timestamp now(6);
 select * from t for system_time all;
 
-truncate t for system_time timestamp between '1-1-1' and now(6);
+truncate t for system_time between '1-1-1' and now(6);
 select * from t for system_time all;
 
 update t set a=5;
-truncate t for system_time timestamp from '1-1-1' to now(6);
+truncate t for system_time from '1-1-1' to now(6);
 select * from t for system_time all;
 
 update t set a=6;
@@ -49,10 +49,10 @@ set @ts1 = now(6);
 update t set a=7;
 set @ts2 = now(6);
 update t set a=8;
-truncate t for system_time timestamp from '1-1-1' to @ts1;
+truncate t for system_time from '1-1-1' to @ts1;
 select * from t for system_time all;
 update t set a=9;
-truncate t for system_time timestamp between '1-1-1' and @ts2;
+truncate t for system_time between '1-1-1' and @ts2;
 select * from t for system_time all;
 
 
@@ -69,11 +69,11 @@ update t set a=4;
 truncate t for system_time as of timestamp now(6);
 select * from t for system_time all;
 
-truncate t for system_time timestamp between '1-1-1' and now(6);
+truncate t for system_time between '1-1-1' and now(6);
 select * from t for system_time all;
 
 update t set a=5;
-truncate t for system_time timestamp from '1-1-1' to now(6);
+truncate t for system_time from '1-1-1' to now(6);
 select * from t for system_time all;
 
 update t set a=6;
@@ -84,10 +84,10 @@ set @ts1 = now(6);
 update t set a=7;
 set @ts2 = now(6);
 update t set a=8;
-truncate t for system_time timestamp from '1-1-1' to @ts1;
+truncate t for system_time from '1-1-1' to timestamp @ts1;
 select * from t for system_time all;
 update t set a=9;
-truncate t for system_time timestamp between '1-1-1' and @ts2;
+truncate t for system_time between '1-1-1' and timestamp @ts2;
 select * from t for system_time all;
 
 create or replace table t (a int) with system versioning;
diff --git a/sql/item.cc b/sql/item.cc
index 117662d9c79..4314ee036e4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -10758,9 +10758,8 @@ Item *Item_field::vers_optimized_fields_transformer(THD *thd, uchar *)
     return this;
 
   if (field->flags & VERS_OPTIMIZED_UPDATE_FLAG && context &&
-      ((field->table->pos_in_table_list &&
-        field->table->pos_in_table_list->vers_conditions) ||
-       (context->select_lex && context->select_lex->vers_conditions)))
+      field->table->pos_in_table_list &&
+      field->table->pos_in_table_list->vers_conditions)
   {
     push_warning_printf(
         current_thd, Sql_condition::WARN_LEVEL_WARN,
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 9265362a2b4..c551bcef809 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7550,18 +7550,6 @@ WARN_VERS_ALIAS_TOO_LONG
 ER_VERS_VTMD_ERROR
         eng "VTMD error: %s"
 
-ER_MULTIPLE_CLAUSE
-        eng "for %`s: multiple '%s'"
-
-ER_MULTIPLE_CLAUSE_FOR
-        eng "for %`s: multiple '%s' for %`s"
-
-ER_MULTIPLE_CLAUSE_2
-        eng "for %`s: multiple '%s' (%`s, %`s)"
-
-ER_MULTIPLE_IDENTIFIER
-        eng "for %`s: multiple %`s for '%s'"
-
 ER_NOT_ALLOWED
         eng "for %`s: not allowed '%s'"
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0a7ad7aef55..27bf5ab6931 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -7593,9 +7593,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
         TABLE *table= f->field->table;
         DBUG_ASSERT(table && table->pos_in_table_list);
         TABLE_LIST *tl= table->pos_in_table_list;
-        vers_range_type_t vers_type=
-          tl->vers_conditions.type == FOR_SYSTEM_TIME_UNSPECIFIED ?
-            slex->vers_conditions.type : tl->vers_conditions.type;
+        vers_range_type_t vers_type= tl->vers_conditions.type;
 
         enum_sql_command sql_command= thd->lex->sql_command;
         unsigned int create_options= thd->lex->create_info.options;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 62171ae2423..8dc6203ed86 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -263,8 +263,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (open_and_lock_tables(thd, table_list, TRUE, 0))
     DBUG_RETURN(TRUE);
 
-  bool truncate_history=
-      select_lex->vers_conditions.type != FOR_SYSTEM_TIME_UNSPECIFIED;
+  bool truncate_history= table_list->vers_conditions;
   if (truncate_history)
   {
     TABLE *table= table_list->table;
@@ -276,12 +275,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
 
     // trx_sees() in InnoDB reads sys_trx_start
     if (!table->versioned_by_sql()) {
-      if (select_lex->vers_conditions.type == FOR_SYSTEM_TIME_BETWEEN ||
-          select_lex->vers_conditions.type == FOR_SYSTEM_TIME_FROM_TO)
+      if (table_list->vers_conditions.type == FOR_SYSTEM_TIME_BETWEEN ||
+          table_list->vers_conditions.type == FOR_SYSTEM_TIME_FROM_TO)
       {
         bitmap_set_bit(table->read_set, table->vers_start_field()->field_index);
       }
-      else if (select_lex->vers_conditions.type == FOR_SYSTEM_TIME_BEFORE)
+      else if (table_list->vers_conditions.type == FOR_SYSTEM_TIME_BEFORE)
       {
         bitmap_set_bit(table->read_set, table->vers_end_field()->field_index);
       }
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index d6a00827fef..cdbb01fa5aa 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -714,7 +714,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
         cursor->outer_join|= JOIN_TYPE_OUTER;
     }
 
-    // System Versioning begin
+    // System Versioning: fix system fields of versioned derived table
 #pragma GCC diagnostic push
 #pragma GCC diagnostic ignored "-Wformat"
 #pragma GCC diagnostic ignored "-Wformat-extra-args"
@@ -722,7 +722,11 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
       && sl->table_list.elements > 0)
     {
       // Similar logic as in mysql_create_view()
-      TABLE_LIST *impli_table= NULL, *expli_table= NULL;
+      // Leading versioning table detected implicitly (first one selected)
+      TABLE_LIST *impli_table= NULL;
+      // Leading versioning table specified explicitly
+      // (i.e. if at least one system field is selected)
+      TABLE_LIST *expli_table= NULL;
       const char *impli_start, *impli_end;
       Item_field *expli_start= NULL, *expli_end= NULL;
 
@@ -826,14 +830,10 @@ expli_table_err:
 
         if (impli_table->vers_conditions)
         {
-          sl->vers_derived_conds= impli_table->vers_conditions;
-          if (derived->is_view() && !sl->vers_conditions)
-            sl->vers_conditions.import_outer= true;
+          sl->vers_export_outer= impli_table->vers_conditions;
         }
-        else if (sl->vers_conditions)
-          sl->vers_derived_conds= sl->vers_conditions;
         else
-          sl->vers_conditions.import_outer= true;
+          sl->vers_import_outer= true; // FIXME: is needed?
       }
     } // if (sl->table_list.elements > 0)
 #pragma GCC diagnostic pop
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 589d02fb55b..481ee76e0f2 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2282,8 +2282,8 @@ void st_select_lex::init_select()
   with_dep= 0;
   join= 0;
   lock_type= TL_READ_DEFAULT;
-  vers_conditions.empty();
-  vers_derived_conds.empty();
+  vers_import_outer= false;
+  vers_export_outer.empty();
 }
 
 /*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5f6232c2c50..6756148431d 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -992,8 +992,9 @@ public:
   thr_lock_type lock_type;
 
   /* System Versioning */
-  vers_select_conds_t vers_conditions;
-  vers_select_conds_t vers_derived_conds;
+  vers_select_conds_t vers_export_outer;
+  bool vers_import_outer;
+  /* push new Item_field into item_list */
   bool vers_push_field(THD *thd, TABLE_LIST *table, const char* field_name);
 
   void init_query();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8575755c267..437c04c2984 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -672,7 +672,7 @@ bool vers_select_conds_t::init_from_sysvar(THD *thd)
 {
   st_vers_current_time &in= thd->variables.vers_current_time;
   type= in.type;
-  unit= UNIT_TIMESTAMP;
+  unit_start= UNIT_TIMESTAMP;
   if (type != FOR_SYSTEM_TIME_UNSPECIFIED && type != FOR_SYSTEM_TIME_ALL)
   {
     DBUG_ASSERT(type == FOR_SYSTEM_TIME_AS_OF);
@@ -714,14 +714,7 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
   }
 
   if (versioned_tables == 0)
-  {
-    if (slex->vers_conditions)
-    {
-      my_error(ER_VERS_UNUSED_CLAUSE, MYF(0), "SYSTEM_TIME");
-      DBUG_RETURN(-1);
-    }
     DBUG_RETURN(0);
-  }
 
   /* For prepared statements we create items on statement arena,
      because they must outlive execution phase for multiple executions. */
@@ -771,53 +764,43 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
   }
 
   SELECT_LEX *outer_slex= slex->next_select_in_list();
-  bool force_slex_conds= false;
-  if (outer_slex)
+  // propagate derived conditions to outer SELECT_LEX
+  if (outer_slex && slex->vers_export_outer)
   {
-    if (slex->vers_derived_conds)
+    for (table= outer_slex->table_list.first; table; table= table->next_local)
     {
-      // Propagate derived conditions to outer SELECT_LEX:
-      if (!outer_slex->vers_conditions)
+      if (!table->vers_conditions)
       {
-        outer_slex->vers_conditions= slex->vers_derived_conds;
-        outer_slex->vers_conditions.from_inner= true;
-        outer_slex->vers_conditions.used= true;
+        table->vers_conditions= slex->vers_export_outer;
+        table->vers_conditions.from_inner= true;
       }
     }
-    if (slex->vers_conditions.import_outer)
+  }
+
+  for (table= tables; table; table= table->next_local)
+  {
+    if (table->table && table->table->versioned())
     {
-      DBUG_ASSERT(slex->master_unit());
-      TABLE_LIST* derived= slex->master_unit()->derived;
-      DBUG_ASSERT(derived);
-      if (derived->vers_conditions)
-      {
-        slex->vers_conditions= derived->vers_conditions;
-        derived->vers_conditions.used= true;
-        force_slex_conds= derived->is_view();
-      }
-      else
+      vers_select_conds_t &vers_conditions= table->vers_conditions;
+
+      // propagate system_time from nearest outer SELECT_LEX
+      if (!vers_conditions && outer_slex && slex->vers_import_outer)
       {
-        // Propagate query conditions from nearest outer SELECT_LEX:
-        while (outer_slex && (!outer_slex->vers_conditions || outer_slex->vers_conditions.from_inner))
+        TABLE_LIST* derived= slex->master_unit()->derived;
+        while (outer_slex && (!derived->vers_conditions || derived->vers_conditions.from_inner))
+        {
+          derived= outer_slex->master_unit()->derived;
           outer_slex= outer_slex->next_select_in_list();
+        }
         if (outer_slex)
         {
-          slex->vers_conditions= outer_slex->vers_conditions;
-          outer_slex->vers_conditions.used= true;
-          force_slex_conds= derived->is_view();
+          DBUG_ASSERT(derived);
+          DBUG_ASSERT(derived->vers_conditions);
+          vers_conditions= derived->vers_conditions;
         }
       }
-    }
-  }
-
-  for (table= tables; table; table= table->next_local)
-  {
-    if (table->table && table->table->versioned())
-    {
-      vers_select_conds_t &vers_conditions= force_slex_conds || !table->vers_conditions?
-          (slex->vers_conditions.used= true, slex->vers_conditions) :
-          table->vers_conditions;
 
+      // propagate system_time from sysvar
       if (!vers_conditions)
       {
         if (vers_conditions.init_from_sysvar(thd))
@@ -869,12 +852,57 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
       bool tmp_from_ib=
           table->table->s->table_category == TABLE_CATEGORY_TEMPORARY &&
           table->table->vers_start_field()->type() == MYSQL_TYPE_LONGLONG;
-      if (table->table->versioned_by_sql() && !tmp_from_ib)
+      bool timestamps_only= table->table->versioned_by_sql() && !tmp_from_ib;
+
+      if (vers_conditions)
       {
-        if (vers_conditions.unit == UNIT_TRX_ID)
+        vers_conditions.resolve_units(timestamps_only);
+        if (timestamps_only)
         {
-          my_error(ER_VERS_ENGINE_UNSUPPORTED, MYF(0), table->table_name);
-          DBUG_RETURN(-1);
+          if (vers_conditions.unit_start == UNIT_TRX_ID || vers_conditions.unit_end == UNIT_TRX_ID)
+          {
+            my_error(ER_VERS_ENGINE_UNSUPPORTED, MYF(0), table->table_name);
+            DBUG_RETURN(-1);
+          }
+        }
+        else if (thd->variables.vers_innodb_algorithm_simple)
+        {
+          DBUG_ASSERT(table->table->s && table->table->s->db_plugin);
+          handlerton *hton= plugin_hton(table->table->s->db_plugin);
+          DBUG_ASSERT(hton);
+          bool convert_start= false;
+          bool convert_end= false;
+          switch (vers_conditions.type)
+          {
+          case FOR_SYSTEM_TIME_AS_OF:
+            if (vers_conditions.unit_start == UNIT_TIMESTAMP)
+              convert_start= convert_end= true;
+            break;
+          case FOR_SYSTEM_TIME_BEFORE:
+            if (vers_conditions.unit_start == UNIT_TIMESTAMP)
+              convert_end= true;
+            break;
+          case FOR_SYSTEM_TIME_FROM_TO:
+          case FOR_SYSTEM_TIME_BETWEEN:
+            if (vers_conditions.unit_start == UNIT_TIMESTAMP)
+              convert_end= true;
+            if (vers_conditions.unit_end == UNIT_TIMESTAMP)
+              convert_start= true;
+          default:
+            break;
+          }
+          if (convert_start)
+            row_start= newx Item_func_vtq_ts(
+              thd,
+              hton,
+              row_start,
+              VTQ_COMMIT_TS);
+          if (convert_end)
+            row_end= newx Item_func_vtq_ts(
+              thd,
+              hton,
+              row_end,
+              VTQ_COMMIT_TS);
         }
       }
 
@@ -943,7 +971,7 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
           cond1= newx Item_func_eq(thd, row_end2, curr);
           break;
         case FOR_SYSTEM_TIME_AS_OF:
-          trx_id0= vers_conditions.unit == UNIT_TIMESTAMP ?
+          trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ?
             newx Item_func_vtq_id(thd, hton, vers_conditions.start, VTQ_TRX_ID) :
             vers_conditions.start;
           cond1= newx Item_func_vtq_trx_sees_eq(thd, hton, trx_id0, row_start);
@@ -951,24 +979,19 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
           break;
         case FOR_SYSTEM_TIME_FROM_TO:
         case FOR_SYSTEM_TIME_BETWEEN:
-          if (vers_conditions.unit == UNIT_TIMESTAMP)
-          {
-            trx_id0= newx Item_func_vtq_id(thd, hton, vers_conditions.start, VTQ_TRX_ID, true);
-            trx_id1= newx Item_func_vtq_id(thd, hton, vers_conditions.end, VTQ_TRX_ID, false);
-          }
-          else
-          {
-            trx_id0= vers_conditions.start;
-            trx_id1= vers_conditions.end;
-          }
-
+          trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ?
+            newx Item_func_vtq_id(thd, hton, vers_conditions.start, VTQ_TRX_ID, true) :
+            vers_conditions.start;
+          trx_id1= vers_conditions.unit_end == UNIT_TIMESTAMP ?
+            newx Item_func_vtq_id(thd, hton, vers_conditions.end, VTQ_TRX_ID, false) :
+            vers_conditions.end;
           cond1= vers_conditions.type == FOR_SYSTEM_TIME_FROM_TO ?
             newx Item_func_vtq_trx_sees(thd, hton, trx_id1, row_start) :
             newx Item_func_vtq_trx_sees_eq(thd, hton, trx_id1, row_start);
           cond2= newx Item_func_vtq_trx_sees_eq(thd, hton, row_end, trx_id0);
           break;
         case FOR_SYSTEM_TIME_BEFORE:
-          trx_id0= vers_conditions.unit == UNIT_TIMESTAMP ?
+          trx_id0= vers_conditions.unit_start == UNIT_TIMESTAMP ?
             newx Item_func_vtq_id(thd, hton, vers_conditions.start, VTQ_TRX_ID) :
             vers_conditions.start;
           cond1= newx Item_func_lt(thd, row_end, trx_id0);
@@ -994,12 +1017,6 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr,
     } // if (... table->table->versioned())
   } // for (table= tables; ...)
 
-  if (!slex->vers_conditions.used && slex->vers_conditions)
-  {
-    my_error(ER_VERS_UNUSED_CLAUSE, MYF(0), "SYSTEM_TIME");
-    DBUG_RETURN(-1);
-  }
-
   DBUG_RETURN(0);
 #undef newx
 }
diff --git a/sql/sql_truncate.cc b/sql/sql_truncate.cc
index 4faad5b4711..b65818a2716 100644
--- a/sql/sql_truncate.cc
+++ b/sql/sql_truncate.cc
@@ -492,18 +492,17 @@ bool Sql_cmd_truncate_table::truncate_table(THD *thd, TABLE_LIST *table_ref)
 bool Sql_cmd_truncate_table::execute(THD *thd)
 {
   bool res= TRUE;
-  TABLE_LIST *first_table= thd->lex->select_lex.table_list.first;
+  TABLE_LIST *table= thd->lex->select_lex.table_list.first;
   DBUG_ENTER("Sql_cmd_truncate_table::execute");
 
-  bool truncate_history= thd->lex->current_select->vers_conditions.type !=
-                         FOR_SYSTEM_TIME_UNSPECIFIED;
-  if (truncate_history)
-    DBUG_RETURN(mysql_delete(thd, first_table, NULL, NULL, -1, 0, NULL));
+  DBUG_ASSERT(table);
+  if (table->vers_conditions)
+    DBUG_RETURN(mysql_delete(thd, table, NULL, NULL, -1, 0, NULL));
 
-  if (check_one_table_access(thd, DROP_ACL, first_table))
+  if (check_one_table_access(thd, DROP_ACL, table))
     DBUG_RETURN(res);
 
-  if (! (res= truncate_table(thd, first_table)))
+  if (! (res= truncate_table(thd, table)))
     my_ok(thd);
 
   DBUG_RETURN(res);
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 46dd7570874..4626a0369fb 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -453,11 +453,15 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
     goto err;
   }
 
-  { /* System Versioning begin */
+  { /* System Versioning: fix system fields of versioned view */
 #pragma GCC diagnostic push
 #pragma GCC diagnostic ignored "-Wformat"
 #pragma GCC diagnostic ignored "-Wformat-extra-args"
-    TABLE_LIST *impli_table= NULL, *expli_table= NULL;
+    // Leading versioning table detected implicitly (first one selected)
+    TABLE_LIST *impli_table= NULL;
+    // Leading versioning table specified explicitly
+    // (i.e. if at least one system field is selected)
+    TABLE_LIST *expli_table= NULL;
     const char *impli_start, *impli_end;
     Item_field *expli_start= NULL, *expli_end= NULL;
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 4cab239795d..c0957699602 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -863,10 +863,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %parse-param { THD *thd }
 %lex-param { THD *thd }
 /*
-  Currently there are 103 shift/reduce conflicts.
+  Currently there are 116 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 103
+%expect 116
 
 /*
    Comments for TOKENS.
@@ -1623,7 +1623,6 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         opt_constraint constraint opt_ident
         sp_decl_ident
         sp_block_label
-        period_for_system_time_column_id
 
 %type <lex_string_with_metadata>
         TEXT_STRING
@@ -1972,7 +1971,7 @@ END_OF_INPUT
 
 %type <lex_str_list> opt_with_column_list
 
-%type <vers_range_unit> trans_or_timestamp
+%type <vers_range_unit> opt_trans_or_timestamp
 %type <BOOL> opt_for_system_time_clause
 %type <vers_column_versioning> with_or_without_system
 %%
@@ -2510,7 +2509,7 @@ create:
                                                      sequence_definition()))
                MYSQL_YYABORT;
          }
-         opt_sequence opt_create_table_options
+         opt_sequence opt_create_sequence_options
          {
             LEX *lex= thd->lex;
 
@@ -4774,7 +4773,7 @@ create_like:
 
 opt_create_select:
           /* empty */ {}
-        | opt_duplicate opt_as create_select_query_expression
+        | opt_duplicate opt_as create_select_query_expression opt_versioning_option
         ;
 
 create_select_query_expression:
@@ -5691,14 +5690,19 @@ create_or_replace:
           }
          ;
 
-opt_create_table_options:
+opt_create_sequence_options:
           /* empty */
         | create_table_options
         ;
 
-create_table_options_space_separated:
-          create_table_option
-        | create_table_option create_table_options_space_separated
+opt_create_table_options:
+          /* empty */
+        | create_table_options_versioning
+        ;
+
+alter_table_options:
+          create_table_option_versioning
+        | create_table_option_versioning alter_table_options
         ;
 
 create_table_options:
@@ -5707,6 +5711,12 @@ create_table_options:
         | create_table_option ',' create_table_options
         ;
 
+create_table_options_versioning:
+          create_table_option_versioning
+        | create_table_option_versioning     create_table_options_versioning
+        | create_table_option_versioning ',' create_table_options_versioning
+        ;
+
 create_table_option:
           ENGINE_SYM opt_equal storage_engines
           {
@@ -5951,31 +5961,30 @@ create_table_option:
 	    Lex->create_info.used_fields|= HA_CREATE_USED_SEQUENCE;
             Lex->create_info.sequence= $3;
 	  }
-        | WITH_SYSTEM_SYM table_versioning
+        ;
+
+create_table_option_versioning:
+          create_table_option
+        | versioning_option
+        ;
+
+opt_versioning_option:
+          /* empty */
+        | versioning_option
+        ;
+
+versioning_option:
+        WITH_SYSTEM_SYM VERSIONING_SYM
           {
             Lex->vers_get_info().with_system_versioning= true;
             Lex->create_info.options|= HA_VERSIONED_TABLE;
           }
-	| WITHOUT SYSTEM table_versioning
+	| WITHOUT SYSTEM VERSIONING_SYM
           {
             Lex->vers_get_info().without_system_versioning= true;
           }
         ;
 
-table_versioning:
-          VERSIONING_SYM
-          {
-            Vers_parse_info &info= Lex->vers_get_info();
-            if (info.with_system_versioning || info.without_system_versioning)
-            {
-              my_error_as(ER_VERS_WRONG_PARAMS, ER_MULTIPLE_CLAUSE, MYF(0),
-                Lex->create_last_non_select_table->table_name,
-                "WITH/WITHOUT SYSTEM VERSIONING");
-              MYSQL_YYABORT;
-            }
-          }
-        ;
-
 default_charset:
           opt_default charset opt_equal charset_name_or_default
           {
@@ -6175,16 +6184,9 @@ constraint_def:
 
 period_for_system_time:
           // If FOR_SYM is followed by SYSTEM_TIME_SYM then they are merged to: FOR_SYSTEM_TIME_SYM .
-          PERIOD_SYM FOR_SYSTEM_TIME_SYM '(' period_for_system_time_column_id ',' period_for_system_time_column_id ')'
+          PERIOD_SYM FOR_SYSTEM_TIME_SYM '(' ident ',' ident ')'
           {
             Vers_parse_info &info= Lex->vers_get_info();
-            if (!my_strcasecmp(system_charset_info, $4.str, $6.str))
-            {
-              my_error_as(ER_VERS_WRONG_PARAMS, ER_MULTIPLE_IDENTIFIER, MYF(0),
-                Lex->create_last_non_select_table->table_name, $4.str,
-                "PERIOD FOR SYSTEM_TIME");
-              MYSQL_YYABORT;
-            }
             info.set_period_for_system_time($4, $6);
           }
         ;
@@ -6297,7 +6299,6 @@ field_def:
             LEX *lex= Lex;
             Vers_parse_info &info= lex->vers_get_info();
             const char *field_name= lex->last_field->field_name;
-            const char *table_name= lex->create_last_non_select_table->table_name;
 
             LString_i *p;
             const char* clause;
@@ -6319,12 +6320,6 @@ field_def:
               break;
             }
             DBUG_ASSERT(p);
-            if (*p)
-            {
-              my_error_as(ER_VERS_WRONG_PARAMS, ER_MULTIPLE_CLAUSE_2, MYF(0),
-                table_name, clause, field_name, p->ptr());
-              MYSQL_YYABORT;
-            }
             *p= field_name;
             if (lex->last_field->implicit_not_null)
             {
@@ -6821,13 +6816,6 @@ serial_attribute:
           }
         | with_or_without_system VERSIONING_SYM
           {
-            if (Lex->last_field->versioning != Column_definition::VERSIONING_NOT_SET)
-            {
-              my_error_as(ER_VERS_WRONG_PARAMS, ER_MULTIPLE_CLAUSE_FOR, MYF(0),
-                   Lex->create_last_non_select_table->table_name,
-                   "WITH/WITHOUT SYSTEM VERSIONING", Lex->last_field->field_name);
-              MYSQL_YYABORT;
-            }
             Lex->last_field->versioning= $1;
             Lex->create_info.options|= HA_VERSIONED_TABLE;
           }
@@ -7928,7 +7916,7 @@ alter_list_item:
               MYSQL_YYABORT;
             Lex->alter_info.flags|= Alter_info::ALTER_OPTIONS;
           }
-        | create_table_options_space_separated
+        | alter_table_options
           {
             LEX *lex=Lex;
             lex->alter_info.flags|= Alter_info::ALTER_OPTIONS;
@@ -8838,8 +8826,12 @@ select_options:
           }
         ;
 
-trans_or_timestamp:
-          TRANSACTION_SYM
+opt_trans_or_timestamp:
+          /* empty */
+          {
+            $$ = UNIT_AUTO;
+          }
+        | TRANSACTION_SYM
           {
             $$ = UNIT_TRX_ID;
           }
@@ -8855,7 +8847,22 @@ opt_system_time_clause:
         | SYSTEM_TIME_SYM system_time_expr
           {
             DBUG_ASSERT(Select);
-            Select->vers_conditions= Lex->vers_conditions;
+            int used= 0;
+            if (Lex->vers_conditions)
+            {
+              for (TABLE_LIST *table= Select->table_list.first; table; table= table->next_local)
+              {
+                if (!table->vers_conditions)
+                {
+                  table->vers_conditions= Lex->vers_conditions;
+                  used++;
+                }
+              }
+              if (!used)
+              {
+                my_yyabort_error((ER_VERS_UNUSED_CLAUSE, MYF(0), "SYSTEM_TIME"));
+              }
+            }
           }
         ;
 
@@ -8871,7 +8878,7 @@ opt_for_system_time_clause:
         ;
 
 system_time_expr:
-          AS OF_SYM trans_or_timestamp simple_expr
+          AS OF_SYM opt_trans_or_timestamp simple_expr
           {
             Lex->vers_conditions.init(FOR_SYSTEM_TIME_AS_OF, $3, $4);
           }
@@ -8884,42 +8891,20 @@ system_time_expr:
           }
         | ALL
           {
-            Lex->vers_conditions.init(FOR_SYSTEM_TIME_ALL, UNIT_TIMESTAMP);
+            Lex->vers_conditions.init(FOR_SYSTEM_TIME_ALL);
           }
-        | FROM trans_or_timestamp simple_expr
-          TO_SYM trans_or_timestamp simple_expr
+        | FROM opt_trans_or_timestamp simple_expr
+          TO_SYM opt_trans_or_timestamp simple_expr
           {
-            if ($2 != $5)
-            {
-              Lex->parse_error(ER_VERS_RANGE_UNITS_MISMATCH);
-              MYSQL_YYABORT;
-            }
-            Lex->vers_conditions.init(FOR_SYSTEM_TIME_FROM_TO, $2, $3, $6);
-          }
-        | trans_or_timestamp
-          FROM simple_expr
-          TO_SYM simple_expr
-          {
-            Lex->vers_conditions.init(FOR_SYSTEM_TIME_FROM_TO, $1, $3, $5);
-          }
-        | BETWEEN_SYM trans_or_timestamp simple_expr
-          AND_SYM trans_or_timestamp simple_expr
-          {
-            if ($2 != $5)
-            {
-              Lex->parse_error(ER_VERS_RANGE_UNITS_MISMATCH);
-              MYSQL_YYABORT;
-            }
-            Lex->vers_conditions.init(FOR_SYSTEM_TIME_BETWEEN, $2, $3, $6);
+            Lex->vers_conditions.init(FOR_SYSTEM_TIME_FROM_TO, $2, $3, $5, $6);
           }
-        | trans_or_timestamp
-          BETWEEN_SYM simple_expr
-          AND_SYM simple_expr
+        | BETWEEN_SYM opt_trans_or_timestamp simple_expr
+          AND_SYM opt_trans_or_timestamp simple_expr
           {
-            Lex->vers_conditions.init(FOR_SYSTEM_TIME_BETWEEN, $1, $3, $5);
+            Lex->vers_conditions.init(FOR_SYSTEM_TIME_BETWEEN, $2, $3, $5, $6);
           }
         | BEFORE_SYM
-          trans_or_timestamp
+          opt_trans_or_timestamp
           simple_expr
           {
             Lex->vers_conditions.init(FOR_SYSTEM_TIME_BEFORE, $2, $3);
@@ -13047,7 +13032,6 @@ truncate:
             lex->select_lex.init_order();
             YYPS->m_lock_type= TL_WRITE;
             YYPS->m_mdl_type= MDL_EXCLUSIVE;
-            Select->vers_conditions.empty();
           }
           table_name opt_for_system_time_clause opt_lock_wait_timeout
           {
@@ -13057,7 +13041,7 @@ truncate:
             if (lex->m_sql_cmd == NULL)
               MYSQL_YYABORT;
             if ($5)
-              Select->vers_conditions= Lex->vers_conditions;
+              Lex->last_table()->vers_conditions= Lex->vers_conditions;
           }
         ;
 
@@ -16244,13 +16228,6 @@ column_list:
         | column_list_id
         ;
 
-period_for_system_time_column_id:
-          ident
-          {
-            $$= $1;
-          }
-        ;
-
 column_list_id:
           ident
           {
diff --git a/sql/table.cc b/sql/table.cc
index 2dcec49d46d..5bb8e6aa3f8 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8423,6 +8423,24 @@ LEX_CSTRING *fk_option_name(enum_fk_option opt)
   return names + opt;
 }
 
+void vers_select_conds_t::resolve_units(bool timestamps_only)
+{
+  DBUG_ASSERT(type != FOR_SYSTEM_TIME_UNSPECIFIED);
+  DBUG_ASSERT(start);
+  if (unit_start == UNIT_AUTO)
+  {
+    unit_start= (!timestamps_only && (start->result_type() == INT_RESULT ||
+      start->result_type() == REAL_RESULT)) ?
+        UNIT_TRX_ID : UNIT_TIMESTAMP;
+  }
+  if (end && unit_end == UNIT_AUTO)
+  {
+    unit_end= (!timestamps_only && (end->result_type() == INT_RESULT ||
+      end->result_type() == REAL_RESULT)) ?
+        UNIT_TRX_ID : UNIT_TIMESTAMP;
+  }
+}
+
 
 Field *TABLE::find_field_by_name(const char *str) const
 {
diff --git a/sql/table.h b/sql/table.h
index 8bcdb001d59..82d41ff831b 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1845,7 +1845,8 @@ class Item_in_subselect;
 
 enum vers_range_unit_t
 {
-  UNIT_TIMESTAMP = 0,
+  UNIT_AUTO = 0,
+  UNIT_TIMESTAMP,
   UNIT_TRX_ID
 };
 
@@ -1853,31 +1854,32 @@ enum vers_range_unit_t
 struct vers_select_conds_t
 {
   vers_range_type_t type;
-  vers_range_unit_t unit;
+  vers_range_unit_t unit_start, unit_end;
   bool import_outer:1;
   bool from_inner:1;
-  bool used:1;
   Item *start, *end;
 
   void empty()
   {
     type= FOR_SYSTEM_TIME_UNSPECIFIED;
-    unit= UNIT_TIMESTAMP;
-    import_outer= from_inner= used= false;
+    unit_start= unit_end= UNIT_AUTO;
+    import_outer= from_inner= false;
     start= end= NULL;
   }
 
   void init(
     vers_range_type_t t,
-    vers_range_unit_t u,
+    vers_range_unit_t u_start= UNIT_AUTO,
     Item * s= NULL,
+    vers_range_unit_t u_end= UNIT_AUTO,
     Item * e= NULL)
   {
     type= t;
-    unit= u;
+    unit_start= u_start;
+    unit_end= u_end;
     start= s;
     end= e;
-    import_outer= from_inner= used= false;
+    import_outer= from_inner= false;
   }
 
   bool init_from_sysvar(THD *thd);
@@ -1894,6 +1896,7 @@ struct vers_select_conds_t
   {
     return type != FOR_SYSTEM_TIME_UNSPECIFIED;
   }
+  void resolve_units(bool timestamps_only);
 };
 
 struct LEX;