[Commits] 85d9876004a570a817b4b5043a23af616cf92dcb Fixed bug mdev-10881 The server missed to call check_dependencies_in_with_clauses() when processing PREPARE ... FROM CREATE ... SELECT / INSERT ... SELECT with WITH clause before SELECT.

Igor Babaev igor at askmonty.org
Sun Sep 25 07:04:54 EEST 2016


commit 85d9876004a570a817b4b5043a23af616cf92dcb
Author: Igor Babaev <igor at askmonty.org>
Commit: Igor Babaev <igor at askmonty.org>

    Fixed bug mdev-10881
    The server missed to call check_dependencies_in_with_clauses()
    when processing PREPARE ... FROM CREATE ... SELECT / INSERT ... SELECT
    with WITH clause before SELECT.
---
 mysql-test/r/cte_recursive.result |   60 +++++++++++++++++++++++++++++++++++++
 mysql-test/t/cte_recursive.test   |   48 +++++++++++++++++++++++++++++
 sql/sql_prepare.cc                |    6 ++++
 3 files changed, 114 insertions(+), 0 deletions(-)

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 7285ef2..6954f76 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -1633,6 +1633,66 @@ id	name	dob	father	mother
 8	Grandpa Ben	1940-10-21	NULL	NULL
 6	Grandgrandma Martha	1923-05-17	NULL	NULL
 deallocate prepare stmt;
+#
+# MDEV-10881: execution of prepared statement from
+#             CREATE ... SELECT, INSERT ... SELECT        
+#
+prepare stmt from"
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+id	name	dob	father	mother
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+10	Grandpa Bill	1940-04-05	NULL	NULL
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
+6	Grandgrandma Martha	1923-05-17	NULL	NULL
+delete from my_ancestors;
+prepare stmt from"
+insert into my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+id	name	dob	father	mother
+20	Dad	1970-02-02	10	9
+30	Mom	1975-03-03	8	7
+10	Grandpa Bill	1940-04-05	NULL	NULL
+9	Grandma Ann	1941-10-15	NULL	NULL
+7	Grandma Sally	1943-08-23	NULL	6
+8	Grandpa Ben	1940-10-21	NULL	NULL
+6	Grandgrandma Martha	1923-05-17	NULL	NULL
+drop table my_ancestors;
 drop table folks;
 #
 # MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index ef0ed5c..1fc27ff 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -1223,6 +1223,54 @@ select p.* from folks as p, ancestor_ids as a where p.id = a.id;
 execute stmt;
 deallocate prepare stmt;
 
+--echo #
+--echo # MDEV-10881: execution of prepared statement from
+--echo #             CREATE ... SELECT, INSERT ... SELECT       
+--echo #
+
+prepare stmt from"
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+prepare stmt from"
+insert into my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+";
+execute stmt;
+deallocate prepare stmt;
+select * from my_ancestors;
+
+drop table my_ancestors;
 
 drop table folks;
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index eab2863..df15a62 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1777,6 +1777,9 @@ static bool mysql_test_create_table(Prepared_statement *stmt)
   if (create_table_precheck(thd, tables, create_table))
     DBUG_RETURN(TRUE);
 
+  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+    DBUG_RETURN(TRUE);
+
   if (select_lex->item_list.elements)
   {
     /* Base table and temporary table are not in the same name space. */
@@ -2167,6 +2170,9 @@ static bool mysql_test_insert_select(Prepared_statement *stmt,
   if (insert_precheck(stmt->thd, tables))
     return 1;
 
+  if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+    return 1;
+
   /* store it, because mysql_insert_select_prepare_tester change it */
   first_local_table= lex->select_lex.table_list.first;
   DBUG_ASSERT(first_local_table != 0);


More information about the commits mailing list