summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/cte_recursive.result85
-rw-r--r--mysql-test/t/cte_recursive.test40
-rw-r--r--sql/sql_cte.cc18
-rw-r--r--sql/sql_cte.h34
-rw-r--r--sql/sql_derived.cc9
-rw-r--r--sql/sql_lex.cc14
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_select.cc5
-rw-r--r--sql/sql_select.h4
-rw-r--r--sql/sql_union.cc14
-rw-r--r--sql/table.cc14
11 files changed, 184 insertions, 55 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 22faade0b9f..be6617b6a1e 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -113,7 +113,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a
)
select * from t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 30
+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)
@@ -595,18 +595,18 @@ select h.name, h.dob, w.name, w.dob
from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w
where c.h_id = h.id and c.w_id= w.id;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 36 100.00
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 468 100.00 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 468 100.00 Using where; Using join buffer (incremental, BNL join)
+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
3 SUBQUERY folks ALL NULL NULL NULL NULL 12 100.00 Using where
-4 UNCACHEABLE UNION p ALL NULL NULL NULL NULL 12 100.00
-4 UNCACHEABLE UNION <derived2> ALL NULL NULL NULL NULL 36 100.00 Using where; Using join buffer (flat, BNL join)
+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)
NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL
-2 UNCACHEABLE SUBQUERY <derived3> ALL NULL NULL NULL NULL 36 100.00 Using where
+2 UNCACHEABLE SUBQUERY <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 (`fa`.`h_id` = `test`.`p`.`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`))
+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`))
with recursive
ancestor_couple_ids(h_id, w_id)
as
@@ -779,7 +779,7 @@ where p.id = a.father or p.id = a.mother
)
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 156 100.00
+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)
@@ -809,9 +809,9 @@ 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
-8 Grandpa Ben 1940-10-21 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
with recursive
ancestors
@@ -896,9 +896,9 @@ generation name
1 Dad
1 Mom
2 Grandpa Bill
-2 Grandpa Ben
2 Grandma Ann
2 Grandma Sally
+2 Grandpa Ben
3 Grandgrandma Martha
set standards_compliant_cte=1;
with recursive
@@ -951,9 +951,9 @@ 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
-8 Grandpa Ben 1940-10-21 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
with recursive
ancestor_ids (id)
as
@@ -998,10 +998,10 @@ 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
-8 Grandpa Ben 1940-10-21 NULL NULL
-25 Uncle Jim 1968-11-18 8 7
9 Grandma Ann 1941-10-15 NULL NULL
+25 Uncle Jim 1968-11-18 8 7
7 Grandma Sally 1943-08-23 NULL 6
+8 Grandpa Ben 1940-10-21 NULL NULL
6 Grandgrandma Martha 1923-05-17 NULL NULL
27 Auntie Melinda 1971-03-29 NULL NULL
with recursive
@@ -1029,9 +1029,9 @@ generation name
1 Dad
1 Mom
2 Grandpa Bill
-2 Grandpa Ben
2 Grandma Ann
2 Grandma Sally
+2 Grandpa Ben
3 Grandgrandma Martha
with recursive
ancestor_ids (id, generation)
@@ -1112,7 +1112,60 @@ generation name
1 Dad
1 Mom
2 Grandpa Bill
-2 Grandpa Ben
2 Grandma Ann
2 Grandma Sally
+2 Grandpa Ben
+alter table folks add primary key (id);
+explain
+with recursive
+ancestors
+as
+(
+select *
+from folks
+where name = 'Me'
+ union
+select p.*
+from folks as p, ancestors as fa
+where p.id = fa.father
+union
+select p.*
+from folks as p, ancestors as ma
+where p.id = ma.mother
+)
+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
+NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
+with recursive
+ancestors
+as
+(
+select *
+from folks
+where name = 'Me'
+ union
+select p.*
+from folks as p, ancestors as fa
+where p.id = fa.father
+union
+select p.*
+from folks as p, ancestors as ma
+where p.id = ma.mother
+)
+select * from ancestors;
+id name dob father mother
+100 Me 2000-01-01 20 30
+20 Dad 1970-02-02 10 9
+30 Mom 1975-03-03 8 7
+10 Grandpa Bill 1940-04-05 NULL NULL
+8 Grandpa Ben 1940-10-21 NULL NULL
+9 Grandma Ann 1941-10-15 NULL NULL
+7 Grandma Sally 1943-08-23 NULL 6
+6 Grandgrandma Martha 1923-05-17 NULL NULL
drop table folks;
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 8f85c7b0480..8262b53c374 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -937,5 +937,45 @@ as
)
select * from ancestors;
+alter table folks add primary key (id);
+
+explain
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+with recursive
+ancestors
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union
+ select p.*
+ from folks as p, ancestors as fa
+ where p.id = fa.father
+ union
+ select p.*
+ from folks as p, ancestors as ma
+ where p.id = ma.mother
+)
+select * from ancestors;
+
+
drop table folks;
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index 3c663d7d260..dd877b5598a 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -3,6 +3,7 @@
#include "sql_cte.h"
#include "sql_view.h" // for make_valid_column_names
#include "sql_parse.h"
+#include "sql_select.h"
/**
@@ -956,3 +957,20 @@ void With_element::print(String *str, enum_query_type query_type)
}
+bool With_element::instantiate_tmp_tables()
+{
+ List_iterator_fast<TABLE> li(rec_result->rec_tables);
+ TABLE *rec_table;
+ while ((rec_table= li++))
+ {
+ if (!rec_table->is_created() &&
+ instantiate_tmp_table(rec_table,
+ rec_result->tmp_table_param.keyinfo,
+ rec_result->tmp_table_param.start_recinfo,
+ &rec_result->tmp_table_param.recinfo,
+ 0))
+ return true;
+ }
+ return false;
+}
+
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 8b81644e838..52b2b8f4f77 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -6,15 +6,15 @@
class select_union;
struct st_unit_ctxt_elem;
-/**
- @class With_clause
- @brief Set of with_elements
- It has a reference to the first with element from this with clause.
- This reference allows to navigate through all the elements of the with clause.
- It contains a reference to the unit to which this with clause is attached.
- It also contains a flag saying whether this with clause was specified as recursive.
-*/
+/**
+ @class With_element
+ @brief Definition of a CTE table
+
+ It contains a reference to the name of the table introduced by this with element,
+ and a reference to the unit that specificies this table. Also it contains
+ a reference to the with clause to which this element belongs to.
+*/
class With_element : public Sql_alloc
{
@@ -184,18 +184,20 @@ public:
void set_result_table(TABLE *tab) { result_table= tab; }
+ bool instantiate_tmp_tables();
+
friend class With_clause;
};
-
/**
- @class With_element
- @brief Definition of a CTE table
-
- It contains a reference to the name of the table introduced by this with element,
- and a reference to the unit that specificies this table. Also it contains
- a reference to the with clause to which this element belongs to.
-*/
+ @class With_clause
+ @brief Set of with_elements
+
+ It has a reference to the first with element from this with clause.
+ This reference allows to navigate through all the elements of the with clause.
+ It contains a reference to the unit to which this with clause is attached.
+ It also contains a flag saying whether this with clause was specified as recursive.
+*/
class With_clause : public Sql_alloc
{
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index b1f665ad70b..e17896f9f24 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -653,7 +653,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
(first_select->options |
thd->variables.option_bits |
TMP_TABLE_ALL_COLUMNS),
- derived->alias, FALSE, TRUE);
+ derived->alias, FALSE, FALSE);
thd->create_tmp_table_for_derived= FALSE;
if (!res && !derived->table)
@@ -681,7 +681,9 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select())
{
sl->context.outer_context= 0;
- if (!derived->is_with_table_recursive_reference())
+ if (!derived->is_with_table_recursive_reference() ||
+ (!derived->with->with_anchor &&
+ !derived->with->is_with_prepared_anchor()))
{
// Prepare underlying views/DT first.
if ((res= sl->handle_derived(lex, DT_PREPARE)))
@@ -928,7 +930,8 @@ bool TABLE_LIST::fill_recursive(THD *thd)
rc= unit->exec_recursive(false);
else
{
- while(!with->all_are_stabilized() && !rc)
+ rc= with->instantiate_tmp_tables();
+ while(!rc && !with->all_are_stabilized())
{
rc= unit->exec_recursive(true);
}
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ee1ffcba336..cffa779a27f 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2065,6 +2065,7 @@ void st_select_lex_unit::init_query()
offset_limit_cnt= 0;
union_distinct= 0;
prepared= optimized= executed= 0;
+ optimize_started= 0;
item= 0;
union_result= 0;
table= 0;
@@ -4393,6 +4394,19 @@ void SELECT_LEX::increase_derived_records(ha_rows records)
SELECT_LEX_UNIT *unit= master_unit();
DBUG_ASSERT(unit->derived);
+ if (unit->with_element && unit->with_element->is_recursive)
+ {
+ st_select_lex *first_recursive= unit->with_element->first_recursive;
+ st_select_lex *sl= unit->first_select();
+ for ( ; sl != first_recursive; sl= sl->next_select())
+ {
+ if (sl == this)
+ break;
+ }
+ if (sl == first_recursive)
+ return;
+ }
+
select_union *result= (select_union*)unit->result;
result->records+= records;
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 785908d9750..de3ccfc08a9 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -611,6 +611,8 @@ public:
executed, // already executed
cleaned;
+ bool optimize_started;
+
// list of fields which points to temporary table for union
List<Item> item_list;
/*
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9b537a61c29..4415596f5b8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -165,10 +165,6 @@ static COND *optimize_cond(JOIN *join, COND *conds,
int flags= 0);
bool const_expression_in_where(COND *conds,Item *item, Item **comp_item);
static int do_select(JOIN *join, Procedure *procedure);
-static bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
- MARIA_COLUMNDEF *start_recinfo,
- MARIA_COLUMNDEF **recinfo,
- ulonglong options);
static enum_nested_loop_state evaluate_join_record(JOIN *, JOIN_TAB *, int);
static enum_nested_loop_state
@@ -17915,7 +17911,6 @@ int rr_sequential_and_unpack(READ_RECORD *info)
TRUE - Error
*/
-static
bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
MARIA_COLUMNDEF *start_recinfo,
MARIA_COLUMNDEF **recinfo,
diff --git a/sql/sql_select.h b/sql/sql_select.h
index c143d58c2e8..535cf5a29e0 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -2235,6 +2235,10 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
TMP_ENGINE_COLUMNDEF *start_recinfo,
TMP_ENGINE_COLUMNDEF **recinfo,
ulonglong options);
+bool instantiate_tmp_table(TABLE *table, KEY *keyinfo,
+ MARIA_COLUMNDEF *start_recinfo,
+ MARIA_COLUMNDEF **recinfo,
+ ulonglong options);
bool open_tmp_table(TABLE *table);
void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
double prev_record_reads(POSITION *positions, uint idx, table_map found_ref);
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 4c32779f347..23c3801f4ab 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -229,7 +229,7 @@ select_union_recursive::create_result_table(THD *thd_arg,
if (! (rec_table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
(ORDER*) 0, false, 1,
options, HA_POS_ERROR, alias,
- !create_table, keep_row_order)))
+ true, keep_row_order)))
return true;
rec_table->keys_in_use_for_query.clear_all();
@@ -283,8 +283,11 @@ void select_union_recursive::cleanup()
TABLE *tab;
while ((tab= it++))
{
- tab->file->extra(HA_EXTRA_RESET_STATE);
- tab->file->ha_delete_all_rows();
+ if (tab->is_created())
+ {
+ tab->file->extra(HA_EXTRA_RESET_STATE);
+ tab->file->ha_delete_all_rows();
+ }
free_tmp_table(thd, tab);
}
}
@@ -840,6 +843,10 @@ bool st_select_lex_unit::optimize()
if (optimized && !uncacheable && !describe)
DBUG_RETURN(FALSE);
+ if (with_element && with_element->is_recursive && optimize_started)
+ DBUG_RETURN(FALSE);
+ optimize_started= true;
+
if (uncacheable || !item || !item->assigned() || describe)
{
if (item)
@@ -1315,6 +1322,7 @@ bool st_select_lex_unit::cleanup()
void st_select_lex_unit::reinit_exec_mechanism()
{
prepared= optimized= executed= 0;
+ optimize_started= 0;
#ifndef DBUG_OFF
if (is_union())
{
diff --git a/sql/table.cc b/sql/table.cc
index e11ea59bcf8..3dff8ac7f59 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7198,24 +7198,14 @@ bool TABLE_LIST::handle_derived(LEX *lex, uint phases)
DBUG_ENTER("handle_derived");
DBUG_PRINT("enter", ("phases: 0x%x", phases));
- if (is_with_table_recursive_reference())
+ if (unit)
{
- if (!(with->with_anchor || with->is_with_prepared_anchor()))
+ if (!is_with_table_recursive_reference())
{
for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
if (sl->handle_derived(lex, phases))
DBUG_RETURN(TRUE);
}
- else if (mysql_handle_single_derived(lex, this, phases))
- DBUG_RETURN(TRUE);
- DBUG_RETURN(FALSE);
- }
-
- if (unit)
- {
- for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
- if (sl->handle_derived(lex, phases))
- DBUG_RETURN(TRUE);
if (mysql_handle_single_derived(lex, this, phases))
DBUG_RETURN(TRUE);
}
View Lorry Controller Status