Fixed bug mdev-11278.

If a recursive CTE referred to a materialized view/derived table then
the query that used this CTE returned a bogus error message.

3 files changed, 53 insertions, 11 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index f22370870c2..a624b5453b8 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -1836,3 +1836,29 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	
 drop table t1,t2;
+#
+# MDEV-11278: non-mergeable view in the spec of recursive CTE
+#
+create table t1 (a int);
+insert into t1 values
+(0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+(1), (2), (3), (4), (5);
+create view v1 as
+select a from t2 where a < 3
+union
+select a from t2 where a > 4;
+with recursive
+t1 as
+(
+select a from v1 where a=1 
+union
+select v1.a from t1,v1 where t1.a+1=v1.a
+)
+select * from t1;
+a
+1
+2
+drop view v1;
+drop table t1,t2;
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 8a07b3ae75a..e6a259b8f58 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -1361,4 +1361,31 @@ select * from t1;
 
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-11278: non-mergeable view in the spec of recursive CTE
+--echo #
+
+create table t1 (a int);
+insert into t1 values
+  (0), (1), (2), (3), (4);
+create table t2 (a int);
+insert into t2 values
+  (1), (2), (3), (4), (5);
+
+create view v1 as
+  select a from t2 where a < 3
+  union
+  select a from t2 where a > 4; 
+
+with recursive
+t1 as
+(
+select a from v1 where a=1 
+union
+select v1.a from t1,v1 where t1.a+1=v1.a
+)
+select * from t1;
+
+drop view v1;
+drop table t1,t2;
 
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index 7e98a9bd1ff..a4ceae52e5e 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1121,17 +1121,6 @@ bool With_element::check_unrestricted_recursive(st_select_lex *sel,
     { 
       if(!tbl->is_with_table())
       {
-        if (tbl->is_materialized_derived())
-        {
-          table_map dep_map;
-          check_dependencies_in_unit(unit, NULL, false, &dep_map);
-          if (dep_map & get_elem_map())
-          {
-	    my_error(ER_REF_TO_RECURSIVE_WITH_TABLE_IN_DERIVED,
-	  	     MYF(0), query_name->str);
-	    return true;
-          }
-        }
         if (check_unrestricted_recursive(unit->first_select(), 
 					 unrestricted, 
 				         encountered))