Hi Alexey, The patch doesn't have any testcase. Did you forget to add them? On Tue, Sep 20, 2016 at 01:22:19PM +0400, Alexey Botchkov wrote:

revision-id: a288cb698195b1e57abbb426f1cc9a804d65ff45 (mariadb-10.1.8-262-ga288cb6) parent(s): cb575abf76be82553b9c1c12c9112cbc6f53a547 committer: Alexey Botchkov timestamp: 2016-09-20 13:19:08 +0400 message:

MDEV-8320 Allow index usage for DATE(datetime_column) = const.

create_reverse_func() method added so functions can specify how to unpack field argument out of it. opt_arguments added to Item_bool_func2 so it can have different arguments for the optimizer and the calcualtion itself.

--- sql/item.h | 8 +++++ sql/item_cmpfunc.h | 52 ++++++++++++------------------- sql/item_func.h | 5 +++ sql/item_timefunc.cc | 87 ++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/item_timefunc.h | 49 ++++++++++++++++++++++++++++- sql/opt_range.cc | 48 +++++++++++++++++++++++++++++ sql/sql_select.cc | 70 ++++++++++++++++++++++++++++++++++++++---- 7 files changed, 279 insertions(+), 40 deletions(-)

diff --git a/sql/item.h b/sql/item.h index 5b82548..200e2e0 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1212,6 +1212,14 @@ class Item: public Value_source, { return; } + virtual bool add_extra_key_fields(THD *thd, + JOIN *join, KEY_FIELD **key_fields, + uint *and_level, + table_map usable_tables, + SARGABLE_PARAM **sargables) + { + return false; + } /* Make a select tree for all keys in a condition or a condition part @param param Context diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 6d432bd..516bb07 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -136,6 +136,14 @@ class Item_bool_func :public Item_int_func { protected: /* + Some functions modify it's arguments for the optimizer. + So for example the condition 'Func(fieldX) = constY' turned into + 'fieldX = cnuR(constY)' so that optimizer can use an index on fieldX. + */ + Item *opt_args[3]; + uint opt_arg_count; + + /* Build a SEL_TREE for a simple predicate @param param PARAM from SQL_SELECT::test_quick_select @param field field in the predicate @@ -189,12 +197,12 @@ class Item_bool_func :public Item_int_func KEY_PART *key_part, Item_func::Functype type, Item *value); public: - Item_bool_func(THD *thd): Item_int_func(thd) {} - Item_bool_func(THD *thd, Item *a): Item_int_func(thd, a) {} - Item_bool_func(THD *thd, Item *a, Item *b): Item_int_func(thd, a, b) {} - Item_bool_func(THD *thd, Item *a, Item *b, Item *c): Item_int_func(thd, a, b, c) {} - Item_bool_func(THD *thd, List<Item> &list): Item_int_func(thd, list) { } - Item_bool_func(THD *thd, Item_bool_func *item) :Item_int_func(thd, item) {} + Item_bool_func(THD *thd): Item_int_func(thd), opt_arg_count(0) {} + Item_bool_func(THD *thd, Item *a): Item_int_func(thd, a), opt_arg_count(0) {} + Item_bool_func(THD *thd, Item *a, Item *b): Item_int_func(thd, a, b), opt_arg_count(0) {} + Item_bool_func(THD *thd, Item *a, Item *b, Item *c): Item_int_func(thd, a, b, c), opt_arg_count(0) {} + Item_bool_func(THD *thd, List<Item> &list): Item_int_func(thd, list), opt_arg_count(0) { } + Item_bool_func(THD *thd, Item_bool_func *item) :Item_int_func(thd, item), opt_arg_count(0) {} bool is_bool_type() { return true; } virtual CHARSET_INFO *compare_collation() const { return NULL; } void fix_length_and_dec() { decimals=0; max_length=1; } @@ -436,33 +444,7 @@ class Item_bool_func2_with_rev :public Item_bool_func2 Item_bool_func2_with_rev(THD *thd, Item *a, Item *b): Item_bool_func2(thd, a, b) { } virtual enum Functype rev_functype() const= 0; - SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) - { - DBUG_ENTER("Item_bool_func2_with_rev::get_mm_tree"); - DBUG_ASSERT(arg_count == 2); - SEL_TREE *ftree; - /* - Even if get_full_func_mm_tree_for_args(param, args[0], args[1]) will not - return a range predicate it may still be possible to create one - by reversing the order of the operands. Note that this only - applies to predicates where both operands are fields. Example: A - query of the form - - WHERE t1.a OP t2.b - - In this case, args[0] == t1.a and args[1] == t2.b. - When creating range predicates for t2, - get_full_func_mm_tree_for_args(param, args[0], args[1]) - will return NULL because 'field' belongs to t1 and only - predicates that applies to t2 are of interest. In this case a - call to get_full_func_mm_tree_for_args() with reversed operands - may succeed. - */ - if (!(ftree= get_full_func_mm_tree_for_args(param, args[0], args[1])) && - !(ftree= get_full_func_mm_tree_for_args(param, args[1], args[0]))) - ftree= Item_func::get_mm_tree(param, cond_ptr); - DBUG_RETURN(ftree); - } + SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr); };

@@ -504,6 +486,10 @@ class Item_bool_rowready_func2 :public Item_bool_func2_with_rev Item_bool_func2::cleanup(); cmp.cleanup(); } + bool add_extra_key_fields(THD *thd, + JOIN *join, KEY_FIELD **key_fields, + uint *and_level, table_map usable_tables, + SARGABLE_PARAM **sargables); void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level, table_map usable_tables, SARGABLE_PARAM **sargables) diff --git a/sql/item_func.h b/sql/item_func.h index ca7c481..1f802db 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -358,6 +358,11 @@ class Item_func :public Item_func_or_sum - or replaced to an Item_int_with_ref */ bool setup_args_and_comparator(THD *thd, Arg_comparator *cmp); + virtual bool create_reverse_func(enum Functype cmp_type, + THD *thd, Item *r_arg, uint *a_cnt, Item** a) + { + return false; + } };

diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 41dc967..3124444 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2569,6 +2569,39 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) }

+bool Item_date_typecast::create_reverse_func(enum Functype cmp_type, + THD *thd, Item *r_arg, uint *a_cnt, Item** a) +{ + switch (cmp_type) + { + case GT_FUNC: + case LE_FUNC: + (*a_cnt)++; + if (!(a[0]= new (thd->mem_root) Item_func_day_end(thd, r_arg)) || + a[0]->fix_fields(thd, a+1)) + return true; + break; + case LT_FUNC: + case GE_FUNC: + (*a_cnt)++; + if (!(a[0]= new (thd->mem_root) Item_func_day_begin(thd, r_arg)) || + a[0]->fix_fields(thd, a+1)) + return true; + break; + case EQ_FUNC: + (*a_cnt)+= 2; + if (!(a[0]= new (thd->mem_root) Item_func_day_begin(thd, r_arg)) || + a[0]->fix_fields(thd, a+1)) + return true; + if (!(a[1]= new (thd->mem_root) Item_func_day_end(thd, r_arg)) || + a[1]->fix_fields(thd, a+2)) + return true; + default:; + } + return false; +} + + bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) { fuzzy_date |= sql_mode_for_dates(current_thd); @@ -3240,3 +3273,57 @@ bool Item_func_last_day::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) ltime->time_type= MYSQL_TIMESTAMP_DATE; return (null_value= 0); } + + +bool Item_func_day_begin::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) +{ + if (get_arg0_date(res, fuzzy_date)) + return (null_value=1); + + res->second_part= res->second= res->minute= res->hour= 0; + res->time_type= MYSQL_TIMESTAMP_DATETIME; + + return null_value= 0; +} + + +bool Item_func_day_end::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) +{ + if (get_arg0_date(res, fuzzy_date)) + return (null_value=1); + + res->hour= 23; + res->second= res->minute= 59; + res->second_part= 999999; + res->time_type= MYSQL_TIMESTAMP_DATETIME; + return null_value= 0; +} + + +bool Item_func_year_begin::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) +{ + res->year= args[0]->val_int(); + if ((null_value= args[0]->null_value || res->year >= 9999)) + return 0; + + res->day= res->month= 1; + res->second_part= res->second= res->minute= res->hour= 0; + res->time_type= MYSQL_TIMESTAMP_DATETIME; + return null_value= 0; +} + + +bool Item_func_year_end::get_date(MYSQL_TIME *res, ulonglong fuzzy_date) +{ + res->year= args[0]->val_int(); + if ((null_value= args[0]->null_value || res->year >= 9999)) + return 0; + + res->month= 12; + res->day= 31; + res->hour= 23; + res->second= res->minute= 59; + res->second_part= 999999; + res->time_type= MYSQL_TIMESTAMP_DATETIME; + return null_value= 0; +} diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index a853c63..b4f64ef 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -745,7 +745,7 @@ class Item_func_now_local :public Item_func_now { public: Item_func_now_local(THD *thd, uint dec): Item_func_now(thd, dec) {} - const char *func_name() const { return "now"; } + const char *func_name() const { return "day_start"; } virtual void store_now_in_TIME(THD *thd, MYSQL_TIME *now_time); virtual enum Functype functype() const { return NOW_FUNC; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) @@ -1074,6 +1074,8 @@ class Item_date_typecast :public Item_temporal_typecast bool get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date); const char *cast_type() const { return "date"; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } + bool create_reverse_func(enum Functype cmp_type, + THD *thd, Item *r_arg, uint *a_cnt, Item** a); Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_date_typecast>(thd, mem_root, this); } }; @@ -1268,4 +1270,49 @@ class Item_func_last_day :public Item_datefunc { return get_item_copy<Item_func_last_day>(thd, mem_root, this); } };

+ +class Item_func_day_begin :public Item_datetimefunc +{ +public: + Item_func_day_begin(THD *thd, Item *a): Item_datetimefunc(thd, a) {} + const char *func_name() const { return "day_begin"; } + bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_day_begin>(thd, mem_root, this); } +}; + + +class Item_func_day_end :public Item_datetimefunc +{ +public: + Item_func_day_end(THD *thd, Item *a): Item_datetimefunc(thd, a) {} + const char *func_name() const { return "day_end"; } + bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_day_end>(thd, mem_root, this); } +}; + + +class Item_func_year_begin :public Item_datetimefunc +{ +public: + Item_func_year_begin(THD *thd, Item *a): Item_datetimefunc(thd, a) {} + const char *func_name() const { return "year_begin"; } + bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_year_begin>(thd, mem_root, this); } +}; + + +class Item_func_year_end :public Item_datetimefunc +{ +public: + Item_func_year_end(THD *thd, Item *a): Item_datetimefunc(thd, a) {} + const char *func_name() const { return "year_end"; } + bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date); + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy<Item_func_year_end>(thd, mem_root, this); } +}; + + #endif /* ITEM_TIMEFUNC_INCLUDED */ diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 3ea9f4e..e533608 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -6998,6 +6998,54 @@ SEL_TREE *Item_bool_func::get_ne_mm_tree(RANGE_OPT_PARAM *param, }

+SEL_TREE *Item_bool_func2_with_rev::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) +{ + DBUG_ENTER("Item_bool_func2_with_rev::get_mm_tree"); + DBUG_ASSERT(arg_count == 2); + SEL_TREE *ftree; + /* + Even if get_full_func_mm_tree_for_args(param, args[0], args[1]) will not + return a range predicate it may still be possible to create one + by reversing the order of the operands. Note that this only + applies to predicates where both operands are fields. Example: A + query of the form + + WHERE t1.a OP t2.b + + In this case, args[0] == t1.a and args[1] == t2.b. + When creating range predicates for t2, + get_full_func_mm_tree_for_args(param, args[0], args[1]) + will return NULL because 'field' belongs to t1 and only + predicates that applies to t2 are of interest. In this case a + call to get_full_func_mm_tree_for_args() with reversed operands + may succeed. + */ + if (opt_arg_count) + { + if (opt_arg_count == 2) + { + ftree= get_full_func_mm_tree_for_args(param, opt_args[0], opt_args[1]); + } + else if (opt_arg_count == 3) + { + Field *f= ((Item_field *) opt_args[0])->field; + ftree= get_mm_parts(param, f, Item_func::GE_FUNC, opt_args[1]); + if (ftree) + { + ftree= tree_and(param, ftree, + get_mm_parts(param, f, + Item_func::LE_FUNC, opt_args[2])); + } + } + } + if (!ftree && + !(ftree= get_full_func_mm_tree_for_args(param, args[0], args[1])) && + !(ftree= get_full_func_mm_tree_for_args(param, args[1], args[0]))) + ftree= Item_func::get_mm_tree(param, cond_ptr); + DBUG_RETURN(ftree); +}; + + SEL_TREE *Item_func_between::get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index aa08420..51f6204 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4833,6 +4833,30 @@ is_local_field (Item *field) }

+static Item_field *get_local_field (Item *field) +{ + Item *ri= field->real_item(); + return (ri->type() == Item::FIELD_ITEM + && !(field->used_tables() & OUTER_REF_TABLE_BIT) + && !((Item_field *)ri)->get_depended_from()) ? (Item_field *) ri : 0; +} + + +static Item_field *field_in_sargable_func(Item *fn) +{ + fn= fn->real_item(); + + if (fn->type() == Item::FUNC_ITEM && + strcmp(((Item_func *)fn)->func_name(), "cast_as_date") == 0) + + { + Item_date_typecast *dt= (Item_date_typecast *) fn; + return get_local_field(dt->arguments()[0]); + } + return 0; +} + + /* In this and other functions, and_level is a number that is ever-growing and is different for the contents of every AND or OR clause. For example, @@ -5036,6 +5060,25 @@ Item_func_like::add_key_fields(JOIN *join, KEY_FIELD **key_fields, }

+bool Item_bool_rowready_func2::add_extra_key_fields(THD *thd, + JOIN *join, KEY_FIELD **key_fields, + uint *and_level, + table_map usable_tables, + SARGABLE_PARAM **sargables) +{ + Item_field *f; + if ((f= field_in_sargable_func(args[0])) && args[1]->const_item()) + { + opt_arg_count= 1; + opt_args[0]= f; + if (((Item_func *) args[0])->create_reverse_func( + functype(), thd, args[1], &opt_arg_count, opt_args+1)) + return true; + } + return false; +} + + void Item_bool_func2::add_key_fields_optimize_op(JOIN *join, KEY_FIELD **key_fields, uint *and_level, @@ -5043,19 +5086,28 @@ Item_bool_func2::add_key_fields_optimize_op(JOIN *join, KEY_FIELD **key_fields, SARGABLE_PARAM **sargables, bool equal_func) { + Item_field *f; /* If item is of type 'field op field/constant' add it to key_fields */ - if (is_local_field(args[0])) + if ((f= get_local_field(args[0]))) { - add_key_equal_fields(join, key_fields, *and_level, this, - (Item_field*) args[0]->real_item(), equal_func, + add_key_equal_fields(join, key_fields, *and_level, this, f, equal_func, args + 1, 1, usable_tables, sargables); } - if (is_local_field(args[1])) + else if ((f= get_local_field(args[1]))) { - add_key_equal_fields(join, key_fields, *and_level, this, - (Item_field*) args[1]->real_item(), equal_func, + add_key_equal_fields(join, key_fields, *and_level, this, f, equal_func, args, 1, usable_tables, sargables); } + if (opt_arg_count == 2) + { + add_key_equal_fields(join, key_fields, *and_level, this, opt_args[0], + equal_func, opt_args+1, 1, usable_tables, sargables); + } + else if (opt_arg_count == 3) + { + add_key_equal_fields(join, key_fields, *and_level, this, opt_args[0], + false, opt_args+1, 2, usable_tables, sargables); + } }

@@ -5521,8 +5573,14 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, if (cond) { KEY_FIELD *saved_field= field; + + if (cond->add_extra_key_fields(thd, join_tab->join, &end, &and_level, + normal_tables, sargables)) + DBUG_RETURN(TRUE); + cond->add_key_fields(join_tab->join, &end, &and_level, normal_tables, sargables); + for (; field != end ; field++) {

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog