1 files changed, 236 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test
new file mode 100644
index 00000000000..9784a4a0d2f
--- /dev/null
+++ b/mysql-test/suite/versioning/t/derived.test
@@ -0,0 +1,236 @@
+create table emp
+(
+  emp_id int,
+  name varchar(127),
+  mgr int
+) with system versioning;
+
+insert into emp values (1, 'bill', 0),
+                       (2, 'bill', 1),
+		       (3, 'kate', 1);
+set @ts=now(6);
+delete from emp;
+insert into emp values (4, 'john', 1);
+
+with ancestors as (select * from emp) select * from ancestors;
+set @tmp= "with ancestors as (select * from emp) select * from ancestors";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+with ancestors as (select * from emp for system_time all) select * from ancestors;
+set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+with recursive ancestors as (select * from emp) select * from ancestors;
+set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp;
+set @tmp= "select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr
+  from emp as e
+  where name = 'john'
+  union
+  select ee.emp_id, ee.name, ee.mgr
+  from emp as ee, ancestors as a
+  where ee.mgr = a.emp_id
+)
+select * from ancestors;
+set @tmp= "
+with recursive
+ancestors
+as
+(
+  select e.emp_id, e.name, e.mgr
+  from emp as e
+  where name = 'john'
+  union
+  select ee.emp_id, ee.name, ee.mgr
+  from emp as ee, ancestors as a
+  where ee.mgr = a.emp_id
+)
+select * from ancestors";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+#385
+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;
+set @tmp= "
+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";
+prepare stmt from @tmp; execute stmt; drop prepare stmt;
+
+drop table emp;
+
+create or replace table t1 (x int) with system versioning;
+create or replace table t2 (y int) with system versioning;
+insert into t1 values (1);
+set @t0= now(6);
+delete from t1;
+insert into t1 values (2);
+insert into t2 values (10);
+
+--replace_column 2 # 3 #
+select * from (select *, t1.row_end, t1.row_end as endo from t1) as s0;
+--replace_column 3 # 4 #
+select * from (select *, t1.row_end, t2.row_start from t1, t2) as s0;
+
+--echo # SYSTEM_TIME propagation from inner to outer
+select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0;
+with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1;
+--echo # leading table selection
+--replace_column 3 #
+select * from (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) as s2;
+--replace_column 3 #
+with s3 as (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) select * from s3;
+
+--echo ### VIEW instead of t1
+set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'");
+prepare q from @q; execute q; drop prepare q;
+create view vt2 as select * from t1;
+
+--echo # SYSTEM_TIME propagation from view
+select * from vt1;
+--echo # SYSTEM_TIME propagation from inner to outer
+select * from (select * from vt1, t2) as s0;
+
+--echo ### SYSTEM_TIME clash
+--error ER_VERS_NOT_VERSIONED
+select * from (select * from t1 for system_time all) for system_time all as dt0;
+--error ER_VERS_NOT_VERSIONED
+select * from vt1 for system_time all;
+--error ER_VERS_NOT_VERSIONED
+with dt1 as (select * from t1 for system_time all)
+select * from dt1 for system_time all;
+
+--echo ### UNION
+set @t1= now(6);
+delete from t2;
+insert into t2 values (3);
+--echo # SYSTEM_TIME is not propagated
+select x from t1 union
+select y from t2;
+select x from t1 for system_time as of @t0 union
+select y from t2;
+select x from t1 union
+select y from t2 for system_time as of @t1;
+select x from t1 for system_time as of @t0 union
+select y from t2 for system_time as of @t1;
+
+--echo # LEFT/RIGHT JOIN
+create or replace table t1 (x int, y int) with system versioning;
+create or replace table t2 (x int, y int) with system versioning;
+
+insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
+insert into t2 values (1, 2), (2, 1), (3, 1);
+
+--echo ## Outer or inner SYSTEM_TIME produces same expression
+
+--disable_warnings
+--disable_query_log
+explain extended
+select * from (
+  select t1.x, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 join t2 on t1.x = t2.x) for system_time as of now() as t;
+
+let $a=`show warnings`;
+--echo Query A:
+echo $a;
+
+explain extended
+select * from (
+  select t1.x, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 for system_time as of now()
+  join t2 for system_time as of now() on t1.x = t2.x) as t;
+
+let $b=`show warnings`;
+--echo Query B:
+echo $b;
+
+if ($a == $b)
+{
+  --echo Fine result: queries A and B are equal.
+}
+--enable_query_log
+--enable_warnings
+
+--echo ## LEFT JOIN: t1, t2 versioned
+select * from (
+  select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 left join t2 on t1.x = t2.x)
+as derived;
+
+alter table t2 drop system versioning;
+
+--echo ## LEFT JOIN: t1 versioned
+select * from (
+  select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 left join t2 on t1.x = t2.x)
+as derived;
+
+alter table t1 drop system versioning;
+alter table t2 add system versioning;
+
+--echo ## LEFT JOIN: t2 versioned
+select * from (
+  select t1.x as LJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 left join t2 on t1.x = t2.x)
+as derived;
+
+alter table t1 add system versioning;
+
+--echo ## RIGHT JOIN: t1, t2 versioned
+select * from (
+  select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 right join t2 on t1.x = t2.x)
+as derived;
+
+alter table t2 drop system versioning;
+
+--echo ## RIGHT JOIN: t1 versioned
+select * from (
+  select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 right join t2 on t1.x = t2.x)
+as derived;
+
+alter table t1 drop system versioning;
+alter table t2 add system versioning;
+
+--echo ## RIGHT JOIN: t2 versioned
+select * from (
+  select t1.x as RJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
+  from t1 right join t2 on t1.x = t2.x)
+as derived;
+
+drop table t1, t2;
+drop view vt1, vt2;
+