[Commits] 07e018e3594535e70d6046b2a12004ba28052e6f Fixed bug mdev-10923. The code for st_select_lex::find_table_def_in_with_clauses() did not take into account the fact that the specs for mergeable CTEs were cloned and were not processed by the function With_element::check_dependencies_in_spec().

Igor Babaev igor at askmonty.org
Fri Sep 30 23:10:58 EEST 2016


commit 07e018e3594535e70d6046b2a12004ba28052e6f
Author: Igor Babaev <igor at askmonty.org>
Commit: Igor Babaev <igor at askmonty.org>

    Fixed bug mdev-10923.
    The code for st_select_lex::find_table_def_in_with_clauses()
    did not take into account the fact that the specs for mergeable
    CTEs were cloned and were not processed by the function
    With_element::check_dependencies_in_spec().
---
 mysql-test/r/cte_nonrecursive.result |   32 ++++++++++++++++++++++++++++++++
 mysql-test/t/cte_nonrecursive.test   |   33 +++++++++++++++++++++++++++++++++
 sql/sql_cte.cc                       |   13 +++++++++----
 3 files changed, 74 insertions(+), 4 deletions(-)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index 7c758c3..616b215 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -895,3 +895,35 @@ SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 )
 ;
 i1	a2	b2
 DROP TABLE t1,t2,t3;
+#
+# MDEV-10923: mergeable CTE used twice in the query
+#
+create table employees (
+name varchar(32),
+dept varchar(32),
+country varchar(8)
+);
+insert into employees 
+values 
+('Sergei Golubchik', 'Development', 'DE'),
+('Claudio Nanni', 'Support', 'ES'),
+('Sergei Petrunia', 'Development', 'RU');
+with eng as 
+(
+select * from employees
+where dept in ('Development','Support')
+),
+eu_eng  as 
+(
+select * from eng where country IN ('DE','ES','RU')
+)
+select * from eu_eng T1 
+where 
+not exists (select 1 from eu_eng T2 
+where T2.country=T1.country
+and T2.name <> T1.name);
+name	dept	country
+Sergei Golubchik	Development	DE
+Claudio Nanni	Support	ES
+Sergei Petrunia	Development	RU
+drop table employees;
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index ccd0894..8dbde47 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -576,3 +576,36 @@ UNION
 SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 )
 ;
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-10923: mergeable CTE used twice in the query
+--echo #
+
+create table employees (
+  name varchar(32),
+  dept varchar(32),
+  country varchar(8)
+);
+ 
+insert into employees 
+values 
+('Sergei Golubchik', 'Development', 'DE'),
+('Claudio Nanni', 'Support', 'ES'),
+('Sergei Petrunia', 'Development', 'RU');
+
+with eng as 
+(
+   select * from employees
+   where dept in ('Development','Support')
+),
+eu_eng  as 
+(
+  select * from eng where country IN ('DE','ES','RU')
+)
+select * from eu_eng T1 
+where 
+  not exists (select 1 from eu_eng T2 
+              where T2.country=T1.country
+              and T2.name <> T1.name);
+
+drop table employees;
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index f203c33..7e98a9b 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -928,13 +928,18 @@ bool With_element::is_anchor(st_select_lex *sel)
     /* 
       If sl->master_unit() is the spec of a with element then the search for 
       a definition was already done by With_element::check_dependencies_in_spec
-      and it was unsuccesful.
+      and it was unsuccesful. Yet for units cloned from the spec it has not 
+      been done yet.
     */
-    if (with_elem)
+    if (with_elem && sl->master_unit() == with_elem->spec)
       break;      
     With_clause *with_clause=sl->get_with_clause();
-    if (with_clause && (found= with_clause->find_table_def(table,NULL)))
-      break;
+    if (with_clause)
+    {
+      With_element *barrier= with_clause->with_recursive ? NULL : with_elem;
+      if ((found= with_clause->find_table_def(table, barrier)))
+        break;
+    }
     master_unit= sl->master_unit();
     /* Do not look for the table's definition beyond the scope of the view */
     if (master_unit->is_view)


More information about the commits mailing list