WL#926 "SUM(DISTINCT) and AVG(DISTINCT)": improvement of SUM(DISTINCT) and

 implementation of AVG(DISTINCT) which utilizes the approach with Fields.
The patch implemented in October is portede to the up-to-date tree 
containing DECIMAL type.
Tests for AVG(DISTINCT) (although there is not much to test provided
that SUM(DISTINCT) works), cleanups for COUNT(DISTINCT) and GROUP_CONCAT()
will follow in another changeset.

9 files changed, 628 insertions, 185 deletions

diff --git a/sql/field.cc b/sql/field.cc
index fd4d83a8b1c..7d17431b4a1 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7150,6 +7150,24 @@ void create_field::create_length_to_internal_length(void)
 }
 
 
+void create_field::init_for_tmp_table(enum_field_types sql_type_arg,
+                                      uint32 length_arg, uint32 decimals,
+                                      bool maybe_null, bool is_unsigned)
+{
+  field_name= "";
+  sql_type= sql_type_arg;
+  length= length_arg;;
+  unireg_check= Field::NONE;
+  interval= 0;
+  charset= &my_charset_bin;
+  geom_type= Field::GEOM_GEOMETRY;
+  pack_flag= (FIELDFLAG_NUMBER |
+              ((decimals & FIELDFLAG_MAX_DEC) << FIELDFLAG_DEC_SHIFT) |
+              (maybe_null ? FIELDFLAG_MAYBE_NULL : 0) |
+              (is_unsigned ? 0 : FIELDFLAG_DECIMAL));
+}
+
+
 enum_field_types get_blob_type_from_length(ulong length)
 {
   enum_field_types type;
diff --git a/sql/field.h b/sql/field.h
index a92ef1db297..083af27d6d9 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -1340,7 +1340,8 @@ public:
   Create field class for CREATE TABLE
 */
 
-class create_field :public Sql_alloc {
+class create_field :public Sql_alloc
+{
 public:
   const char *field_name;
   const char *change;			// If done with alter table
@@ -1362,6 +1363,11 @@ public:
   create_field() :after(0) {}
   create_field(Field *field, Field *orig_field);
   void create_length_to_internal_length(void);
+
+  /* Init for a tmp table field. To be extended if need be. */
+  void init_for_tmp_table(enum_field_types sql_type_arg,
+                          uint32 max_length, uint32 decimals,
+                          bool maybe_null, bool is_unsigned);
 };
 
 
diff --git a/sql/item.cc b/sql/item.cc
index 1096114021e..994bdc86413 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -33,6 +33,131 @@ static void mark_as_dependent(THD *thd,
 
 const String my_null_string("NULL", 4, default_charset_info);
 
+/****************************************************************************/
+
+/* Hybrid_type_traits {_real} */
+
+void Hybrid_type_traits::fix_length_and_dec(Item *item, Item *arg) const
+{
+  item->decimals= NOT_FIXED_DEC;
+  item->max_length= item->float_length(arg->decimals);
+}
+
+
+const Hybrid_type_traits *Hybrid_type_traits::instance()
+{
+  const static Hybrid_type_traits real_traits;
+  return &real_traits;
+}
+
+
+my_decimal *
+Hybrid_type_traits::val_decimal(Hybrid_type *val, my_decimal *to) const
+{
+  double2my_decimal(E_DEC_FATAL_ERROR, val->real, val->dec_buf);
+  return val->dec_buf;
+}
+
+
+String *
+Hybrid_type_traits::val_str(Hybrid_type *val, String *to, uint8 decimals) const
+{
+  to->set(val->real, decimals, &my_charset_bin);
+  return to;
+}
+
+/* Hybrid_type_traits_decimal */
+
+const Hybrid_type_traits_decimal *Hybrid_type_traits_decimal::instance()
+{
+  const static Hybrid_type_traits_decimal decimal_traits;
+  return &decimal_traits;
+}
+
+
+void
+Hybrid_type_traits_decimal::fix_length_and_dec(Item *item, Item *arg) const
+{
+  item->decimals= arg->decimals;
+  item->max_length= min(arg->max_length + DECIMAL_LONGLONG_DIGITS,
+                        DECIMAL_MAX_LENGTH);
+}
+
+
+void Hybrid_type_traits_decimal::set_zero(Hybrid_type *val) const
+{
+  my_decimal_set_zero(&val->dec_buf[0]);
+  val->used_dec_buf_no= 0;
+}
+
+
+void Hybrid_type_traits_decimal::add(Hybrid_type *val, Field *f) const
+{
+  my_decimal_add(E_DEC_FATAL_ERROR,
+                 &val->dec_buf[val->used_dec_buf_no ^ 1],
+                 &val->dec_buf[val->used_dec_buf_no],
+                 f->val_decimal(&val->dec_buf[2]));
+  val->used_dec_buf_no^= 1;
+}
+
+
+void Hybrid_type_traits_decimal::div(Hybrid_type *val, ulonglong u) const
+{
+  int2my_decimal(E_DEC_FATAL_ERROR, u, TRUE, &val->dec_buf[2]);
+  /* XXX: what is '4' for scale? */
+  my_decimal_div(E_DEC_FATAL_ERROR,
+                 &val->dec_buf[val->used_dec_buf_no ^ 1],
+                 &val->dec_buf[val->used_dec_buf_no],
+                 &val->dec_buf[2], 4);
+  val->used_dec_buf_no^= 1;
+}
+
+
+longlong
+Hybrid_type_traits_decimal::val_int(Hybrid_type *val, bool unsigned_flag) const
+{
+  longlong result;
+  my_decimal2int(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no],
+                 unsigned_flag, &result);
+  return result;
+}
+
+
+double
+Hybrid_type_traits_decimal::val_real(Hybrid_type *val) const
+{
+  my_decimal2double(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no],
+                    &val->real);
+  return val->real;
+}
+
+
+String *
+Hybrid_type_traits_decimal::val_str(Hybrid_type *val, String *to,
+                                    uint8 decimals) const
+{
+  my_decimal_round(E_DEC_FATAL_ERROR, &val->dec_buf[val->used_dec_buf_no],
+                   decimals, FALSE, &val->dec_buf[2]);
+  my_decimal2string(E_DEC_FATAL_ERROR, &val->dec_buf[2], 0, 0, 0, to);
+  return to;
+}
+
+/* Hybrid_type_traits_integer */
+
+const Hybrid_type_traits_integer *Hybrid_type_traits_integer::instance()
+{
+  const static Hybrid_type_traits_integer integer_traits;
+  return &integer_traits;
+}
+
+void
+Hybrid_type_traits_integer::fix_length_and_dec(Item *item, Item *arg) const
+{
+  item->decimals= 0;
+  item->max_length= 21;
+  item->unsigned_flag= 0;
+}
+
 /*****************************************************************************
 ** Item functions
 *****************************************************************************/
diff --git a/sql/item.h b/sql/item.h
index 97913e40916..a2bf33398cc 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -106,6 +106,120 @@ public:
   }
 };
 
+
+/*************************************************************************/
+/*
+  A framework to easily handle different return types for hybrid items
+  (hybrid item is an item whose operand can be of any type, e.g. integer,
+  real, decimal).
+*/
+
+struct Hybrid_type_traits;
+
+struct Hybrid_type
+{
+  longlong integer;
+
+  double real;
+  /*
+    Use two decimal buffers interchangeably to speed up += operation
+    which has no native support in decimal library.
+    Hybrid_type+= arg is implemented as dec_buf[1]= dec_buf[0] + arg.
+    The third decimal is used as a handy temporary storage.
+  */
+  my_decimal dec_buf[3];
+  int used_dec_buf_no;
+
+  /*
+    Traits moved to a separate class to
+      a) be able to easily change object traits in runtime
+      b) they work as a differentiator for the union above
+  */
+  const Hybrid_type_traits *traits;
+
+  Hybrid_type() {}
+  /* XXX: add traits->copy() when needed */
+  Hybrid_type(const Hybrid_type &rhs) :traits(rhs.traits) {}
+};
+
+
+/* Hybryd_type_traits interface + default implementation for REAL_RESULT */
+
+struct Hybrid_type_traits
+{
+  virtual Item_result type() const { return REAL_RESULT; }
+
+  virtual void
+  fix_length_and_dec(Item *item, Item *arg) const;
+
+  /* Hybrid_type operations. */
+  virtual void set_zero(Hybrid_type *val) const { val->real= 0.0; }
+  virtual void add(Hybrid_type *val, Field *f) const
+  { val->real+= f->val_real(); }
+  virtual void div(Hybrid_type *val, ulonglong u) const
+  { val->real/= ulonglong2double(u); }
+
+  virtual longlong val_int(Hybrid_type *val, bool unsigned_flag) const
+  { return (longlong) val->real; }
+  virtual double val_real(Hybrid_type *val) const { return val->real; }
+  virtual my_decimal *val_decimal(Hybrid_type *val, my_decimal *buf) const;
+  virtual String *val_str(Hybrid_type *val, String *buf, uint8 decimals) const;
+  static const Hybrid_type_traits *instance();
+};
+
+
+struct Hybrid_type_traits_decimal: public Hybrid_type_traits
+{
+  virtual Item_result type() const { return DECIMAL_RESULT; }
+
+  virtual void
+  fix_length_and_dec(Item *arg, Item *item) const;
+
+  /* Hybrid_type operations. */
+  virtual void set_zero(Hybrid_type *val) const;
+  virtual void add(Hybrid_type *val, Field *f) const;
+  virtual void div(Hybrid_type *val, ulonglong u) const;
+
+  virtual longlong val_int(Hybrid_type *val, bool unsigned_flag) const;
+  virtual double val_real(Hybrid_type *val) const;
+  virtual my_decimal *val_decimal(Hybrid_type *val, my_decimal *buf) const
+  { return &val->dec_buf[val->used_dec_buf_no]; }
+  virtual String *val_str(Hybrid_type *val, String *buf, uint8 decimals) const;
+  static const Hybrid_type_traits_decimal *instance();
+};
+
+
+struct Hybrid_type_traits_integer: public Hybrid_type_traits
+{
+  virtual Item_result type() const { return INT_RESULT; }
+
+  virtual void
+  fix_length_and_dec(Item *arg, Item *item) const;
+
+  /* Hybrid_type operations. */
+  virtual void set_zero(Hybrid_type *val) const
+  { val->integer= 0; }
+  virtual void add(Hybrid_type *val, Field *f) const
+  { val->integer+= f->val_int(); }
+  virtual void div(Hybrid_type *val, ulonglong u) const
+  { val->integer/= (longlong) u; }
+
+  virtual longlong val_int(Hybrid_type *val, bool unsigned_flag) const
+  { return val->integer; }
+  virtual double val_real(Hybrid_type *val) const
+  { return (double) val->integer; }
+  virtual my_decimal *val_decimal(Hybrid_type *val, my_decimal *buf) const
+  {
+    int2my_decimal(E_DEC_FATAL_ERROR, val->integer, 0, &val->dec_buf[2]);
+    return &val->dec_buf[2];
+  }
+  virtual String *val_str(Hybrid_type *val, String *buf, uint8 decimals) const
+  { buf->set(val->integer, &my_charset_bin); return buf;}
+  static const Hybrid_type_traits_integer *instance();
+};
+
+/*************************************************************************/
+
 typedef bool (Item::*Item_processor)(byte *arg);
 typedef Item* (Item::*Item_transformer) (byte *arg);
 
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index dbba02c3cf7..b3a7332fe52 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -456,11 +456,30 @@ my_decimal *Item_sum_sum::val_decimal(my_decimal *val)
   return val_decimal_from_real(val);
 }
 
+/***************************************************************************/
 
-/* Item_sum_sum_distinct */
+C_MODE_START
+
+/* Declarations for auxilary C-callbacks */
+
+static int simple_raw_key_cmp(void* arg, const void* key1, const void* key2)
+{
+    return memcmp(key1, key2, *(uint *) arg);
+}
+
+
+static int item_sum_distinct_walk(void *element, element_count num_of_dups,
+                                  void *item)
+{
+    return ((Item_sum_distinct*) (item))->unique_walk_function(element);
+}
+
+C_MODE_END
 
-Item_sum_sum_distinct::Item_sum_sum_distinct(Item *item)
-  :Item_sum_sum(item), tree(0)
+/* Item_sum_distinct */
+
+Item_sum_distinct::Item_sum_distinct(Item *item_arg)
+  :Item_sum_num(item_arg), tree(0)
 {
   /*
     quick_group is an optimizer hint, which means that GROUP BY can be
@@ -472,239 +491,242 @@ Item_sum_sum_distinct::Item_sum_sum_distinct(Item *item)
 }
 
 
-Item_sum_sum_distinct::Item_sum_sum_distinct(THD *thd,
-                                             Item_sum_sum_distinct *original)
-  :Item_sum_sum(thd, original), tree(0), dec_bin_buff(original->dec_bin_buff)
+Item_sum_distinct::Item_sum_distinct(THD *thd, Item_sum_distinct *original)
+  :Item_sum_num(thd, original), val(original->val), tree(0),
+  table_field_type(original->table_field_type)
 {
   quick_group= 0;
 }
 
 
-void Item_sum_sum_distinct::fix_length_and_dec()
+/*
+  Behaves like an Integer except to fix_length_and_dec().
+  Additionally div() converts val with this traits to a val with true
+  decimal traits along with conversion of integer value to decimal value.
+  This is to speedup SUM/AVG(DISTINCT) evaluation for 8-32 bit integer
+  values.
+*/
+
+struct Hybrid_type_traits_fast_decimal: public
+       Hybrid_type_traits_integer
 {
-  Item_sum_sum::fix_length_and_dec();
-  if (hybrid_type == DECIMAL_RESULT)
+  virtual Item_result type() const { return DECIMAL_RESULT; }
+  virtual void fix_length_and_dec(Item *item, Item *arg) const
+  { Hybrid_type_traits_decimal::instance()->fix_length_and_dec(item, arg); }
+
+  virtual void div(Hybrid_type *val, ulonglong u) const
   {
-    dec_bin_buff= (byte *)
-      sql_alloc(my_decimal_get_binary_size(args[0]->max_length,
-                                           args[0]->decimals));
+    int2my_decimal(E_DEC_FATAL_ERROR, val->integer, 0, val->dec_buf);
+    val->used_dec_buf_no= 0;
+    val->traits= Hybrid_type_traits_decimal::instance();
+    val->traits->div(val, u);
   }
-}
+  static const Hybrid_type_traits_fast_decimal *instance()
+  {
+    static const Hybrid_type_traits_fast_decimal fast_decimal_traits;
+    return &fast_decimal_traits;
+  }
+};
 
 
-Item *
-Item_sum_sum_distinct::copy_or_same(THD *thd)
+void Item_sum_distinct::fix_length_and_dec()
 {
-  return new (thd->mem_root) Item_sum_sum_distinct(thd, this);
-}
+  DBUG_ASSERT(args[0]->fixed);
 
-C_MODE_START
+  table_field_type= args[0]->field_type();
 
-static int simple_raw_key_cmp(void* arg, const void* key1, const void* key2)
-{
-  return memcmp(key1, key2, *(uint *) arg);
+  /* Adjust tmp table type according to the chosen aggregation type */
+  switch (args[0]->result_type()) {
+  case STRING_RESULT:
+  case REAL_RESULT:
+    val.traits= Hybrid_type_traits::instance();
+    if (table_field_type != MYSQL_TYPE_FLOAT)
+      table_field_type= MYSQL_TYPE_DOUBLE;
+    break;
+  case INT_RESULT:
+  /*
+    Preserving int8, int16, int32 field types gives ~10% performance boost
+    as the size of result tree becomes significantly smaller.
+    Another speed up we gain by using longlong for intermediate
+    calculations. The range of int64 is enough to hold sum 2^32 distinct
+    integers each <= 2^32.
+  */
+  if (table_field_type == MYSQL_TYPE_INT24 ||
+      table_field_type >= MYSQL_TYPE_TINY &&
+      table_field_type <= MYSQL_TYPE_LONG)
+  {
+    val.traits= Hybrid_type_traits_fast_decimal::instance();
+    break;
+  }
+  table_field_type= MYSQL_TYPE_LONGLONG;
+  /* fallthrough */
+  case DECIMAL_RESULT:
+    val.traits= Hybrid_type_traits_decimal::instance();
+    if (table_field_type != MYSQL_TYPE_LONGLONG)
+      table_field_type= MYSQL_TYPE_NEWDECIMAL;
+    break;
+  case ROW_RESULT:
+  default:
+    DBUG_ASSERT(0);
+  }
+  val.traits->fix_length_and_dec(this, args[0]);
 }
 
-C_MODE_END
-
 
-bool Item_sum_sum_distinct::setup(THD *thd)
+bool Item_sum_distinct::setup(THD *thd)
 {
-  DBUG_ENTER("Item_sum_sum_distinct::setup");
-  SELECT_LEX *select_lex= thd->lex->current_select;
-  /* what does it mean??? */
-  if (select_lex->linkage == GLOBAL_OPTIONS_TYPE)
-    DBUG_RETURN(1);
+  List<create_field> field_list;
+  create_field field_def;                              /* field definition */
+
+  DBUG_ENTER("Item_sum_distinct::setup");
 
   DBUG_ASSERT(tree == 0);                 /* setup can not be called twice */
 
   /*
-    Uniques handles all unique elements in a tree until they can't fit in.
-    Then thee tree is dumped to the temporary file.
-    See class Unique for details.
+    Virtual table and the tree are created anew on each re-execution of
+    PS/SP. Hence all further allocations are performed in the runtime
+    mem_root.
   */
+  if (field_list.push_back(&field_def))
+    return TRUE;
+
   null_value= maybe_null= 1;
-  /*
-    TODO: if underlying item result fits in 4 bytes we can take advantage
-    of it and have tree of long/ulong. It gives 10% performance boost
-  */
+  quick_group= 0;
+
+  DBUG_ASSERT(args[0]->fixed);
+
+  field_def.init_for_tmp_table(table_field_type, args[0]->max_length,
+                               args[0]->decimals, args[0]->maybe_null,
+                               args[0]->unsigned_flag);
+
+  if (! (table= create_virtual_tmp_table(thd, field_list)))
+      return TRUE;
+
+  /* XXX: check that the case of CHAR(0) works OK */
+  tree_key_length= table->s->reclength - table->s->null_bytes;
 
   /*
-    It's safe to use key_length here as even if we do copy_or_same()
-    the new item will just share the old items key_length, which will not
-    change or disappear during the life time of this item.
+    Unique handles all unique elements in a tree until they can't fit
+    in.  Then the tree is dumped to the temporary file. We can use
+    simple_raw_key_cmp because the table contains numbers only; decimals
+    are converted to binary representation as well.
   */
-  key_length= ((hybrid_type == DECIMAL_RESULT) ?
-               my_decimal_get_binary_size(args[0]->max_length,
-                                          args[0]->decimals) :
-               sizeof(double));
-  tree= new Unique(simple_raw_key_cmp, &key_length, key_length,
+  tree= new Unique(simple_raw_key_cmp, &tree_key_length, tree_key_length,
                    thd->variables.max_heap_table_size);
-  DBUG_PRINT("info", ("tree 0x%lx, key length %d", (ulong)tree,
-                      key_length));
-  DBUG_RETURN(tree == 0);
-}
-
-void Item_sum_sum_distinct::clear()
-{
-  DBUG_ENTER("Item_sum_sum_distinct::clear");
-  DBUG_ASSERT(tree != 0);                        /* we always have a tree */
-  null_value= 1; 
-  tree->reset();
-  DBUG_VOID_RETURN;
-}
 
-void Item_sum_sum_distinct::cleanup()
-{
-  Item_sum_num::cleanup();
-  delete tree;
-  tree= 0;
+  DBUG_RETURN(tree == 0);
 }
 
 
-bool Item_sum_sum_distinct::add()
+bool Item_sum_distinct::add()
 {
-  DBUG_ENTER("Item_sum_sum_distinct::add");
-  if (hybrid_type == DECIMAL_RESULT)
+  args[0]->save_in_field(table->field[0], FALSE);
+  if (!table->field[0]->is_null())
   {
-    my_decimal value, *val= args[0]->val_decimal(&value);
-    if (!args[0]->null_value)
-    {
-      DBUG_ASSERT(tree != 0);
-      null_value= 0;
-      my_decimal2binary(E_DEC_FATAL_ERROR, val, (char *) dec_bin_buff,
-                        args[0]->max_length, args[0]->decimals);
-      DBUG_RETURN(tree->unique_add(dec_bin_buff));
-    }
-  }
-  else
-  {
-    /* args[0]->val() may reset args[0]->null_value */
-    double val= args[0]->val_real();
-    if (!args[0]->null_value)
-    {
-      DBUG_ASSERT(tree != 0);
-      null_value= 0;
-      DBUG_PRINT("info", ("real: %lg, tree 0x%lx", val, (ulong)tree));
-      if (val)
-        DBUG_RETURN(tree->unique_add(&val));
-    }
-    else
-      DBUG_PRINT("info", ("real: NULL"));
+    DBUG_ASSERT(tree);
+    null_value= 0;
+    /*
+      '0' values are also stored in the tree. This doesn't matter
+      for SUM(DISTINCT), but is important for AVG(DISTINCT)
+    */
+    return tree->unique_add(table->field[0]->ptr);
   }
-  DBUG_RETURN(0);
+  return 0;
 }
 
 
-void Item_sum_sum_distinct::add_real(double val)
+bool Item_sum_distinct::unique_walk_function(void *element)
 {
-  DBUG_ENTER("Item_sum_sum_distinct::add_real");
-  sum+= val;
-  DBUG_PRINT("info", ("sum %lg, val %lg", sum, val));
-  DBUG_VOID_RETURN;
+  memcpy(table->field[0]->ptr, element, tree_key_length);
+  ++count;
+  val.traits->add(&val, table->field[0]);
+  return 0;
 }
 
 
-void Item_sum_sum_distinct::add_decimal(byte *val)
+void Item_sum_distinct::clear()
 {
-  binary2my_decimal(E_DEC_FATAL_ERROR, (char *) val, &tmp_dec,
-                    args[0]->max_length, args[0]->decimals);
-  my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1),
-                 &tmp_dec, dec_buffs + curr_dec_buff);
-  curr_dec_buff^= 1;
+  DBUG_ENTER("Item_sum_distinct::clear");
+  DBUG_ASSERT(tree != 0);                        /* we always have a tree */
+  null_value= 1;
+  tree->reset();
+  DBUG_VOID_RETURN;
 }
 
-C_MODE_START
-
-static int sum_sum_distinct_real(void *element, element_count num_of_dups,
-                                 void *item_sum_sum_distinct)
-{ 
-  ((Item_sum_sum_distinct *)
-   (item_sum_sum_distinct))->add_real(* (double *) element);
-  return 0;
+void Item_sum_distinct::cleanup()
+{
+  Item_sum_num::cleanup();
+  delete tree;
+  tree= 0;
+  table= 0;
 }
 
-static int sum_sum_distinct_decimal(void *element, element_count num_of_dups,
-                                    void *item_sum_sum_distinct)
-{ 
-  ((Item_sum_sum_distinct *)
-   (item_sum_sum_distinct))->add_decimal((byte *)element);
-  return 0;
+Item_sum_distinct::~Item_sum_distinct()
+{
+  delete tree;
+  /* no need to free the table */
 }
 
-C_MODE_END
-
 
-double Item_sum_sum_distinct::val_real()
+void Item_sum_distinct::calculate_val_and_count()
 {
-  DBUG_ENTER("Item_sum_sum_distinct::val");
+  count= 0;
+  val.traits->set_zero(&val);
   /*
     We don't have a tree only if 'setup()' hasn't been called;
     this is the case of sql_select.cc:return_zero_rows.
   */
-  if (hybrid_type == DECIMAL_RESULT)
+  if (tree)
   {
-    /* Item_sum_sum_distinct::val_decimal do not use argument */
-    my_decimal *val= val_decimal(0);
-    if (!null_value)
-      my_decimal2double(E_DEC_FATAL_ERROR, val, &sum);
-  }
-  else
-  {
-    sum= 0.0;
-    DBUG_PRINT("info", ("tree 0x%lx", (ulong)tree));
-    if (tree) 
-      tree->walk(sum_sum_distinct_real, (void *) this);
+    table->field[0]->set_notnull();
+    tree->walk(item_sum_distinct_walk, (void*) this);
   }
-  DBUG_RETURN(sum);
 }
 
 
-my_decimal *Item_sum_sum_distinct::val_decimal(my_decimal *fake)
+double Item_sum_distinct::val_real()
 {
-  if (hybrid_type == DECIMAL_RESULT)
-  {
-    my_decimal_set_zero(dec_buffs);
-    curr_dec_buff= 0;
-    if (tree)
-      tree->walk(sum_sum_distinct_decimal, (void *)this);
-  }
-  else
-  {
-    double real= val_real();
-    curr_dec_buff= 0;
-    double2my_decimal(E_DEC_FATAL_ERROR, real, dec_buffs);
-  }
-  return(dec_buffs + curr_dec_buff);
+  calculate_val_and_count();
+  return val.traits->val_real(&val);
 }
 
 
-longlong Item_sum_sum_distinct::val_int()
+my_decimal *Item_sum_distinct::val_decimal(my_decimal *to)
 {
-  longlong result;
-  if (hybrid_type == DECIMAL_RESULT)
-  {
-    /* Item_sum_sum_distinct::val_decimal do not use argument */
-    my_decimal *val= val_decimal(0);
-    if (!null_value)
-      my_decimal2int(E_DEC_FATAL_ERROR, val, unsigned_flag, &result);
-  }
-  else
-    result= (longlong) val_real();
-  return result;
+  calculate_val_and_count();
+  if (null_value)
+    return 0;
+  return val.traits->val_decimal(&val, to);
 }
 
 
-String *Item_sum_sum_distinct::val_str(String *str)
+longlong Item_sum_distinct::val_int()
 {
-  DBUG_ASSERT(fixed == 1);
-  if (hybrid_type == DECIMAL_RESULT)
-    return val_string_from_decimal(str);
-  return val_string_from_real(str);
+  calculate_val_and_count();
+  return val.traits->val_int(&val, unsigned_flag);
 }
 
 
-/* end of Item_sum_sum_distinct */
+String *Item_sum_distinct::val_str(String *str)
+{
+  calculate_val_and_count();
+  if (null_value)
+    return 0;
+  return val.traits->val_str(&val, str, decimals);
+}
+
+/* end of Item_sum_distinct */
+
+/* Item_sum_avg_distinct */
+
+void
+Item_sum_avg_distinct::calculate_val_and_count()
+{
+  Item_sum_distinct::calculate_val_and_count();
+  if (count)
+    val.traits->div(&val, count);
+}
+
 
 Item *Item_sum_count::copy_or_same(THD* thd)
 {
diff --git a/sql/item_sum.h b/sql/item_sum.h
index af2710d7800..641acb0efd9 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -30,8 +30,8 @@ class Item_sum :public Item_result_field
 public:
   enum Sumfunctype
   { COUNT_FUNC, COUNT_DISTINCT_FUNC, SUM_FUNC, SUM_DISTINCT_FUNC, AVG_FUNC,
-    MIN_FUNC, MAX_FUNC, UNIQUE_USERS_FUNC, STD_FUNC, VARIANCE_FUNC,
-    SUM_BIT_FUNC, UDF_SUM_FUNC, GROUP_CONCAT_FUNC
+    AVG_DISTINCT_FUNC, MIN_FUNC, MAX_FUNC, UNIQUE_USERS_FUNC, STD_FUNC,
+    VARIANCE_FUNC, SUM_BIT_FUNC, UDF_SUM_FUNC, GROUP_CONCAT_FUNC
   };
 
   Item **args, *tmp_args[2];
@@ -68,6 +68,9 @@ public:
     a temporary table. Similar to reset(), but must also store value in
     result_field. Like reset() it is supposed to reset start value to
     default.
+    This set of methods (reult_field(), reset_field, update_field()) of
+    Item_sum is used only if quick_group is not null. Otherwise
+    copy_or_same() is used to obtain a copy of this item.
   */
   virtual void reset_field()=0;
   /*
@@ -161,26 +164,28 @@ public:
 };
 
 
-/*
-  Item_sum_sum_distinct - SELECT SUM(DISTINCT expr) FROM ... 
-  support. See also: MySQL manual, chapter 'Adding New Functions To MySQL'
-  and comments in item_sum.cc.
-*/
+
+/* Common class for SUM(DISTINCT), AVG(DISTINCT) */
 
 class Unique;
 
-class Item_sum_sum_distinct :public Item_sum_sum
+class Item_sum_distinct :public Item_sum_num
 {
+protected:
+  /* storage for the summation result */
+  ulonglong count;
+  Hybrid_type val;
+  /* storage for unique elements */
   Unique *tree;
-  byte *dec_bin_buff;
-  my_decimal tmp_dec;
-  uint key_length;
-private:
-  Item_sum_sum_distinct(THD *thd, Item_sum_sum_distinct *item);
+  TABLE *table;
+  enum enum_field_types table_field_type;
+  uint tree_key_length;
+protected:
+  Item_sum_distinct(THD *thd, Item_sum_distinct *item);
 public:
-  Item_sum_sum_distinct(Item *item_par);
-  ~Item_sum_sum_distinct() {}
-  
+  Item_sum_distinct(Item *item_par);
+  ~Item_sum_distinct();
+
   bool setup(THD *thd);
   void clear();
   void cleanup();
@@ -190,15 +195,54 @@ public:
   longlong val_int();
   String *val_str(String *str);
 
-  void add_real(double val);
-  void add_decimal(byte *val);
+  /* XXX: does it need make_unique? */
+
   enum Sumfunctype sum_func () const { return SUM_DISTINCT_FUNC; }
   void reset_field() {} // not used
   void update_field() {} // not used
   const char *func_name() const { return "sum_distinct"; }
-  Item *copy_or_same(THD* thd);
   virtual void no_rows_in_result() {}
   void fix_length_and_dec();
+  enum Item_result result_type () const { return val.traits->type(); }
+  virtual void calculate_val_and_count();
+  virtual bool unique_walk_function(void *elem);
+};
+
+
+/*
+  Item_sum_sum_distinct - implementation of SUM(DISTINCT expr).
+  See also: MySQL manual, chapter 'Adding New Functions To MySQL'
+  and comments in item_sum.cc.
+*/
+
+class Item_sum_sum_distinct :public Item_sum_distinct
+{
+private:
+  Item_sum_sum_distinct(THD *thd, Item_sum_sum_distinct *item)
+    :Item_sum_distinct(thd, item) {}
+public:
+  Item_sum_sum_distinct(Item *item_arg) :Item_sum_distinct(item_arg) {}
+
+  enum Sumfunctype sum_func () const { return SUM_DISTINCT_FUNC; }
+  const char *func_name() const { return "sum_distinct"; }
+  Item *copy_or_same(THD* thd) { return new Item_sum_sum_distinct(thd, this); }
+};
+
+
+/* Item_sum_avg_distinct - SELECT AVG(DISTINCT expr) FROM ... */
+
+class Item_sum_avg_distinct: public Item_sum_distinct
+{
+private:
+  Item_sum_avg_distinct(THD *thd, Item_sum_avg_distinct *original)
+    :Item_sum_distinct(thd, original) {}
+public:
+  Item_sum_avg_distinct(Item *item_arg) : Item_sum_distinct(item_arg) {}
+
+  virtual void calculate_val_and_count();
+  enum Sumfunctype sum_func () const { return AVG_DISTINCT_FUNC; }
+  const char *func_name() const { return "avg_distinct"; }
+  Item *copy_or_same(THD* thd) { return new Item_sum_avg_distinct(thd, this); }
 };
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 76d8ec1740a..cb906931b2b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8359,6 +8359,117 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 }
 
 
+/****************************************************************************/
+
+/*
+  Create a reduced TABLE object with properly set up Field list from a
+  list of field definitions.
+
+  SYNOPSIS
+    create_virtual_tmp_table()
+      thd         connection handle
+      field_list  list of column definitions
+
+  DESCRIPTION
+    The created table doesn't have a table handler assotiated with
+    it, has no keys, no group/distinct, no copy_funcs array.
+    The sole purpose of this TABLE object is to use the power of Field
+    class to read/write data to/from table->record[0]. Then one can store
+    the record in any container (RB tree, hash, etc).
+    The table is created in THD mem_root, so are the table's fields.
+    Consequently, if you don't BLOB fields, you don't need to free it.
+
+  RETURN
+    0 if out of memory, TABLE object in case of success
+*/
+
+TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list)
+{
+  uint field_count= field_list.elements;
+  Field **field;
+  create_field *cdef;                           /* column definition */
+  uint record_length= 0;
+  uint null_count= 0;                 /* number of columns which may be null */
+  uint null_pack_length;              /* NULL representation array length */
+  TABLE_SHARE *s;
+  /* Create the table and list of all fields */
+  TABLE *table= (TABLE*) thd->calloc(sizeof(*table));
+  field= (Field**) thd->alloc((field_count + 1) * sizeof(Field*));
+  if (!table || !field)
+    return 0;
+
+  table->field= field;
+  table->s= s= &table->share_not_to_be_used;
+  s->fields= field_count;
+
+  /* Create all fields and calculate the total length of record */
+  List_iterator_fast<create_field> it(field_list);
+  while ((cdef= it++))
+  {
+    *field= make_field(0, cdef->length,
+                       (uchar*) (f_maybe_null(cdef->pack_flag) ? "" : 0),
+                       f_maybe_null(cdef->pack_flag) ? 1 : 0,
+                       cdef->pack_flag, cdef->sql_type, cdef->charset,
+                       cdef->geom_type, cdef->unireg_check,
+                       cdef->interval, cdef->field_name, table);
+    if (!*field)
+      goto error;
+    record_length+= (**field).pack_length();
+    if (! ((**field).flags & NOT_NULL_FLAG))
+      ++null_count;
+    ++field;
+  }
+  *field= NULL;                                 /* mark the end of the list */
+
+  null_pack_length= (null_count + 7)/8;
+  s->reclength= record_length + null_pack_length;
+  s->rec_buff_length= ALIGN_SIZE(s->reclength + 1);
+  table->record[0]= (byte*) thd->alloc(s->rec_buff_length);
+  if (!table->record[0])
+    goto error;
+
+  if (null_pack_length)
+  {
+    table->null_flags= (uchar*) table->record[0];
+    s->null_fields= null_count;
+    s->null_bytes= null_pack_length;
+  }
+
+  table->in_use= thd;           /* field->reset() may access table->in_use */
+  {
+    /* Set up field pointers */
+    byte *null_pos= table->record[0];
+    byte *field_pos= null_pos + s->null_bytes;
+    uint null_bit= 1;
+
+    for (field= table->field; *field; ++field)
+    {
+      Field *cur_field= *field;
+      if ((cur_field->flags & NOT_NULL_FLAG))
+        cur_field->move_field((char*) field_pos);
+      else
+      {
+        cur_field->move_field((char*) field_pos, (uchar*) null_pos, null_bit);
+        null_bit<<= 1;
+        if (null_bit == (1 << 8))
+        {
+          ++null_pos;
+          null_bit= 1;
+        }
+      }
+      cur_field->reset();
+
+      field_pos+= cur_field->pack_length();
+    }
+  }
+  return table;
+error:
+  for (field= table->field; *field; ++field)
+    delete *field;                         /* just invokes field destructor */
+  return 0;
+}
+
+
 static bool open_tmp_table(TABLE *table)
 {
   int error;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 1b7893dbc7c..f00fd476edd 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -387,6 +387,7 @@ TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 			ORDER *group, bool distinct, bool save_sum_fields,
 			ulong select_options, ha_rows rows_limit,
 			char* alias);
+TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list);
 void free_tmp_table(THD *thd, TABLE *entry);
 void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
 		       bool reset_with_sum_func);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 212f004e3bf..56dd6409eba 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -4754,6 +4754,8 @@ udf_expr:
 sum_expr:
 	AVG_SYM '(' in_sum_expr ')'
 	  { $$=new Item_sum_avg($3); }
+	| AVG_SYM '(' DISTINCT in_sum_expr ')'
+	  { $$=new Item_sum_avg_distinct($4); }
 	| BIT_AND  '(' in_sum_expr ')'
 	  { $$=new Item_sum_and($3); }
 	| BIT_OR  '(' in_sum_expr ')'