diff options
| -rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/cte_recursive.result | 2 | ||||
| -rw-r--r-- | sql/sql_cte.cc | 14 | ||||
| -rw-r--r-- | sql/sql_cte.h | 2 |
4 files changed, 9 insertions, 13 deletions
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 81e77ca60bd..33e14af900a 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1, t r2 where r1.c=r2.c and r2.c=4; show create view v4; View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci select * from v4; c d 4 4 @@ -1126,7 +1126,7 @@ NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` +Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2` drop table t1; # # MDEV-13753: embedded CTE in a VIEW created in prepared statement diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index db1c95788e8..4e9b4aee67b 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 +2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL -2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where Warnings: Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` # simple mutual recursion diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 7e28a680e66..ac27f1713f9 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1001,6 +1001,10 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) and it was unsuccesful. Yet for units cloned from the spec it has not been done yet. */ + With_clause *attached_with_clause= sl->get_with_clause(); + if (attached_with_clause && + (found= attached_with_clause->find_table_def(table, NULL))) + break; master_unit= sl->master_unit(); outer_sl= master_unit->outer_select(); With_element *with_elem= sl->get_with_element(); @@ -1014,13 +1018,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) if (outer_sl && !outer_sl->get_with_element()) break; } - else - { - With_clause *attached_with_clause= sl->get_with_clause(); - if (attached_with_clause && - (found= attached_with_clause->find_table_def(table, NULL))) - break; - } /* Do not look for the table's definition beyond the scope of the view */ if (master_unit->is_view) break; @@ -1062,7 +1059,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) if (!with_elem->is_referenced() || with_elem->is_recursive) { derived= with_elem->spec; - if (derived->get_master() != select_lex && + if (derived != select_lex->master_unit() && !is_with_table_recursive_reference()) { derived->move_as_slave(select_lex); @@ -1072,7 +1069,6 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { if(!(derived= with_elem->clone_parsed_spec(thd, this))) return true; - derived->with_element= with_elem; } derived->first_select()->linkage= DERIVED_TABLE_TYPE; with_elem->inc_references(); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 64b2f1cb1c8..70526e862dc 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -142,7 +142,7 @@ public: query_name(name), column_list(list), spec(unit), is_recursive(false), with_anchor(false), level(0), rec_result(NULL) - {} + { unit->with_element= this; } bool check_dependencies_in_spec(); |