summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/cte_recursive.result366
-rw-r--r--mysql-test/t/cte_recursive.test101
-rw-r--r--sql/sql_explain.cc7
-rw-r--r--sql/sql_explain.h2
-rw-r--r--sql/sql_lex.cc25
-rw-r--r--sql/sql_select.cc3
-rw-r--r--sql/sql_select.h4
-rw-r--r--sql/sql_union.cc2
8 files changed, 493 insertions, 17 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index be6617b6a1e..a21416b5f43 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -115,8 +115,8 @@ select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where
-3 UNCACHEABLE UNION <derived2> ALL NULL NULL NULL NULL 5
-3 UNCACHEABLE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5
+3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
# just WITH : types of t1 columns are determined by all parts of union
create view v1 as
@@ -599,10 +599,10 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
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
3 SUBQUERY folks ALL NULL NULL NULL NULL 12 100.00 Using where
-4 UNCACHEABLE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
-4 UNCACHEABLE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
-5 UNCACHEABLE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
-5 UNCACHEABLE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
+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 UNCACHEABLE SUBQUERY <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
Warnings:
@@ -781,8 +781,8 @@ select * from ancestors;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00
2 SUBQUERY folks ALL NULL NULL NULL NULL 12 100.00 Using where
-3 UNCACHEABLE UNION p ALL NULL NULL NULL NULL 12 100.00
-3 UNCACHEABLE UNION <derived2> ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
+3 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 with recursive 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') and (`test`.`folks`.`dob` = DATE'2000-01-01')) union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestors` `a` where ((`a`.`father` = `p`.`id`) or (`a`.`mother` = `p`.`id`)))select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors`
@@ -1137,10 +1137,10 @@ select * from ancestors;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12
2 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where
-3 UNCACHEABLE UNION p ALL PRIMARY NULL NULL NULL 12
-3 UNCACHEABLE UNION <derived2> ref key0 key0 5 test.p.id 2
-4 UNCACHEABLE UNION p ALL PRIMARY NULL NULL NULL 12
-4 UNCACHEABLE UNION <derived2> ref key0 key0 5 test.p.id 2
+3 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12
+3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2
+4 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12
+4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
with recursive
ancestors
@@ -1168,4 +1168,346 @@ id name dob father mother
9 Grandma Ann 1941-10-15 NULL NULL
7 Grandma Sally 1943-08-23 NULL 6
6 Grandgrandma Martha 1923-05-17 NULL NULL
+#
+# EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another:
+#
+explain
+with recursive
+prev_gen
+as
+(
+select folks.*
+from folks, prev_gen
+where folks.id=prev_gen.father or folks.id=prev_gen.mother
+union
+select *
+from folks
+where name='Me'
+),
+ancestors
+as
+(
+select *
+from folks
+where name='Me'
+ union
+select *
+from ancestors
+union
+select *
+from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24
+4 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where
+6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
+NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
+3 SUBQUERY folks ALL NULL NULL NULL NULL 12 Using where
+2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12
+2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
+NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL
+explain FORMAT=JSON
+with recursive
+prev_gen
+as
+(
+select folks.*
+from folks, prev_gen
+where folks.id=prev_gen.father or folks.id=prev_gen.mother
+union
+select *
+from folks
+where name='Me'
+),
+ancestors
+as
+(
+select *
+from folks
+where name='Me2'
+ union
+select *
+from ancestors where id < 234
+union
+select *
+from prev_gen where id < 345
+)
+select ancestors.name, ancestors.dob from ancestors;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "rows": 24,
+ "filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union4,6,5>",
+ "access_type": "ALL",
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 4,
+ "table": {
+ "table_name": "folks",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100,
+ "attached_condition": "(folks.`name` = 'Me2')"
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 6,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100,
+ "attached_condition": "(prev_gen.`id` < 345)",
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union3,2>",
+ "access_type": "ALL",
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "folks",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100,
+ "attached_condition": "(folks.`name` = 'Me')"
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "folks",
+ "access_type": "ALL",
+ "possible_keys": ["PRIMARY"],
+ "rows": 12,
+ "filtered": 100
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "256Kb",
+ "join_type": "BNL",
+ "attached_condition": "((prev_gen.father = folks.`id`) or (prev_gen.mother = folks.`id`))"
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 5,
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "rows": 24,
+ "filtered": 100,
+ "attached_condition": "(ancestors.`id` < 234)"
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+}
+#
+explain format=json
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived3>",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union3,2>",
+ "access_type": "ALL",
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "v",
+ "access_type": "ALL",
+ "rows": 12,
+ "filtered": 100,
+ "attached_condition": "((v.`name` = 'Me') and (v.father is not null) and (v.mother is not null))"
+ },
+ "table": {
+ "table_name": "h",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["id"],
+ "ref": ["test.v.father"],
+ "rows": 1,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "w",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["id"],
+ "ref": ["test.v.mother"],
+ "rows": 1,
+ "filtered": 100
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "((a.father is not null) and (a.mother is not null))"
+ },
+ "table": {
+ "table_name": "h",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["id"],
+ "ref": ["a.father"],
+ "rows": 1,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "w",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["id"],
+ "ref": ["a.mother"],
+ "rows": 1,
+ "filtered": 100
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+}
drop table folks;
+#
+# MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+explain format=json
+with recursive T as (select a from t1 union select a+10 from T where a < 1000)
+select * from T;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union2,3>",
+ "access_type": "ALL",
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100,
+ "attached_condition": "(T.a < 1000)"
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+}
+drop table t1;
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 8262b53c374..60f058b15f7 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -977,5 +977,106 @@ as
select * from ancestors;
+--echo #
+--echo # EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another:
+--echo #
+explain
+with recursive
+prev_gen
+as
+(
+ select folks.*
+ from folks, prev_gen
+ where folks.id=prev_gen.father or folks.id=prev_gen.mother
+ union
+ select *
+ from folks
+ where name='Me'
+),
+ancestors
+as
+(
+ select *
+ from folks
+ where name='Me'
+ union
+ select *
+ from ancestors
+ union
+ select *
+ from prev_gen
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+explain FORMAT=JSON
+with recursive
+prev_gen
+as
+(
+ select folks.*
+ from folks, prev_gen
+ where folks.id=prev_gen.father or folks.id=prev_gen.mother
+ union
+ select *
+ from folks
+ where name='Me'
+),
+ancestors
+as
+(
+ select *
+ from folks
+ where name='Me2'
+ union
+ select *
+ from ancestors where id < 234
+ union
+ select *
+ from prev_gen where id < 345
+)
+select ancestors.name, ancestors.dob from ancestors;
+
+--echo #
+explain format=json
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union all
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+
drop table folks;
+--echo #
+--echo # MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+explain format=json
+with recursive T as (select a from t1 union select a+10 from T where a < 1000)
+select * from T;
+
+drop table t1;
+
+
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 1f8b4f2dcb1..131c5a3bcfa 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -547,7 +547,12 @@ void Explain_union::print_explain_json(Explain_query *query,
bool started_object= print_explain_json_cache(writer, is_analyze);
writer->add_member("query_block").start_object();
- writer->add_member("union_result").start_object();
+
+ if (is_recursive_cte)
+ writer->add_member("recursive_union").start_object();
+ else
+ writer->add_member("union_result").start_object();
+
// using_temporary_table
make_union_table_name(table_name_buffer);
writer->add_member("table_name").add_str(table_name_buffer);
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index abdb1bb978b..5793599f4e1 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -327,6 +327,7 @@ class Explain_union : public Explain_node
public:
Explain_union(MEM_ROOT *root, bool is_analyze) :
Explain_node(root),
+ is_recursive_cte(false),
fake_select_lex_explain(root, is_analyze)
{}
@@ -362,6 +363,7 @@ public:
const char *fake_select_type;
bool using_filesort;
bool using_tmp;
+ bool is_recursive_cte;
/*
Explain data structure for "fake_select_lex" (i.e. for the degenerate
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index cffa779a27f..d0fa273b0b0 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4368,7 +4368,26 @@ void st_select_lex::set_explain_type(bool on_the_fly)
type= is_uncacheable ? "UNCACHEABLE UNION": "UNION";
if (this == master_unit()->fake_select_lex)
type= "UNION RESULT";
-
+ /*
+ join below may be =NULL when this functions is called at an early
+ stage. It will be later called again and we will set the correct
+ value.
+ */
+ if (join)
+ {
+ bool uses_cte= false;
+ for (JOIN_TAB *tab= first_explain_order_tab(join); tab;
+ tab= next_explain_order_tab(join, tab))
+ {
+ if (tab->table->pos_in_table_list->with)
+ {
+ uses_cte= true;
+ break;
+ }
+ }
+ if (uses_cte)
+ type= "RECURSIVE UNION";
+ }
}
}
}
@@ -4683,7 +4702,9 @@ int st_select_lex_unit::save_union_explain(Explain_query *output)
new (output->mem_root) Explain_union(output->mem_root,
thd->lex->analyze_stmt);
-
+ if (with_element && with_element->is_recursive)
+ eu->is_recursive_cte= true;
+
if (derived)
eu->connection_type= Explain_node::EXPLAIN_NODE_DERIVED;
/*
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e4257d6e94d..55d2838e869 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -24186,7 +24186,8 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta,
In case this is a derived table, here we remember the number of
subselect that used to produce it.
*/
- eta->derived_select_number= table->derived_select_number;
+ if (!(table_list && table_list->is_with_table_recursive_reference()))
+ eta->derived_select_number= table->derived_select_number;
/* The same for non-merged semi-joins */
eta->non_merged_sjm_number = get_non_merged_semijoin_select();
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 535cf5a29e0..12a40996258 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -2278,4 +2278,8 @@ public:
bool test_if_order_compatible(SQL_I_List<ORDER> &a, SQL_I_List<ORDER> &b);
int test_if_group_changed(List<Cached_item> &list);
int create_sort_index(THD *thd, JOIN *join, JOIN_TAB *tab, Filesort *fsort);
+
+JOIN_TAB *first_explain_order_tab(JOIN* join);
+JOIN_TAB *next_explain_order_tab(JOIN* join, JOIN_TAB* tab);
+
#endif /* SQL_SELECT_INCLUDED */
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 23c3801f4ab..4a73a503ebe 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1166,7 +1166,7 @@ err:
}
-
+// One step of recursive execution
bool st_select_lex_unit::exec_recursive(bool is_driving_recursive)
{
st_select_lex *lex_select_save= thd->lex->current_select;
View Lorry Controller Status