summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/cte_recursive.result210
-rw-r--r--mysql-test/t/cte_recursive.test173
-rw-r--r--sql/item_subselect.cc34
-rw-r--r--sql/item_subselect.h8
-rw-r--r--sql/sql_cte.cc1
-rw-r--r--sql/sql_cte.h3
-rw-r--r--sql/sql_select.cc3
-rw-r--r--sql/sql_union.cc9
8 files changed, 433 insertions, 8 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 7ec2b7a7072..368d7e571ed 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -2549,3 +2549,213 @@ m
m1
m2
drop table value_nodes, module_nodes, module_arguments, module_results;
+#
+# mdev-12519: recursive references in subqueries
+#
+create table t1 (lp char(4) not null, rp char(4) not null);
+insert into t1 values
+('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'),
+('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4');
+set standard_compliant_cte=0;
+with recursive
+reachables(p) as
+(
+select lp from t1 where lp = 'p1'
+ union
+select t1.rp from reachables, t1
+where t1.lp = reachables.p
+)
+select * from reachables;
+p
+p1
+p2
+p3
+p7
+p4
+p8
+p5
+with recursive
+reachables(p) as
+(
+select lp from t1 where lp = 'p1'
+ union
+select t1.rp from reachables, t1
+where 'p3' not in (select * from reachables) and
+t1.lp = reachables.p
+)
+select * from reachables;
+p
+p1
+p2
+p3
+p7
+with recursive
+reachables(p) as
+(
+select lp from t1 where lp = 'p1'
+ union
+select t1.rp from reachables, t1
+where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+select p from reachables where p > 'p5') and
+t1.lp = reachables.p
+)
+select * from reachables;
+p
+p1
+p2
+p3
+p7
+prepare stmt from "
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+ select p from reachables where p > 'p5') and
+ t1.lp = reachables.p
+)
+select * from reachables;
+";
+execute stmt;
+p
+p1
+p2
+p3
+p7
+execute stmt;
+p
+p1
+p2
+p3
+p7
+deallocate prepare stmt;
+drop table t1;
+create table objects(v char(4) not null);
+insert into objects values
+('v1'), ('v2'), ('v3'), ('v4'), ('v5'),
+('v6'), ('v7'), ('v8'), ('v9'), ('v10');
+create table modules(m char(4) not null);
+insert into modules values
+('m1'), ('m2'), ('m3'), ('m4');
+create table module_arguments(m char(4) not null, v char(4) not null);
+insert into module_arguments values
+('m1','v3'), ('m1','v9'),
+('m2','v4'), ('m2','v7'),
+('m3','v6'), ('m4','v2');
+create table module_results(m char(4) not null, v char(4) not null);
+insert into module_results values
+('m1','v4'),
+('m2','v1'), ('m2','v6'),
+('m3','v10'), ('m4','v7');
+set standard_compliant_cte=0;
+with recursive
+reached_objects as
+(
+select v, 'init' as m from objects where v in ('v3','v7','v9')
+union
+select module_results.v, module_results.m from module_results, applied_modules
+where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+select * from modules where 1=0
+union
+select modules.m
+from
+modules
+where
+not exists (select * from module_arguments
+where module_arguments.m = modules.m and
+module_arguments.v not in
+(select v from reached_objects))
+)
+select * from reached_objects;
+v m
+v3 init
+v7 init
+v9 init
+v4 m1
+v1 m2
+v6 m2
+v10 m3
+with recursive
+reached_objects as
+(
+select v, 'init' as m from objects where v in ('v3','v7','v9')
+union
+select module_results.v, module_results.m from module_results, applied_modules
+where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+select * from modules where 1=0
+union
+select modules.m
+from
+modules
+where
+'v6' not in (select v from reached_objects) and
+not exists (select * from module_arguments
+where module_arguments.m = modules.m and
+module_arguments.v not in
+(select v from reached_objects))
+)
+select * from reached_objects;
+v m
+v3 init
+v7 init
+v9 init
+v4 m1
+v1 m2
+v6 m2
+prepare stmt from "
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ 'v6' not in (select v from reached_objects) and
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+";
+execute stmt;
+v m
+v3 init
+v7 init
+v9 init
+v4 m1
+v1 m2
+v6 m2
+execute stmt;
+v m
+v3 init
+v7 init
+v9 init
+v4 m1
+v1 m2
+v6 m2
+deallocate prepare stmt;
+drop table objects, modules, module_arguments, module_results;
+set standard_compliant_cte=default;
+select @@standard_compliant_cte;
+@@standard_compliant_cte
+1
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index dd49582e719..26dbe2bccc0 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -1661,3 +1661,176 @@ select * from applied_modules;
drop table value_nodes, module_nodes, module_arguments, module_results;
+--echo #
+--echo # mdev-12519: recursive references in subqueries
+--echo #
+
+create table t1 (lp char(4) not null, rp char(4) not null);
+insert into t1 values
+ ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'),
+ ('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4');
+
+set standard_compliant_cte=0;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select * from reachables) and
+ t1.lp = reachables.p
+)
+select * from reachables;
+
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+ select p from reachables where p > 'p5') and
+ t1.lp = reachables.p
+)
+select * from reachables;
+
+prepare stmt from "
+with recursive
+reachables(p) as
+(
+ select lp from t1 where lp = 'p1'
+ union
+ select t1.rp from reachables, t1
+ where 'p3' not in (select p from reachables where p <= 'p5'
+ union
+ select p from reachables where p > 'p5') and
+ t1.lp = reachables.p
+)
+select * from reachables;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table t1;
+
+create table objects(v char(4) not null);
+insert into objects values
+ ('v1'), ('v2'), ('v3'), ('v4'), ('v5'),
+ ('v6'), ('v7'), ('v8'), ('v9'), ('v10');
+
+create table modules(m char(4) not null);
+insert into modules values
+ ('m1'), ('m2'), ('m3'), ('m4');
+
+create table module_arguments(m char(4) not null, v char(4) not null);
+insert into module_arguments values
+ ('m1','v3'), ('m1','v9'),
+ ('m2','v4'), ('m2','v7'),
+ ('m3','v6'), ('m4','v2');
+
+create table module_results(m char(4) not null, v char(4) not null);
+insert into module_results values
+ ('m1','v4'),
+ ('m2','v1'), ('m2','v6'),
+ ('m3','v10'), ('m4','v7');
+
+set standard_compliant_cte=0;
+
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ 'v6' not in (select v from reached_objects) and
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+
+prepare stmt from "
+with recursive
+reached_objects as
+(
+ select v, 'init' as m from objects where v in ('v3','v7','v9')
+ union
+ select module_results.v, module_results.m from module_results, applied_modules
+ where module_results.m = applied_modules.m
+),
+applied_modules as
+(
+ select * from modules where 1=0
+ union
+ select modules.m
+ from
+ modules
+ where
+ 'v6' not in (select v from reached_objects) and
+ not exists (select * from module_arguments
+ where module_arguments.m = modules.m and
+ module_arguments.v not in
+ (select v from reached_objects))
+)
+select * from reached_objects;
+";
+
+execute stmt;
+execute stmt;
+
+deallocate prepare stmt;
+
+drop table objects, modules, module_arguments, module_results;
+
+set standard_compliant_cte=default;
+select @@standard_compliant_cte;
+
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 76c754d5627..d144dddf764 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -40,6 +40,7 @@
#include "set_var.h"
#include "sql_select.h"
#include "sql_parse.h" // check_stack_overrun
+#include "sql_cte.h"
#include "sql_test.h"
double get_post_group_estimate(JOIN* join, double join_op_rows);
@@ -312,7 +313,8 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
else
goto end;
- if ((uncacheable= engine->uncacheable() & ~UNCACHEABLE_EXPLAIN))
+ if ((uncacheable= engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+ with_recursive_reference)
{
const_item_cache= 0;
if (uncacheable & UNCACHEABLE_RAND)
@@ -917,7 +919,7 @@ table_map Item_subselect::used_tables() const
bool Item_subselect::const_item() const
{
DBUG_ASSERT(thd);
- return (thd->lex->context_analysis_only ?
+ return (thd->lex->context_analysis_only || with_recursive_reference ?
FALSE :
forced_const || const_item_cache);
}
@@ -937,7 +939,8 @@ void Item_subselect::update_used_tables()
if (!(engine->uncacheable() & ~UNCACHEABLE_EXPLAIN))
{
// did all used tables become static?
- if (!(used_tables_cache & ~engine->upper_select_const_tables()))
+ if (!(used_tables_cache & ~engine->upper_select_const_tables()) &&
+ ! with_recursive_reference)
const_item_cache= 1;
}
}
@@ -1735,7 +1738,7 @@ bool Item_in_subselect::val_bool()
if (forced_const)
return value;
DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
- ! engine->is_executed());
+ ! engine->is_executed() || with_recursive_reference);
null_value= was_null= FALSE;
if (exec())
{
@@ -2828,7 +2831,8 @@ bool Item_exists_subselect::exists2in_processor(void *opt_arg)
join->having ||
first_select->with_sum_func ||
!first_select->leaf_tables.elements||
- !join->conds)
+ !join->conds ||
+ with_recursive_reference)
DBUG_RETURN(FALSE);
DBUG_ASSERT(first_select->order_list.elements == 0 &&
@@ -3480,6 +3484,11 @@ int subselect_single_select_engine::get_identifier()
return select_lex->select_number;
}
+void subselect_single_select_engine::force_reexecution()
+{
+ executed= false;
+}
+
void subselect_single_select_engine::cleanup()
{
DBUG_ENTER("subselect_single_select_engine::cleanup");
@@ -3508,6 +3517,11 @@ bool subselect_union_engine::is_executed() const
return unit->executed;
}
+void subselect_union_engine::force_reexecution()
+{
+ unit->executed= false;
+}
+
/*
Check if last execution of the subquery engine produced any rows
@@ -3827,7 +3841,8 @@ int subselect_single_select_engine::exec()
tab->read_record.read_record= tab->save_read_record;
}
executed= 1;
- if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN))
+ if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN) &&
+ !item->with_recursive_reference)
item->make_const();
thd->where= save_where;
thd->lex->current_select= save_select;
@@ -6674,6 +6689,13 @@ void subselect_table_scan_engine::cleanup()
}
+void Item_subselect::register_as_with_rec_ref(With_element *with_elem)
+{
+ with_elem->sq_with_rec_ref.link_in_list(this, &this->next_with_rec_ref);
+ with_recursive_reference= true;
+}
+
+
/*
Create an execution tracker for the expression cache we're using for this
subselect; add the tracker to the query plan.
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index aa01f571a3d..83bd9ed0bdf 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -32,6 +32,7 @@ class subselect_engine;
class subselect_hash_sj_engine;
class Item_bool_func2;
class Comp_creator;
+class With_element;
typedef class st_select_lex SELECT_LEX;
@@ -135,6 +136,9 @@ public:
*/
bool with_recursive_reference;
+ /* To link Item_subselects containing references to the same recursive CTE */
+ Item_subselect *next_with_rec_ref;
+
enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS,
EXISTS_SUBS, IN_SUBS, ALL_SUBS, ANY_SUBS};
@@ -256,6 +260,7 @@ public:
return TRUE;
}
+ void register_as_with_rec_ref(With_element *with_elem);
void init_expr_cache_tracker(THD *thd);
Item* build_clone(THD *thd, MEM_ROOT *mem_root) { return 0; }
@@ -836,6 +841,7 @@ public:
virtual bool no_rows() = 0;
virtual enum_engine_type engine_type() { return ABSTRACT_ENGINE; }
virtual int get_identifier() { DBUG_ASSERT(0); return 0; }
+ virtual void force_reexecution() {}
protected:
void set_row(List<Item> &item_list, Item_cache **row);
};
@@ -869,6 +875,7 @@ public:
bool no_rows();
virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; }
int get_identifier();
+ void force_reexecution();
friend class subselect_hash_sj_engine;
friend class Item_in_subselect;
@@ -899,6 +906,7 @@ public:
bool temp= FALSE);
bool no_tables();
bool is_executed() const;
+ void force_reexecution();
bool no_rows();
virtual enum_engine_type engine_type() { return UNION_ENGINE; }
};
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index d76ee13a010..c9976baeabd 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1229,6 +1229,7 @@ bool st_select_lex::check_subqueries_with_recursive_references()
continue;
Item_subselect *subq= (Item_subselect *) sl_master->item;
subq->with_recursive_reference= true;
+ subq->register_as_with_rec_ref(tbl->with);
}
}
return false;
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
index 27d5923ad07..1cb77af8099 100644
--- a/sql/sql_cte.h
+++ b/sql/sql_cte.h
@@ -118,6 +118,9 @@ public:
stage and is used at the execution stage.
*/
select_union_recursive *rec_result;
+
+ /* List of Item_subselects containing recursive references to this CTE */
+ SQL_I_List<Item_subselect> sq_with_rec_ref;
With_element(LEX_STRING *name,
List <LEX_STRING> list,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ecbac041dc4..fbddab20908 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -868,7 +868,8 @@ JOIN::prepare(TABLE_LIST *tables_init,
select_lex->check_unrestricted_recursive(
thd->variables.only_standard_compliant_cte))
DBUG_RETURN(-1);
- select_lex->check_subqueries_with_recursive_references();
+ if (select_lex->first_execution)
+ select_lex->check_subqueries_with_recursive_references();
int res= check_and_do_in_subquery_rewrites(this);
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index f8fe8b0be00..58601a178b7 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -930,7 +930,8 @@ bool st_select_lex_unit::exec()
if (executed && !uncacheable && !describe)
DBUG_RETURN(FALSE);
executed= 1;
- if (!(uncacheable & ~UNCACHEABLE_EXPLAIN) && item)
+ if (!(uncacheable & ~UNCACHEABLE_EXPLAIN) && item &&
+ !item->with_recursive_reference)
item->make_const();
saved_error= optimize();
@@ -1263,6 +1264,12 @@ bool st_select_lex_unit::exec_recursive()
if (with_element->level == 1)
rec_table->reginfo.join_tab->preread_init_done= true;
}
+ for (Item_subselect *sq= with_element->sq_with_rec_ref.first;
+ sq;
+ sq= sq->next_with_rec_ref)
+ {
+ sq->engine->force_reexecution();
+ }
thd->lex->current_select= lex_select_save;
err:
View Lorry Controller Status