commit 308cee547f6573a9825b5f08f938d4452d0095d9
Author: Igor Babaev <igor@xxxxxxxxxxxx>
Commit: Igor Babaev <igor@xxxxxxxxxxxx>
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.
---
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@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
