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

Alexey Botchkov holyfoot at askmonty.org
Tue Sep 20 12:22:19 EEST 2016


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++)
     {
 


More information about the commits mailing list