14 files changed, 431 insertions, 69 deletions

diff --git a/mysql-test/suite/versioning/disabled.def b/mysql-test/suite/versioning/disabled.def
index 11e45360f19..888298bbb09 100644
--- a/mysql-test/suite/versioning/disabled.def
+++ b/mysql-test/suite/versioning/disabled.def
@@ -9,4 +9,3 @@
 #  Do not use any TAB characters for whitespace.
 #
 ##############################################################################
-cte: MDEV-14820
diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result
index fda5e086be2..f5dfcd345fa 100644
--- a/mysql-test/suite/versioning/r/cte.result
+++ b/mysql-test/suite/versioning/r/cte.result
@@ -1,9 +1,9 @@
+set time_zone="+00:00";
 set default_storage_engine=innodb;
 create or replace table dept (
-dept_id int(10) primary key, 
+dept_id int(10) primary key,
 name varchar(100)
-)
-with system versioning;
+) with system versioning;
 create or replace table emp (
 emp_id int(10) primary key,
 dept_id int(10) not null,
@@ -18,16 +18,51 @@ constraint `mgr-fk`
     foreign key (mgr) references emp (emp_id)
 on delete restrict
 on update restrict
-) 
-with system versioning;
+) with system versioning;
 insert into dept (dept_id, name) values (10, "accounting");
-insert into emp (emp_id, name, salary, dept_id, mgr) values 
+insert into emp (emp_id, name, salary, dept_id, mgr) values
 (1, "bill", 1000, 10, null),
 (20, "john", 500, 10, 1),
 (30, "jane", 750, 10,1 );
-select max(sys_trx_start) into @ts_1 from emp;
+select row_start into @ts_1 from emp where name="jane";
 update emp set mgr=30 where name ="john";
-select sys_trx_start into @ts_2 from emp where name="john";
+explain extended
+with ancestors as (
+select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
+    union
+select e.emp_id, e.name, e.mgr, e.salary from emp as e
+) select * from ancestors for system_time as of @ts_1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	8	100.00	
+2	DERIVED	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	UNION	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	with ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
+select row_start into @ts_2 from emp where name="john";
+explain extended /* All report to 'Bill' */
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp for system_time as of timestamp @ts_1 as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp for system_time as of timestamp @ts_1 as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	DERIVED	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	e	ALL	mgr-fk	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	4	test.e.mgr	2	100.00	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
 /* All report to 'Bill' */
 with recursive
 ancestors
@@ -36,7 +71,7 @@ as
 select e.emp_id, e.name, e.mgr, e.salary
 from emp for system_time as of timestamp @ts_1 as e
 where name = 'bill'
-  union 
+  union
 select e.emp_id, e.name, e.mgr, e.salary
 from emp for system_time as of timestamp @ts_1 as e,
 ancestors as a
@@ -47,25 +82,186 @@ emp_id	name	mgr	salary
 1	bill	NULL	1000
 20	john	1	500
 30	jane	1	750
-/* Expected 3 rows */
+explain extended with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of timestamp @ts_1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	DERIVED	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	e	ALL	mgr-fk	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	4	test.e.mgr	2	100.00	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
 with recursive
 ancestors
 as
 (
 select e.emp_id, e.name, e.mgr, e.salary
-from emp for system_time as of timestamp @ts_2 as e
+from emp as e
 where name = 'bill'
-  union 
+  union
 select e.emp_id, e.name, e.mgr, e.salary
-from emp for system_time as of timestamp @ts_2 as e,
+from emp as e,
 ancestors as a
 where e.mgr = a.emp_id
 )
-select * from ancestors;
+select * from ancestors for system_time as of timestamp @ts_1;
 emp_id	name	mgr	salary
 1	bill	NULL	1000
+20	john	1	500
 30	jane	1	750
-20	john	30	500
+explain extended with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	
+1	PRIMARY	emp	ALL	PRIMARY	NULL	NULL	NULL	4	75.00	Using where; Using join buffer (flat, BNL join)
+4	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	DERIVED	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	e	ALL	mgr-fk	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	4	test.e.mgr	2	100.00	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`emp_id` = `ancestors`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999'
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+name
+bill
+john
+jane
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors for system_time as of @ts_2 a2;
+emp_id	name	mgr	salary	emp_id	name	mgr	salary
+1	bill	NULL	1000	1	bill	NULL	1000
+30	jane	1	750	1	bill	NULL	1000
+20	john	30	500	1	bill	NULL	1000
+1	bill	NULL	1000	30	jane	1	750
+30	jane	1	750	30	jane	1	750
+20	john	30	500	30	jane	1	750
+1	bill	NULL	1000	20	john	30	500
+30	jane	1	750	20	john	30	500
+20	john	30	500	20	john	30	500
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors for system_time as of now() a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors,
+ancestors for system_time as of @ts_2 a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+  union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2
+where emp_id in (select * from ancestors);
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+# SYSTEM_TIME to internal recursive instance is prohibited
+with recursive cte as
+(
+select * from emp
+union all
+select * from cte for system_time as of @ts_1
+)
+select * from cte;
+ERROR HY000: Table `cte` is not system-versioned
 create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
 create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
 insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1);
diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test
index 9df0bb3dfba..0055b2100d3 100644
--- a/mysql-test/suite/versioning/t/cte.test
+++ b/mysql-test/suite/versioning/t/cte.test
@@ -1,10 +1,10 @@
 -- source include/have_innodb.inc
+set time_zone="+00:00";
 set default_storage_engine=innodb;
 create or replace table dept (
-  dept_id int(10) primary key, 
+  dept_id int(10) primary key,
   name varchar(100)
-)
-with system versioning;
+) with system versioning;
 
 create or replace table emp (
   emp_id int(10) primary key,
@@ -20,21 +20,29 @@ create or replace table emp (
     foreign key (mgr) references emp (emp_id)
       on delete restrict
       on update restrict
-) 
-with system versioning;
+) with system versioning;
 
 insert into dept (dept_id, name) values (10, "accounting");
 
-insert into emp (emp_id, name, salary, dept_id, mgr) values 
+insert into emp (emp_id, name, salary, dept_id, mgr) values
 (1, "bill", 1000, 10, null),
 (20, "john", 500, 10, 1),
 (30, "jane", 750, 10,1 );
 
-select max(sys_trx_start) into @ts_1 from emp;
+select row_start into @ts_1 from emp where name="jane";
 
 update emp set mgr=30 where name ="john";
-select sys_trx_start into @ts_2 from emp where name="john";
 
+explain extended
+with ancestors as (
+  select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
+    union
+  select e.emp_id, e.name, e.mgr, e.salary from emp as e
+) select * from ancestors for system_time as of @ts_1;
+
+select row_start into @ts_2 from emp where name="john";
+
+let $q=
 /* All report to 'Bill' */
 with recursive
 ancestors
@@ -43,7 +51,7 @@ as
   select e.emp_id, e.name, e.mgr, e.salary
   from emp for system_time as of timestamp @ts_1 as e
   where name = 'bill'
-  union 
+  union
   select e.emp_id, e.name, e.mgr, e.salary
   from emp for system_time as of timestamp @ts_1 as e,
        ancestors as a
@@ -51,21 +59,138 @@ as
 )
 select * from ancestors;
 
-/* Expected 3 rows */
+eval explain extended $q;
+eval $q;
+
+let $q=with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of timestamp @ts_1;
+
+eval explain extended $q;
+eval $q;
+
+let $q=with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+
+eval explain extended $q;
+eval $q;
+
 with recursive
 ancestors
 as
 (
   select e.emp_id, e.name, e.mgr, e.salary
-  from emp for system_time as of timestamp @ts_2 as e
+  from emp as e
   where name = 'bill'
-  union 
+  union
   select e.emp_id, e.name, e.mgr, e.salary
-  from emp for system_time as of timestamp @ts_2 as e,
+  from emp as e,
        ancestors as a
   where e.mgr = a.emp_id
 )
-select * from ancestors;
+select * from ancestors for system_time as of @ts_2,
+              ancestors for system_time as of @ts_2 a2;
+
+--error ER_CONFLICTING_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+              ancestors for system_time as of now() a2;
+
+--error ER_CONFLICTING_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors,
+              ancestors for system_time as of @ts_2 a2;
+
+--error ER_CONFLICTING_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+              ancestors a2;
+
+--error ER_CONFLICTING_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e
+  where name = 'bill'
+  union
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2
+ where emp_id in (select * from ancestors);
+
+--echo # SYSTEM_TIME to internal recursive instance is prohibited
+--error ER_VERS_NOT_VERSIONED
+with recursive cte as
+(
+  select * from emp
+  union all
+  select * from cte for system_time as of @ts_1
+)
+select * from cte;
 
 create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
 create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 69b819e08f3..99be2024c2f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3674,7 +3674,7 @@ int subselect_single_select_engine::prepare(THD *thd)
 int subselect_union_engine::prepare(THD *thd_arg)
 {
   set_thd(thd_arg);
-  return unit->prepare(thd, result, SELECT_NO_UNLOCK);
+  return unit->prepare(unit->derived, result, SELECT_NO_UNLOCK);
 }
 
 int subselect_uniquesubquery_engine::prepare(THD *)
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index c55ac4f2001..4d94e6704ec 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7859,8 +7859,8 @@ ER_VERS_ALTER_ENGINE_PROHIBITED
 ER_VERS_RANGE_PROHIBITED
         eng "SYSTEM_TIME range selector is not allowed"
 
-ER_UNUSED_26
-        eng "You should never see it"
+ER_CONFLICTING_FOR_SYSTEM_TIME
+        eng "Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE"
 
 ER_VERS_TABLE_MUST_HAVE_COLUMNS
         eng "Table %`s must have at least one versioned column"
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index 4ae15524dab..c92fb3d97ba 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -978,7 +978,7 @@ bool With_element::prepare_unreferenced(THD *thd)
 
   thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
   if (!spec->prepared &&
-      (spec->prepare(thd, 0, 0) ||
+      (spec->prepare(spec->derived, 0, 0) ||
        rename_columns_of_derived_unit(thd, spec) ||
        check_duplicate_names(thd, first_sl->item_list, 1)))
     rc= true;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index b7d59c576f3..b9396c092ef 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -748,8 +748,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
     }
   }
 
-  unit->derived= derived;
-
   /*
     Above cascade call of prepare is important for PS protocol, but after it
     is called we can check if we really need prepare for this derived
@@ -767,7 +765,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
 
   lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
   // st_select_lex_unit::prepare correctly work for single select
-  if ((res= unit->prepare(thd, derived->derived_result, 0)))
+  if ((res= unit->prepare(derived, derived->derived_result, 0)))
     goto exit;
   if (derived->with &&
       (res= derived->with->rename_columns_of_derived_unit(thd, unit)))
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b78fbcf9d18..4a98fd1d454 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -864,7 +864,8 @@ public:
   bool is_excluded() { return prev == NULL; }
 
   /* UNION methods */
-  bool prepare(THD *thd, select_result *result, ulong additional_options);
+  bool prepare(TABLE_LIST *derived_arg, select_result *sel_result,
+               ulong additional_options);
   bool optimize();
   bool exec();
   bool exec_recursive();
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 6db7bc777cf..b149727484d 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1530,7 +1530,7 @@ static int mysql_test_select(Prepared_statement *stmt,
     It is not SELECT COMMAND for sure, so setup_tables will be called as
     usual, and we pass 0 as setup_tables_done_option
   */
-  if (unit->prepare(thd, 0, 0))
+  if (unit->prepare(unit->derived, 0, 0))
     goto error;
   if (!lex->describe && !thd->lex->analyze_stmt && !stmt->is_sql_prepare())
   {
@@ -1701,7 +1701,7 @@ static bool select_like_stmt_test(Prepared_statement *stmt,
   thd->lex->used_tables= 0;                        // Updated by setup_fields
 
   /* Calls JOIN::prepare */
-  DBUG_RETURN(lex->unit.prepare(thd, 0, setup_tables_done_option));
+  DBUG_RETURN(lex->unit.prepare(lex->unit.derived, 0, setup_tables_done_option));
 }
 
 /**
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bf6c2a49aba..110a68fae45 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25647,7 +25647,8 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
       unit->fake_select_lex->type= unit_operation_text[unit->common_op()];
       unit->fake_select_lex->options|= SELECT_DESCRIBE;
     }
-    if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
+    if (!(res= unit->prepare(unit->derived, result,
+                             SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
       res= unit->exec();
   }
   else
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index caba0e24c91..b928376a568 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -36,7 +36,7 @@ bool mysql_union(THD *thd, LEX *lex, select_result *result,
 {
   DBUG_ENTER("mysql_union");
   bool res;
-  if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK |
+  if (!(res= unit->prepare(unit->derived, result, SELECT_NO_UNLOCK |
                            setup_tables_done_option)))
     res= unit->exec();
   res|= unit->cleanup();
@@ -807,10 +807,11 @@ bool st_select_lex_unit::join_union_item_types(THD *thd_arg,
 }
 
 
-bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
+bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
+                                 select_result *sel_result,
                                  ulong additional_options)
 {
-  SELECT_LEX *lex_select_save= thd_arg->lex->current_select;
+  SELECT_LEX *lex_select_save= thd->lex->current_select;
   SELECT_LEX *sl, *first_sl= first_select();
   bool is_recursive= with_element && with_element->is_recursive;
   bool is_rec_result_table_created= false;
@@ -821,7 +822,6 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   bool instantiate_tmp_table= false;
   bool single_tvc= !first_sl->next_select() && first_sl->tvc;
   DBUG_ENTER("st_select_lex_unit::prepare");
-  DBUG_ASSERT(thd == thd_arg);
   DBUG_ASSERT(thd == current_thd);
 
   describe= additional_options & SELECT_DESCRIBE;
@@ -873,7 +873,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   prepared= 1;
   saved_error= FALSE;
   
-  thd_arg->lex->current_select= sl= first_sl;
+  thd->lex->current_select= sl= first_sl;
   found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS;
   is_union_select= is_unit_op() || fake_select_lex || single_tvc;
 
@@ -902,7 +902,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       while (last->next_select())
         last= last->next_select();
       if (!(tmp_result= union_result=
-              new (thd_arg->mem_root) select_union_direct(thd_arg, sel_result,
+              new (thd->mem_root) select_union_direct(thd, sel_result,
                                                           last)))
         goto err; /* purecov: inspected */
       fake_select_lex= NULL;
@@ -911,11 +911,11 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
     else
     {
       if (!is_recursive)
-	union_result= new (thd_arg->mem_root) select_unit(thd_arg);
+	union_result= new (thd->mem_root) select_unit(thd);
       else
       {
         with_element->rec_result=
-          new (thd_arg->mem_root) select_union_recursive(thd_arg);
+          new (thd->mem_root) select_union_recursive(thd);
         union_result=  with_element->rec_result;
         fake_select_lex= NULL;
       }
@@ -933,10 +933,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   {
     if (sl->tvc)
     {
-      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+      if (sl->tvc->prepare(thd, sl, tmp_result, this))
 	goto err;
     }
-    else if (prepare_join(thd_arg, first_sl, tmp_result, additional_options,
+    else if (prepare_join(thd, first_sl, tmp_result, additional_options,
                      is_union_select))
       goto err;
     types= first_sl->item_list;
@@ -947,10 +947,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   {
     if (sl->tvc)
     {
-      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+      if (sl->tvc->prepare(thd, sl, tmp_result, this))
 	goto err;
     }
-    else if (prepare_join(thd_arg, sl, tmp_result, additional_options,
+    else if (prepare_join(thd, sl, tmp_result, additional_options,
                           is_union_select))
       goto err;
 
@@ -970,7 +970,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
     {
       if (with_element)
       {
-        if (derived->with->rename_columns_of_derived_unit(thd, this))
+        if (derived_arg->with->rename_columns_of_derived_unit(thd, this))
           goto err;
         if (check_duplicate_names(thd, sl->item_list, 0))
           goto err;
@@ -981,7 +981,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       if (first_sl->item_list.elements != sl->item_list.elements)
       {
         my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT,
-                   ER_THD(thd_arg, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),
+                   ER_THD(thd, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),
                    MYF(0));
         goto err;
       }
@@ -995,20 +995,20 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
           sl->next_select() == with_element->first_recursive))
       {
         ulonglong create_options;
-        create_options= (first_sl->options | thd_arg->variables.option_bits |
+        create_options= (first_sl->options | thd->variables.option_bits |
                          TMP_TABLE_ALL_COLUMNS);
         // Join data types for all non-recursive parts of a recursive UNION
         if (join_union_item_types(thd, types, union_part_count + 1))
           goto err;
         if (union_result->create_result_table(thd, &types,
                                               MY_TEST(union_distinct),
-                                              create_options, &derived->alias,
-                                              false,
+                                              create_options,
+                                              &derived_arg->alias, false,
                                               instantiate_tmp_table, false,
                                               0))
           goto err;
-        if (!derived->table)
-          derived->table= derived->derived_result->table= 
+        if (!derived_arg->table)
+          derived_arg->table= derived_arg->derived_result->table=
             with_element->rec_result->rec_tables.head();
         with_element->mark_as_with_prepared_anchor();
         is_rec_result_table_created= true;
@@ -1079,7 +1079,7 @@ cont:
     }
 
 
-    create_options= (first_sl->options | thd_arg->variables.option_bits |
+    create_options= (first_sl->options | thd->variables.option_bits |
                      TMP_TABLE_ALL_COLUMNS);
     /*
       Force the temporary table to be a MyISAM table if we're going to use
@@ -1107,7 +1107,7 @@ cont:
           Query_arena *arena, backup_arena;
           arena= thd->activate_stmt_arena_if_needed(&backup_arena);
 
-          intersect_mark= new (thd_arg->mem_root) Item_int(thd, 0);
+          intersect_mark= new (thd->mem_root) Item_int(thd, 0);
 
           if (arena)
             thd->restore_active_arena(arena, &backup_arena);
@@ -1151,7 +1151,7 @@ cont:
       result_table_list.maybe_null_exec= save_maybe_null;
     }
 
-    thd_arg->lex->current_select= lex_select_save;
+    thd->lex->current_select= lex_select_save;
     if (!item_list.elements)
     {
       Query_arena *arena, backup_arena;
@@ -1191,7 +1191,7 @@ cont:
         */
 	fake_select_lex->item_list= item_list;
 
-	thd_arg->lex->current_select= fake_select_lex;
+	thd->lex->current_select= fake_select_lex;
 
         /*
           We need to add up n_sum_items in order to make the correct
@@ -1219,12 +1219,12 @@ cont:
     }
   }
 
-  thd_arg->lex->current_select= lex_select_save;
+  thd->lex->current_select= lex_select_save;
 
-  DBUG_RETURN(saved_error || thd_arg->is_fatal_error);
+  DBUG_RETURN(saved_error || thd->is_fatal_error);
 
 err:
-  thd_arg->lex->current_select= lex_select_save;
+  thd->lex->current_select= lex_select_save;
   (void) cleanup();
   DBUG_RETURN(TRUE);
 }
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 903e30f43a6..711115cfdad 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -530,7 +530,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
 
   /* prepare select to resolve all fields */
   lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW;
-  if (unit->prepare(thd, 0, 0))
+  if (unit->prepare(unit->derived, 0, 0))
   {
     /*
       some errors from prepare are reported to user, if is not then
diff --git a/sql/table.cc b/sql/table.cc
index 1ae0b526d10..eb9dd24c119 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8117,7 +8117,21 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
       (first_table && first_table->is_multitable()))
     set_multitable();
 
-  unit->derived= this;
+  if (!unit->derived)
+    unit->derived= this;
+  else if (!is_with_table_recursive_reference() && unit->derived != this)
+  {
+    if (unit->derived->is_with_table_recursive_reference())
+      unit->derived= this;
+    else if (vers_conditions.eq(unit->derived->vers_conditions))
+      vers_conditions.empty();
+    else
+    {
+      my_error(ER_CONFLICTING_FOR_SYSTEM_TIME, MYF(0));
+      return TRUE;
+    }
+  }
+
   if (init_view && !view)
   {
     /* This is all what we can do for a derived table for now. */
@@ -8867,6 +8881,26 @@ void vers_select_conds_t::resolve_units(bool timestamps_only)
   end.resolve_unit(timestamps_only);
 }
 
+bool vers_select_conds_t::eq(const vers_select_conds_t &conds)
+{
+  if (type != conds.type)
+    return false;
+  switch (type) {
+  case SYSTEM_TIME_UNSPECIFIED:
+  case SYSTEM_TIME_ALL:
+    return true;
+  case SYSTEM_TIME_BEFORE:
+    DBUG_ASSERT(0);
+  case SYSTEM_TIME_AS_OF:
+    return start.eq(conds.start);
+  case SYSTEM_TIME_FROM_TO:
+  case SYSTEM_TIME_BETWEEN:
+    return start.eq(conds.start) && end.eq(conds.end);
+  }
+  DBUG_ASSERT(0);
+  return false;
+}
+
 void Vers_history_point::resolve_unit(bool timestamps_only)
 {
   if (item && unit == VERS_UNDEFINED)
@@ -8888,6 +8922,12 @@ void Vers_history_point::fix_item()
     item->decimals= 6;
 }
 
+
+bool Vers_history_point::eq(const vers_history_point_t &point)
+{
+  return unit == point.unit && item->eq(point.item, false);
+}
+
 void Vers_history_point::print(String *str, enum_query_type query_type,
                                const char *prefix, size_t plen)
 {
diff --git a/sql/table.h b/sql/table.h
index 30588be762c..a6a27d21fc3 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1829,6 +1829,7 @@ public:
   void empty() { unit= VERS_UNDEFINED; item= NULL; }
   void print(String *str, enum_query_type, const char *prefix, size_t plen);
   void resolve_unit(bool timestamps_only);
+  bool eq(const vers_history_point_t &point);
 };
 
 struct vers_select_conds_t
@@ -1878,6 +1879,7 @@ struct vers_select_conds_t
   {
     return !from_query && type != SYSTEM_TIME_UNSPECIFIED;
   }
+  bool eq(const vers_select_conds_t &conds);
 };
 
 /*