Hi, Igor! On 20.04.2016 00:37, Igor Babaev wrote:

commit 308cee547f6573a9825b5f08f938d4452d0095d9 Author: Igor Babaev <igor@askmonty.org> Commit: Igor Babaev <igor@askmonty.org>

Fixed bug mdev-9931.

When the specification of a WITH table referred to a view that used a based table with the same name as the WITH table the server went into an infinite loop because it erroneously resolved the reference to the base table as the reference to the WITH table.

With tables used in a view cannot be searched for beyond the scope the view. ---

It looks OK to push. I'd name it 'is_view_border' (because it can be more units which belon to a view), but if you think 'is_view' is better, OK to leave it as is.

mysql-test/r/cte_nonrecursive.result | 13 +++++++++++++ mysql-test/t/cte_nonrecursive.test | 14 ++++++++++++++ sql/sql_cte.cc | 5 ++++- sql/sql_lex.cc | 1 + sql/sql_lex.h | 1 + sql/sql_view.cc | 2 ++ 6 files changed, 35 insertions(+), 1 deletions(-)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index df64115..a9c13f3 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -746,3 +746,16 @@ with t(f1,f1) as (select * from t1 where b >= 'c') select t1.b from t2,t1 where t1.a = t2.c; ERROR 42S21: Duplicate column name 'f1' drop table t1,t2; +# +# Bug mdev-9937: View used in the specification of with table +# refers to the base table with the same name +# +create table t1 (a int); +insert into t1 values (20), (30), (10); +create view v1 as select * from t1 where a > 10; +with t1 as (select * from v1) select * from t1; +a +20 +30 +drop view v1; +drop table t1; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 5a6e07e..e3164f5 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -434,3 +434,17 @@ with t(f1,f1) as (select * from t1 where b >= 'c') select t1.b from t2,t1 where t1.a = t2.c;

drop table t1,t2; + +--echo # +--echo # Bug mdev-9937: View used in the specification of with table +--echo # refers to the base table with the same name +--echo # + +create table t1 (a int); +insert into t1 values (20), (30), (10); +create view v1 as select * from t1 where a > 10; + +with t1 as (select * from v1) select * from t1; + +drop view v1; +drop table t1; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 1203a4c..77f0bcf 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -512,7 +512,10 @@ bool With_element::prepare_unreferenced(THD *thd) { With_clause *with_clause=sl->get_with_clause(); if (with_clause && (found= with_clause->find_table_def(table))) - return found; + return found; + /* Do not look for the table's definition beyond the scope of the view */ + if (sl->master_unit()->is_view) + break; } return found; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 6056b03..de345b4 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2078,6 +2078,7 @@ void st_select_lex_unit::init_query() found_rows_for_union= 0; insert_table_with_stored_vcol= 0; derived= 0; + is_view= false; with_clause= 0; with_element= 0; columns_are_renamed= false; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 0b4f0da..10247bd 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -645,6 +645,7 @@ class st_select_lex_unit: public st_select_lex_node { derived tables/views handling. */ TABLE_LIST *derived; + bool is_view; /* With clause attached to this unit (if any) */ With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 41fd5b7..b66f678 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1612,6 +1612,8 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, sl->context.error_processor_data= (void *)table; }

+ table->select_lex->master_unit()->is_view= true; + /* check MERGE algorithm ability - algorithm is not explicit TEMPORARY TABLE _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits