diff options
author | Igor Babaev <igor@askmonty.org> | 2016-09-22 01:45:05 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2016-09-22 01:45:05 -0700 |
commit | 48b4e33596597b2c883adb0608729d69ffbe9ff5 (patch) | |
tree | 16b5e4e93597e3f026c799e49d47b103b0d41af5 | |
parent | 4368efe870f225279106798f71978b68c473e2ab (diff) | |
download | mariadb-git-bb-10.2-mdev9864.tar.gz |
Allowed to use WITH clauses before SELECT in CREATE ... SELECTbb-10.2-mdev9864
and INSERT ... SELECT.
Added test cases.
-rw-r--r-- | mysql-test/r/cte_recursive.result | 48 | ||||
-rw-r--r-- | mysql-test/t/cte_recursive.test | 38 | ||||
-rw-r--r-- | sql/sql_parse.cc | 6 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 26 |
4 files changed, 108 insertions, 10 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index b37a32a8ae7..1aa469029dd 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -1556,6 +1556,54 @@ EXPLAIN } } } +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; +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; +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; +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 5eb84bae4fb..c2c02a6680d 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -1162,6 +1162,44 @@ select h_name, h_dob, w_name, w_dob from ancestor_couples; +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; + +select * from my_ancestors; + +delete from my_ancestors; + +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; + +select * from my_ancestors; + +drop table my_ancestors; + drop table folks; --echo # diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index effc0230536..ac00b21c837 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3781,7 +3781,8 @@ mysql_execute_command(THD *thd) /* Copy temporarily the statement flags to thd for lock_table_names() */ uint save_thd_create_info_options= thd->lex->create_info.options; thd->lex->create_info.options|= create_info.options; - res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); + if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list))) + res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); thd->lex->create_info.options= save_thd_create_info_options; if (res) { @@ -4394,7 +4395,8 @@ end_with_restore_list: unit->set_limit(select_lex); - if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0))) + if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list)) && + !(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) { MYSQL_INSERT_SELECT_START(thd->query()); /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 551a86e4a41..1facf0623d4 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4810,16 +4810,22 @@ create_like: opt_create_select: /* empty */ {} - | opt_duplicate opt_as create_select_query_expression_body + | opt_duplicate opt_as create_select_query_expression ; -create_select_query_expression_body: - SELECT_SYM create_select_part2 opt_table_expression +create_select_query_expression: + opt_with_clause SELECT_SYM create_select_part2 opt_table_expression create_select_part4 - { Select->set_braces(0);} + { + Select->set_braces(0); + Select->set_with_clause($1); + } union_clause - | SELECT_SYM create_select_part2 create_select_part3_union_not_ready - create_select_part4 + | opt_with_clause SELECT_SYM create_select_part2 + create_select_part3_union_not_ready create_select_part4 + { + Select->set_with_clause($1); + } | '(' create_select_query_specification ')' | '(' create_select_query_specification ')' { Select->set_braces(1);} union_list {} @@ -5519,7 +5525,11 @@ opt_part_option: */ create_select_query_specification: - SELECT_SYM create_select_part2 create_select_part3 create_select_part4 + SELECT_SYM opt_with_clause create_select_part2 create_select_part3 + create_select_part4 + { + Select->set_with_clause($2); + } ; create_select_part2: @@ -12308,7 +12318,7 @@ fields: insert_values: VALUES values_list {} | VALUE_SYM values_list {} - | create_select_query_expression_body {} + | create_select_query_expression {} ; values_list: |