[Commits] 7709b2a: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist

IgorBabaev igor at mariadb.com
Wed May 5 21:09:06 EEST 2021


revision-id: 7709b2a93a1da3a886c9e16e81b9f5d6e92cf788 (mariadb-10.4.11-510-g7709b2a)
parent(s): 3454b5cf35a61e8f6cfab376638520dee4a50609
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-05-05 11:09:06 -0700
message:

MDEV-23886 Reusing CTE inside a function fails with table doesn't exist

Intermediate commit (not to be pushed).

---
 mysql-test/main/brackets.result         |   2 +-
 mysql-test/main/cte_nonrecursive.result | 156 ++++++++++++
 mysql-test/main/cte_nonrecursive.test   | 163 +++++++++++++
 sql/item_subselect.cc                   |   1 -
 sql/sp_head.cc                          |   3 +-
 sql/sql_base.cc                         |  33 +--
 sql/sql_class.cc                        |  57 +++++
 sql/sql_class.h                         |   8 +-
 sql/sql_cte.cc                          | 418 ++++++++++++++++++++++----------
 sql/sql_cte.h                           |  87 ++++++-
 sql/sql_derived.cc                      |   2 +
 sql/sql_lex.cc                          |   9 +-
 sql/sql_lex.h                           |  25 +-
 sql/sql_parse.cc                        |  15 +-
 sql/sql_prepare.cc                      |   3 -
 sql/sql_view.cc                         |   9 -
 sql/sql_yacc.yy                         |  27 ++-
 sql/sql_yacc_ora.yy                     |  26 +-
 sql/table.cc                            |   1 +
 sql/table.h                             |  37 +++
 20 files changed, 870 insertions(+), 212 deletions(-)

diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result
index 99e84f9..1da5168 100644
--- a/mysql-test/main/brackets.result
+++ b/mysql-test/main/brackets.result
@@ -4508,7 +4508,7 @@ s as (select * from t where a > 3)
 select a from t where a=1 union select a from s where a=7 order by a desc;
 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 with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
 select * from v1;
 a
 1
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index ee2320c..b8e885e 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1846,4 +1846,160 @@ set sql_mode="oracle";
 with data as (select 1 as id)
 select id into @myid from data;
 set sql_mode= @save_sql_mode;
+#
+# MDEV-23886: Stored Function returning the result of a query
+#             that uses CTE over a table twice
+#
+create table t1 (c1 int);
+insert into t1 values (1),(2),(6);
+create function f1() returns int return
+( with cte1 as (select c1 from t1)
+select sum(c1) from
+(select * from cte1 union all select * from cte1) dt
+);
+select f1();
+f1()
+18
+create function f2() returns int return
+( with cte1 as (select c1 from t1)
+select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1
+);
+select f2();
+f2()
+9
+create function f3() returns int return
+( with cte1 as (select c1 from t1)
+select
+case
+when exists(select 1 from cte1 where c1 between 1 and 2) then 1
+when exists(select 1 from cte1 where c1 between 5 and 6) then 2
+else 0
+end
+);
+select f3();
+f3()
+1
+drop function f1;
+drop function f2;
+drop function f3;
+create table t2 (a int, b int);
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5;
+select * from t2;
+a	b
+6	6
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+select * from t2;
+a	b
+6	6
+2	2
+drop procedure p1;
+# checking CTE resolution for queries with hanging CTEs
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from cte3;
+a	b
+1	2
+select * from t2;
+a	b
+6	6
+2	2
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+a	b
+6	6
+2	2
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where c1 >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+ERROR 42S22: Unknown column 'c1' in 'where clause'
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
+select * from t2;
+ERROR 42S22: Unknown column 'cte2.c1' in 'where clause'
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from cte2;
+a	b
+1	1
+2	2
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from t2;
+a	b
+6	6
+2	2
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
+select * from t2;
+ERROR 23000: Column 'c1' in where clause is ambiguous
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from cte3;
+a	b
+1	1
+2	1
+1	2
+2	2
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from t2;
+a	b
+6	6
+2	2
+with cte3 as
+( with cte2(a,b) as
+( with cte1 as (select * from t1 where c1 <= 2)
+select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
+select * from t2;
+ERROR 42S22: Unknown column 'r1.c1' in 'field list'
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+select * from t2;
+a	b
+6	6
+2	2
+2	2
+drop procedure p1;
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select a from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+call p1();
+ERROR 42S22: Unknown column 'a' in 'field list'
+drop procedure p1;
+drop table t1,t2;
 # End of 10.4 tests
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 2242dce..2dd4857 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1347,4 +1347,167 @@ with data as (select 1 as id)
 select id into @myid from data;
 set sql_mode= @save_sql_mode;
 
+--echo #
+--echo # MDEV-23886: Stored Function returning the result of a query
+--echo #             that uses CTE over a table twice
+--echo #
+
+create table t1 (c1 int);
+insert into t1 values (1),(2),(6);
+
+create function f1() returns int return
+( with cte1 as (select c1 from t1)
+  select sum(c1) from
+  (select * from cte1 union all select * from cte1) dt
+);
+select f1();
+
+create function f2() returns int return
+( with cte1 as (select c1 from t1)
+  select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1
+);
+select f2();
+
+create function f3() returns int return
+( with cte1 as (select c1 from t1)
+  select
+  case
+    when exists(select 1 from cte1 where c1 between 1 and 2) then 1
+    when exists(select 1 from cte1 where c1 between 5 and 6) then 2
+    else 0
+  end
+);
+select f3();
+
+drop function f1;
+drop function f2;
+drop function f3;
+
+create table t2 (a int, b int);
+
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5;
+
+select * from t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+call p1();
+select * from t2;
+
+drop procedure p1;
+
+--echo # checking CTE resolution for queries with hanging CTEs
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from cte3;
+
+select * from t2;
+
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where c1 >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with
+cte1(a) as (select * from t1 where c1 <= 2),
+cte2(b) as (select * from cte1 where a >= 2),
+cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
+select * from t2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from cte2;
+
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+select * from t2;
+
+--error ER_NON_UNIQ_ERROR
+with
+cte1 as (select * from t1 where c1 <= 2),
+cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
+select * from t2;
+
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from cte3;
+
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.a,r2.b from cte2 as r1, cte2 as r2)
+select * from t2;
+
+--error ER_BAD_FIELD_ERROR
+with cte3 as
+( with cte2(a,b) as
+  ( with cte1 as (select * from t1 where c1 <= 2)
+    select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
+  select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
+select * from t2;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select c1 from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+call p1();
+select * from t2;
+
+drop procedure p1;
+
+delimiter |;
+
+create procedure p1()
+begin
+insert into t2
+with cte1 as (select a from t1)
+select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
+end |
+
+delimiter ;|
+
+--error ER_BAD_FIELD_ERROR
+call p1();
+
+drop procedure p1;
+
+drop table t1,t2;
+
 --echo # End of 10.4 tests
+
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e205847..f6768d0 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1723,7 +1723,6 @@ double Item_in_subselect::val_real()
     As far as Item_in_subselect called only from Item_in_optimizer this
     method should not be used
   */
-  DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
   if (forced_const)
     return value;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index aa4f809..3ea4938 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
   Json_writer_object trace_command(thd);
   Json_writer_array trace_command_steps(thd, "steps");
   if (open_tables)
-    res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) ||
-         instr->exec_open_and_lock_tables(thd, m_lex->query_tables);
+    res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables);
 
   if (likely(!res))
   {
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8bb6011..c3aeb7d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3695,7 +3695,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags,
   if (tables->derived)
   {
     if (!tables->view)
+    {
+      if (!tables->is_derived())
+        tables->set_derived();
       goto end;
+    }
     /*
       We restore view's name and database wiped out by derived tables
       processing and fall back to standard open process in order to
@@ -3705,35 +3709,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags,
     tables->db= tables->view_db;
     tables->table_name= tables->view_name;
   }
-  else if (tables->select_lex) 
-  {
-    /*
-      Check whether 'tables' refers to a table defined in a with clause.
-      If so set the reference to the definition in tables->with.
-    */ 
-    if (!tables->with)
-      tables->with= tables->select_lex->find_table_def_in_with_clauses(tables);
-    /*
-      If 'tables' is defined in a with clause set the pointer to the
-      specification from its definition in tables->derived.
-    */
-    if (tables->with)
-    {
-      if (tables->is_recursive_with_table() &&
-          !tables->is_with_table_recursive_reference())
-      {
-        tables->with->rec_outer_references++;
-        With_element *with_elem= tables->with;
-        while ((with_elem= with_elem->get_next_mutually_recursive()) !=
-               tables->with)
-	  with_elem->rec_outer_references++;
-      }
-      if (tables->set_as_with_table(thd, tables->with))
-        DBUG_RETURN(1);
-      else
-        goto end;
-    }
-  }
 
   if (!tables->derived && is_infoschema_db(&tables->db))
   {
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index ceb8dc1..b32b792 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2804,6 +2804,63 @@ void THD::close_active_vio()
 #endif
 
 
+/*
+  @brief MySQL parser used for recursive invocations
+
+  @param old_lex  The LEX structure in the state when this parser
+                  is called recursively
+  @param lex      The LEX structure used to parse a new SQL fragment
+  @param str      The SQL fragment to parse
+  @param str_len  The length of the SQL fragment to parse
+  @param stmt_prepare_mode true <=> when parsing a prepare statement
+
+  @details
+    This function is to be used when parsing of an SQL fragment is
+    needed within one of the grammar rules.
+
+  @notes
+    Currently the function is used only when the specification of a CTE
+    is parsed for the not first and not recursive references of the CTE.
+
+  @retval false   On a successful parsing of the fragment
+  @retval true    Otherwise
+*/
+
+bool THD::sql_parser(LEX *old_lex, LEX *lex,
+                     char *str, uint str_len, bool stmt_prepare_mode)
+{
+  extern int MYSQLparse(THD * thd);
+  extern int ORAparse(THD * thd);
+
+  bool parse_status= false;
+  Parser_state parser_state;
+  Parser_state *old_parser_state= m_parser_state;
+
+  if (parser_state.init(this, str, str_len))
+    return true;
+
+  m_parser_state= &parser_state;
+  parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode;
+  parser_state.m_lip.multi_statements= false;
+  parser_state.m_lip.m_digest= NULL;
+
+  lex->param_list= old_lex->param_list;
+  lex->sphead= old_lex->sphead;
+  lex->spname= old_lex->spname;
+  lex->spcont= old_lex->spcont;
+  lex->sp_chistics= old_lex->sp_chistics;
+  lex->trg_chistics= old_lex->trg_chistics;
+
+  parse_status= (variables.sql_mode & MODE_ORACLE) ?
+                 ORAparse(this) : MYSQLparse(this) != 0;
+
+  m_parser_state= old_parser_state;
+
+  return parse_status;
+}
+
+
+
 struct Item_change_record: public ilink
 {
   Item **place;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 936437f..ecab13e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -4241,14 +4241,11 @@ class THD: public THD_count, /* this must be first */
         to resolve all CTE names as we don't need this message to be thrown
         for any CTE references.
       */
-      if (!lex->with_clauses_list)
+      if (!lex->with_cte_resolution)
       {
         my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0));
         return TRUE;
       }
-      /* This will allow to throw an error later for non-CTE references */
-      to->str= NULL;
-      to->length= 0;
       return FALSE;
     }
 
@@ -5043,6 +5040,9 @@ class THD: public THD_count, /* this must be first */
   Item *sp_prepare_func_item(Item **it_addr, uint cols= 1);
   bool sp_eval_expr(Field *result_field, Item **expr_item_ptr);
 
+  bool sql_parser(LEX *old_lex, LEX *lex,
+                  char *str, uint str_len, bool stmt_prepare_mode);
+
 };
 
 /** A short cut for thd->get_stmt_da()->set_ok_status(). */
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index a9f5443..cfabe01 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1,3 +1,4 @@
+
 /*
    Copyright (c) 2016, 2017 MariaDB
 
@@ -83,7 +84,7 @@ void  st_select_lex_unit::set_with_clause(With_clause *with_cl)
     true    on failure
 */
 
-bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
+bool LEX::check_dependencies_in_with_clauses()
 {
   for (With_clause *with_clause= with_clauses_list;
        with_clause;
@@ -101,6 +102,201 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
 
 /**
   @brief
+    Resolve references to CTE in specification of hanging CTE
+
+  @details
+    A CTE to which there are no references in the query is called hanging CTE.
+    Although such CTE is not used for execution its specification must be
+    subject to context analysis. All errors concerning references to
+    non-existing tables or fields occurred in the specification must be
+    reported as well as all other errors caught at the prepare stage.
+    The specification of a hanging CTE might contain references to other
+    CTE outside of the specification and within it if the specification
+    contains a with clause. This function resolves all such references for
+    all hanging CTEs encountered in the processed query.
+
+  @retval
+    false   on success
+    true    on failure
+*/
+
+bool
+LEX::resolve_references_to_cte_in_hanging_cte()
+{
+  for (With_clause *with_clause= with_clauses_list;
+       with_clause; with_clause= with_clause->next_with_clause)
+  {
+    for (With_element *with_elem= with_clause->with_list.first;
+         with_elem; with_elem= with_elem->next)
+    {
+      if (!with_elem->is_referenced())
+      {
+        TABLE_LIST *first_tbl=
+                     with_elem->spec->first_select()->table_list.first;
+        TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos;
+        if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos))
+          return true;
+      }
+    }
+  }
+  return false;
+}
+
+
+/**
+  @brief
+    Resolve table references to CTE from a sub-chain of table references
+
+  @param tables      Points to the beginning of the sub-chain
+  @param tables_last Points to the address with the sub-chain barrier
+
+  @details
+    The method resolves tables references to CTE from the chain of
+    table references specified by the parameters 'tables' and 'tables_last'.
+    It resolves the references against the CTE definition occurred in a query
+    or the specification of a CTE whose parsing tree is represented by
+    this LEX structure. The method is always called right after the process
+    of parsing the query or of the specification of a CTE has been finished,
+    thus the chain of table references used in the parsed fragment has been
+    already built. It is assumed that parameters of the method specify a
+    a sub-chain of this chain.
+    If a table reference can be potentially a table reference to a CTE and it
+    has not been resolved yet then the method tries to find the definition
+    of the CTE against which the reference can be resolved. If it succeeds
+    it sets the field TABLE_LIST::with to point to the found definition.
+    It also sets the field TABLE_LIST::derived to point to the specification
+    of the found CTE and sets TABLE::db.str to empty_c_string. This will
+    allow to handle this table reference like a reference to a derived handle.
+    If another table reference has been already resolved against this CTE
+    and this CTE is not recursive then a clone of the CTE specification is
+    constructed using the function With_element::clone_parsed_spec() and
+    TABLE_LIST::derived is set to point to this clone rather than to the
+    original specification.
+    If the method does not find a matched CTE definition in the parsed fragment
+    then in the case when the flag this->only_cte_resolution is set to true
+    it just moves to the resolution of the next table reference from the
+    specified sub-chain while in the case when this->only_cte_resolution is set
+    to false the method additionally sets an mdl request for this table
+    reference.
+
+  @notes
+    The flag this->only_cte_resolution is set to true in the cases when
+    the failure to resolve a table reference as a CTE reference within
+    the fragment associated with this LEX structure does not imply that
+    this table reference cannot be resolved as such at all.
+
+  @retval false  On success: no errors reported, no memory allocations failed
+  @retval true   Otherwise
+*/
+
+bool LEX::resolve_references_to_cte(TABLE_LIST *tables,
+                                    TABLE_LIST **tables_last)
+{
+  With_element *with_elem= 0;
+
+  for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global)
+  {
+    if (tbl->derived)
+      continue;
+    if (!tbl->db.str && !tbl->with)
+      tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl);
+    if (!tbl->with)    // no CTE matches table reference tbl
+    {
+      if (only_cte_resolution)
+        continue;
+      if (!tbl->db.str)   // no database specified in table reference tbl
+      {
+        if (!thd->db.str) // no default database is set
+        {
+          my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0));
+          return true;
+        }
+        if (copy_db_to(&tbl->db))
+          return true;
+        if (!(tbl->table_options & TL_OPTION_ALIAS))
+          tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str,
+                                tbl->table_name.str,
+                                tbl->mdl_type, MDL_TRANSACTION);
+        tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ?
+                                   MDL_SHARED_WRITE : MDL_SHARED_READ);
+      }
+      continue;
+    }
+    with_elem= tbl->with;
+    if (tbl->is_recursive_with_table() &&
+        !tbl->is_with_table_recursive_reference())
+    {
+      tbl->with->rec_outer_references++;
+      while ((with_elem= with_elem->get_next_mutually_recursive()) !=
+             tbl->with)
+	with_elem->rec_outer_references++;
+    }
+    if (!with_elem->is_used_in_query || with_elem->is_recursive)
+    {
+      tbl->derived= with_elem->spec;
+      if (tbl->derived != tbl->select_lex->master_unit() &&
+          !with_elem->is_recursive &&
+          !tbl->is_with_table_recursive_reference())
+      {
+        tbl->derived->move_as_slave(tbl->select_lex);
+      }
+      with_elem->is_used_in_query= true;
+    }
+    else
+    {
+      if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl)))
+        return true;
+    }
+    tbl->db.str= empty_c_string;
+    tbl->db.length= 0;
+    tbl->schema_table= 0;
+    if (tbl->derived)
+    {
+      tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE);
+      tbl->select_lex->add_statistics(tbl->derived);
+    }
+    if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference())
+      continue;
+    with_elem->inc_references();
+  }
+  return false;
+}
+
+
+/**
+  @brief
+    Find out dependencies between CTEs, resolve references to them
+
+  @details
+    The function can be called in two modes. With this->with_cte_resolution
+    set to false the function only finds out all dependencies between CTEs
+    used in a query expression with a WITH clause whose parsing has been
+    just finished. Based on these dependencies recursive CTEs are detected.
+    If this->with_cte_resolution is set to true the function additionally
+    resolves all references to CTE occurred in this query expression.
+
+  @retval
+    true   on failure
+    false  on success
+*/
+
+bool
+LEX::check_cte_dependencies_and_resolve_references()
+{
+  if (check_dependencies_in_with_clauses())
+    return true;
+  if (!with_cte_resolution)
+    return false;
+  if (resolve_references_to_cte(query_tables, query_tables_last))
+    return true;
+  if (resolve_references_to_cte_in_hanging_cte())
+    return true;
+  return false;
+}
+
+
+/**
+  @brief
     Check dependencies between tables defined in this with clause
 
  @details
@@ -137,10 +333,11 @@ bool With_clause::check_dependencies()
          elem != with_elem;
          elem= elem->next)
     {
-      if (lex_string_cmp(system_charset_info, with_elem->query_name,
-                         elem->query_name) == 0)
+      if (lex_string_cmp(system_charset_info, with_elem->get_name(),
+                         elem->get_name()) == 0)
       {
-	my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str);
+        my_error(ER_DUP_QUERY_NAME, MYF(0),
+                 with_elem->get_name_str());
 	return true;
       }
     }
@@ -247,13 +444,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table,
        with_elem != barrier;
        with_elem= with_elem->next)
   {
-    if (my_strcasecmp(system_charset_info, with_elem->query_name->str,
-		      table->table_name.str) == 0 &&
+    if (my_strcasecmp(system_charset_info, with_elem->get_name_str(),
+                      table->table_name.str) == 0 &&
         !table->is_fqtn)
     {
       table->set_derived();
-      table->db.str= empty_c_string;
-      table->db.length= 0;
+      with_elem->referenced= true;
       return with_elem;
     }
   }
@@ -610,7 +806,7 @@ bool With_clause::check_anchors()
       if (elem == with_elem)
       {
         my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0),
-        with_elem->query_name->str);
+        with_elem->get_name_str());
         return true;
       }
     }
@@ -643,7 +839,7 @@ bool With_clause::check_anchors()
         if (elem->work_dep_map & elem->get_elem_map())
 	{
           my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0),
-          with_elem->query_name->str);
+          with_elem->get_name_str());
           return true;
 	}
       }
@@ -797,9 +993,10 @@ bool With_element::set_unparsed_spec(THD *thd,
   @brief
    Create a clone of the specification for the given with table
     
-  @param thd        The context of the statement containing this with element
+  @param old_lex    The LEX structure created for the query or CTE specification
+                    where this With_element is defined
   @param with_table The reference to the table defined in this element for which
-                     the clone is created.
+                    the clone is created.
 
   @details
     The method creates a clone of the specification used in this element.
@@ -807,12 +1004,13 @@ bool With_element::set_unparsed_spec(THD *thd,
     this element.
     The clone is created when the string with the specification saved in
     unparsed_spec is fed into the parser as an input string. The parsing
-    this string a unit object representing the specification is build.
+    this string a unit object representing the specification is built.
     A chain of all table references occurred in the specification is also
     formed.
     The method includes the new unit and its sub-unit into hierarchy of
     the units of the main query. I also insert the constructed chain of the 
     table references into the chain of all table references of the main query.
+    The method resolves all references to CTE in the clone.
 
   @note
     Clones is created only for not first references to tables defined in
@@ -828,113 +1026,127 @@ bool With_element::set_unparsed_spec(THD *thd,
      NULL - otherwise
 */
 
-st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
+st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex,
                                                     TABLE_LIST *with_table)
 {
+  THD *thd= old_lex->thd;
   LEX *lex;
-  st_select_lex_unit *res= NULL; 
-  Query_arena backup;
-  Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+  st_select_lex_unit *res= NULL;
 
   if (!(lex= (LEX*) new(thd->mem_root) st_lex_local))
-  {
-    if (arena)
-      thd->restore_active_arena(arena, &backup);
     return res;
-  }
-  LEX *old_lex= thd->lex;
   thd->lex= lex;
 
   bool parse_status= false;
-  Parser_state parser_state;
-  TABLE_LIST *spec_tables;
-  TABLE_LIST *spec_tables_tail;
   st_select_lex *with_select;
 
   char save_end= unparsed_spec.str[unparsed_spec.length];
   ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0';
-  if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length))
-    goto err;
-  parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode;
-  parser_state.m_lip.multi_statements= false;
-  parser_state.m_lip.m_digest= NULL;
 
   lex_start(thd);
   lex->clone_spec_offset= unparsed_spec_offset;
-  lex->param_list= old_lex->param_list;
-  lex->sphead= old_lex->sphead;
-  lex->spname= old_lex->spname;
-  lex->spcont= old_lex->spcont;
-  lex->sp_chistics= old_lex->sp_chistics;
-
-  lex->stmt_lex= old_lex;
-  parse_status= parse_sql(thd, &parser_state, 0);
+  lex->with_cte_resolution= true;
+
+  /*
+    The specification of a CTE is to be parsed as a regular query.
+    At the very end of the parsing query the function
+    check_cte_dependencies_and_resolve_references() will be called.
+    It will check the dependencies between CTEs that are defined
+    within the query and will resolve CTE references in this query.
+    If a table reference is not resolved as a CTE reference within
+    this query it still can be resolved as a reference to a CTE defined
+    in the same clause as the CTE whose specification is to be parsed
+    or defined in an embedding CTE definition.
+
+    Example:
+      with
+      cte1 as ( ... ),
+      cte2 as ([WITH ...] select ... from cte1 ...)
+      select ... from cte2 as r, ..., cte2 as s ...
+
+    Here the specification of cte2 has be cloned for table reference
+    with alias s1. The specification contains a reference to cte1
+    that is defined outside this specification. If the reference to
+    cte1 cannot be resolved within the specification of cte2 it's
+    not necessarily has to be a reference to a non-CTE table. That's
+    why the flag lex->only_cte_resolution has to be set to true
+    before parsing of the specification of cte2 invoked by this
+    function starts. Otherwise an mdl_lock would be requested for s
+    and this would not be correct
+  */
+
+  lex->only_cte_resolution= true;
+
+  lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex;
+
+  parse_status= thd->sql_parser(old_lex, lex,
+                                (char*) unparsed_spec.str,
+                                (unsigned int)unparsed_spec.length,
+                                stmt_prepare_mode);
+
   ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end;
   with_select= lex->first_select_lex();
 
   if (parse_status)
     goto err;
 
-  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
-    goto err;
-
-  spec_tables= lex->query_tables;
-  spec_tables_tail= 0;
-  for (TABLE_LIST *tbl= spec_tables;
-       tbl;
-       tbl= tbl->next_global)
-  {
-    if (!tbl->derived && !tbl->schema_table &&
-        thd->open_temporary_table(tbl))
-      goto err;
-    spec_tables_tail= tbl;
-  }
-  if (spec_tables)
+  /*
+    The global chain of TABLE_LIST objects created for the specification that
+    just has been parsed is added to such chain that contains the reference
+    to the CTE whose specification is parsed right after the TABLE_LIST object
+    created for the reference.
+  */
+  if (lex->query_tables)
   {
-    if (with_table->next_global)
+    head->tables_pos.set_start_pos(&with_table->next_global);
+    head->tables_pos.set_end_pos(lex->query_tables_last);
+    TABLE_LIST *next_tbl= with_table->next_global;
+    if (next_tbl)
     {
-      spec_tables_tail->next_global= with_table->next_global;
-      with_table->next_global->prev_global= &spec_tables_tail->next_global;
+      *(lex->query_tables->prev_global= next_tbl->prev_global)=
+        lex->query_tables;
+      *(next_tbl->prev_global= lex->query_tables_last)= next_tbl;
     }
     else
     {
-      old_lex->query_tables_last= &spec_tables_tail->next_global;
+      *(lex->query_tables->prev_global= old_lex->query_tables_last)=
+        lex->query_tables;
+      old_lex->query_tables_last= lex->query_tables_last;
     }
-    spec_tables->prev_global= &with_table->next_global;
-    with_table->next_global= spec_tables;
   }
   res= &lex->unit;
   
+  /*
+    The unit of the specification that just has been parsed is included
+    as a slave of the select that contained in its from list the table
+    reference for which the unit has been created.
+  */
   lex->unit.include_down(with_table->select_lex);
-  lex->unit.set_slave(with_select); 
+  lex->unit.set_slave(with_select);
+  lex->unit.cloned_from= spec;
   old_lex->all_selects_list=
     (st_select_lex*) (lex->all_selects_list->
 		      insert_chain_before(
 			(st_select_lex_node **) &(old_lex->all_selects_list),
                         with_select));
-  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
-    res= NULL;
+
   /*
-    Resolve references to CTE from the spec_tables list that has not
-    been resolved yet.
+    Now all references to the CTE defined outside of the cloned specification
+    has to be resolved. Additionally if old_lex->only_cte_resolution == false
+    for the table references that has not been resolved requests for mdl_locks
+    has to be set.
   */
-  for (TABLE_LIST *tbl= spec_tables;
-       tbl;
-       tbl= tbl->next_global)
+  lex->only_cte_resolution= old_lex->only_cte_resolution;
+  if (lex->resolve_references_to_cte(lex->query_tables,
+                                     lex->query_tables_last))
   {
-    if (!tbl->with)
-      tbl->with= with_select->find_table_def_in_with_clauses(tbl);
-    if (tbl == spec_tables_tail)
-      break;
-  }
-  if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE))
+    res= NULL;
     goto err;
+  }
 
   lex->sphead= NULL;    // in order not to delete lex->sphead
   lex_end(lex);
 err:
-  if (arena)
-    thd->restore_active_arena(arena, &backup);
   thd->lex= old_lex;
   return res;
 }
@@ -1104,58 +1316,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
 }
 
 
-/**
-   @brief
-     Set the specifying unit in this reference to a with table  
-     
-  @details  
-    The method assumes that the given element with_elem defines the table T
-    this table reference refers to.
-    If this is the first reference to T the method just sets its specification
-    in the field 'derived' as the unit that yields T. Otherwise the method  
-    first creates a clone specification and sets rather this clone in this field.
- 
-  @retval
-    false   on success
-    true    on failure
-*/    
-
-bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
-{
-  if (table)
-  {
-    /*
-      This table was prematurely identified as a temporary table.
-      We correct it here, but it's not a nice solution in the case
-      when the temporary table with this name is not used anywhere
-      else in the query.
-    */
-    thd->mark_tmp_table_as_free_for_reuse(table);
-    table= 0;
-  }
-  with= with_elem;
-  schema_table= NULL;
-  if (!with_elem->is_referenced() || with_elem->is_recursive)
-  {
-    derived= with_elem->spec;
-    if (derived != select_lex->master_unit() &&
-        !with_elem->is_recursive &&
-        !is_with_table_recursive_reference())
-    {
-       derived->move_as_slave(select_lex);
-    }
-  }
-  else 
-  {
-    if(!(derived= with_elem->clone_parsed_spec(thd, this)))
-      return true;
-  }
-  derived->first_select()->set_linkage(DERIVED_TABLE_TYPE);
-  select_lex->add_statistics(derived);
-  with_elem->inc_references();
-  return false;
-}
-
 
 bool TABLE_LIST::is_recursive_with_table()
 {
@@ -1256,7 +1416,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant)
   if (only_standard_compliant && with_elem->is_unrestricted())
   {
     my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE,
-	     MYF(0), with_elem->query_name->str);
+             MYF(0), with_elem->get_name_str());
     return true;
   }
 
@@ -1456,7 +1616,7 @@ void With_clause::print(String *str, enum_query_type query_type)
 
 void With_element::print(String *str, enum_query_type query_type)
 {
-  str->append(query_name);
+  str->append(get_name());
   if (column_list.elements)
   {
     List_iterator_fast<LEX_CSTRING> li(column_list);
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 80d5664..f30c287 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -23,6 +23,38 @@
 class select_unit;
 struct st_unit_ctxt_elem;
 
+/**
+  @class With_element_head
+  @brief Head of the definition of a CTE table
+
+  It contains the name of the CTE and it contains the position of the subchain
+  of table references used in the definition in the global chain of table
+  references used in the query where this definition is encountered.
+*/
+
+class With_element_head : public Sql_alloc
+{
+  /* The name of the defined CTE */
+  LEX_CSTRING *query_name;
+
+public:
+  /*
+    The structure describing the subchain of the table references used in
+    the specification of the defined CTE in the global chain of table
+    references used in the query. The structure is fully defined only
+    after the CTE definition has been parsed.
+  */
+  TABLE_CHAIN tables_pos;
+
+  With_element_head(LEX_CSTRING *name)
+    : query_name(name)
+  {
+    tables_pos.set_start_pos(0);
+    tables_pos.set_end_pos(0);
+  }
+  friend class With_element;
+};
+
 
 /**
   @class With_element
@@ -85,9 +117,22 @@ class With_element : public Sql_alloc
     subqueries and specifications of other with elements).
   */ 
   uint references;
+
+  /*
+    true <=> this With_element is referred in the query in which the
+    element is defined
+  */
+  bool referenced;
+
+  /*
+    true <=> this With_element is needed for the execution of the query
+    in which the element is defined
+  */
+  bool is_used_in_query;
+
   /* 
     Unparsed specification of the query that specifies this element.
-    It used to build clones of the specification if they are needed.
+    It's used to build clones of the specification if they are needed.
   */
   LEX_CSTRING unparsed_spec;
   /* Offset of the specification in the input string */
@@ -101,10 +146,12 @@ class With_element : public Sql_alloc
  
 public:
   /*
-    The name of the table introduced by this with elememt. The name
-     can be used in FROM lists of the queries in the scope of the element.
+    Contains the name of the defined With element and the position of
+    the subchain of the tables references used by its definition in the
+    global chain of TABLE_LIST objects created for the whole query.
   */
-  LEX_CSTRING *query_name;
+  With_element_head *head;
+
   /*
     Optional list of column names to name the columns of the table introduced
     by this with element. It is used in the case when the names are not
@@ -162,18 +209,27 @@ class With_element : public Sql_alloc
   /* List of derived tables containing recursive references to this CTE */
   SQL_I_List<TABLE_LIST> derived_with_rec_ref;
 
-  With_element(LEX_CSTRING *name,
+  With_element(With_element_head *h,
                List <LEX_CSTRING> list,
                st_select_lex_unit *unit)
     : next(NULL), base_dep_map(0), derived_dep_map(0),
       sq_dep_map(0), work_dep_map(0), mutually_recursive(0),
       top_level_dep_map(0), sq_rec_ref(NULL),
       next_mutually_recursive(NULL), references(0), 
-      query_name(name), column_list(list), spec(unit),
+      referenced(false), is_used_in_query(false),
+      head(h), column_list(list), spec(unit),
       is_recursive(false), rec_outer_references(0), with_anchor(false),
       level(0), rec_result(NULL)
   { unit->with_element= this; }
 
+  LEX_CSTRING *get_name() { return head->query_name; }
+  const char *get_name_str() { return get_name()->str; }
+
+  void set_tables_start_pos(TABLE_LIST **pos)
+  { head->tables_pos.set_start_pos(pos); }
+  void set_tables_end_pos(TABLE_LIST **pos)
+  { head->tables_pos.set_end_pos(pos); }
+
   bool check_dependencies_in_spec();
   
   void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt,
@@ -200,9 +256,9 @@ class With_element : public Sql_alloc
   bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end,
                          my_ptrdiff_t spec_offset);
 
-  st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table);
+  st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table);
 
-  bool is_referenced() { return references != 0; }
+  bool is_referenced() { return referenced; }
 
   void inc_references() { references++; }
 
@@ -260,6 +316,12 @@ class With_element : public Sql_alloc
   void prepare_for_next_iteration();
 
   friend class With_clause;
+
+  friend
+  bool LEX::resolve_references_to_cte(TABLE_LIST *tables,
+                                      TABLE_LIST **tables_last);
+  friend
+  bool LEX::resolve_references_to_cte_in_hanging_cte();
 };
 
 const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8;
@@ -291,6 +353,7 @@ class With_clause : public Sql_alloc
     in the current statement
   */
   With_clause *next_with_clause;
+
   /* Set to true if dependencies between with elements have been checked */
   bool dependencies_are_checked;
   /* 
@@ -338,7 +401,7 @@ class With_clause : public Sql_alloc
   void attach_to(st_select_lex *select_lex);
 
   With_clause *pop() { return embedding_with_clause; }
-      
+
   bool check_dependencies();
 
   bool check_anchors();
@@ -358,8 +421,10 @@ class With_clause : public Sql_alloc
   friend class With_element;
 
   friend
-  bool
-  check_dependencies_in_with_clauses(With_clause *with_clauses_list);
+  bool LEX::check_dependencies_in_with_clauses();
+
+  friend
+  bool LEX::resolve_references_to_cte_in_hanging_cte();
 };
 
 inline
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index dc079ba..89b1900 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -917,6 +917,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
     if (derived->is_view())
       table->grant= derived->grant;
+#if 1
     else
     {
       DBUG_ASSERT(derived->is_derived());
@@ -925,6 +926,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
       derived->grant.privilege= SELECT_ACL;
     }
 #endif
+#endif
     /* Add new temporary table to list of open derived tables */
     if (!derived->is_with_table_recursive_reference())
     {
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e196765..c674495 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg)
   explain_json= false;
   context_analysis_only= 0;
   derived_tables= 0;
+  with_cte_resolution= false;
+  only_cte_resolution= false;
   safe_to_cache_query= 1;
   parsing_options.reset();
   empty_field_list_on_rset= 0;
@@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query()
   is_view= false;
   with_clause= 0;
   with_element= 0;
+  cloned_from= 0;
   columns_are_renamed= false;
   intersect_mark= NULL;
   with_wrapped_tvc= false;
@@ -8266,6 +8269,8 @@ bool LEX::check_main_unit_semantics()
   if (unit.set_nest_level(0) ||
       unit.check_parameters(first_select_lex()))
     return TRUE;
+  if (check_cte_dependencies_and_resolve_references())
+    return TRUE;
   return FALSE;
 }
 
@@ -8948,8 +8953,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit)
 bool LEX::main_select_push(bool service)
 {
   DBUG_ENTER("LEX::main_select_push");
-  current_select_number= 1;
-  builtin_select.select_number= 1;
+  current_select_number= ++thd->lex->stmt_lex->current_select_number;
+  builtin_select.select_number= current_select_number;
   builtin_select.is_service_select= service;
   if (push_select(&builtin_select))
     DBUG_RETURN(TRUE);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 66c44f2..f2fd67c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node {
   With_clause *with_clause;
   /* With element where this unit is used as the specification (if any) */
   With_element *with_element;
+  /* The unit used as a CTE specification from which this unit is cloned */
+  st_select_lex_unit *cloned_from;
   /* thread handler */
   THD *thd;
   /*
@@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node
   }
   With_element *get_with_element()
   {
-    return master_unit()->with_element;
+    return master_unit()->cloned_from ?
+           master_unit()->cloned_from->with_element :
+           master_unit()->with_element;
   }
   With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
   bool check_unrestricted_recursive(bool only_standard_compliant);
@@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list
   */
   uint8 derived_tables;
   uint8 context_analysis_only;
+  /*
+    true <=> The parsed fragment requires resolution of references to CTE
+    at the end of parsing. This name resolution process involves searching
+    for possible dependencies between CTE defined in the parsed fragment and
+    detecting possible recursive references.
+    The flag is set to true if the fragment contains CTE definitions.
+  */
+  bool with_cte_resolution;
+  /*
+    true <=> only resolution of references to CTE are required in the parsed
+    fragment, no checking of dependecies between CTE is required.
+    This flag is used only when parsing clones of CTE specifications.
+  */
+  bool only_cte_resolution;
   bool local_file;
   bool check_exists;
   bool autocommit;
@@ -4547,6 +4565,11 @@ struct LEX: public Query_tables_list
             select_stack[0]->is_service_select);
   }
 
+  bool check_dependencies_in_with_clauses();
+  bool resolve_references_to_cte_in_hanging_cte();
+  bool check_cte_dependencies_and_resolve_references();
+  bool resolve_references_to_cte(TABLE_LIST *tables,
+                                 TABLE_LIST **tables_last);
 
 };
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 209caa9..5ce070a 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3408,9 +3408,6 @@ mysql_execute_command(THD *thd)
       thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);
   }
 
-  if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
-    DBUG_RETURN(1);
-
 #ifdef HAVE_REPLICATION
   if (unlikely(thd->slave_thread))
   {
@@ -8150,7 +8147,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
     ptr->is_fqtn= TRUE;
     ptr->db= table->db;
   }
-  else if (lex->copy_db_to(&ptr->db))
+  else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db))
     DBUG_RETURN(0);
   else
     ptr->is_fqtn= FALSE;
@@ -8167,7 +8164,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   }
       
   ptr->table_name= table->table;
-  ptr->lock_type=   lock_type;
+  ptr->lock_type= lock_type;
+  ptr->mdl_type= mdl_type;
+  ptr->table_options= table_options;
   ptr->updating=    MY_TEST(table_options & TL_OPTION_UPDATING);
   /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */
   ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX);
@@ -8849,8 +8848,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update
   {
     tables->lock_type= lock_type;
     tables->updating=  for_update;
-    tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
-                                 MDL_SHARED_WRITE : MDL_SHARED_READ);
+
+    if (tables->db.str && tables->db.str[0])
+      tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
+                                   MDL_SHARED_WRITE : MDL_SHARED_READ);
   }
   DBUG_VOID_RETURN;
 }
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index ee50402..64e138f 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2376,9 +2376,6 @@ static bool check_prepared_statement(Prepared_statement *stmt)
   if (tables)
     thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);
 
-  if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
-    goto error;
-
   if (sql_command_flags[sql_command] & CF_HA_CLOSE)
     mysql_ha_rm_tables(thd, tables);
 
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 126db90..1e3c4ce 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -431,12 +431,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
   lex->link_first_table_back(view, link_to_local);
   view->open_type= OT_BASE_ONLY;
 
-  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
-  {
-    res= TRUE;
-    goto err;
-  }
-
   WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL);
 
   /*
@@ -1413,9 +1407,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table,
     TABLE_LIST *tbl;
     Security_context *security_ctx= 0;
 
-    if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list))
-      goto err;
-
     /*
       Check rights to run commands which show underlying tables.
       In the optimizer trace we would not like to show trace for
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 2548dc2..f3e1cf2 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -770,6 +770,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr)
   class sp_head *sphead;
   class sp_name *spname;
   class sp_variable *spvar;
+  class With_element_head *with_element_head;
   class With_clause *with_clause;
   class Virtual_column_info *virtual_column;
 
@@ -2188,7 +2189,7 @@ END_OF_INPUT
 
 %type <with_clause> with_clause
 
-%type <lex_str_ptr> query_name
+%type <with_element_head> with_element_head
 
 %type <lex_str_list> opt_with_column_list
 
@@ -3336,7 +3337,11 @@ call:
             if (unlikely(Lex->call_statement_start(thd, $2)))
               MYSQL_YYABORT;
           }
-          opt_sp_cparam_list {}
+          opt_sp_cparam_list
+          {
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
+          }
         ;
 
 /* CALL parameters */
@@ -4185,6 +4190,8 @@ sp_proc_stmt_return:
             LEX *lex= Lex;
             sp_head *sp= lex->sphead;
             Lex->pop_select(); //main select
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
             if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont,
                                                           $3, lex)) ||
                 unlikely(sp->restore_lex(thd)))
@@ -13331,6 +13338,8 @@ do:
           {
             Lex->insert_list= $3;
             Lex->pop_select(); //main select
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
           }
         ;
 
@@ -15497,6 +15506,7 @@ with_clause:
              if (unlikely(with_clause == NULL))
                MYSQL_YYABORT;
              lex->derived_tables|= DERIVED_WITH;
+             lex->with_cte_resolution= true;
              lex->curr_with_clause= with_clause;
              with_clause->add_to_list(Lex->with_clauses_list_last_next);
              if (lex->current_select &&
@@ -15524,7 +15534,7 @@ with_list:
 
 
 with_list_element:
-	  query_name
+          with_element_head
 	  opt_with_column_list 
           {
             $2= new List<LEX_CSTRING> (Lex->with_column_list);
@@ -15544,6 +15554,7 @@ with_list_element:
             if (elem->set_unparsed_spec(thd, spec_start, $7.pos(),
                                         spec_start - query_start))
               MYSQL_YYABORT;
+            elem->set_tables_end_pos(lex->query_tables_last);
 	  }
 	;
 
@@ -15570,16 +15581,18 @@ with_column_list:
         ;
 
 
-query_name: 
+with_element_head:
           ident
           {
-            $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
-            if (unlikely($$ == NULL))
+            LEX_CSTRING *name=
+              (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
+            $$= new (thd->mem_root) With_element_head(name);
+            if (unlikely(name == NULL || $$ == NULL))
               MYSQL_YYABORT;
+            $$->tables_pos.set_start_pos(Lex->query_tables_last);
           }
         ;
 
-
 	
 /**********************************************************************
 ** Creating different items.
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 30727a8..f2e112c 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -248,6 +248,7 @@ void ORAerror(THD *thd, const char *s)
   class sp_head *sphead;
   class sp_name *spname;
   class sp_variable *spvar;
+  class With_element_head *with_element_head;
   class With_clause *with_clause;
   class Virtual_column_info *virtual_column;
 
@@ -1689,7 +1690,7 @@ END_OF_INPUT
 
 %type <with_clause> with_clause
 
-%type <lex_str_ptr> query_name
+%type <with_element_head> with_element_head
 
 %type <lex_str_list> opt_with_column_list
 
@@ -3138,7 +3139,11 @@ call:
             if (unlikely(Lex->call_statement_start(thd, $2)))
               MYSQL_YYABORT;
           }
-          opt_sp_cparam_list {}
+          opt_sp_cparam_list
+          {
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
+          }
         ;
 
 /* CALL parameters */
@@ -4092,6 +4097,8 @@ sp_proc_stmt_return:
             LEX *lex= Lex;
             sp_head *sp= lex->sphead;
             Lex->pop_select(); //main select
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
             if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont,
                                                           $3, lex)) ||
                 unlikely(sp->restore_lex(thd)))
@@ -13436,6 +13443,8 @@ do:
           {
             Lex->insert_list= $3;
             Lex->pop_select(); //main select
+            if (Lex->check_cte_dependencies_and_resolve_references())
+              MYSQL_YYABORT;
           }
         ;
 
@@ -15620,6 +15629,7 @@ with_clause:
              if (unlikely(with_clause == NULL))
                MYSQL_YYABORT;
              lex->derived_tables|= DERIVED_WITH;
+             lex->with_cte_resolution= true;
              lex->curr_with_clause= with_clause;
              with_clause->add_to_list(Lex->with_clauses_list_last_next);
              if (lex->current_select &&
@@ -15647,7 +15657,7 @@ with_list:
 
 
 with_list_element:
-	  query_name
+          with_element_head
 	  opt_with_column_list 
           {
             $2= new List<LEX_CSTRING> (Lex->with_column_list);
@@ -15667,6 +15677,7 @@ with_list_element:
             if (elem->set_unparsed_spec(thd, spec_start, $7.pos(),
                                         spec_start - query_start))
               MYSQL_YYABORT;
+            elem->set_tables_end_pos(lex->query_tables_last);
 	  }
 	;
 
@@ -15693,12 +15704,15 @@ with_column_list:
         ;
 
 
-query_name: 
+with_element_head:
           ident
           {
-            $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
-            if (unlikely($$ == NULL))
+            LEX_CSTRING *name=
+              (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING));
+            $$= new (thd->mem_root) With_element_head(name);
+            if (unlikely(name == NULL || $$ == NULL))
               MYSQL_YYABORT;
+            $$->tables_pos.set_start_pos(Lex->query_tables_last);
           }
         ;
 
diff --git a/sql/table.cc b/sql/table.cc
index 084b441..67b2b14 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock)
   }
 }
 
+
 bool TABLE_LIST::is_with_table()
 {
   return derived && derived->with_element;
diff --git a/sql/table.h b/sql/table.h
index 6073e35..248c239 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2139,6 +2139,29 @@ struct vers_select_conds_t
 
 struct LEX;
 class Index_hint;
+
+/*
+  @struct TABLE_CHAIN
+  @brief Subchain of global chain of table references
+
+  The structure contains a pointer to the address of the next_global
+  pointer to the first TABLE_LIST objectof the subchain and the address
+  of the next_global pointer to the element right after the last
+  TABLE_LIST object of the subchain.  For an empty subchain both pointers
+  have the same value.
+*/
+
+struct TABLE_CHAIN
+{
+  TABLE_CHAIN() {}
+
+  TABLE_LIST **start_pos;
+  TABLE_LIST ** end_pos;
+
+  void set_start_pos(TABLE_LIST **pos) { start_pos= pos; }
+  void set_end_pos(TABLE_LIST **pos) { end_pos= pos; }
+};
+
 struct TABLE_LIST
 {
   TABLE_LIST() {}                          /* Remove gcc warning */
@@ -2473,6 +2496,20 @@ struct TABLE_LIST
   /* call back function for asking handler about caching in query cache */
   qc_engine_callback callback_func;
   thr_lock_type lock_type;
+
+  /*
+    Two fields below are set during parsing this table reference in the cases
+    when the table reference can be potentially a reference to a CTE table.
+    In this cases the fact that the reference is a reference to a CTE or not
+    will be ascertained at the very end of parsing of the query when referencies
+    to CTE are resolved. For references to CTE and to derived tables no mdl
+    requests are needed while for other table references they are. If a request
+    is possibly postponed the info that allows to issue this request must be
+    saved in 'mdl_type' and 'table_options'.
+  */
+  enum_mdl_type mdl_type;
+  ulong         table_options;
+
   uint		outer_join;		/* Which join type */
   uint		shared;			/* Used in multi-upd */
   bool          updatable;		/* VIEW/TABLE can be updated now */


More information about the commits mailing list