[Commits] 9ef5afa: # This is a combination of 2 commits.

Oleksandr Byelkin sanja at mariadb.com
Sat May 28 15:44:18 EEST 2016


revision-id: 9ef5afa56b679fc5999f6149295d90489dc879f3 (mariadb-10.2.0-50-g9ef5afa)
parent(s): 1f89ea8ea049909228c441d125a2b13348ec56af
committer: Oleksandr Byelkin
timestamp: 2016-05-28 14:44:18 +0200
message:

# This is a combination of 2 commits.
# The first commit's message is:

MDEV-3944: Allow derived tables in VIEWS

# The 2nd commit message will be skipped:

#	MDEV-9671 Wrong result upon select from a view with a FROM subquery
#
#	do not take marker OIN_TYPE_OUTER as a LEFT JOIN on print

---
 mysql-test/r/sp-error.result                    |   5 +-
 mysql-test/r/view.result                        | 250 +++++++++++++++++++++++-
 mysql-test/suite/funcs_1/r/innodb_views.result  |   4 +-
 mysql-test/suite/funcs_1/r/memory_views.result  |   4 +-
 mysql-test/suite/funcs_1/views/views_master.inc |   4 +-
 mysql-test/t/sp-error.test                      |   4 +-
 mysql-test/t/view.test                          | 172 +++++++++++++++-
 sql/sql_derived.cc                              |   6 +-
 sql/sql_lex.cc                                  |   3 +-
 sql/sql_lex.h                                   |   1 -
 sql/sql_select.cc                               |   3 +-
 sql/sql_yacc.yy                                 |   7 -
 sql/table.cc                                    |   8 +-
 13 files changed, 435 insertions(+), 36 deletions(-)

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 447afe3..4ea4e9c 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1235,8 +1235,9 @@ 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
+ERROR HY000: View's SELECT contains a 'PROCEDURE' clause
+CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1 into @w;
+ERROR HY000: View's SELECT contains a 'INTO' clause
 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 ca1a1df..2ca921c 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;
@@ -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 HY000: View's SELECT contains a 'INTO' clause
 # 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;
@@ -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 x 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 45de953..fa9d9df 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 ab4e2a9..989f533 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 f55788d..54e7f2a 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 b1d4f67..7fc8c43 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_VIEW_SELECT_CLAUSE
+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 9fdabca..ef3fde3 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_VIEW_SELECT_CLAUSE
 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 79e57cd..05e07a4 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 8d5f54f..adc443a 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 028ede4..58fa7ec 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1817,7 +1817,6 @@ class Query_tables_list
 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 6c4d2e1..77a7f8d 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_yacc.yy b/sql/sql_yacc.yy
index 37a543a..1aa6b07 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 dc1730b..44d4b49 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();


More information about the commits mailing list