[Commits] a288cb6: MDEV-8320 Allow index usage for DATE(datetime_column) = const.

Sergey Petrunia sergey at mariadb.com
Tue Sep 20 18:45:43 EEST 2016


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 at 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




More information about the commits mailing list