add some direct aggregate feature.

13 files changed, 1181 insertions, 37 deletions

diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc
index c069ddfb770..3e56cda98b7 100644
--- a/storage/spider/ha_spider.cc
+++ b/storage/spider/ha_spider.cc
@@ -126,6 +126,10 @@ ha_spider::ha_spider(
   init_ha_mem_root = FALSE;
 */
 #endif
+  prev_index_rnd_init = SPD_NONE;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  direct_aggregate_item_first = NULL;
+#endif
   result_link_idx = 0;
   result_list.have_sql_kind_backup = FALSE;
   result_list.sqls = NULL;
@@ -144,6 +148,9 @@ ha_spider::ha_spider(
   result_list.set_split_read = FALSE;
   result_list.insert_dup_update_pushdown = FALSE;
   result_list.tmp_pos_row_first = NULL;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  result_list.direct_aggregate = FALSE;
+#endif
   DBUG_VOID_RETURN;
 }
 
@@ -220,6 +227,10 @@ ha_spider::ha_spider(
   init_ha_mem_root = FALSE;
 */
 #endif
+  prev_index_rnd_init = SPD_NONE;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  direct_aggregate_item_first = NULL;
+#endif
   result_link_idx = 0;
   result_list.have_sql_kind_backup = FALSE;
   result_list.sqls = NULL;
@@ -238,6 +249,9 @@ ha_spider::ha_spider(
   result_list.set_split_read = FALSE;
   result_list.insert_dup_update_pushdown = FALSE;
   result_list.tmp_pos_row_first = NULL;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  result_list.direct_aggregate = FALSE;
+#endif
   ref_length = sizeof(SPIDER_POSITION);
   DBUG_VOID_RETURN;
 }
@@ -641,6 +655,18 @@ int ha_spider::close()
     } while (bulk_access_link_first);
   }
 #endif
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  while (direct_aggregate_item_first)
+  {
+    direct_aggregate_item_current = direct_aggregate_item_first->next;
+    if (direct_aggregate_item_first->item)
+    {
+      delete direct_aggregate_item_first->item;
+    }
+    spider_free(spider_current_trx, direct_aggregate_item_first, MYF(0));
+    direct_aggregate_item_first = direct_aggregate_item_current;
+  }
+#endif
   if (is_clone)
   {
     for (roop_count = 0; roop_count < (int) share->link_count; roop_count++)
@@ -1539,6 +1565,19 @@ int ha_spider::reset()
     } while (bulk_access_link_first);
   }
 #endif
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  direct_aggregate_item_current = direct_aggregate_item_first;
+  while (direct_aggregate_item_current)
+  {
+    if (direct_aggregate_item_current->item)
+    {
+      delete direct_aggregate_item_current->item;
+      direct_aggregate_item_current->item = NULL;
+    }
+    direct_aggregate_item_current = direct_aggregate_item_current->next;
+  }
+  result_list.direct_aggregate = FALSE;
+#endif
   store_error_num = 0;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (
@@ -1714,6 +1753,7 @@ int ha_spider::reset()
 #ifdef INFO_KIND_FORCE_LIMIT_BEGIN
   info_limit = 9223372036854775807LL;
 #endif
+  prev_index_rnd_init = SPD_NONE;
   result_list.have_sql_kind_backup = FALSE;
   result_list.direct_order_limit = FALSE;
 #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET)
@@ -1861,6 +1901,7 @@ int ha_spider::index_init(
     DBUG_RETURN(error_num);
 #endif
   result_list.check_direct_order_limit = FALSE;
+  prev_index_rnd_init = SPD_INDEX;
   DBUG_RETURN(0);
 }
 
@@ -5136,6 +5177,10 @@ int ha_spider::read_multi_range_first_internal(
           DBUG_RETURN(error_num);
         result_list.use_union = TRUE;
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+        bool direct_aggregate_backup = result_list.direct_aggregate;
+        result_list.direct_aggregate = FALSE;
+#endif
         if (result_list.direct_order_limit)
         {
           if ((error_num =
@@ -5147,6 +5192,9 @@ int ha_spider::read_multi_range_first_internal(
             NULL, 0, SPIDER_SQL_TYPE_SELECT_SQL)))
             DBUG_RETURN(error_num);
         }
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+        result_list.direct_aggregate = direct_aggregate_backup;
+#endif
         if ((error_num = append_limit_sql_part(
           result_list.internal_offset,
           result_list.limit_num,
@@ -6486,6 +6534,10 @@ int ha_spider::read_multi_range_next(
           DBUG_RETURN(error_num);
         result_list.use_union = TRUE;
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+        bool direct_aggregate_backup = result_list.direct_aggregate;
+        result_list.direct_aggregate = FALSE;
+#endif
         if (result_list.direct_order_limit)
         {
           if ((error_num =
@@ -6497,6 +6549,9 @@ int ha_spider::read_multi_range_next(
             NULL, 0, SPIDER_SQL_TYPE_SELECT_SQL)))
             DBUG_RETURN(error_num);
         }
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+        result_list.direct_aggregate = direct_aggregate_backup;
+#endif
         if ((error_num = append_limit_sql_part(
           result_list.internal_offset,
           result_list.limit_num,
@@ -6882,6 +6937,7 @@ int ha_spider::rnd_init(
   int error_num;
   DBUG_ENTER("ha_spider::rnd_init");
   DBUG_PRINT("info",("spider this=%p", this));
+  DBUG_PRINT("info",("spider scan=%s", scan ? "TRUE" : "FALSE"));
   pushed_pos = NULL;
   if (result_list.lock_type == F_WRLCK)
     check_and_start_bulk_update(SPD_BU_START_BY_INDEX_OR_RND_INIT);
@@ -6895,7 +6951,8 @@ int ha_spider::rnd_init(
     pk_update = FALSE;
     if (
       result_list.current &&
-      !result_list.low_mem_read
+      !result_list.low_mem_read &&
+      prev_index_rnd_init == SPD_RND
     ) {
       result_list.current = result_list.first;
       spider_db_set_pos_to_first_row(&result_list);
@@ -6978,6 +7035,7 @@ int ha_spider::rnd_init(
       result_list.check_direct_order_limit = FALSE;
     }
   }
+  prev_index_rnd_init = SPD_RND;
   DBUG_RETURN(0);
 }
 
@@ -10708,6 +10766,16 @@ int ha_spider::info_push(
   DBUG_RETURN(error_num);
 }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+void ha_spider::return_record_by_parent()
+{
+  DBUG_ENTER("ha_spider::return_record_by_parent");
+  DBUG_PRINT("info",("spider this=%p", this));
+  spider_db_refetch_for_item_sum_funcs(this);
+  DBUG_VOID_RETURN;
+}
+#endif
+
 TABLE *ha_spider::get_table()
 {
   DBUG_ENTER("ha_spider::get_table");
@@ -12920,6 +12988,32 @@ int ha_spider::append_condition_sql_part(
   DBUG_RETURN(0);
 }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+int ha_spider::append_sum_select_sql_part(
+  ulong sql_type,
+  const char *alias,
+  uint alias_length
+) {
+  int error_num;
+  uint roop_count, dbton_id;
+  spider_db_handler *dbton_hdl;
+  DBUG_ENTER("ha_spider::append_sum_select_sql_part");
+  for (roop_count = 0; roop_count < share->use_sql_dbton_count; roop_count++)
+  {
+    dbton_id = share->use_sql_dbton_ids[roop_count];
+    dbton_hdl = dbton_handler[dbton_id];
+    if (
+      dbton_hdl->first_link_idx >= 0 &&
+      (error_num = dbton_hdl->append_sum_select_part(sql_type,
+        alias, alias_length))
+    ) {
+      DBUG_RETURN(error_num);
+    }
+  }
+  DBUG_RETURN(0);
+}
+#endif
+
 int ha_spider::append_match_select_sql_part(
   ulong sql_type,
   const char *alias,
diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h
index 50ddeb1c050..4d1f6067bdc 100644
--- a/storage/spider/ha_spider.h
+++ b/storage/spider/ha_spider.h
@@ -227,6 +227,11 @@ public:
 #ifdef INFO_KIND_FORCE_LIMIT_BEGIN
   longlong           info_limit;
 #endif
+  spider_index_rnd_init prev_index_rnd_init;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  SPIDER_ITEM_HLD    *direct_aggregate_item_first;
+  SPIDER_ITEM_HLD    *direct_aggregate_item_current;
+#endif
 
   /* for fulltext search */
   bool               ft_init_and_first;
@@ -697,6 +702,9 @@ public:
     uint info_type,
     void *info
   );
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  void return_record_by_parent();
+#endif
   TABLE *get_table();
   void set_ft_discard_bitmap();
   void set_searched_bitmap();
@@ -918,6 +926,13 @@ public:
     ulong sql_type,
     bool test_flg
   );
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  int append_sum_select_sql_part(
+    ulong sql_type,
+    const char *alias,
+    uint alias_length
+  );
+#endif
   int append_match_select_sql_part(
     ulong sql_type,
     const char *alias,
diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc
index 9eeab73bb05..a582fb4c270 100644
--- a/storage/spider/spd_db_conn.cc
+++ b/storage/spider/spd_db_conn.cc
@@ -26,6 +26,9 @@
 #include "sql_analyse.h"
 #include "sql_base.h"
 #include "tztime.h"
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+#include "sql_select.h"
+#endif
 #endif
 #include "sql_common.h"
 #include <errmsg.h>
@@ -1365,6 +1368,14 @@ int spider_db_append_select_columns(
   DBUG_ENTER("spider_db_append_select_columns");
   if (spider->sql_kinds & SPIDER_SQL_KIND_SQL)
   {
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+    if (
+      result_list->direct_aggregate &&
+      (error_num = spider->append_sum_select_sql_part(
+        SPIDER_SQL_TYPE_SELECT_SQL, NULL, 0))
+    )
+      DBUG_RETURN(error_num);
+#endif
     if ((error_num = spider->append_match_select_sql_part(
       SPIDER_SQL_TYPE_SELECT_SQL, NULL, 0)))
       DBUG_RETURN(error_num);
@@ -2359,6 +2370,163 @@ int spider_db_append_key_where(
   DBUG_RETURN(0);
 }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+int spider_db_refetch_for_item_sum_funcs(
+  ha_spider *spider
+) {
+  int error_num;
+  SPIDER_RESULT_LIST *result_list = &spider->result_list;
+  DBUG_ENTER("spider_db_refetch_for_item_sum_funcs");
+  if (result_list->snap_direct_aggregate)
+  {
+    SPIDER_DB_ROW *row = result_list->snap_row;
+    row->first();
+    if (result_list->snap_mrr_with_cnt)
+    {
+      row->next();
+    }
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+  DBUG_RETURN(0);
+}
+
+int spider_db_fetch_for_item_sum_funcs(
+  SPIDER_DB_ROW *row,
+  ha_spider *spider
+) {
+  int error_num;
+  st_select_lex *select_lex;
+  DBUG_ENTER("spider_db_fetch_for_item_sum_funcs");
+  select_lex = spider_get_select_lex(spider);
+  JOIN *join = select_lex->join;
+  Item_sum **item_sum_ptr;
+  spider->direct_aggregate_item_current = NULL;
+  for (item_sum_ptr = join->sum_funcs; *item_sum_ptr; ++item_sum_ptr)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_func(row, *item_sum_ptr,
+      spider)))
+      DBUG_RETURN(error_num);
+  }
+  DBUG_RETURN(0);
+}
+
+int spider_db_fetch_for_item_sum_func(
+  SPIDER_DB_ROW *row,
+  Item_sum *item_sum,
+  ha_spider *spider
+) {
+  int error_num;
+  SPIDER_SHARE *share = spider->share;
+  DBUG_ENTER("spider_db_fetch_for_item_sum_func");
+  DBUG_PRINT("info",("spider Sumfunctype = %d", item_sum->sum_func()));
+  switch (item_sum->sum_func())
+  {
+    case Item_sum::COUNT_FUNC:
+      {
+        Item_sum_count *item_sum_count = (Item_sum_count *) item_sum;
+        if (!row->is_null())
+          item_sum_count->direct_add(row->val_int());
+        else
+          DBUG_RETURN(ER_SPIDER_UNKNOWN_NUM);
+        row->next();
+      }
+      break;
+    case Item_sum::SUM_FUNC:
+      {
+        Item_sum_sum *item_sum_sum = (Item_sum_sum *) item_sum;
+        if (item_sum_sum->result_type() == DECIMAL_RESULT)
+        {
+          my_decimal decimal_value;
+          item_sum_sum->direct_add(row->val_decimal(&decimal_value,
+            share->access_charset));
+        } else {
+          item_sum_sum->direct_add(row->val_real(), row->is_null());
+        }
+        row->next();
+      }
+      break;
+    case Item_sum::MIN_FUNC:
+    case Item_sum::MAX_FUNC:
+      {
+        if (!spider->direct_aggregate_item_current)
+        {
+          if (!spider->direct_aggregate_item_first)
+          {
+            if (!spider_bulk_malloc(spider_current_trx, 240, MYF(MY_WME),
+              &spider->direct_aggregate_item_first, sizeof(SPIDER_ITEM_HLD),
+              NullS)
+            ) {
+              DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+            }
+            spider->direct_aggregate_item_first->next = NULL;
+            spider->direct_aggregate_item_first->item = NULL;
+            spider->direct_aggregate_item_first->tgt_num = 0;
+          }
+          spider->direct_aggregate_item_current =
+            spider->direct_aggregate_item_first;
+        } else {
+          if (!spider->direct_aggregate_item_current->next)
+          {
+            if (!spider_bulk_malloc(spider_current_trx, 241, MYF(MY_WME),
+              &spider->direct_aggregate_item_current->next,
+              sizeof(SPIDER_ITEM_HLD), NullS)
+            ) {
+              DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+            }
+            spider->direct_aggregate_item_current->next->next = NULL;
+            spider->direct_aggregate_item_current->next->item = NULL;
+            spider->direct_aggregate_item_current->next->tgt_num =
+              spider->direct_aggregate_item_current->tgt_num + 1;
+          }
+          spider->direct_aggregate_item_current =
+            spider->direct_aggregate_item_current->next;
+        }
+        if (!spider->direct_aggregate_item_current->item)
+        {
+          spider->direct_aggregate_item_current->item =
+            new Item_string(share->access_charset);
+          if (!spider->direct_aggregate_item_current->item)
+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);
+        }
+
+        Item_sum_hybrid *item_hybrid = (Item_sum_hybrid *) item_sum;
+        Item_string *item =
+          (Item_string *) spider->direct_aggregate_item_current->item;
+        if (row->is_null())
+        {
+          item->set_str_with_copy(NULL, 0);
+          item->null_value = TRUE;
+        } else {
+          char buf[MAX_FIELD_WIDTH];
+          spider_string tmp_str(buf, MAX_FIELD_WIDTH, share->access_charset);
+          tmp_str.init_calc_mem(242);
+          tmp_str.length(0);
+          if ((error_num = row->append_to_str(&tmp_str)))
+            DBUG_RETURN(error_num);
+          item->set_str_with_copy(tmp_str.ptr(), tmp_str.length());
+          item->null_value = FALSE;
+        }
+        item_hybrid->direct_add(item);
+        row->next();
+      }
+      break;
+    case Item_sum::COUNT_DISTINCT_FUNC:
+    case Item_sum::SUM_DISTINCT_FUNC:
+    case Item_sum::AVG_FUNC:
+    case Item_sum::AVG_DISTINCT_FUNC:
+    case Item_sum::STD_FUNC:
+    case Item_sum::VARIANCE_FUNC:
+    case Item_sum::SUM_BIT_FUNC:
+    case Item_sum::UDF_SUM_FUNC:
+    case Item_sum::GROUP_CONCAT_FUNC:
+    default:
+      DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
+  }
+  DBUG_RETURN(0);
+}
+#endif
+
 int spider_db_append_match_fetch(
   ha_spider *spider,
   st_spider_ft_info *ft_first,
@@ -2556,6 +2724,12 @@ int spider_db_fetch_table(
       }
     }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+    result_list->snap_mrr_with_cnt = spider->mrr_with_cnt;
+    result_list->snap_direct_aggregate = result_list->direct_aggregate;
+    result_list->snap_row = row;
+#endif
+
     /* for mrr */
     if (spider->mrr_with_cnt)
     {
@@ -2567,10 +2741,23 @@ int spider_db_fetch_table(
         else
           DBUG_RETURN(ER_SPIDER_UNKNOWN_NUM);
         row->next();
-      } else
+      } else {
         spider->multi_range_hit_point = 0;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+        result_list->snap_mrr_with_cnt = FALSE;
+#endif
+      }
     }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+    /* for direct_aggregate */
+    if (result_list->direct_aggregate)
+    {
+      if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+        DBUG_RETURN(error_num);
+    }
+#endif
+
     if ((error_num = spider_db_append_match_fetch(spider,
       spider->ft_first, spider->ft_current, row)))
       DBUG_RETURN(error_num);
@@ -2717,6 +2904,12 @@ int spider_db_fetch_key(
     }
   }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  result_list->snap_mrr_with_cnt = spider->mrr_with_cnt;
+  result_list->snap_direct_aggregate = result_list->direct_aggregate;
+  result_list->snap_row = row;
+#endif
+
   /* for mrr */
   if (spider->mrr_with_cnt)
   {
@@ -2727,6 +2920,16 @@ int spider_db_fetch_key(
       DBUG_RETURN(ER_SPIDER_UNKNOWN_NUM);
     row->next();
   }
+
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  /* for direct_aggregate */
+  if (result_list->direct_aggregate)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+#endif
+
   if ((error_num = spider_db_append_match_fetch(spider,
     spider->ft_first, spider->ft_current, row)))
     DBUG_RETURN(error_num);
@@ -2798,6 +3001,12 @@ int spider_db_fetch_minimum_columns(
     }
   }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  result_list->snap_mrr_with_cnt = spider->mrr_with_cnt;
+  result_list->snap_direct_aggregate = result_list->direct_aggregate;
+  result_list->snap_row = row;
+#endif
+
   /* for mrr */
   if (spider->mrr_with_cnt)
   {
@@ -2808,6 +3017,16 @@ int spider_db_fetch_minimum_columns(
       DBUG_RETURN(ER_SPIDER_UNKNOWN_NUM);
     row->next();
   }
+
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  /* for direct_aggregate */
+  if (result_list->direct_aggregate)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+#endif
+
   if ((error_num = spider_db_append_match_fetch(spider,
     spider->ft_first, spider->ft_current, row)))
     DBUG_RETURN(error_num);
@@ -4527,6 +4746,9 @@ void spider_db_create_position(
   current->use_position = TRUE;
   pos->use_position = TRUE;
   pos->mrr_with_cnt = spider->mrr_with_cnt;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  pos->direct_aggregate = result_list->direct_aggregate;
+#endif
   pos->sql_kind = spider->sql_kind[spider->result_link_idx];
   pos->position_bitmap = spider->position_bitmap;
   pos->ft_first = spider->ft_first;
@@ -4591,6 +4813,12 @@ int spider_db_seek_tmp_table(
 */
   }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  spider->result_list.snap_mrr_with_cnt = pos->mrr_with_cnt;
+  spider->result_list.snap_direct_aggregate = pos->direct_aggregate;
+  spider->result_list.snap_row = row;
+#endif
+
   /* for mrr */
   if (pos->mrr_with_cnt)
   {
@@ -4598,8 +4826,22 @@ int spider_db_seek_tmp_table(
     if (pos->sql_kind == SPIDER_SQL_KIND_SQL)
     {
       row->next();
+    } else {
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+      spider->result_list.snap_mrr_with_cnt = FALSE;
+#endif
     }
   }
+
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  /* for direct_aggregate */
+  if (pos->direct_aggregate)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+#endif
+
   if ((error_num = spider_db_append_match_fetch(spider,
     pos->ft_first, pos->ft_current, row)))
     DBUG_RETURN(error_num);
@@ -4656,12 +4898,28 @@ int spider_db_seek_tmp_key(
 */
   }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  spider->result_list.snap_mrr_with_cnt = pos->mrr_with_cnt;
+  spider->result_list.snap_direct_aggregate = pos->direct_aggregate;
+  spider->result_list.snap_row = row;
+#endif
+
   /* for mrr */
   if (pos->mrr_with_cnt)
   {
     DBUG_PRINT("info", ("spider mrr_with_cnt"));
     row->next();
   }
+
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  /* for direct_aggregate */
+  if (pos->direct_aggregate)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+#endif
+
   if ((error_num = spider_db_append_match_fetch(spider,
     pos->ft_first, pos->ft_current, row)))
     DBUG_RETURN(error_num);
@@ -4718,12 +4976,28 @@ int spider_db_seek_tmp_minimum_columns(
 */
   }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  spider->result_list.snap_mrr_with_cnt = pos->mrr_with_cnt;
+  spider->result_list.snap_direct_aggregate = pos->direct_aggregate;
+  spider->result_list.snap_row = row;
+#endif
+
   /* for mrr */
   if (pos->mrr_with_cnt)
   {
     DBUG_PRINT("info", ("spider mrr_with_cnt"));
     row->next();
   }
+
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  /* for direct_aggregate */
+  if (pos->direct_aggregate)
+  {
+    if ((error_num = spider_db_fetch_for_item_sum_funcs(row, spider)))
+      DBUG_RETURN(error_num);
+  }
+#endif
+
   if ((error_num = spider_db_append_match_fetch(spider,
     pos->ft_first, pos->ft_current, row)))
     DBUG_RETURN(error_num);
@@ -7241,6 +7515,11 @@ int spider_db_print_item_type(
     case Item::FUNC_ITEM:
       DBUG_RETURN(spider_db_open_item_func((Item_func *) item, spider, str,
         alias, alias_length, dbton_id));
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+    case Item::SUM_FUNC_ITEM:
+      DBUG_RETURN(spider_db_open_item_sum_func((Item_sum *)item, spider, str,
+        alias, alias_length, dbton_id));
+#endif
     case Item::COND_ITEM:
       DBUG_RETURN(spider_db_open_item_cond((Item_cond *) item, spider, str,
         alias, alias_length, dbton_id));
@@ -7388,6 +7667,21 @@ int spider_db_open_item_func(
     item_func, spider, str, alias, alias_length));
 }
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+int spider_db_open_item_sum_func(
+  Item_sum *item_sum,
+  ha_spider *spider,
+  spider_string *str,
+  const char *alias,
+  uint alias_length,
+  uint dbton_id
+) {
+  DBUG_ENTER("spider_db_open_item_func");
+  DBUG_RETURN(spider_dbton[dbton_id].db_util->open_item_sum_func(
+    item_sum, spider, str, alias, alias_length));
+}
+#endif
+
 int spider_db_open_item_ident(
   Item_ident *item_ident,
   ha_spider *spider,
diff --git a/storage/spider/spd_db_conn.h b/storage/spider/spd_db_conn.h
index d819255394d..3a5cc1b6181 100644
--- a/storage/spider/spd_db_conn.h
+++ b/storage/spider/spd_db_conn.h
@@ -421,6 +421,23 @@ int spider_db_append_key_where(
   ha_spider *spider
 );
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+int spider_db_refetch_for_item_sum_funcs(
+  ha_spider *spider
+);
+
+int spider_db_fetch_for_item_sum_funcs(
+  SPIDER_DB_ROW *row,
+  ha_spider *spider
+);
+
+int spider_db_fetch_for_item_sum_func(
+  SPIDER_DB_ROW *row,
+  Item_sum *item_sum,
+  ha_spider *spider
+);
+#endif
+
 int spider_db_append_match_fetch(
   ha_spider *spider,
   st_spider_ft_info *ft_first,
@@ -793,6 +810,17 @@ int spider_db_open_item_func(
   uint dbton_id
 );
 
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+int spider_db_open_item_sum_func(
+  Item_sum *item_sum,
+  ha_spider *spider,
+  spider_string *str,
+  const char *alias,
+  uint alias_length,
+  uint dbton_id
+);
+#endif
+
 int spider_db_open_item_ident(
   Item_ident *item_ident,
   ha_spider *spider,
diff --git a/storage/spider/spd_db_handlersocket.cc b/storage/spider/spd_db_handlersocket.cc
index 876eeeb656c..8d5c52d6bf2 100644
--- a/storage/spider/spd_db_handlersocket.cc
+++ b/storage/spider/spd_db_handlersocket.cc
@@ -463,6 +463,26 @@ double spider_db_handlersocket_row::val_real()
   DBUG_RETURN(hs_row->begin() ? my_atof(hs_row->begin()) : 0.0);

 }

 

+my_decimal *spider_db_handlersocket_row::val_decimal(

+  my_decimal *decimal_value,

+  CHARSET_INFO *access_charset

+) {

+  DBUG_ENTER("spider_db_handlersocket_row::val_decimal");

+  DBUG_PRINT("info",("spider this=%p", this));

+  if (!hs_row->begin())

+    DBUG_RETURN(NULL);

+

+#ifdef SPIDER_HAS_DECIMAL_OPERATION_RESULTS_VALUE_TYPE

+  decimal_operation_results(str2my_decimal(0, hs_row->begin(), hs_row->size(),

+    access_charset, decimal_value), "", "");

+#else

+  decimal_operation_results(str2my_decimal(0, hs_row->begin(), hs_row->size(),

+    access_charset, decimal_value));

+#endif

+

+  DBUG_RETURN(decimal_value);

+}

+

 SPIDER_DB_ROW *spider_db_handlersocket_row::clone()

 {

   spider_db_handlersocket_row *clone_row;

@@ -3375,6 +3395,81 @@ int spider_db_handlersocket_util::open_item_func(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_db_handlersocket_util::open_item_sum_func(

+  Item_sum *item_sum,

+  ha_spider *spider,

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  uint dbton_id = spider_dbton_handlersocket.dbton_id;

+  uint roop_count, item_count = item_sum->get_arg_count();

+  int error_num;

+  DBUG_ENTER("spider_db_handlersocket_util::open_item_sum_func");

+  DBUG_PRINT("info",("spider Sumfunctype = %d", item_sum->sum_func()));

+  switch (item_sum->sum_func())

+  {

+    case Item_sum::COUNT_FUNC:

+    case Item_sum::SUM_FUNC:

+    case Item_sum::MIN_FUNC:

+    case Item_sum::MAX_FUNC:

+      {

+        const char *func_name = item_sum->func_name();

+        uint func_name_length = strlen(func_name);

+        Item *item, **args = item_sum->get_args();

+        if (str)

+        {

+          if (str->reserve(func_name_length))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(func_name, func_name_length);

+        }

+        if (item_count)

+        {

+          item_count--;

+          for (roop_count = 0; roop_count < item_count; roop_count++)

+          {

+            item = args[roop_count];

+            if ((error_num = spider_db_print_item_type(item, spider, str,

+              alias, alias_length, dbton_id)))

+              DBUG_RETURN(error_num);

+            if (str)

+            {

+              if (str->reserve(SPIDER_SQL_COMMA_LEN))

+                DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+              str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+            }

+          }

+          item = args[roop_count];

+          if ((error_num = spider_db_print_item_type(item, spider, str,

+            alias, alias_length, dbton_id)))

+            DBUG_RETURN(error_num);

+        }

+        if (str)

+        {

+          if (str->reserve(SPIDER_SQL_CLOSE_PAREN_LEN))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(SPIDER_SQL_CLOSE_PAREN_STR,

+            SPIDER_SQL_CLOSE_PAREN_LEN);

+        }

+      }

+      break;

+    case Item_sum::COUNT_DISTINCT_FUNC:

+    case Item_sum::SUM_DISTINCT_FUNC:

+    case Item_sum::AVG_FUNC:

+    case Item_sum::AVG_DISTINCT_FUNC:

+    case Item_sum::STD_FUNC:

+    case Item_sum::VARIANCE_FUNC:

+    case Item_sum::SUM_BIT_FUNC:

+    case Item_sum::UDF_SUM_FUNC:

+    case Item_sum::GROUP_CONCAT_FUNC:

+    default:

+      DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 int spider_db_handlersocket_util::append_escaped_util(

   spider_string *to,

   String *from

@@ -4322,6 +4417,19 @@ int spider_handlersocket_handler::append_match_select_part(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_handlersocket_handler::append_sum_select_part(

+  ulong sql_type,

+  const char *alias,

+  uint alias_length

+) {

+  DBUG_ENTER("spider_handlersocket_handler::append_sum_select_part");

+  DBUG_PRINT("info",("spider this=%p", this));

+  DBUG_ASSERT(0);

+  DBUG_RETURN(0);

+}

+#endif

+

 void spider_handlersocket_handler::set_order_pos(

   ulong sql_type

 ) {

diff --git a/storage/spider/spd_db_handlersocket.h b/storage/spider/spd_db_handlersocket.h
index ed3d55752bc..7d93598ecc8 100644
--- a/storage/spider/spd_db_handlersocket.h
+++ b/storage/spider/spd_db_handlersocket.h
@@ -96,6 +96,15 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int open_item_sum_func(

+    Item_sum *item_sum,

+    ha_spider *spider,

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   int append_escaped_util(

     spider_string *to,

     String *from

@@ -127,6 +136,10 @@ public:
   bool is_null();

   int val_int();

   double val_real();

+  my_decimal *val_decimal(

+    my_decimal *decimal_value,

+    CHARSET_INFO *access_charset

+  );

   SPIDER_DB_ROW *clone();

   int store_to_tmp_table(

     TABLE *tmp_table,

@@ -645,6 +658,13 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int append_sum_select_part(

+    ulong sql_type,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   void set_order_pos(

     ulong sql_type

   );

diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h
index c51bca146cd..218358c631d 100644
--- a/storage/spider/spd_db_include.h
+++ b/storage/spider/spd_db_include.h
@@ -28,6 +28,7 @@
 
 #if defined(MARIADB_BASE_VERSION) && MYSQL_VERSION_ID >= 100004
 #define SPIDER_HAS_TIME_STATUS
+#define SPIDER_HAS_DECIMAL_OPERATION_RESULTS_VALUE_TYPE
 #endif
 
 class spider_db_conn;
@@ -191,6 +192,12 @@ enum spider_bulk_upd_start {
   SPD_BU_START_BY_BULK_INIT
 };
 
+enum spider_index_rnd_init {
+  SPD_NONE,
+  SPD_INDEX,
+  SPD_RND
+};
+
 struct st_spider_ft_info;
 struct st_spider_result;
 typedef struct st_spider_transaction SPIDER_TRX;
@@ -645,6 +652,15 @@ public:
     const char *alias,
     uint alias_length
   ) = 0;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  virtual int open_item_sum_func(
+    Item_sum *item_sum,
+    ha_spider *spider,
+    spider_string *str,
+    const char *alias,
+    uint alias_length
+  ) = 0;
+#endif
   virtual int append_escaped_util(
     spider_string *to,
     String *from
@@ -674,6 +690,10 @@ public:
   virtual bool is_null() = 0;
   virtual int val_int() = 0;
   virtual double val_real() = 0;
+  virtual my_decimal *val_decimal(
+    my_decimal *decimal_value,
+    CHARSET_INFO *access_charset
+  ) = 0;
   virtual SPIDER_DB_ROW *clone() = 0;
   virtual int store_to_tmp_table(
     TABLE *tmp_table,
@@ -1121,6 +1141,13 @@ public:
     const char *alias,
     uint alias_length
   ) = 0;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  virtual int append_sum_select_part(
+    ulong sql_type,
+    const char *alias,
+    uint alias_length
+  ) = 0;
+#endif
   virtual void set_order_pos(
     ulong sql_type
   ) = 0;
@@ -1475,6 +1502,9 @@ typedef struct st_spider_position
   uint                   pos_mode;
   bool                   use_position;
   bool                   mrr_with_cnt;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  bool                   direct_aggregate;
+#endif
   uint                   sql_kind;
   uchar                  *position_bitmap;
   st_spider_ft_info      *ft_first;
@@ -1579,6 +1609,12 @@ typedef struct st_spider_result_list
   spider_bulk_upd_start   bulk_update_start;
   bool                    check_direct_order_limit;
   bool                    direct_order_limit;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+  bool                    direct_aggregate;
+  bool                    snap_mrr_with_cnt;
+  bool                    snap_direct_aggregate;
+  SPIDER_DB_ROW           *snap_row;
+#endif
   bool                    set_split_read;
   bool                    insert_dup_update_pushdown;
   longlong                split_read_base;
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index f6ef1ff947e..a09b9e4a804 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -26,6 +26,9 @@
 #include "sql_analyse.h"

 #include "sql_base.h"

 #include "tztime.h"

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+#include "sql_select.h"

+#endif

 #endif

 #include "sql_common.h"

 #include <mysql.h>

@@ -336,6 +339,26 @@ double spider_db_mysql_row::val_real()
   DBUG_RETURN(*row ? my_atof(*row) : 0.0);

 }

 

+my_decimal *spider_db_mysql_row::val_decimal(

+  my_decimal *decimal_value,

+  CHARSET_INFO *access_charset

+) {

+  DBUG_ENTER("spider_db_mysql_row::val_decimal");

+  DBUG_PRINT("info",("spider this=%p", this));

+  if (!*row)

+    DBUG_RETURN(NULL);

+

+#ifdef SPIDER_HAS_DECIMAL_OPERATION_RESULTS_VALUE_TYPE

+  decimal_operation_results(str2my_decimal(0, *row, *lengths, access_charset,

+    decimal_value), "", "");

+#else

+  decimal_operation_results(str2my_decimal(0, *row, *lengths, access_charset,

+    decimal_value));

+#endif

+

+  DBUG_RETURN(decimal_value);

+}

+

 SPIDER_DB_ROW *spider_db_mysql_row::clone()

 {

   spider_db_mysql_row *clone_row;

@@ -3613,6 +3636,81 @@ int spider_db_mysql_util::open_item_func(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_db_mysql_util::open_item_sum_func(

+  Item_sum *item_sum,

+  ha_spider *spider,

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  uint dbton_id = spider_dbton_mysql.dbton_id;

+  uint roop_count, item_count = item_sum->get_arg_count();

+  int error_num;

+  DBUG_ENTER("spider_db_mysql_util::open_item_sum_func");

+  DBUG_PRINT("info",("spider Sumfunctype = %d", item_sum->sum_func()));

+  switch (item_sum->sum_func())

+  {

+    case Item_sum::COUNT_FUNC:

+    case Item_sum::SUM_FUNC:

+    case Item_sum::MIN_FUNC:

+    case Item_sum::MAX_FUNC:

+      {

+        const char *func_name = item_sum->func_name();

+        uint func_name_length = strlen(func_name);

+        Item *item, **args = item_sum->get_args();

+        if (str)

+        {

+          if (str->reserve(func_name_length))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(func_name, func_name_length);

+        }

+        if (item_count)

+        {

+          item_count--;

+          for (roop_count = 0; roop_count < item_count; roop_count++)

+          {

+            item = args[roop_count];

+            if ((error_num = spider_db_print_item_type(item, spider, str,

+              alias, alias_length, dbton_id)))

+              DBUG_RETURN(error_num);

+            if (str)

+            {

+              if (str->reserve(SPIDER_SQL_COMMA_LEN))

+                DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+              str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+            }

+          }

+          item = args[roop_count];

+          if ((error_num = spider_db_print_item_type(item, spider, str,

+            alias, alias_length, dbton_id)))

+            DBUG_RETURN(error_num);

+        }

+        if (str)

+        {

+          if (str->reserve(SPIDER_SQL_CLOSE_PAREN_LEN))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(SPIDER_SQL_CLOSE_PAREN_STR,

+            SPIDER_SQL_CLOSE_PAREN_LEN);

+        }

+      }

+      break;

+    case Item_sum::COUNT_DISTINCT_FUNC:

+    case Item_sum::SUM_DISTINCT_FUNC:

+    case Item_sum::AVG_FUNC:

+    case Item_sum::AVG_DISTINCT_FUNC:

+    case Item_sum::STD_FUNC:

+    case Item_sum::VARIANCE_FUNC:

+    case Item_sum::SUM_BIT_FUNC:

+    case Item_sum::UDF_SUM_FUNC:

+    case Item_sum::GROUP_CONCAT_FUNC:

+    default:

+      DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 int spider_db_mysql_util::append_escaped_util(

   spider_string *to,

   String *from

@@ -5987,6 +6085,13 @@ int spider_mysql_handler::append_select_columns_with_alias(
   int error_num;

   SPIDER_RESULT_LIST *result_list = &spider->result_list;

   DBUG_ENTER("spider_mysql_handler::append_select_columns_with_alias");

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (

+    result_list->direct_aggregate &&

+    (error_num = append_sum_select(str, alias, alias_length))

+  )

+    DBUG_RETURN(error_num);

+#endif

   if ((error_num = append_match_select(str, alias, alias_length)))

     DBUG_RETURN(error_num);

   if (!spider->select_column_mode)

@@ -7038,6 +7143,55 @@ int spider_mysql_handler::append_match_select(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_mysql_handler::append_sum_select_part(

+  ulong sql_type,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  spider_string *str;

+  DBUG_ENTER("spider_mysql_handler::append_sum_select_part");

+  DBUG_PRINT("info",("spider this=%p", this));

+  switch (sql_type)

+  {

+    case SPIDER_SQL_TYPE_SELECT_SQL:

+      str = &sql;

+      break;

+    default:

+      DBUG_RETURN(0);

+  }

+  error_num = append_sum_select(str, alias, alias_length);

+  DBUG_RETURN(error_num);

+}

+

+int spider_mysql_handler::append_sum_select(

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  st_select_lex *select_lex;

+  DBUG_ENTER("spider_mysql_handler::append_sum_select");

+  DBUG_PRINT("info",("spider this=%p", this));

+  select_lex = spider_get_select_lex(spider);

+  JOIN *join = select_lex->join;

+  Item_sum **item_sum_ptr;

+  for (item_sum_ptr = join->sum_funcs; *item_sum_ptr; ++item_sum_ptr)

+  {

+    if ((error_num = spider_db_mysql_utility.open_item_sum_func(*item_sum_ptr,

+      spider, str, alias, alias_length)))

+    {

+      DBUG_RETURN(error_num);

+    }

+    if (str->reserve(SPIDER_SQL_COMMA_LEN))

+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+    str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 void spider_mysql_handler::set_order_pos(

   ulong sql_type

 ) {

@@ -7090,6 +7244,40 @@ void spider_mysql_handler::set_order_to_pos(
   DBUG_VOID_RETURN;

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_mysql_handler::append_group_by(

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  st_select_lex *select_lex;

+  DBUG_ENTER("spider_mysql_handler::append_group_by");

+  DBUG_PRINT("info",("spider this=%p", this));

+  select_lex = spider_get_select_lex(spider);

+  ORDER *group = (ORDER *) select_lex->group_list.first;

+  if (group)

+  {

+    if (str->reserve(SPIDER_SQL_GROUP_LEN))

+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+    str->q_append(SPIDER_SQL_GROUP_STR, SPIDER_SQL_GROUP_LEN);

+    for (; group; group = group->next)

+    {

+      if ((error_num = spider_db_print_item_type((*group->item), spider, str,

+        alias, alias_length, spider_dbton_mysql.dbton_id)))

+      {

+        DBUG_RETURN(error_num);

+      }

+      if (str->reserve(SPIDER_SQL_COMMA_LEN))

+        DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+      str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+    }

+    str->length(str->length() - SPIDER_SQL_COMMA_LEN);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 int spider_mysql_handler::append_key_order_for_merge_with_alias_part(

   const char *alias,

   uint alias_length,

@@ -7134,6 +7322,14 @@ int spider_mysql_handler::append_key_order_for_merge_with_alias(
   uint key_name_length;

   DBUG_ENTER("spider_mysql_handler::append_key_order_for_merge_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    int error_num;

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   if (table->s->primary_key < MAX_KEY)

   {

     /* sort by primary key */

@@ -7242,6 +7438,13 @@ int spider_mysql_handler::append_key_order_for_direct_order_limit_with_alias(
   longlong offset_limit;

   DBUG_ENTER("spider_mysql_handler::append_key_order_for_direct_order_limit_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   spider_get_select_limit(spider, &select_lex, &select_limit,

     &offset_limit);

   if (select_lex->order_list.first)

@@ -7337,6 +7540,14 @@ int spider_mysql_handler::append_key_order_with_alias(
   uint key_name_length;

   DBUG_ENTER("spider_mysql_handler::append_key_order_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    int error_num;

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   if (result_list->sorted == TRUE)

   {

     if (result_list->desc_flg == TRUE)

diff --git a/storage/spider/spd_db_mysql.h b/storage/spider/spd_db_mysql.h
index 71d6a262cf8..b95d4e6e1a2 100644
--- a/storage/spider/spd_db_mysql.h
+++ b/storage/spider/spd_db_mysql.h
@@ -101,6 +101,15 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int open_item_sum_func(

+    Item_sum *item_sum,

+    ha_spider *spider,

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   int append_escaped_util(

     spider_string *to,

     String *from

@@ -134,6 +143,10 @@ public:
   bool is_null();

   int val_int();

   double val_real();

+  my_decimal *val_decimal(

+    my_decimal *decimal_value,

+    CHARSET_INFO *access_charset

+  );

   SPIDER_DB_ROW *clone();

   int store_to_tmp_table(

     TABLE *tmp_table,

@@ -845,12 +858,31 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int append_sum_select_part(

+    ulong sql_type,

+    const char *alias,

+    uint alias_length

+  );

+  int append_sum_select(

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   void set_order_pos(

     ulong sql_type

   );

   void set_order_to_pos(

     ulong sql_type

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int append_group_by(

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   int append_key_order_for_merge_with_alias_part(

     const char *alias,

     uint alias_length,

diff --git a/storage/spider/spd_db_oracle.cc b/storage/spider/spd_db_oracle.cc
index 1254dd8332e..9631f6505d5 100644
--- a/storage/spider/spd_db_oracle.cc
+++ b/storage/spider/spd_db_oracle.cc
@@ -23,6 +23,9 @@
 #include "probes_mysql.h"

 #include "sql_partition.h"

 #include "sql_analyse.h"

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+#include "sql_select.h"

+#endif

 #endif

 

 #ifdef HAVE_ORACLE_OCI

@@ -466,6 +469,26 @@ double spider_db_oracle_row::val_real()
   DBUG_RETURN((*ind != -1) ? my_atof(*val) : 0.0);

 }

 

+my_decimal *spider_db_oracle_row::val_decimal(

+  my_decimal *decimal_value,

+  CHARSET_INFO *access_charset

+) {

+  DBUG_ENTER("spider_db_oracle_row::val_decimal");

+  DBUG_PRINT("info",("spider this=%p", this));

+  if (*ind == -1)

+    DBUG_RETURN(NULL);

+

+#ifdef SPIDER_HAS_DECIMAL_OPERATION_RESULTS_VALUE_TYPE

+  decimal_operation_results(str2my_decimal(0, *val, *rlen, access_charset,

+    decimal_value), "", "");

+#else

+  decimal_operation_results(str2my_decimal(0, *val, *rlen, access_charset,

+    decimal_value));

+#endif

+

+  DBUG_RETURN(decimal_value);

+}

+

 SPIDER_DB_ROW *spider_db_oracle_row::clone()

 {

   uint i;

@@ -3658,6 +3681,81 @@ int spider_db_oracle_util::open_item_func(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_db_oracle_util::open_item_sum_func(

+  Item_sum *item_sum,

+  ha_spider *spider,

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  uint dbton_id = spider_dbton_oracle.dbton_id;

+  uint roop_count, item_count = item_sum->get_arg_count();

+  int error_num;

+  DBUG_ENTER("spider_db_oracle_util::open_item_sum_func");

+  DBUG_PRINT("info",("spider Sumfunctype = %d", item_sum->sum_func()));

+  switch (item_sum->sum_func())

+  {

+    case Item_sum::COUNT_FUNC:

+    case Item_sum::SUM_FUNC:

+    case Item_sum::MIN_FUNC:

+    case Item_sum::MAX_FUNC:

+      {

+        const char *func_name = item_sum->func_name();

+        uint func_name_length = strlen(func_name);

+        Item *item, **args = item_sum->get_args();

+        if (str)

+        {

+          if (str->reserve(func_name_length))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(func_name, func_name_length);

+        }

+        if (item_count)

+        {

+          item_count--;

+          for (roop_count = 0; roop_count < item_count; roop_count++)

+          {

+            item = args[roop_count];

+            if ((error_num = spider_db_print_item_type(item, spider, str,

+              alias, alias_length, dbton_id)))

+              DBUG_RETURN(error_num);

+            if (str)

+            {

+              if (str->reserve(SPIDER_SQL_COMMA_LEN))

+                DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+              str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+            }

+          }

+          item = args[roop_count];

+          if ((error_num = spider_db_print_item_type(item, spider, str,

+            alias, alias_length, dbton_id)))

+            DBUG_RETURN(error_num);

+        }

+        if (str)

+        {

+          if (str->reserve(SPIDER_SQL_CLOSE_PAREN_LEN))

+            DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+          str->q_append(SPIDER_SQL_CLOSE_PAREN_STR,

+            SPIDER_SQL_CLOSE_PAREN_LEN);

+        }

+      }

+      break;

+    case Item_sum::COUNT_DISTINCT_FUNC:

+    case Item_sum::SUM_DISTINCT_FUNC:

+    case Item_sum::AVG_FUNC:

+    case Item_sum::AVG_DISTINCT_FUNC:

+    case Item_sum::STD_FUNC:

+    case Item_sum::VARIANCE_FUNC:

+    case Item_sum::SUM_BIT_FUNC:

+    case Item_sum::UDF_SUM_FUNC:

+    case Item_sum::GROUP_CONCAT_FUNC:

+    default:

+      DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 size_t spider_db_oracle_util::escape_string(

   char *to,

   const char *from,

@@ -6001,6 +6099,13 @@ int spider_oracle_handler::append_select_columns_with_alias(
   int error_num;

   SPIDER_RESULT_LIST *result_list = &spider->result_list;

   DBUG_ENTER("spider_oracle_handler::append_select_columns_with_alias");

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (

+    result_list->direct_aggregate &&

+    (error_num = append_sum_select(str, alias, alias_length))

+  )

+    DBUG_RETURN(error_num);

+#endif

   if ((error_num = append_match_select(str, alias, alias_length)))

     DBUG_RETURN(error_num);

   if (!spider->select_column_mode)

@@ -7065,6 +7170,55 @@ int spider_oracle_handler::append_match_select(
   DBUG_RETURN(0);

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_oracle_handler::append_sum_select_part(

+  ulong sql_type,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  spider_string *str;

+  DBUG_ENTER("spider_oracle_handler::append_sum_select_part");

+  DBUG_PRINT("info",("spider this=%p", this));

+  switch (sql_type)

+  {

+    case SPIDER_SQL_TYPE_SELECT_SQL:

+      str = &sql;

+      break;

+    default:

+      DBUG_RETURN(0);

+  }

+  error_num = append_sum_select(str, alias, alias_length);

+  DBUG_RETURN(error_num);

+}

+

+int spider_oracle_handler::append_sum_select(

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  st_select_lex *select_lex;

+  DBUG_ENTER("spider_oracle_handler::append_sum_select");

+  DBUG_PRINT("info",("spider this=%p", this));

+  select_lex = spider_get_select_lex(spider);

+  JOIN *join = select_lex->join;

+  Item_sum **item_sum_ptr;

+  for (item_sum_ptr = join->sum_funcs; *item_sum_ptr; ++item_sum_ptr)

+  {

+    if ((error_num = spider_db_oracle_utility.open_item_sum_func(*item_sum_ptr,

+      spider, str, alias, alias_length)))

+    {

+      DBUG_RETURN(error_num);

+    }

+    if (str->reserve(SPIDER_SQL_COMMA_LEN))

+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+    str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 void spider_oracle_handler::set_order_pos(

   ulong sql_type

 ) {

@@ -7117,6 +7271,40 @@ void spider_oracle_handler::set_order_to_pos(
   DBUG_VOID_RETURN;

 }

 

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+int spider_oracle_handler::append_group_by(

+  spider_string *str,

+  const char *alias,

+  uint alias_length

+) {

+  int error_num;

+  st_select_lex *select_lex;

+  DBUG_ENTER("spider_oracle_handler::append_group_by");

+  DBUG_PRINT("info",("spider this=%p", this));

+  select_lex = spider_get_select_lex(spider);

+  ORDER *group = (ORDER *) select_lex->group_list.first;

+  if (group)

+  {

+    if (str->reserve(SPIDER_SQL_GROUP_LEN))

+      DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+    str->q_append(SPIDER_SQL_GROUP_STR, SPIDER_SQL_GROUP_LEN);

+    for (; group; group = group->next)

+    {

+      if ((error_num = spider_db_print_item_type((*group->item), spider, str,

+        alias, alias_length, spider_dbton_oracle.dbton_id)))

+      {

+        DBUG_RETURN(error_num);

+      }

+      if (str->reserve(SPIDER_SQL_COMMA_LEN))

+        DBUG_RETURN(HA_ERR_OUT_OF_MEM);

+      str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN);

+    }

+    str->length(str->length() - SPIDER_SQL_COMMA_LEN);

+  }

+  DBUG_RETURN(0);

+}

+#endif

+

 int spider_oracle_handler::append_key_order_for_merge_with_alias_part(

   const char *alias,

   uint alias_length,

@@ -7161,6 +7349,14 @@ int spider_oracle_handler::append_key_order_for_merge_with_alias(
   uint key_name_length;

   DBUG_ENTER("spider_oracle_handler::append_key_order_for_merge_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    int error_num;

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   if (

     spider->result_list.direct_order_limit ||

     spider->result_list.internal_limit < 9223372036854775807LL ||

@@ -7383,6 +7579,13 @@ int spider_oracle_handler::append_key_order_for_direct_order_limit_with_alias(
   longlong offset_limit;

   DBUG_ENTER("spider_oracle_handler::append_key_order_for_direct_order_limit_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   spider_get_select_limit(spider, &select_lex, &select_limit,

     &offset_limit);

   if (

@@ -7601,6 +7804,14 @@ int spider_oracle_handler::append_key_order_with_alias(
   uint key_name_length;

   DBUG_ENTER("spider_oracle_handler::append_key_order_with_alias");

   DBUG_PRINT("info",("spider this=%p", this));

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  if (spider->result_list.direct_aggregate)

+  {

+    int error_num;

+    if ((error_num = append_group_by(str, alias, alias_length)))

+      DBUG_RETURN(error_num);

+  }

+#endif

   if (

     spider->result_list.direct_order_limit ||

     spider->result_list.internal_limit < 9223372036854775807LL ||

diff --git a/storage/spider/spd_db_oracle.h b/storage/spider/spd_db_oracle.h
index db5eac88ca9..7b43b9d5e0b 100644
--- a/storage/spider/spd_db_oracle.h
+++ b/storage/spider/spd_db_oracle.h
@@ -104,6 +104,15 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int open_item_sum_func(

+    Item_sum *item_sum,

+    ha_spider *spider,

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   size_t escape_string(

     char *to,

     const char *from,

@@ -159,6 +168,10 @@ public:
   bool is_null();

   int val_int();

   double val_real();

+  my_decimal *val_decimal(

+    my_decimal *decimal_value,

+    CHARSET_INFO *access_charset

+  );

   SPIDER_DB_ROW *clone();

   int store_to_tmp_table(

     TABLE *tmp_table,

@@ -928,12 +941,31 @@ public:
     const char *alias,

     uint alias_length

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int append_sum_select_part(

+    ulong sql_type,

+    const char *alias,

+    uint alias_length

+  );

+  int append_sum_select(

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   void set_order_pos(

     ulong sql_type

   );

   void set_order_to_pos(

     ulong sql_type

   );

+#ifdef HANDLER_HAS_DIRECT_AGGREGATE

+  int append_group_by(

+    spider_string *str,

+    const char *alias,

+    uint alias_length

+  );

+#endif

   int append_key_order_for_merge_with_alias_part(

     const char *alias,

     uint alias_length,

diff --git a/storage/spider/spd_include.h b/storage/spider/spd_include.h
index 6db537462ef..9eaeeb99139 100644
--- a/storage/spider/spd_include.h
+++ b/storage/spider/spd_include.h
@@ -118,7 +118,7 @@
 #define SPIDER_TMP_SHARE_LONG_COUNT         15
 #define SPIDER_TMP_SHARE_LONGLONG_COUNT      3
 
-#define SPIDER_MEM_CALC_LIST_NUM           240
+#define SPIDER_MEM_CALC_LIST_NUM           243
 
 #define SPIDER_BACKUP_DASTATUS \
   bool da_status; if (thd) da_status = thd->is_error(); else da_status = FALSE;
@@ -1158,6 +1158,13 @@ typedef struct st_spider_int_hld
   st_spider_int_hld *next;
 } SPIDER_INT_HLD;
 
+typedef struct st_spider_item_hld
+{
+  uint tgt_num;
+  Item *item;
+  st_spider_item_hld *next;
+} SPIDER_ITEM_HLD;
+
 char *spider_create_string(
   const char *str,
   uint length
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index a06aeb6adc7..bb94928bfe3 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -25,6 +25,9 @@
 #include "sql_class.h"
 #include "sql_partition.h"
 #include "sql_servers.h"
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+#include "sql_select.h"
+#endif
 #endif
 #include "spd_err.h"
 #include "spd_param.h"
@@ -7478,17 +7481,17 @@ bool spider_check_direct_order_limit(
 ) {
   THD *thd = spider->trx->thd;
   SPIDER_SHARE *share = spider->share;
-  longlong direct_order_limit = spider_param_direct_order_limit(thd,
-    share->direct_order_limit);
+  st_select_lex *select_lex;
+  longlong select_limit;
+  longlong offset_limit;
   DBUG_ENTER("spider_check_direct_order_limit");
-  if (
-    direct_order_limit &&
-    spider->sql_command != SQLCOM_HA_READ
-  ) {
-    st_select_lex *select_lex;
-    longlong select_limit;
-    longlong offset_limit;
+  if (spider->sql_command != SQLCOM_HA_READ)
+  {
     spider_get_select_limit(spider, &select_lex, &select_limit, &offset_limit);
+    bool first_check = TRUE;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+    spider->result_list.direct_aggregate = TRUE;
+#endif
     DBUG_PRINT("info",("spider select_limit=%lld", select_limit));
     DBUG_PRINT("info",("spider offset_limit=%lld", offset_limit));
     if (
@@ -7499,38 +7502,91 @@ bool spider_check_direct_order_limit(
         OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) ||
 #endif
       !select_lex ||
-      !select_lex->explicit_limit ||
-      (select_lex->options & OPTION_FOUND_ROWS) ||
-      select_lex->group_list.elements ||
-      select_lex->with_sum_func ||
-      select_lex->having ||
-      select_lex->table_list.elements != 1 ||
-      !select_lex->order_list.elements ||
-      select_limit > direct_order_limit - offset_limit
+      select_lex->table_list.elements != 1
     ) {
-      DBUG_PRINT("info",("spider FALSE by select_lex"));
-      DBUG_RETURN(FALSE);
-    }
-    ORDER *order;
-    for (order = (ORDER *) select_lex->order_list.first; order;
-      order = order->next)
+      DBUG_PRINT("info",("spider first_check is FALSE"));
+      first_check = FALSE;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+      spider->result_list.direct_aggregate = FALSE;
+#endif
+    } else if (spider_db_append_condition(spider, NULL, 0, TRUE))
     {
-      if (spider->print_item_type((*order->item), NULL, NULL, 0))
+      DBUG_PRINT("info",("spider FALSE by condition"));
+      first_check = FALSE;
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+      spider->result_list.direct_aggregate = FALSE;
+    } else if (
+      !select_lex->group_list.elements &&
+      !select_lex->with_sum_func
+    ) {
+      DBUG_PRINT("info",("spider this SQL is not aggregate SQL"));
+      spider->result_list.direct_aggregate = FALSE;
+    } else {
+      ORDER *group;
+      for (group = (ORDER *) select_lex->group_list.first; group;
+        group = group->next)
       {
-        DBUG_PRINT("info",("spider FALSE by order"));
-        DBUG_RETURN(FALSE);
+        if (spider->print_item_type((*group->item), NULL, NULL, 0))
+        {
+          DBUG_PRINT("info",("spider aggregate FALSE by group"));
+          spider->result_list.direct_aggregate = FALSE;
+          break;
+        }
       }
+      JOIN *join = select_lex->join;
+      Item_sum **item_sum_ptr;
+      for (item_sum_ptr = join->sum_funcs; *item_sum_ptr; ++item_sum_ptr)
+      {
+        if (spider->print_item_type(*item_sum_ptr, NULL, NULL, 0))
+        {
+          DBUG_PRINT("info",("spider aggregate FALSE by not supported"));
+          spider->result_list.direct_aggregate = FALSE;
+          break;
+        }
+      }
+#endif
     }
-    if (spider_db_append_condition(spider, NULL, 0, TRUE))
+
+    longlong direct_order_limit = spider_param_direct_order_limit(thd,
+      share->direct_order_limit);
+    if (direct_order_limit)
     {
-      DBUG_PRINT("info",("spider FALSE by condition"));
-      DBUG_RETURN(FALSE);
+      if (
+        !first_check ||
+        !select_lex->explicit_limit ||
+        (select_lex->options & OPTION_FOUND_ROWS) ||
+        (
+#ifdef HANDLER_HAS_DIRECT_AGGREGATE
+          !spider->result_list.direct_aggregate &&
+#endif
+          (
+            select_lex->group_list.elements ||
+            select_lex->with_sum_func
+          )
+        ) ||
+        select_lex->having ||
+        !select_lex->order_list.elements ||
+        select_limit > direct_order_limit - offset_limit
+      ) {
+        DBUG_PRINT("info",("spider FALSE by select_lex"));
+        DBUG_RETURN(FALSE);
+      }
+      ORDER *order;
+      for (order = (ORDER *) select_lex->order_list.first; order;
+        order = order->next)
+      {
+        if (spider->print_item_type((*order->item), NULL, NULL, 0))
+        {
+          DBUG_PRINT("info",("spider FALSE by order"));
+          DBUG_RETURN(FALSE);
+        }
+      }
+      DBUG_PRINT("info",("spider TRUE"));
+      spider->result_list.internal_limit = select_limit + offset_limit;
+      spider->result_list.split_read = select_limit + offset_limit;
+      spider->trx->direct_order_limit_count++;
+      DBUG_RETURN(TRUE);
     }
-    DBUG_PRINT("info",("spider TRUE"));
-    spider->result_list.internal_limit = select_limit + offset_limit;
-    spider->result_list.split_read = select_limit + offset_limit;
-    spider->trx->direct_order_limit_count++;
-    DBUG_RETURN(TRUE);
   }
   DBUG_PRINT("info",("spider FALSE by parameter"));
   DBUG_RETURN(FALSE);