MDEV-3944: Allow derived tables in VIEWS

15 files changed, 450 insertions, 57 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index c1f2739bd97..dbfcf4ed7c9 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -543,9 +543,8 @@ c	a
 explain
 select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	32	
-2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	4	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
 # with claused in the specification of a materialized view
 create view v2 as 
 with t as (select a, count(*) from t1 where b >= 'c' group by a) 
@@ -560,9 +559,8 @@ c	a	count(*)
 explain
 select * from v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	8	
-2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
-2	DERIVED	<derived3>	ref	key0	key0	5	test.t2.c	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t2.c	2	
 3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 # with clause in the specification of a view that whose definition
 # table alias for a with table
@@ -593,9 +591,8 @@ c	d
 explain
 select * from v4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	64	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
 drop view v1,v2,v3,v4;
 # currently any views containing with clause are not updatable
 create view v1(a) as 
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 447afe3d330..2a719eafc76 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1235,8 +1235,8 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 CREATE PROCEDURE bug20953()
 CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE()' at line 2
-CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1 into @w;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @w' at line 1
 CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;
 ERROR HY000: View's SELECT contains a variable or parameter
 CREATE PROCEDURE bug20953()
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index ca1a1dfa48a..8e9bad9e899 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -930,7 +930,7 @@ create table t1 (a int);
 create view v1 as select a from t1 procedure analyse();
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'procedure analyse()' at line 1
 create view v1 as select 1 from (select 1) as d1;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+drop view v1;
 drop table t1;
 create table t1 (s1 int, primary key (s1));
 create view v1 as select * from t1;
@@ -1494,7 +1494,7 @@ a
 create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2;
 set updatable_views_with_limit=NO;
 update v2 set a= 10 where a=200 limit 1;
-ERROR HY000: The target table t1 of the UPDATE is not updatable
+ERROR HY000: The target table v2 of the UPDATE is not updatable
 set updatable_views_with_limit=DEFAULT;
 select * from v3;
 a	b
@@ -3209,15 +3209,11 @@ code	COUNT(DISTINCT country)
 DROP VIEW v1;
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
-SELECT * FROM (SELECT 1) AS t;
-1
-1
-CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+SELECT * FROM (SELECT 1) AS t into @w;
+CREATE VIEW v1 AS SELECT * FROM (SELECT 1) AS t into @w;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @w' at line 1
 # Previously the following would fail.
-SELECT * FROM (SELECT 1) AS t;
-1
-1
+SELECT * FROM (SELECT 1) AS t into @w;
 drop view if exists view_24532_a;
 drop view if exists view_24532_b;
 drop table if exists table_24532;
@@ -4100,7 +4096,7 @@ ERROR HY000: The target table v1 of the INSERT is not insertable-into
 DELETE from v1;
 ERROR HY000: The target table v1 of the DELETE is not updatable
 UPDATE v3 SET b= 10;
-ERROR HY000: The target table v2 of the UPDATE is not updatable
+ERROR HY000: The target table v3 of the UPDATE is not updatable
 REPLACE v3 SET b= 10;
 ERROR HY000: The target table v3 of the INSERT is not insertable-into
 INSERT into v3(b) values (20);
@@ -5953,5 +5949,241 @@ t3	CREATE TABLE `t3` (
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
+# MDEV-3944: Allow derived tables in VIEWS
+#
+create table t1 (s1 int);
+insert into t1 values (1),(2),(3);
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1>1) AS x;
+CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1<3) AS x;
+select * from v1;
+s1
+2
+3
+select * from v2;
+s1
+1
+2
+select * from v1 natural join v2;
+s1
+2
+select * from v1 natural join t1;
+s1
+2
+3
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+s1
+2
+select * from v1 left join v2 on (v1.s1=v2.s1);
+s1	s1
+2	2
+3	NULL
+select * from v1 left join t1 on (v1.s1=t1.s1);
+s1	s1
+2	2
+3	3
+select * from t1 left join v2 on (t1.s1=v2.s1);
+s1	s1
+1	1
+2	2
+3	NULL
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+s1	s1
+2	2
+3	NULL
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+s1	s1
+2	2
+3	NULL
+drop view v1,v2;
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+> -100) as xx WHERE s1<3) AS x;
+insert into t1 values (200),(-200);
+select * from t1;
+s1
+-200
+1
+2
+200
+3
+select * from v1;
+s1
+2
+3
+select * from v2;
+s1
+1
+2
+select * from v1 natural join v2;
+s1
+2
+select * from v1 natural join t1;
+s1
+2
+3
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+s1
+2
+select * from v1 left join v2 on (v1.s1=v2.s1);
+s1	s1
+2	2
+3	NULL
+select * from v1 left join t1 on (v1.s1=t1.s1);
+s1	s1
+2	2
+3	3
+select * from t1 left join v2 on (t1.s1=v2.s1);
+s1	s1
+-200	NULL
+1	1
+2	2
+200	NULL
+3	NULL
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+s1	s1
+2	2
+3	NULL
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+s1	s1
+2	2
+200	NULL
+3	NULL
+drop view v1,v2;
+CREATE algorithm=temptable VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+CREATE algorithm=temptable VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+> -100) as xx WHERE s1<3) AS x;
+select * from t1;
+s1
+-200
+1
+2
+200
+3
+select * from v1;
+s1
+2
+3
+select * from v2;
+s1
+1
+2
+select * from v1 natural join v2;
+s1
+2
+select * from v1 natural join t1;
+s1
+2
+3
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+s1
+2
+select * from v1 left join v2 on (v1.s1=v2.s1);
+s1	s1
+2	2
+3	NULL
+select * from v1 left join t1 on (v1.s1=t1.s1);
+s1	s1
+2	2
+3	3
+select * from t1 left join v2 on (t1.s1=v2.s1);
+s1	s1
+-200	NULL
+1	1
+2	2
+200	NULL
+3	NULL
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+s1	s1
+2	2
+3	NULL
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+s1	s1
+2	2
+200	NULL
+3	NULL
+drop view v1,v2;
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+insert into v1 values (-300);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+update v1 set s1=s1+1;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+drop view v1;
+CREATE VIEW v1 AS SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 <
+100) x, t1 WHERE t1.s1=x.s2;
+select * from v1;
+s1	s2
+1	1
+2	2
+3	3
+-200	-200
+insert into v1 (s1) values (-300);
+update v1 set s1=s1+1;
+select * from v1;
+s1	s2
+2	2
+3	3
+4	4
+-199	-199
+-299	-299
+select * from t1;
+s1
+2
+3
+4
+200
+-199
+-299
+insert into v1(s2) values (-300);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+update v1 set s2=s2+1;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+drop view v1;
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1
+< 100) AS x;
+insert into v1 values (-300);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+update v1 set s1=s1+1;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+drop view v1;
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+insert into v1 values (-300);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+update v1 set s1=s1+1;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+create view v2 as select * from v1;
+insert into v2 values (-300);
+ERROR HY000: The target table v2 of the INSERT is not insertable-into
+update v2 set s1=s1+1;
+ERROR HY000: The target table v2 of the UPDATE is not updatable
+drop view v1, v2;
+drop table t1;
+#
+# MDEV-9671:Wrong result upon select from a view with a FROM subquery
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3),(2);
+CREATE TABLE t2 (j INT);
+INSERT INTO t2 VALUES (8),(3),(3);
+CREATE TABLE t3 (k INT);
+INSERT INTO t3 VALUES (1),(8);
+CREATE VIEW v1 AS SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j );
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i`,`alias1`.`j` AS `j` from (`test`.`t1` left join (select `test`.`t2`.`j` AS `j` from (`test`.`t2` join `test`.`t3` on((`test`.`t3`.`k` = `test`.`t2`.`j`)))) `alias1` on((`test`.`t1`.`i` = `alias1`.`j`)))	latin1	latin1_swedish_ci
+SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j );
+i	j
+3	NULL
+2	NULL
+SELECT * FROM v1;
+i	j
+3	NULL
+2	NULL
+DROP VIEW v1;
+DROP TABLE t1, t2, t3;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result
index 45de9534df2..fa9d9df59d9 100644
--- a/mysql-test/suite/funcs_1/r/innodb_views.result
+++ b/mysql-test/suite/funcs_1/r/innodb_views.result
@@ -3503,7 +3503,7 @@ Select @x;
 0
 CREATE or REPLACE VIEW v1 AS Select 1
 FROM (SELECT 1 FROM t1) my_table;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f1 FROM t1;
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
 SET @a:=0 ;
@@ -7535,7 +7535,7 @@ Testcase 3.3.1.39
 Drop view if exists test.v1 ;
 CREATE VIEW test.v1
 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW test.v1;
 SELECT * FROM test.v1 order by f59 ;
 ERROR 42S02: Table 'test.v1' doesn't exist
 Drop view if exists test.v1 ;
diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result
index ab4e2a9933c..989f533af16 100644
--- a/mysql-test/suite/funcs_1/r/memory_views.result
+++ b/mysql-test/suite/funcs_1/r/memory_views.result
@@ -3504,7 +3504,7 @@ Select @x;
 0
 CREATE or REPLACE VIEW v1 AS Select 1
 FROM (SELECT 1 FROM t1) my_table;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW v1;
 CREATE VIEW v1 AS SELECT f1 FROM t1;
 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
 SET @a:=0 ;
@@ -7536,7 +7536,7 @@ Testcase 3.3.1.39
 Drop view if exists test.v1 ;
 CREATE VIEW test.v1
 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
-ERROR HY000: View's SELECT contains a subquery in the FROM clause
+DROP VIEW test.v1;
 SELECT * FROM test.v1 order by f59 ;
 ERROR 42S02: Table 'test.v1' doesn't exist
 Drop view if exists test.v1 ;
diff --git a/mysql-test/suite/funcs_1/views/views_master.inc b/mysql-test/suite/funcs_1/views/views_master.inc
index f55788de6c6..54e7f2af54b 100644
--- a/mysql-test/suite/funcs_1/views/views_master.inc
+++ b/mysql-test/suite/funcs_1/views/views_master.inc
@@ -271,9 +271,9 @@ CREATE or REPLACE VIEW v1 AS Select 1 INTO @x;
 Select @x;
 
 # Subquery in the FROM clause is illegal
---error ER_VIEW_SELECT_DERIVED
 CREATE or REPLACE VIEW v1 AS Select 1
 FROM (SELECT 1 FROM t1) my_table;
+DROP VIEW v1;
 
 # Triggers cannot be associated with VIEWs
 CREATE VIEW v1 AS SELECT f1 FROM t1;
@@ -1557,9 +1557,9 @@ let $message= Testcase 3.3.1.39 ;
 --disable_warnings
 Drop view if exists test.v1 ;
 --enable_warnings
---error ER_VIEW_SELECT_DERIVED
 CREATE VIEW test.v1
 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
+DROP VIEW test.v1;
 --error ER_NO_SUCH_TABLE
 SELECT * FROM test.v1 order by f59 ;
 --disable_warnings
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index b1d4f67f357..15cd0ec4e81 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -1794,8 +1794,8 @@ CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file";
 --error ER_PARSE_ERROR
 CREATE PROCEDURE bug20953()
   CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();
---error ER_VIEW_SELECT_DERIVED
-CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1 into @w;
 --error ER_VIEW_SELECT_VARIABLE
 CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;
 delimiter |;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9fdabca82de..2e413c0988a 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -847,8 +847,9 @@ create view v1 as select 5 into outfile 'ttt';
 create table t1 (a int);
 -- error ER_PARSE_ERROR
 create view v1 as select a from t1 procedure analyse();
--- error ER_VIEW_SELECT_DERIVED
+# now derived tables are allowed
 create view v1 as select 1 from (select 1) as d1;
+drop view v1;
 drop table t1;
 
 #
@@ -3173,10 +3174,10 @@ DROP TABLE t1;
 DROP VIEW IF EXISTS v1;
 --enable_warnings
 
-let $query = SELECT * FROM (SELECT 1) AS t;
+let $query = SELECT * FROM (SELECT 1) AS t into @w;
 
 eval $query;
---error ER_VIEW_SELECT_DERIVED
+--error ER_PARSE_ERROR
 eval CREATE VIEW v1 AS $query;
 --echo # Previously the following would fail.
 eval $query;
@@ -5784,7 +5785,172 @@ SHOW CREATE TABLE t3;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # MDEV-3944: Allow derived tables in VIEWS
+--echo #
+create table t1 (s1 int);
+insert into t1 values (1),(2),(3);
+
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1>1) AS x;
+CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1<3) AS x;
+
+--sorted_result
+select * from v1;
+--sorted_result
+select * from v2;
+--sorted_result
+select * from v1 natural join v2;
+--sorted_result
+select * from v1 natural join t1;
+--sorted_result
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+--sorted_result
+select * from v1 left join v2 on (v1.s1=v2.s1);
+--sorted_result
+select * from v1 left join t1 on (v1.s1=t1.s1);
+--sorted_result
+select * from t1 left join v2 on (t1.s1=v2.s1);
+--sorted_result
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+--sorted_result
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+
+drop view v1,v2;
+
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+CREATE VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+> -100) as xx WHERE s1<3) AS x;
+insert into t1 values (200),(-200);
+--sorted_result
+select * from t1;
+--sorted_result
+select * from v1;
+--sorted_result
+select * from v2;
+--sorted_result
+select * from v1 natural join v2;
+--sorted_result
+select * from v1 natural join t1;
+--sorted_result
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+--sorted_result
+select * from v1 left join v2 on (v1.s1=v2.s1);
+--sorted_result
+select * from v1 left join t1 on (v1.s1=t1.s1);
+--sorted_result
+select * from t1 left join v2 on (t1.s1=v2.s1);
+--sorted_result
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+--sorted_result
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+
+drop view v1,v2;
+
+CREATE algorithm=temptable VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+CREATE algorithm=temptable VIEW v2 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+> -100) as xx WHERE s1<3) AS x;
+--sorted_result
+select * from t1;
+--sorted_result
+select * from v1;
+--sorted_result
+select * from v2;
+--sorted_result
+select * from v1 natural join v2;
+--sorted_result
+select * from v1 natural join t1;
+--sorted_result
+select * from v1 natural join (SELECT s1 FROM t1 WHERE s1<3) as x;
+--sorted_result
+select * from v1 left join v2 on (v1.s1=v2.s1);
+--sorted_result
+select * from v1 left join t1 on (v1.s1=t1.s1);
+--sorted_result
+select * from t1 left join v2 on (t1.s1=v2.s1);
+--sorted_result
+select * from v1 left join (SELECT s1 FROM t1 WHERE s1<3) as x on (v1.s1=x.s1);
+--sorted_result
+select * from (SELECT s1 FROM t1 WHERE s1>1) AS x left join v2 on (x.s1=v2.s1);
+
+drop view v1,v2;
+
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+
+--error ER_NON_INSERTABLE_TABLE
+insert into v1 values (-300);
+--error ER_NON_UPDATABLE_TABLE
+update v1 set s1=s1+1;
+
+drop view v1;
+
+CREATE VIEW v1 AS SELECT s1,s2 FROM (SELECT s1 as s2 FROM t1 WHERE s1 <
+100) x, t1 WHERE t1.s1=x.s2;
+select * from v1;
+
+insert into v1 (s1) values (-300);
+update v1 set s1=s1+1;
+select * from v1;
+select * from t1;
+--error ER_NON_INSERTABLE_TABLE
+insert into v1(s2) values (-300);
+--error ER_NON_UPDATABLE_TABLE
+update v1 set s2=s2+1;
+
+drop view v1;
+
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM t1 WHERE s1
+< 100) AS x;
+
+--error ER_NON_INSERTABLE_TABLE
+insert into v1 values (-300);
+--error ER_NON_UPDATABLE_TABLE
+update v1 set s1=s1+1;
+
+drop view v1;
+
+CREATE VIEW v1 AS SELECT * FROM (SELECT s1 FROM (SELECT s1 FROM t1 WHERE s1
+< 100) as xx WHERE s1>1) AS x;
+
+--error ER_NON_INSERTABLE_TABLE
+insert into v1 values (-300);
+--error ER_NON_UPDATABLE_TABLE
+update v1 set s1=s1+1;
+
+create view v2 as select * from v1;
+
+--error ER_NON_INSERTABLE_TABLE
+insert into v2 values (-300);
+--error ER_NON_UPDATABLE_TABLE
+update v2 set s1=s1+1;
 
+drop view v1, v2;
+drop table t1;
+
+--echo #
+--echo # MDEV-9671:Wrong result upon select from a view with a FROM subquery
+--echo #
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (3),(2);
+
+CREATE TABLE t2 (j INT);
+INSERT INTO t2 VALUES (8),(3),(3);
+
+CREATE TABLE t3 (k INT);
+INSERT INTO t3 VALUES (1),(8);
+
+CREATE VIEW v1 AS SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j );
+
+show create view v1;
+
+SELECT * FROM t1 LEFT JOIN ( SELECT t2.* FROM t2 INNER JOIN t3 ON ( k = j ) ) AS alias1 ON ( i = j );
+
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1, t2, t3;
 --echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 79e57cded81..05e07a48b49 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -567,7 +567,11 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived)
   if (!unit || unit->prepared)
     DBUG_RETURN(FALSE);
 
-  DBUG_RETURN(derived->init_derived(thd, TRUE));
+  bool res= derived->init_derived(thd, TRUE);
+
+  derived->updatable= derived->updatable && derived->is_view();
+
+  DBUG_RETURN(res);
 }
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 8d5f54f2766..adc443affab 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -232,7 +232,6 @@ void
 st_parsing_options::reset()
 {
   allows_variable= TRUE;
-  allows_derived= TRUE;
 }
 
 
@@ -2951,7 +2950,7 @@ bool LEX::can_be_merged()
          tmp_unit= tmp_unit->next_unit())
     {
       if (tmp_unit->first_select()->parent_lex == this &&
-          (tmp_unit->item == 0 ||
+          (tmp_unit->item != 0 &&
            (tmp_unit->item->place() != IN_WHERE &&
             tmp_unit->item->place() != IN_ON &&
             tmp_unit->item->place() != SELECT_LIST)))
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 028ede480d4..58fa7ec9a2d 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1817,7 +1817,6 @@ private:
 struct st_parsing_options
 {
   bool allows_variable;
-  bool allows_derived;
 
   st_parsing_options() { reset(); }
   void reset();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6c4d2e1fc9c..77a7f8d6c50 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -24541,7 +24541,8 @@ static void print_table_array(THD *thd,
       continue;
     }
 
-    if (curr->outer_join)
+    /* JOIN_TYPE_OUTER is just a marker unrelated to real join */
+    if (curr->outer_join & (JOIN_TYPE_LEFT|JOIN_TYPE_RIGHT))
     {
       /* MySQL converts right to left joins */
       str->append(STRING_WITH_LEN(" left join "));
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 6c60350844e..76454d7c56f 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1234,10 +1234,8 @@ bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
           {
             // Partitioned key is updated
             my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
-                     tl->belong_to_view ? tl->belong_to_view->alias
-                                        : tl->alias,
-                     tl2->belong_to_view ? tl2->belong_to_view->alias
-                                         : tl2->alias);
+                     tl->top_table()->alias,
+                     tl2->top_table()->alias);
             return true;
           }
 
@@ -1255,10 +1253,8 @@ bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
               {
                 // Clustered primary key is updated
                 my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0),
-                         tl->belong_to_view ? tl->belong_to_view->alias
-                         : tl->alias,
-                         tl2->belong_to_view ? tl2->belong_to_view->alias
-                         : tl2->alias);
+                         tl->top_table()->alias,
+                         tl2->top_table()->alias);
                 return true;
               }
             }
@@ -1459,11 +1455,13 @@ int mysql_multi_update_prepare(THD *thd)
     {
       if (!tl->single_table_updatable() || check_key_in_view(thd, tl))
       {
-        my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
+        my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
+                 tl->top_table()->alias, "UPDATE");
         DBUG_RETURN(TRUE);
       }
 
-      DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
+      DBUG_PRINT("info",("setting table `%s` for update",
+                         tl->top_table()->alias));
       /*
         If table will be updated we should not downgrade lock for it and
         leave it as is.
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 37a543aef53..1aa6b07adeb 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11373,11 +11373,6 @@ get_select_lex_derived:
 select_derived_init:
           {
             LEX *lex= Lex;
-            if (! lex->parsing_options.allows_derived)
-            {
-              my_error(ER_VIEW_SELECT_DERIVED, MYF(0));
-              MYSQL_YYABORT;
-            }
 
             TABLE_LIST *embedding= lex->current_select->embedding;
             $$= embedding &&
@@ -16745,7 +16740,6 @@ view_select:
           {
             LEX *lex= Lex;
             lex->parsing_options.allows_variable= FALSE;
-            lex->parsing_options.allows_derived= FALSE;
             lex->create_view_select.str= (char *) YYLIP->get_cpp_ptr();
           }
           opt_with_clause query_expression_body_view view_check_option
@@ -16757,7 +16751,6 @@ view_select:
             lex->create_view_select.str= (char *) create_view_select;
             trim_whitespace(thd->charset(), &lex->create_view_select);
             lex->parsing_options.allows_variable= TRUE;
-            lex->parsing_options.allows_derived= TRUE;
             lex->current_select->set_with_clause($2);
           }
         ;
diff --git a/sql/table.cc b/sql/table.cc
index dc1730b5b6f..44d4b49d0dc 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4448,13 +4448,14 @@ bool TABLE_LIST::single_table_updatable()
 {
   if (!updatable)
     return false;
-  if (view_tables && view_tables->elements == 1)
+  if (view && view->select_lex.table_list.elements == 1)
   {
     /*
       We need to check deeply only single table views. Multi-table views
       will be turned to multi-table updates and then checked by leaf tables
     */
-    return view_tables->head()->single_table_updatable();
+    return (((TABLE_LIST *)view->select_lex.table_list.first)->
+            single_table_updatable());
   }
   return true;
 }
@@ -5499,6 +5500,8 @@ const char *Field_iterator_table_ref::get_table_name()
 {
   if (table_ref->view)
     return table_ref->view_name.str;
+  if (table_ref->is_derived())
+    return table_ref->table->s->table_name.str;
   else if (table_ref->is_natural_join)
     return natural_join_it.column_ref()->table_name();
 
@@ -7259,6 +7262,7 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
     /* A subquery might be forced to be materialized due to a side-effect. */
     if (!is_materialized_derived() && first_select->is_mergeable() &&
         optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) &&
+        !thd->lex->can_not_use_merged() &&
         !(thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
           thd->lex->sql_command == SQLCOM_DELETE_MULTI))
       set_merged_derived();