Add direct aggregates

Spider patches 026 (MDEV-7723), 031 (MDEV-7727) and 058 (MDEV-12532)

This allows the storage engine to internally compute sum and count
operations.

- Enhance sum items to be able to store the sum value directly.
- return_record_by_parent() is enabled in spider as
  HANDLER_HAS_DIRECT_AGGREGATE is defined
- Added spd_environ.h to spider. This is loaded first to ensure that all
  MariaDB specific defines that are used by include files are properly
  defined.
- This code is tested by the existing spider tests direct_aggregate.test
  and direct_aggregate_part.test and also partition.test

28 files changed, 379 insertions, 96 deletions

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index f398ef07d56..7a614a146a1 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -513,6 +513,9 @@ COUNT(*)
 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
 SUM(c)
 400
+SELECT SUM(c+0.0) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
+SUM(c+0.0)
+400.0
 ALTER TABLE t1 DROP INDEX b;
 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
 COUNT(*)
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 767c9a21119..2357b806a18 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -445,6 +445,7 @@ INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5),
 (1,19,1);
 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
+SELECT SUM(c+0.0) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
 ALTER TABLE t1 DROP INDEX b;
 SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
 SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index 6cfff8f60ed..099d0ad6c48 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -4212,7 +4212,6 @@ int ha_partition::write_row(uchar * buf)
   sql_mode_t saved_sql_mode= thd->variables.sql_mode;
   bool saved_auto_inc_field_not_null= table->auto_increment_field_not_null;
   DBUG_ENTER("ha_partition::write_row");
-  DBUG_ASSERT(buf == m_rec0);
 
   /*
     If we have an auto_increment column and we are writing a changed row
diff --git a/sql/handler.h b/sql/handler.h
index 97f46db5c44..43a28c1d473 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -3499,7 +3499,7 @@ public:
     return 0;
   }
   virtual void set_part_info(partition_info *part_info) {return;}
-  virtual void return_record_by_parent() {return;}
+  virtual void return_record_by_parent() { return; }
 
   virtual ulong index_flags(uint idx, uint part, bool all_parts) const =0;
 
diff --git a/sql/item.h b/sql/item.h
index 6f6a2060f90..d21fc86ea48 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5848,6 +5848,7 @@ public:
   }
 
   virtual void store(Item *item);
+  virtual Item *get_item() { return example; }
   virtual bool cache_value()= 0;
   bool basic_const_item() const
   { return MY_TEST(example && example->basic_const_item()); }
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 69db8b6b98b..9654f84d2bf 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1251,6 +1251,7 @@ Field *Item_sum_hybrid::create_tmp_field(bool group, TABLE *table)
 Item_sum_sum::Item_sum_sum(THD *thd, Item_sum_sum *item) 
   :Item_sum_num(thd, item),
    Type_handler_hybrid_field_type(item),
+   direct_added(FALSE), direct_reseted_field(FALSE),
    curr_dec_buff(item->curr_dec_buff),
    count(item->count)
 {
@@ -1270,6 +1271,15 @@ Item *Item_sum_sum::copy_or_same(THD* thd)
 }
 
 
+void Item_sum_sum::cleanup()
+{
+  DBUG_ENTER("Item_sum_sum::cleanup");
+  direct_added= direct_reseted_field= FALSE;
+  Item_sum_num::cleanup();
+  DBUG_VOID_RETURN;
+}
+
+
 void Item_sum_sum::clear()
 {
   DBUG_ENTER("Item_sum_sum::clear");
@@ -1319,6 +1329,38 @@ void Item_sum_sum::fix_length_and_dec()
 }
 
 
+void Item_sum_sum::direct_add(my_decimal *add_sum_decimal)
+{
+  DBUG_ENTER("Item_sum_sum::direct_add");
+  DBUG_PRINT("info", ("add_sum_decimal: %p", add_sum_decimal));
+  direct_added= TRUE;
+  direct_reseted_field= FALSE;
+  if (add_sum_decimal)
+  {
+    direct_sum_is_null= FALSE;
+    direct_sum_decimal= *add_sum_decimal;
+  }
+  else
+  {
+    direct_sum_is_null= TRUE;
+    direct_sum_decimal= decimal_zero;
+  }
+  DBUG_VOID_RETURN;
+}
+
+
+void Item_sum_sum::direct_add(double add_sum_real, bool add_sum_is_null)
+{
+  DBUG_ENTER("Item_sum_sum::direct_add");
+  DBUG_PRINT("info", ("add_sum_real: %f", add_sum_real));
+  direct_added= TRUE;
+  direct_reseted_field= FALSE;
+  direct_sum_is_null= add_sum_is_null;
+  direct_sum_real= add_sum_real;
+  DBUG_VOID_RETURN;
+}
+
+
 bool Item_sum_sum::add()
 {
   DBUG_ENTER("Item_sum_sum::add");
@@ -1332,50 +1374,84 @@ void Item_sum_sum::add_helper(bool perform_removal)
 
   if (result_type() == DECIMAL_RESULT)
   {
-    my_decimal value;
-    const my_decimal *val= aggr->arg_val_decimal(&value);
-    if (!aggr->arg_is_null(true))
+    if (unlikely(direct_added))
     {
-      if (perform_removal)
+      /* Add value stored by Item_sum_sum::direct_add */
+      DBUG_ASSERT(!perform_removal);
+
+      direct_added= FALSE;
+      if (likely(!direct_sum_is_null))
       {
-        if (count > 0)
+        my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1),
+                       &direct_sum_decimal, dec_buffs + curr_dec_buff);
+        curr_dec_buff^= 1;
+        null_value= 0;
+      }
+    }
+    else
+    {
+      direct_reseted_field= FALSE;
+      my_decimal value;
+      const my_decimal *val= aggr->arg_val_decimal(&value);
+      if (!aggr->arg_is_null(true))
+      {
+        if (perform_removal)
         {
-          my_decimal_sub(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1),
-                         dec_buffs + curr_dec_buff, val);
-          count--;
+          if (count > 0)
+          {
+            my_decimal_sub(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1),
+              dec_buffs + curr_dec_buff, val);
+            count--;
+          }
+          else
+            DBUG_VOID_RETURN;
         }
         else
-          DBUG_VOID_RETURN;
-      }
-      else
-      {
-        count++;
-        my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1),
-                       val, dec_buffs + curr_dec_buff);
+        {
+          count++;
+          my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1),
+            val, dec_buffs + curr_dec_buff);
+        }
+        curr_dec_buff^= 1;
+        null_value= (count > 0) ? 0 : 1;
       }
-      curr_dec_buff^= 1;
-      null_value= (count > 0) ? 0 : 1;
     }
   }
   else
   {
-    if (perform_removal && count > 0)
-      sum-= aggr->arg_val_real();
+    if (unlikely(direct_added))
+    {
+      /* Add value stored by Item_sum_sum::direct_add */
+      DBUG_ASSERT(!perform_removal);
+
+      direct_added= FALSE;
+      if (!direct_sum_is_null)
+      {
+        sum+= direct_sum_real;
+        null_value= 0;
+      }
+    }
     else
-      sum+= aggr->arg_val_real();
-    if (!aggr->arg_is_null(true))
     {
-      if (perform_removal)
+      direct_reseted_field= FALSE;
+      if (perform_removal && count > 0)
+        sum-= aggr->arg_val_real();
+      else
+        sum+= aggr->arg_val_real();
+      if (!aggr->arg_is_null(true))
       {
-        if (count > 0)
+        if (perform_removal)
         {
-          count--;
+          if (count > 0)
+          {
+            count--;
+          }
         }
-      }
-      else
-        count++;
+        else
+          count++;
 
-      null_value= (count > 0) ? 0 : 1;
+        null_value= (count > 0) ? 0 : 1;
+      }
     }
   }
   DBUG_VOID_RETURN;
@@ -1567,6 +1643,17 @@ Item *Item_sum_count::copy_or_same(THD* thd)
 }
 
 
+void Item_sum_count::direct_add(longlong add_count)
+{
+  DBUG_ENTER("Item_sum_count::direct_add");
+  DBUG_PRINT("info", ("add_count: %lld", add_count));
+  direct_counted= TRUE;
+  direct_reseted_field= FALSE;
+  direct_count= add_count;
+  DBUG_VOID_RETURN;
+}
+
+
 void Item_sum_count::clear()
 {
   DBUG_ENTER("Item_sum_count::clear");
@@ -1577,10 +1664,20 @@ void Item_sum_count::clear()
 
 bool Item_sum_count::add()
 {
-  if (aggr->arg_is_null(false))
-    return 0;
-  count++;
-  return 0;
+  DBUG_ENTER("Item_sum_count::add");
+  if (direct_counted)
+  {
+    direct_counted= FALSE;
+    count+= direct_count;
+  }
+  else
+  {
+    direct_reseted_field= FALSE;
+    if (aggr->arg_is_null(false))
+      DBUG_RETURN(0);
+    count++;
+  }
+  DBUG_RETURN(0);
 }
 
 
@@ -1611,6 +1708,8 @@ void Item_sum_count::cleanup()
 {
   DBUG_ENTER("Item_sum_count::cleanup");
   count= 0;
+  direct_counted= FALSE;
+  direct_reseted_field= FALSE;
   Item_sum_int::cleanup();
   DBUG_VOID_RETURN;
 }
@@ -2031,6 +2130,7 @@ void Item_sum_hybrid::clear()
   DBUG_VOID_RETURN;
 }
 
+
 bool
 Item_sum_hybrid::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
 {
@@ -2043,6 +2143,17 @@ Item_sum_hybrid::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
   return retval;
 }
 
+
+void Item_sum_hybrid::direct_add(Item *item)
+{
+  DBUG_ENTER("Item_sum_hybrid::direct_add");
+  DBUG_PRINT("info", ("item: %p", item));
+  direct_added= TRUE;
+  direct_item= item;
+  DBUG_VOID_RETURN;
+}
+
+
 double Item_sum_hybrid::val_real()
 {
   DBUG_ENTER("Item_sum_hybrid::val_real");
@@ -2147,8 +2258,17 @@ Item *Item_sum_min::copy_or_same(THD* thd)
 
 bool Item_sum_min::add()
 {
+  Item *tmp_item;
   DBUG_ENTER("Item_sum_min::add");
   DBUG_PRINT("enter", ("this: %p", this));
+
+  if (unlikely(direct_added))
+  {
+    /* Change to use direct_item */
+    tmp_item= arg_cache->get_item();
+    arg_cache->store(direct_item);
+  }
+  DBUG_PRINT("info", ("null_value: %s", null_value ? "TRUE" : "FALSE"));
   /* args[0] < value */
   arg_cache->cache_value();
   if (!arg_cache->null_value &&
@@ -2158,6 +2278,12 @@ bool Item_sum_min::add()
     value->cache_value();
     null_value= 0;
   }
+  if (unlikely(direct_added))
+  {
+    /* Restore original item */
+    direct_added= FALSE;
+    arg_cache->store(tmp_item);
+  }
   DBUG_RETURN(0);
 }
 
@@ -2172,8 +2298,16 @@ Item *Item_sum_max::copy_or_same(THD* thd)
 
 bool Item_sum_max::add()
 {
+  Item *tmp_item;
   DBUG_ENTER("Item_sum_max::add");
   DBUG_PRINT("enter", ("this: %p", this));
+
+  if (unlikely(direct_added))
+  {
+    /* Change to use direct_item */
+    tmp_item= arg_cache->get_item();
+    arg_cache->store(direct_item);
+  }
   /* args[0] > value */
   arg_cache->cache_value();
   DBUG_PRINT("info", ("null_value: %s", null_value ? "TRUE" : "FALSE"));
@@ -2184,6 +2318,12 @@ bool Item_sum_max::add()
     value->cache_value();
     null_value= 0;
   }
+  if (unlikely(direct_added))
+  {
+    /* Restore original item */
+    direct_added= FALSE;
+    arg_cache->store(tmp_item);
+  }
   DBUG_RETURN(0);
 }
 
@@ -2363,15 +2503,26 @@ void Item_sum_num::reset_field()
 
 void Item_sum_hybrid::reset_field()
 {
+  Item *tmp_item, *arg0;
   DBUG_ENTER("Item_sum_hybrid::reset_field");
+
+  arg0= args[0];
+  if (unlikely(direct_added))
+  {
+    /* Switch to use direct item */
+    tmp_item= value->get_item();
+    value->store(direct_item);
+    arg0= direct_item;
+  }
+
   switch(result_type()) {
   case STRING_RESULT:
   {
     char buff[MAX_FIELD_WIDTH];
     String tmp(buff,sizeof(buff),result_field->charset()),*res;
 
-    res=args[0]->val_str(&tmp);
-    if (args[0]->null_value)
+    res= arg0->val_str(&tmp);
+    if (arg0->null_value)
     {
       result_field->set_null();
       result_field->reset();
@@ -2385,11 +2536,11 @@ void Item_sum_hybrid::reset_field()
   }
   case INT_RESULT:
   {
-    longlong nr=args[0]->val_int();
+    longlong nr= arg0->val_int();
 
     if (maybe_null)
     {
-      if (args[0]->null_value)
+      if (arg0->null_value)
       {
 	nr=0;
 	result_field->set_null();
@@ -2403,11 +2554,11 @@ void Item_sum_hybrid::reset_field()
   }
   case REAL_RESULT:
   {
-    double nr= args[0]->val_real();
+    double nr= arg0->val_real();
 
     if (maybe_null)
     {
-      if (args[0]->null_value)
+      if (arg0->null_value)
       {
 	nr=0.0;
 	result_field->set_null();
@@ -2420,11 +2571,11 @@ void Item_sum_hybrid::reset_field()
   }
   case DECIMAL_RESULT:
   {
-    my_decimal value_buff, *arg_dec= args[0]->val_decimal(&value_buff);
+    my_decimal value_buff, *arg_dec= arg0->val_decimal(&value_buff);
 
     if (maybe_null)
     {
-      if (args[0]->null_value)
+      if (arg0->null_value)
         result_field->set_null();
       else
         result_field->set_notnull();
@@ -2442,27 +2593,49 @@ void Item_sum_hybrid::reset_field()
   case TIME_RESULT:
     DBUG_ASSERT(0);
   }
+
+  if (unlikely(direct_added))
+  {
+    direct_added= FALSE;
+    value->store(tmp_item);
+  }
   DBUG_VOID_RETURN;
 }
 
 
 void Item_sum_sum::reset_field()
 {
+  my_bool null_flag;
   DBUG_ASSERT (aggr->Aggrtype() != Aggregator::DISTINCT_AGGREGATOR);
   if (result_type() == DECIMAL_RESULT)
   {
-    my_decimal value, *arg_val= args[0]->val_decimal(&value);
-    if (!arg_val)                               // Null
-      arg_val= &decimal_zero;
+    my_decimal value, *arg_val;
+    if (unlikely(direct_added))
+      arg_val= &direct_sum_decimal;
+    else
+    {
+      if (!(arg_val= args[0]->val_decimal(&value)))
+        arg_val= &decimal_zero;                 // Null
+    }
     result_field->store_decimal(arg_val);
   }
   else
   {
     DBUG_ASSERT(result_type() == REAL_RESULT);
-    double nr= args[0]->val_real();			// Nulls also return 0
+    double nr= likely(!direct_added) ? args[0]->val_real() : direct_sum_real;
     float8store(result_field->ptr, nr);
   }
-  if (args[0]->null_value)
+
+  if (unlikely(direct_added))
+  {
+    direct_added= FALSE;
+    direct_reseted_field= TRUE;
+    null_flag= direct_sum_is_null;
+  }
+  else
+    null_flag= args[0]->null_value;
+
+  if (null_flag)
     result_field->set_null();
   else
     result_field->set_notnull();
@@ -2476,8 +2649,14 @@ void Item_sum_count::reset_field()
   longlong nr=0;
   DBUG_ASSERT (aggr->Aggrtype() != Aggregator::DISTINCT_AGGREGATOR);
 
-  if (!args[0]->maybe_null || !args[0]->is_null())
-    nr=1;
+  if (unlikely(direct_counted))
+  {
+    nr= direct_count;
+    direct_counted= FALSE;
+    direct_reseted_field= TRUE;
+  }
+  else if (!args[0]->maybe_null || !args[0]->is_null())
+    nr= 1;
   DBUG_PRINT("info", ("nr: %lld", nr));
   int8store(res,nr);
   DBUG_VOID_RETURN;
@@ -2548,13 +2727,26 @@ void Item_sum_sum::update_field()
   DBUG_ASSERT (aggr->Aggrtype() != Aggregator::DISTINCT_AGGREGATOR);
   if (result_type() == DECIMAL_RESULT)
   {
-    my_decimal value, *arg_val= args[0]->val_decimal(&value);
-    if (!args[0]->null_value)
+    my_decimal value, *arg_val;
+    my_bool null_flag;
+    if (unlikely(direct_added || direct_reseted_field))
+    {
+      direct_added= direct_reseted_field= FALSE;
+      arg_val= &direct_sum_decimal;
+      null_flag= direct_sum_is_null;
+    }
+    else
+    {
+      arg_val= args[0]->val_decimal(&value);
+      null_flag= args[0]->null_value;
+    }
+
+    if (!null_flag)
     {
       if (!result_field->is_null())
       {
-        my_decimal field_value,
-                   *field_val= result_field->val_decimal(&field_value);
+        my_decimal field_value;
+        my_decimal *field_val= result_field->val_decimal(&field_value);
         my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs, arg_val, field_val);
         result_field->store_decimal(dec_buffs);
       }
@@ -2568,11 +2760,22 @@ void Item_sum_sum::update_field()
   else
   {
     double old_nr,nr;
-    uchar *res=result_field->ptr;
+    uchar *res= result_field->ptr;
+    my_bool null_flag;
 
     float8get(old_nr,res);
-    nr= args[0]->val_real();
-    if (!args[0]->null_value)
+    if (unlikely(direct_added || direct_reseted_field))
+    {
+      direct_added= direct_reseted_field= FALSE;
+      null_flag= direct_sum_is_null;
+      nr= direct_sum_real;
+    }
+    else
+    {
+      nr= args[0]->val_real();
+      null_flag= args[0]->null_value;
+    }
+    if (!null_flag)
     {
       old_nr+=nr;
       result_field->set_notnull();
@@ -2589,8 +2792,14 @@ void Item_sum_count::update_field()
   uchar *res=result_field->ptr;
 
   nr=sint8korr(res);
-  if (!args[0]->maybe_null || !args[0]->is_null())
+  if (unlikely(direct_counted || direct_reseted_field))
+  {
+    direct_counted= direct_reseted_field= FALSE;
+    nr+= direct_count;
+  }
+  else if (!args[0]->maybe_null || !args[0]->is_null())
     nr++;
+  DBUG_PRINT("info", ("nr: %lld", nr));
   int8store(res,nr);
   DBUG_VOID_RETURN;
 }
@@ -2651,6 +2860,12 @@ Item *Item_sum_avg::result_item(THD *thd, Field *field)
 void Item_sum_hybrid::update_field()
 {
   DBUG_ENTER("Item_sum_hybrid::update_field");
+  Item *tmp_item;
+  if (unlikely(direct_added))
+  {
+    tmp_item= args[0];
+    args[0]= direct_item;
+  }
   switch (result_type()) {
   case STRING_RESULT:
     min_max_update_str_field();
@@ -2664,6 +2879,11 @@ void Item_sum_hybrid::update_field()
   default:
     min_max_update_real_field();
   }
+  if (unlikely(direct_added))
+  {
+    direct_added= FALSE;
+    args[0]= tmp_item;
+  }
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 9822f9e5430..d05fdbca5e1 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -521,6 +521,7 @@ public:
   Item *get_arg(uint i) const { return args[i]; }
   Item *set_arg(uint i, THD *thd, Item *new_val);
   uint get_arg_count() const { return arg_count; }
+  virtual Item **get_args() { return fixed ? orig_args : args; }
 
   /* Initialization of distinct related members */
   void init_aggregator()
@@ -757,14 +758,20 @@ class Item_sum_sum :public Item_sum_num,
                    public Type_handler_hybrid_field_type 
 {
 protected:
+  bool direct_added;
+  bool direct_reseted_field;
+  bool direct_sum_is_null;
+  double direct_sum_real;
   double sum;
+  my_decimal direct_sum_decimal;
   my_decimal dec_buffs[2];
   uint curr_dec_buff;
   void fix_length_and_dec();
 
 public:
   Item_sum_sum(THD *thd, Item *item_par, bool distinct):
-    Item_sum_num(thd, item_par)
+    Item_sum_num(thd, item_par), direct_added(FALSE),
+    direct_reseted_field(FALSE)
   {
     set_distinct(distinct);
   }
@@ -773,6 +780,9 @@ public:
   { 
     return has_with_distinct() ? SUM_DISTINCT_FUNC : SUM_FUNC; 
   }
+  void cleanup();
+  void direct_add(my_decimal *add_sum_decimal);
+  void direct_add(double add_sum_real, bool add_sum_is_null);
   void clear();
   bool add();
   double val_real();
@@ -808,6 +818,9 @@ private:
 
 class Item_sum_count :public Item_sum_int
 {
+  bool direct_counted;
+  bool direct_reseted_field;
+  longlong direct_count;
   longlong count;
 
   friend class Aggregator_distinct;
@@ -817,9 +830,10 @@ class Item_sum_count :public Item_sum_int
   void cleanup();
   void remove();
 
-  public:
+public:
   Item_sum_count(THD *thd, Item *item_par):
-    Item_sum_int(thd, item_par), count(0)
+    Item_sum_int(thd, item_par), direct_counted(FALSE),
+    direct_reseted_field(FALSE), count(0)
   {}
 
   /**
@@ -831,12 +845,14 @@ class Item_sum_count :public Item_sum_int
   */
 
   Item_sum_count(THD *thd, List<Item> &list):
-    Item_sum_int(thd, list), count(0)
+    Item_sum_int(thd, list), direct_counted(FALSE),
+    direct_reseted_field(FALSE), count(0)
   {
     set_distinct(TRUE);
   }
   Item_sum_count(THD *thd, Item_sum_count *item):
-    Item_sum_int(thd, item), count(item->count)
+    Item_sum_int(thd, item), direct_counted(FALSE),
+    direct_reseted_field(FALSE), count(item->count)
   {}
   enum Sumfunctype sum_func () const 
   { 
@@ -851,6 +867,7 @@ class Item_sum_count :public Item_sum_int
   longlong val_int();
   void reset_field();
   void update_field();
+  void direct_add(longlong add_count);
   const char *func_name() const 
   { 
     return has_with_distinct() ? "count(distinct " : "count(";
@@ -1009,6 +1026,8 @@ class Item_cache;
 class Item_sum_hybrid :public Item_sum, public Type_handler_hybrid_field_type
 {
 protected:
+  bool direct_added;
+  Item *direct_item;
   Item_cache *value, *arg_cache;
   Arg_comparator *cmp;
   int cmp_sign;
@@ -1019,19 +1038,20 @@ protected:
   Item_sum_hybrid(THD *thd, Item *item_par,int sign):
     Item_sum(thd, item_par),
     Type_handler_hybrid_field_type(&type_handler_longlong),
-    value(0), arg_cache(0), cmp(0),
+    direct_added(FALSE), value(0), arg_cache(0), cmp(0),
     cmp_sign(sign), was_values(TRUE)
   { collation.set(&my_charset_bin); }
   Item_sum_hybrid(THD *thd, Item_sum_hybrid *item)
     :Item_sum(thd, item),
     Type_handler_hybrid_field_type(item),
-    value(item->value), arg_cache(0),
+    direct_added(FALSE), value(item->value), arg_cache(0),
     cmp_sign(item->cmp_sign), was_values(item->was_values)
   { }
   bool fix_fields(THD *, Item **);
   void fix_length_and_dec();
   void setup_hybrid(THD *thd, Item *item, Item *value_arg);
   void clear();
+  void direct_add(Item *item);
   double val_real();
   longlong val_int();
   my_decimal *val_decimal(my_decimal *);
diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc
index 385ca6937e7..ab8ea470d92 100644
--- a/storage/spider/ha_spider.cc
+++ b/storage/spider/ha_spider.cc
@@ -20,6 +20,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h
index 4903c8a25fe..a28d98264dc 100644
--- a/storage/spider/ha_spider.h
+++ b/storage/spider/ha_spider.h
@@ -17,18 +17,7 @@
 #pragma interface
 #endif
 
-#if (defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100000)
-#define SPIDER_HANDLER_START_BULK_INSERT_HAS_FLAGS
-#endif
-
-#if MYSQL_VERSION_ID >=	100203
-#define HANDLER_HAS_TOP_TABLE_FIELDS
-#define PARTITION_HAS_EXTRA_ATTACH_CHILDREN
-#define PARTITION_HAS_GET_CHILD_HANDLERS
-#define PARTITION_HAS_EXTRA_ATTACH_CHILDREN
-#define PARTITION_HAS_GET_CHILD_HANDLERS
-#define HA_EXTRA_HAS_STARTING_ORDERED_INDEX_SCAN
-#endif
+#include "spd_environ.h"
 
 #define SPIDER_CONNECT_INFO_MAX_LEN 64
 #define SPIDER_CONNECT_INFO_PATH_MAX_LEN FN_REFLEN
diff --git a/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result b/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result
index 9a8660ba79e..af06349c65a 100644
--- a/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result
+++ b/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result
@@ -54,31 +54,31 @@ COUNT(*)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	1
 SELECT MAX(a) FROM ta_l;
 MAX(a)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	2
 SELECT MIN(a) FROM ta_l;
 MIN(a)
 1
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	3
 SELECT MAX(a) FROM ta_l WHERE a < 5;
 MAX(a)
 4
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	4
 SELECT MIN(a) FROM ta_l WHERE a > 1;
 MIN(a)
 2
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	5
 
 deinit
 connection master_1;
diff --git a/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result b/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result
index 760b39e16d5..02cdc033a88 100644
--- a/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result
+++ b/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result
@@ -44,31 +44,31 @@ COUNT(*)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	2
 SELECT MAX(a) FROM ta_l2;
 MAX(a)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	4
 SELECT MIN(a) FROM ta_l2;
 MIN(a)
 1
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	6
 SELECT MAX(a) FROM ta_l2 WHERE a < 5;
 MAX(a)
 4
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	8
 SELECT MIN(a) FROM ta_l2 WHERE a > 1;
 MIN(a)
 2
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	10
 
 deinit
 connection master_1;
diff --git a/storage/spider/mysql-test/spider/r/direct_aggregate.result b/storage/spider/mysql-test/spider/r/direct_aggregate.result
index 9a8660ba79e..af06349c65a 100644
--- a/storage/spider/mysql-test/spider/r/direct_aggregate.result
+++ b/storage/spider/mysql-test/spider/r/direct_aggregate.result
@@ -54,31 +54,31 @@ COUNT(*)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	1
 SELECT MAX(a) FROM ta_l;
 MAX(a)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	2
 SELECT MIN(a) FROM ta_l;
 MIN(a)
 1
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	3
 SELECT MAX(a) FROM ta_l WHERE a < 5;
 MAX(a)
 4
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	4
 SELECT MIN(a) FROM ta_l WHERE a > 1;
 MIN(a)
 2
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	5
 
 deinit
 connection master_1;
diff --git a/storage/spider/mysql-test/spider/r/direct_aggregate_part.result b/storage/spider/mysql-test/spider/r/direct_aggregate_part.result
index 760b39e16d5..02cdc033a88 100644
--- a/storage/spider/mysql-test/spider/r/direct_aggregate_part.result
+++ b/storage/spider/mysql-test/spider/r/direct_aggregate_part.result
@@ -44,31 +44,31 @@ COUNT(*)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	2
 SELECT MAX(a) FROM ta_l2;
 MAX(a)
 5
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	4
 SELECT MIN(a) FROM ta_l2;
 MIN(a)
 1
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	6
 SELECT MAX(a) FROM ta_l2 WHERE a < 5;
 MAX(a)
 4
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	8
 SELECT MIN(a) FROM ta_l2 WHERE a > 1;
 MIN(a)
 2
 SHOW STATUS LIKE 'Spider_direct_aggregate';
 Variable_name	Value
-Spider_direct_aggregate	0
+Spider_direct_aggregate	10
 
 deinit
 connection master_1;
diff --git a/storage/spider/spd_conn.cc b/storage/spider/spd_conn.cc
index 01ed4a19ee0..e41fb33af5d 100644
--- a/storage/spider/spd_conn.cc
+++ b/storage/spider/spd_conn.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_copy_tables.cc b/storage/spider/spd_copy_tables.cc
index 58ec690bd8d..36db8a9f7af 100644
--- a/storage/spider/spd_copy_tables.cc
+++ b/storage/spider/spd_copy_tables.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc
index ab10ddcab86..8c27abe3f28 100644
--- a/storage/spider/spd_db_conn.cc
+++ b/storage/spider/spd_db_conn.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_db_handlersocket.cc b/storage/spider/spd_db_handlersocket.cc
index 5ffa00f5fe6..6e4a5dcc5ec 100644
--- a/storage/spider/spd_db_handlersocket.cc
+++ b/storage/spider/spd_db_handlersocket.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 4e2a41e9fa6..6dee59a10a5 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_db_oracle.cc b/storage/spider/spd_db_oracle.cc
index 3e0b96c8492..45e40661760 100644
--- a/storage/spider/spd_db_oracle.cc
+++ b/storage/spider/spd_db_oracle.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_direct_sql.cc b/storage/spider/spd_direct_sql.cc
index 1aa532e500b..0ef19673e89 100644
--- a/storage/spider/spd_direct_sql.cc
+++ b/storage/spider/spd_direct_sql.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_environ.h b/storage/spider/spd_environ.h
new file mode 100644
index 00000000000..f5141a7e3b4
--- /dev/null
+++ b/storage/spider/spd_environ.h
@@ -0,0 +1,35 @@
+/* Copyright (C) 2008-2015 Kentoku Shiba & 2017 MariaDB corp
+
+  This program is free software; you can redistribute it and/or modify
+  it under the terms of the GNU General Public License as published by
+  the Free Software Foundation; version 2 of the License.
+
+  This program is distributed in the hope that it will be useful,
+  but WITHOUT ANY WARRANTY; without even the implied warranty of
+  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+  GNU General Public License for more details.
+
+  You should have received a copy of the GNU General Public License
+  along with this program; if not, write to the Free Software
+  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+
+/*
+  Define functinolity offered by MySQL or MariaDB
+*/
+
+#ifndef SPD_ENVIRON_INCLUDED
+
+#if (defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100000)
+#define SPIDER_HANDLER_START_BULK_INSERT_HAS_FLAGS
+#endif
+
+#if MYSQL_VERSION_ID >=	100204
+#define HANDLER_HAS_TOP_TABLE_FIELDS
+#define HANDLER_HAS_DIRECT_AGGREGATE
+#define PARTITION_HAS_EXTRA_ATTACH_CHILDREN
+#define PARTITION_HAS_GET_CHILD_HANDLERS
+#define PARTITION_HAS_EXTRA_ATTACH_CHILDREN
+#define PARTITION_HAS_GET_CHILD_HANDLERS
+#define HA_EXTRA_HAS_STARTING_ORDERED_INDEX_SCAN
+#endif
+#endif /* SPD_ENVIRON_INCLUDED */
diff --git a/storage/spider/spd_i_s.cc b/storage/spider/spd_i_s.cc
index a9bdab638a1..f9fb5e17b2a 100644
--- a/storage/spider/spd_i_s.cc
+++ b/storage/spider/spd_i_s.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_malloc.cc b/storage/spider/spd_malloc.cc
index acf32e48966..4135cd9beb2 100644
--- a/storage/spider/spd_malloc.cc
+++ b/storage/spider/spd_malloc.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc
index 390dec43a16..c419e05a459 100644
--- a/storage/spider/spd_param.cc
+++ b/storage/spider/spd_param.cc
@@ -17,6 +17,7 @@
 #include <my_global.h>
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_ping_table.cc b/storage/spider/spd_ping_table.cc
index a4d731bb0a5..c4b8588ad7c 100644
--- a/storage/spider/spd_ping_table.cc
+++ b/storage/spider/spd_ping_table.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_sys_table.cc b/storage/spider/spd_sys_table.cc
index 54af725bb64..4ebbd3b0a13 100644
--- a/storage/spider/spd_sys_table.cc
+++ b/storage/spider/spd_sys_table.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index 64bfce88d1c..802acd3996f 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>
diff --git a/storage/spider/spd_trx.cc b/storage/spider/spd_trx.cc
index 1b47c8ccf3c..1a0f34eaa56 100644
--- a/storage/spider/spd_trx.cc
+++ b/storage/spider/spd_trx.cc
@@ -16,6 +16,7 @@
 #define MYSQL_SERVER 1
 #include <my_global.h>
 #include "mysql_version.h"
+#include "spd_environ.h"
 #if MYSQL_VERSION_ID < 50500
 #include "mysql_priv.h"
 #include <mysql/plugin.h>