From 6b97fe067db1b1d8e8dee56508e6d78a36e92481 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 9 May 2017 00:41:45 -0700 Subject: Fixed the bugs mdev-12670 and mdev-12675. The code that blocked conversion of a IN subselect pedicate to a semi-join if it occurred in the ON expression of an outer join did not do it correctly. As a result, the conversion was blocked for IN subselect predicates encountered in ON expressions of INNER joins or in WHERE conditions of mergeable views / derived tables. This patch fixes this problem. --- sql/item_subselect.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_subselect.cc') diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 78dcfc4215c..12337ec2b1c 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1366,7 +1366,7 @@ Item_in_subselect::Item_in_subselect(Item * left_exp, Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), in_strategy(SUBS_NOT_TRANSFORMED), optimizer(0), pushed_cond_guards(NULL), emb_on_expr_nest(NULL), - is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE), + do_not_convert_to_sj(FALSE), is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE), is_flattenable_semijoin(FALSE), is_registered_semijoin(FALSE), upper_item(0) -- cgit v1.2.1 From e0352fb07961f09ff6481136dc22f3c0db376def Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 15 May 2017 09:51:01 -0700 Subject: Fixed the bug mdev-7599. At some conditions the function opt_sum_query() can apply MIN/MAX optimizations to to Item_sum objects of a select These optimizations becomes invalid if this select is the subquery of an IN subquery predicate that is converted to a EXISTS subquery. Thus in this case the MIX/MAX optimizations that have been applied in opt_sum_query() must be rolled back. This bug appeared in 5.3 when the code for the cost base choice between materialization and in-to-exists transformation of non-correlated IN subqueries was introduced. Before this code in-to-exists transformations were always performed before the call of opt_sum_query(). --- sql/item_subselect.cc | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'sql/item_subselect.cc') diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 12337ec2b1c..e0da946d190 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2493,6 +2493,27 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) DBUG_ENTER("Item_in_subselect::inject_in_to_exists_cond"); DBUG_ASSERT(thd == join_arg->thd); + if (select_lex->min_max_opt_list.elements) + { + /* + MIN/MAX optimizations have been applied to Item_sum objects + of the subquery this subquery predicate in opt_sum_query(). + Injection of new condition invalidates this optimizations. + Thus those optimizations must be rolled back. + */ + List_iterator_fast it(select_lex->min_max_opt_list); + Item_sum *item; + while ((item= it++)) + { + item->clear(); + item->reset_forced_const(); + } + if (where_item) + where_item->update_used_tables(); + if (having_item) + having_item->update_used_tables(); + } + if (where_item) { List *and_args= NULL; -- cgit v1.2.1