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 > comm...@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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp