maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09959
Re: [Commits] a288cb6: MDEV-8320 Allow index usage for DATE(datetime_column) = const.
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@xxxxxxxxxxx
> 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
