From da5214831d3d0f0880b4068a06ce062ca703293f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 17 May 2012 13:46:05 +0300 Subject: Fix for bug lp:944706, task MDEV-193 The patch enables back constant subquery execution during query optimization after it was disabled during the development of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION). The main idea is that constant subqueries are allowed to be executed during optimization if their execution is not expensive. The approach is as follows: - Constant subqueries are recursively optimized in the beginning of JOIN::optimize of the outer query. This is done by the new method JOIN::optimize_constant_subqueries(). This is done so that the cost of executing these queries can be estimated. - Optimization of the outer query proceeds normally. During this phase the optimizer may request execution of non-expensive constant subqueries. Each place where the optimizer may potentially execute an expensive expression is guarded with the predicate Item::is_expensive(). - The implementation of Item_subselect::is_expensive has been extended to use the number of examined rows (estimated by the optimizer) as a way to determine whether the subquery is expensive or not. - The new system variable "expensive_subquery_limit" controls how many examined rows are considered to be not expensive. The default is 100. In addition, multiple changes were needed to make this solution work in the light of the changes made by MWL#89. These changes were needed to fix various crashes and wrong results, and legacy bugs discovered during development. --- sql/item_cmpfunc.cc | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index d3251bc1fb3..b45b138ced2 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -5539,7 +5539,15 @@ void Item_equal::add_const(Item *c, Item *f) else { Item_func_eq *func= new Item_func_eq(c, const_item); - func->set_cmp_func(); + if (func->set_cmp_func()) + { + /* + Setting a comparison function fails when trying to compare + incompatible charsets. Charset compatibility is checked earlier, + except for constant subqueries where we may do it here. + */ + return; + } func->quick_fix_field(); cond_false= !func->val_int(); } -- cgit v1.2.1 From 950abd526837b8772044d521387fa6d410de5706 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 22 May 2012 08:48:10 +0300 Subject: Fix of LP bug#992380 + revise fix_fields about missing with_subselect collection The problem is that some fix_fields do not call Item_func::fix_fields and do not collect with subselect_information. --- sql/item_cmpfunc.cc | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index ea216944dbb..dda9ab418a4 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1791,6 +1791,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) } if (args[1]->maybe_null) maybe_null=1; + with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); @@ -4907,6 +4908,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref) args[1]->fix_fields(thd, args + 1)) || args[1]->check_cols(1)) return TRUE; /* purecov: inspected */ with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func; + with_subselect|= args[0]->with_subselect | args[1]->with_subselect; max_length= 1; decimals= 0; @@ -5597,6 +5599,7 @@ bool Item_equal::fix_fields(THD *thd, Item **ref) used_tables_cache|= item->used_tables(); tmp_table_map= item->not_null_tables(); not_null_tables_cache|= tmp_table_map; + DBUG_ASSERT(!item->with_sum_func && !item->with_subselect); if (item->maybe_null) maybe_null=1; } -- cgit v1.2.1 From 4fa89b5fe05280fefa2b30df927d0db6c6888f98 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 24 May 2012 14:08:28 +0300 Subject: Test case for bug lp:1001117, MySQL BUG#12330344 Analysis: The problem in the original MySQL bug is that the range optimizer performs its analysis in a separate MEM_ROOT object that is freed after the range optimzier is done. During range analysis get_mm_tree calls Item_func_like::select_optimize, which in turn evaluates its right argument. In the test case the right argument is a subquery. In MySQL, subqueries are optimized lazyly, thus the call to val_str triggers optimization for the subquery. All objects needed by the subquery plan end up in the temporary MEM_ROOT used by the range optimizer. When execution ends, the JOIN::cleanup process tries to cleanup objects of the subquery plan, but all these objects are gone with the temporary MEM_ROOT. The solution for MySQL is to switch the mem_root. In MariaDB with the patch for bug lp:944706, all constant subqueries that may be used by the optimization process are preoptimized. Therefore Item_func_like::select_optimize only triggers subquery execution, and the above problem is not present. The patch however adds a test whether the evaluated right argument of the LIKE predicate is expensive. This is consistent with our approach not to evaluate expensive expressions during optimization. --- sql/item_cmpfunc.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index b45b138ced2..560373b69e6 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4736,7 +4736,7 @@ longlong Item_func_like::val_int() Item_func::optimize_type Item_func_like::select_optimize() const { - if (args[1]->const_item()) + if (args[1]->const_item() && !args[1]->is_expensive()) { String* res2= args[1]->val_str((String *)&cmp.value2); const char *ptr2; -- cgit v1.2.1 From f45784c8503d0bbe77f3aec351fc87ff536b19be Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 25 May 2012 10:29:53 +0300 Subject: Fix of LP bug#992380 + revise fix_fields about missing with_subselect collection The problem is that some fix_fields do not call Item_func::fix_fields and do not collect with subselect_information. --- sql/item_cmpfunc.cc | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index dda9ab418a4..61554bdf420 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4271,6 +4271,22 @@ Item_cond::fix_fields(THD *thd, Item **ref) if (abort_on_null) item->top_level_item(); + /* + replace degraded condition: + was: + become: = 1 + */ + if (item->type() == FIELD_ITEM) + { + Query_arena backup, *arena; + Item *new_item; + arena= thd->activate_stmt_arena_if_needed(&backup); + if ((new_item= new Item_func_ne(item, new Item_int(0, 1)))) + li.replace(item= new_item); + if (arena) + thd->restore_active_arena(arena, &backup); + } + // item can be substituted in fix_fields if ((!item->fixed && item->fix_fields(thd, li.ref())) || @@ -5283,6 +5299,28 @@ Item *Item_func_not::neg_transformer(THD *thd) /* NOT(x) -> x */ } +bool Item_func_not::fix_fields(THD *thd, Item **ref) +{ + if (args[0]->type() == FIELD_ITEM) + { + /* replace "NOT " with " == 0" */ + Query_arena backup, *arena; + Item *new_item; + bool rc= TRUE; + arena= thd->activate_stmt_arena_if_needed(&backup); + if ((new_item= new Item_func_eq(args[0], new Item_int(0, 1)))) + { + new_item->name= name; + rc= (*ref= new_item)->fix_fields(thd, ref); + } + if (arena) + thd->restore_active_arena(arena, &backup); + return rc; + } + return Item_func::fix_fields(thd, ref); +} + + Item *Item_bool_rowready_func2::neg_transformer(THD *thd) { Item *item= negated_item(); -- cgit v1.2.1 From a247b12fe6d3356a2f9b115e3cd850bd004b4bd3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 15 Jun 2012 17:22:49 +0200 Subject: MDEV-316 lp:1009085 Assertion failed: warn_item, file item_cmpfunc.cc, line 3613 make sure that find_date_time_item() is called before agg_arg_charsets_for_comparison(). optimize Item_func_conv_charset to avoid conversion if no string result is needed --- sql/item_cmpfunc.cc | 19 ++++++++++--------- 1 file changed, 10 insertions(+), 9 deletions(-) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 5f1a863d8fd..e38fe057356 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -3032,6 +3032,11 @@ void Item_func_case::fix_length_and_dec() nagg++; if (!(found_types= collect_cmp_types(agg, nagg))) return; + + Item *date_arg= 0; + if (found_types & (1 << TIME_RESULT)) + date_arg= find_date_time_item(args, arg_count, 0); + if (found_types & (1 << STRING_RESULT)) { /* @@ -3071,16 +3076,12 @@ void Item_func_case::fix_length_and_dec() change_item_tree_if_needed(thd, &args[nagg * 2], agg[nagg + 1]); } - Item *date_arg= 0; for (i= 0; i <= (uint)TIME_RESULT; i++) { if (found_types & (1 << i) && !cmp_items[i]) { DBUG_ASSERT((Item_result)i != ROW_RESULT); - if ((Item_result)i == TIME_RESULT) - date_arg= find_date_time_item(args, arg_count, 0); - if (!(cmp_items[i]= cmp_item::get_comparator((Item_result)i, date_arg, cmp_collation.collation))) @@ -4051,15 +4052,15 @@ void Item_func_in::fix_length_and_dec() } else { + if (found_types & (1 << TIME_RESULT)) + date_arg= find_date_time_item(args, arg_count, 0); + if (found_types & (1 << STRING_RESULT) && + agg_arg_charsets_for_comparison(cmp_collation, args, arg_count)) + return; for (i= 0; i <= (uint) TIME_RESULT; i++) { if (found_types & (1 << i) && !cmp_items[i]) { - if ((Item_result)i == STRING_RESULT && - agg_arg_charsets_for_comparison(cmp_collation, args, arg_count)) - return; - if ((Item_result)i == TIME_RESULT) - date_arg= find_date_time_item(args, arg_count, 0); if (!cmp_items[i] && !(cmp_items[i]= cmp_item::get_comparator((Item_result)i, date_arg, cmp_collation.collation))) -- cgit v1.2.1