SQL: VIEW system fields propagation removed [fixes #393]

4 files changed, 15 insertions, 24 deletions

diff --git a/mysql-test/suite/versioning/r/derived.result b/mysql-test/suite/versioning/r/derived.result
index bc1176da175..138f43fa4bd 100644
--- a/mysql-test/suite/versioning/r/derived.result
+++ b/mysql-test/suite/versioning/r/derived.result
@@ -178,10 +178,6 @@ x
 select * from (select * from vt1, t2) as s0;
 x	y
 1	10
-# leading table selection
-select * from (select *, vt1.sys_trx_end from t2, vt1) as s0;
-y	x
-10	1
 ### SYSTEM_TIME clash
 select * from (select * from t1 for system_time all) dt0 for system_time all;
 ERROR HY000: SYSTEM_TIME is not allowed outside historical `dt0`
diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result
index 03c8b08ce2f..128c849b18c 100644
--- a/mysql-test/suite/versioning/r/view.result
+++ b/mysql-test/suite/versioning/r/view.result
@@ -17,7 +17,7 @@ x
 create or replace view vt1 as select * from t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
 drop view vt1;
 drop view vt2;
 create or replace view vt1 as select * from t1 for system_time all;
@@ -69,7 +69,7 @@ create or replace table t1 (x int) with system versioning;
 create or replace view vt1(c) as select x from t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c` from `t1` FOR SYSTEM_TIME ALL where `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
 # VIEW over JOIN of versioned tables [#153]
 create or replace table t1 (a int) with system versioning;
 create or replace table t2 (b int) with system versioning;
@@ -83,29 +83,29 @@ create or replace view vt12 as select * from t1 for system_time as of timestamp
 select * from vt12;
 a	b
 # VIEW improvements [#183]
-create or replace view vt1 as select a, t1.sys_trx_start, t2.sys_trx_end from t1, t2;
-ERROR HY000: Creating VIEW `vt1` is prohibited: system fields from multiple tables `t1`, `t2` in query!
-create or replace view vt1 as select a, t1.sys_trx_end, t2.sys_trx_end from t1, t2;
-ERROR HY000: Creating VIEW `vt1` is prohibited: multiple end system fields `t1.sys_trx_end`, `t2.sys_trx_end` in query!
 create or replace table t3 (x int);
 create or replace view vt1 as select * from t1, t2, t3;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x`,`t1`.`sys_trx_start` AS `sys_trx_start`,`t1`.`sys_trx_end` AS `sys_trx_end` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where `t1`.`sys_trx_end` = MAX_RESULT and `t2`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x` from ((`t1` FOR SYSTEM_TIME ALL join `t2` FOR SYSTEM_TIME ALL) join `t3`) where `t1`.`sys_trx_end` = MAX_RESULT and `t2`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
 create or replace view vt1 as select * from t3, t2, t1;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a`,`t2`.`sys_trx_start` AS `sys_trx_start`,`t2`.`sys_trx_end` AS `sys_trx_end` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where `t2`.`sys_trx_end` = MAX_RESULT and `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a` from ((`t3` join `t2` FOR SYSTEM_TIME ALL) join `t1` FOR SYSTEM_TIME ALL) where `t2`.`sys_trx_end` = MAX_RESULT and `t1`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
 create or replace view vt1 as select a, t2.sys_trx_end as endo from t3, t1, t2;
 show create view vt1;
 View	Create View	character_set_client	collation_connection
-vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`sys_trx_end` AS `endo`,`t2`.`sys_trx_start` AS `sys_trx_start` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where `t1`.`sys_trx_end` = MAX_RESULT and `t2`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
+vt1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`sys_trx_end` AS `endo` from ((`t3` join `t1` FOR SYSTEM_TIME ALL) join `t2` FOR SYSTEM_TIME ALL) where `t1`.`sys_trx_end` = MAX_RESULT and `t2`.`sys_trx_end` = MAX_RESULT	latin1	latin1_swedish_ci
 # VIEW over UNION [#269]
 create or replace view vt1 as select * from t1 union select * from t1;
 select * from vt1;
 a
 1
-create or replace view vvt1 as select * from t1, t2, vt1;
-ERROR HY000: Creating VIEW `vvt1` is prohibited: versioned VIEW `vt1` in query!
+# VIEW over UNION with non-versioned [#393]
+create or replace table t2 (a int);
+create or replace view vt1 as select * from t1 union select * from t2;
+select * from vt1;
+a
+1
 drop database test;
 create database test;
diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test
index 590f4bc9292..3ef57aef1a8 100644
--- a/mysql-test/suite/versioning/t/derived.test
+++ b/mysql-test/suite/versioning/t/derived.test
@@ -125,8 +125,6 @@ create view vt2 as select * from t1;
 select * from vt1;
 --echo # SYSTEM_TIME propagation from inner to outer
 select * from (select * from vt1, t2) as s0;
---echo # leading table selection
-select * from (select *, vt1.sys_trx_end from t2, vt1) as s0;
 
 --echo ### SYSTEM_TIME clash
 --error ER_VERS_SYSTEM_TIME_CLASH
diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test
index a82949673f6..e2ad96e70cc 100644
--- a/mysql-test/suite/versioning/t/view.test
+++ b/mysql-test/suite/versioning/t/view.test
@@ -69,11 +69,6 @@ create or replace view vt12 as select * from t1 for system_time as of timestamp
 select * from vt12;
 
 --echo # VIEW improvements [#183]
---error ER_VERS_VIEW_PROHIBITED
-create or replace view vt1 as select a, t1.sys_trx_start, t2.sys_trx_end from t1, t2;
---error ER_VERS_VIEW_PROHIBITED
-create or replace view vt1 as select a, t1.sys_trx_end, t2.sys_trx_end from t1, t2;
-
 create or replace table t3 (x int);
 create or replace view vt1 as select * from t1, t2, t3;
 --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT
@@ -89,8 +84,10 @@ show create view vt1;
 create or replace view vt1 as select * from t1 union select * from t1;
 select * from vt1;
 
---error ER_VERS_VIEW_PROHIBITED
-create or replace view vvt1 as select * from t1, t2, vt1;
+--echo # VIEW over UNION with non-versioned [#393]
+create or replace table t2 (a int);
+create or replace view vt1 as select * from t1 union select * from t2;
+select * from vt1;
 
 drop database test;
 create database test;