1 files changed, 101 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test
new file mode 100644
index 00000000000..9df0bb3dfba
--- /dev/null
+++ b/mysql-test/suite/versioning/t/cte.test
@@ -0,0 +1,101 @@
+-- source include/have_innodb.inc
+set default_storage_engine=innodb;
+create or replace table dept (
+  dept_id int(10) primary key, 
+  name varchar(100)
+)
+with system versioning;
+
+create or replace table emp (
+  emp_id int(10) primary key,
+  dept_id int(10) not null,
+  name varchar(100) not null,
+  mgr int(10),
+  salary int(10) not null,
+  constraint `dept-emp-fk`
+    foreign key (dept_id) references dept (dept_id)
+      on delete cascade
+      on update restrict,
+  constraint `mgr-fk`
+    foreign key (mgr) references emp (emp_id)
+      on delete restrict
+      on update restrict
+) 
+with system versioning;
+
+insert into dept (dept_id, name) values (10, "accounting");
+
+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;
+
+update emp set mgr=30 where name ="john";
+select sys_trx_start into @ts_2 from emp where name="john";
+
+/* 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;
+
+/* Expected 3 rows */
+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
+  where name = 'bill'
+  union 
+  select e.emp_id, e.name, e.mgr, e.salary
+  from emp for system_time as of timestamp @ts_2 as e,
+       ancestors as a
+  where e.mgr = a.emp_id
+)
+select * from ancestors;
+
+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);
+insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London');
+set @ts=now(6);
+delete from emp;
+delete from addr;
+
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr
+  from emp for system_time as of timestamp @ts as e
+  where name = 'bill'
+  union
+  select ee.emp_id, ee.name, ee.mgr
+  from emp for system_time as of timestamp @ts as ee, ancestors as a
+  where ee.mgr = a.emp_id
+)
+select * from ancestors;
+
+insert emp values (4, 'john', 1);
+insert addr values (4, 'Paris');
+with ancestors as (select * from emp natural join addr) select * from ancestors;
+with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all;
+with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors;
+select * from (select * from emp natural join addr) for system_time all as t;
+
+drop table emp;
+drop table dept;
+drop table addr;