Below is the list of changes that have just been committed into a local
5.2 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2155 06/04/11 18:25:53 gkodinov@stripped +6 -0
WL#3220 : Loose index scan for COUNT DISTINCT
Changed the layout :
Item_sum_num-->Item_sum_distinct-->Item_sum_sum_distinct
\ \->Item_sum_avg_distinct
\
\->Item_sum_sum-->Item_sum_avg
to :
Item_sum_num->Item_sum_distinct-->Item_sum_sum-->Item_sum_avg
Merged the Item_sum_sum/Item_sum_sum_distinct and Item_sum_avg/Item_sum_avg_distinct
Proveded switch functions to control the behaviour of the new classes
Extrended the checks to accomodate for the SUM/AVG in addition to COUNT.
sql/sql_yacc.yy
1.466 06/04/11 18:25:47 gkodinov@stripped +4 -4
WL#3220 : Loose index scan for COUNT DISTINCT
Changed the layout :
Item_sum_num-->Item_sum_distinct-->Item_sum_sum_distinct
\ \->Item_sum_avg_distinct
\
\->Item_sum_sum-->Item_sum_avg
to :
Item_sum_num->Item_sum_distinct-->Item_sum_sum-->Item_sum_avg
Merged the Item_sum_sum/Item_sum_sum_distinct and Item_sum_avg/Item_sum_avg_distinct
Proveded switch functions to control the behaviour of the new classes
Extrended the checks to accomodate for the SUM/AVG in addition to COUNT.
sql/sql_select.cc
1.393 06/04/11 18:25:47 gkodinov@stripped +11 -1
WL#3220 : Loose index scan for COUNT DISTINCT
Changed the layout :
Item_sum_num-->Item_sum_distinct-->Item_sum_sum_distinct
\ \->Item_sum_avg_distinct
\
\->Item_sum_sum-->Item_sum_avg
to :
Item_sum_num->Item_sum_distinct-->Item_sum_sum-->Item_sum_avg
Merged the Item_sum_sum/Item_sum_sum_distinct and Item_sum_avg/Item_sum_avg_distinct
Proveded switch functions to control the behaviour of the new classes
Extrended the checks to accomodate for the SUM/AVG in addition to COUNT.
sql/item_sum.h
1.100 06/04/11 18:25:47 gkodinov@stripped +147 -96
WL#3220 : Loose index scan for COUNT DISTINCT
Changed the layout :
Item_sum_num-->Item_sum_distinct-->Item_sum_sum_distinct
\ \->Item_sum_avg_distinct
\
\->Item_sum_sum-->Item_sum_avg
to :
Item_sum_num->Item_sum_distinct-->Item_sum_sum-->Item_sum_avg
Merged the Item_sum_sum/Item_sum_sum_distinct and Item_sum_avg/Item_sum_avg_distinct
Proveded switch functions to control the behaviour of the new classes
Extrended the checks to accomodate for the SUM/AVG in addition to COUNT.
sql/item_sum.cc
1.175 06/04/11 18:25:47 gkodinov@stripped +122 -38
WL#3220 : Loose index scan for COUNT DISTINCT
Changed the layout :
Item_sum_num-->Item_sum_distinct-->Item_sum_sum_distinct
\ \->Item_sum_avg_distinct
\
\->Item_sum_sum-->Item_sum_avg
to :
Item_sum_num->Item_sum_distinct-->Item_sum_sum-->Item_sum_avg
Merged the Item_sum_sum/Item_sum_sum_distinct and Item_sum_avg/Item_sum_avg_distinct
Proveded switch functions to control the behaviour of the new classes
Extrended the checks to accomodate for the SUM/AVG in addition to COUNT.
mysql-test/t/group_min_max.test
1.20 06/04/11 18:25:46 gkodinov@stripped +38 -0
WL#3220 : Loose index scan for COUNT DISTINCT
Test suite extended to check for the AVG/SUM(DISTINCT)
mysql-test/r/group_min_max.result
1.22 06/04/11 18:25:46 gkodinov@stripped +56 -0
WL#3220 : Loose index scan for COUNT DISTINCT
Test suite extended to check for the AVG/SUM(DISTINCT)
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: gkodinov
# Host: rakia.progem.bg
# Root: /home/kgeorge/mysql/5.2/WL3220
--- 1.174/sql/item_sum.cc 2006-04-07 14:07:07 +03:00
+++ 1.175/sql/item_sum.cc 2006-04-11 18:25:47 +03:00
@@ -585,10 +585,11 @@
***********************************************************************/
Item_sum_sum::Item_sum_sum(THD *thd, Item_sum_sum *item)
- :Item_sum_num(thd, item), hybrid_type(item->hybrid_type),
+ :Item_sum_distinct(thd, item), hybrid_type(item->hybrid_type),
curr_dec_buff(item->curr_dec_buff)
{
/* TODO: check if the following assignments are really needed */
+ set_distinct (item->distinct);
if (hybrid_type == DECIMAL_RESULT)
{
my_decimal2decimal(item->dec_buffs, dec_buffs);
@@ -604,9 +605,10 @@
}
-void Item_sum_sum::clear()
+void Item_sum_sum::plain_clear()
{
- DBUG_ENTER("Item_sum_sum::clear");
+ DBUG_ENTER("Item_sum_sum::plain_clear");
+ DBUG_ASSERT(!distinct);
null_value=1;
if (hybrid_type == DECIMAL_RESULT)
{
@@ -619,9 +621,10 @@
}
-void Item_sum_sum::fix_length_and_dec()
+void Item_sum_sum::plain_fix_length_and_dec()
{
- DBUG_ENTER("Item_sum_sum::fix_length_and_dec");
+ DBUG_ENTER("Item_sum_sum::plain_fix_length_and_dec");
+ DBUG_ASSERT(!distinct);
maybe_null=null_value=1;
decimals= args[0]->decimals;
switch (args[0]->result_type()) {
@@ -657,9 +660,10 @@
}
-bool Item_sum_sum::add()
+bool Item_sum_sum::plain_add()
{
- DBUG_ENTER("Item_sum_sum::add");
+ DBUG_ENTER("Item_sum_sum::plain_add");
+ DBUG_ASSERT(!distinct);
if (hybrid_type == DECIMAL_RESULT)
{
my_decimal value, *val= args[0]->val_decimal(&value);
@@ -681,9 +685,10 @@
}
-longlong Item_sum_sum::val_int()
+longlong Item_sum_sum::plain_val_int()
{
DBUG_ASSERT(fixed == 1);
+ DBUG_ASSERT(!distinct);
if (hybrid_type == DECIMAL_RESULT)
{
longlong result;
@@ -695,25 +700,28 @@
}
-double Item_sum_sum::val_real()
+double Item_sum_sum::plain_val_real()
{
DBUG_ASSERT(fixed == 1);
+ DBUG_ASSERT(!distinct);
if (hybrid_type == DECIMAL_RESULT)
my_decimal2double(E_DEC_FATAL_ERROR, dec_buffs + curr_dec_buff, &sum);
return sum;
}
-String *Item_sum_sum::val_str(String *str)
+String *Item_sum_sum::plain_val_str(String *str)
{
+ DBUG_ASSERT(!distinct);
if (hybrid_type == DECIMAL_RESULT)
return val_string_from_decimal(str);
return val_string_from_real(str);
}
-my_decimal *Item_sum_sum::val_decimal(my_decimal *val)
+my_decimal *Item_sum_sum::plain_val_decimal(my_decimal *val)
{
+ DBUG_ASSERT(!distinct);
if (hybrid_type == DECIMAL_RESULT)
return (dec_buffs + curr_dec_buff);
return val_decimal_from_real(val);
@@ -795,7 +803,7 @@
return &fast_decimal_traits_instance;
}
-void Item_sum_distinct::fix_length_and_dec()
+void Item_sum_distinct::distinct_fix_length_and_dec()
{
DBUG_ASSERT(args[0]->fixed);
@@ -839,7 +847,7 @@
}
-bool Item_sum_distinct::setup(THD *thd)
+bool Item_sum_distinct::distinct_setup(THD *thd)
{
List<create_field> field_list;
create_field field_def; /* field definition */
@@ -882,7 +890,7 @@
}
-bool Item_sum_distinct::add()
+bool Item_sum_distinct::distinct_add()
{
args[0]->save_in_field(table->field[0], FALSE);
if (!table->field[0]->is_null())
@@ -908,7 +916,7 @@
}
-void Item_sum_distinct::clear()
+void Item_sum_distinct::distinct_clear()
{
DBUG_ENTER("Item_sum_distinct::clear");
DBUG_ASSERT(tree != 0); /* we always have a tree */
@@ -917,7 +925,7 @@
DBUG_VOID_RETURN;
}
-void Item_sum_distinct::cleanup()
+void Item_sum_distinct::distinct_cleanup()
{
Item_sum_num::cleanup();
delete tree;
@@ -948,14 +956,14 @@
}
-double Item_sum_distinct::val_real()
+double Item_sum_distinct::distinct_val_real()
{
calculate_val_and_count();
return val.traits->val_real(&val);
}
-my_decimal *Item_sum_distinct::val_decimal(my_decimal *to)
+my_decimal *Item_sum_distinct::distinct_val_decimal(my_decimal *to)
{
calculate_val_and_count();
if (null_value)
@@ -964,14 +972,14 @@
}
-longlong Item_sum_distinct::val_int()
+longlong Item_sum_distinct::distinct_val_int()
{
calculate_val_and_count();
return val.traits->val_int(&val, unsigned_flag);
}
-String *Item_sum_distinct::val_str(String *str)
+String *Item_sum_distinct::distinct_val_str(String *str)
{
calculate_val_and_count();
if (null_value)
@@ -984,9 +992,10 @@
/* Item_sum_avg_distinct */
void
-Item_sum_avg_distinct::fix_length_and_dec()
+Item_sum_avg::distinct_fix_length_and_dec()
{
- Item_sum_distinct::fix_length_and_dec();
+ DBUG_ASSERT(distinct);
+ Item_sum_distinct::distinct_fix_length_and_dec();
prec_increment= current_thd->variables.div_precincrement;
/*
AVG() will divide val by count. We need to reserve digits
@@ -997,8 +1006,9 @@
void
-Item_sum_avg_distinct::calculate_val_and_count()
+Item_sum_avg::calculate_val_and_count()
{
+ DBUG_ASSERT(distinct);
Item_sum_distinct::calculate_val_and_count();
if (count)
val.traits->div(&val, count);
@@ -1103,9 +1113,10 @@
/*
Avgerage
*/
-void Item_sum_avg::fix_length_and_dec()
+void Item_sum_avg::plain_fix_length_and_dec()
{
- Item_sum_sum::fix_length_and_dec();
+ DBUG_ASSERT(!distinct);
+ Item_sum_sum::plain_fix_length_and_dec();
maybe_null=null_value=1;
prec_increment= current_thd->variables.div_precincrement;
if (hybrid_type == DECIMAL_RESULT)
@@ -1155,23 +1166,23 @@
}
-void Item_sum_avg::clear()
+void Item_sum_avg::plain_clear()
{
- Item_sum_sum::clear();
+ Item_sum_sum::plain_clear();
count=0;
}
-bool Item_sum_avg::add()
+bool Item_sum_avg::plain_add()
{
- if (Item_sum_sum::add())
+ if (Item_sum_sum::plain_add())
return TRUE;
if (!args[0]->null_value)
count++;
return FALSE;
}
-double Item_sum_avg::val_real()
+double Item_sum_avg::plain_val_real()
{
DBUG_ASSERT(fixed == 1);
if (!count)
@@ -1179,11 +1190,11 @@
null_value=1;
return 0.0;
}
- return Item_sum_sum::val_real() / ulonglong2double(count);
+ return Item_sum_sum::plain_val_real() / ulonglong2double(count);
}
-my_decimal *Item_sum_avg::val_decimal(my_decimal *val)
+my_decimal *Item_sum_avg::plain_val_decimal(my_decimal *val)
{
my_decimal sum, cnt;
const my_decimal *sum_dec;
@@ -1193,14 +1204,14 @@
null_value=1;
return NULL;
}
- sum_dec= Item_sum_sum::val_decimal(&sum);
+ sum_dec= Item_sum_sum::plain_val_decimal(&sum);
int2my_decimal(E_DEC_FATAL_ERROR, count, 0, &cnt);
my_decimal_div(E_DEC_FATAL_ERROR, val, sum_dec, &cnt, prec_increment);
return val;
}
-String *Item_sum_avg::val_str(String *str)
+String *Item_sum_avg::plain_val_str(String *str)
{
if (hybrid_type == DECIMAL_RESULT)
return val_string_from_decimal(str);
@@ -1965,7 +1976,7 @@
}
-void Item_sum_sum::reset_field()
+void Item_sum_sum::plain_reset_field()
{
if (hybrid_type == DECIMAL_RESULT)
{
@@ -2005,7 +2016,7 @@
}
-void Item_sum_avg::reset_field()
+void Item_sum_avg::plain_reset_field()
{
char *res=result_field->ptr;
if (hybrid_type == DECIMAL_RESULT)
@@ -2059,7 +2070,7 @@
** calc next value and merge it with field_value
*/
-void Item_sum_sum::update_field()
+void Item_sum_sum::plain_update_field()
{
if (hybrid_type == DECIMAL_RESULT)
{
@@ -2097,6 +2108,64 @@
}
+void Item_sum_sum::set_distinct(bool is_distinct)
+{
+ distinct = is_distinct;
+ quick_group = !is_distinct;
+ if (distinct)
+ {
+ setup_ptr= &Item_sum_sum::distinct_setup;
+ clear_ptr= &Item_sum_sum::distinct_clear;
+ add_ptr= &Item_sum_sum::distinct_add;
+ sum_func_ptr= &Item_sum_sum::distinct_sum_func;
+ val_real_ptr= &Item_sum_sum::distinct_val_real;
+ val_int_ptr= &Item_sum_sum::distinct_val_int;
+ val_str_ptr= &Item_sum_sum::distinct_val_str;
+ val_decimal_ptr= &Item_sum_sum::distinct_val_decimal;
+ result_type_ptr= &Item_sum_sum::distinct_result_type;
+ reset_field_ptr= &Item_sum_sum::distinct_reset_field;
+ update_field_ptr= &Item_sum_sum::distinct_update_field;
+ no_rows_in_result_ptr= &Item_sum_sum::distinct_no_rows_in_result;
+ func_name_ptr= &Item_sum_sum::distinct_func_name;
+ fix_length_and_dec_ptr= &Item_sum_sum::distinct_fix_length_and_dec;
+ cleanup_ptr= &Item_sum_sum::distinct_cleanup;
+ }
+ else
+ {
+ setup_ptr= &Item_sum_sum::plain_setup;
+ clear_ptr= &Item_sum_sum::plain_clear;
+ add_ptr= &Item_sum_sum::plain_add;
+ sum_func_ptr= &Item_sum_sum::plain_sum_func;
+ val_real_ptr= &Item_sum_sum::plain_val_real;
+ val_int_ptr= &Item_sum_sum::plain_val_int;
+ val_str_ptr= &Item_sum_sum::plain_val_str;
+ val_decimal_ptr= &Item_sum_sum::plain_val_decimal;
+ result_type_ptr= &Item_sum_sum::plain_result_type;
+ reset_field_ptr= &Item_sum_sum::plain_reset_field;
+ update_field_ptr= &Item_sum_sum::plain_update_field;
+ no_rows_in_result_ptr= &Item_sum_sum::plain_no_rows_in_result;
+ func_name_ptr= &Item_sum_sum::plain_func_name;
+ fix_length_and_dec_ptr= &Item_sum_sum::plain_fix_length_and_dec;
+ cleanup_ptr= &Item_sum_sum::plain_cleanup;
+ }
+
+ DBUG_ASSERT(setup_ptr);
+ DBUG_ASSERT(clear_ptr);
+ DBUG_ASSERT(add_ptr);
+ DBUG_ASSERT(sum_func_ptr);
+ DBUG_ASSERT(val_real_ptr);
+ DBUG_ASSERT(val_int_ptr);
+ DBUG_ASSERT(val_str_ptr);
+ DBUG_ASSERT(val_decimal_ptr);
+ DBUG_ASSERT(result_type_ptr);
+ DBUG_ASSERT(reset_field_ptr);
+ DBUG_ASSERT(update_field_ptr);
+ DBUG_ASSERT(no_rows_in_result_ptr);
+ DBUG_ASSERT(func_name_ptr);
+ DBUG_ASSERT(fix_length_and_dec_ptr);
+ DBUG_ASSERT(cleanup_ptr);
+}
+
void Item_sum_count::plain_update_field()
{
DBUG_ASSERT(!distinct);
@@ -2116,7 +2185,7 @@
}
-void Item_sum_avg::update_field()
+void Item_sum_avg::plain_update_field()
{
longlong field_count;
char *res=result_field->ptr;
@@ -2152,6 +2221,21 @@
field_count++;
int8store(res, field_count);
}
+ }
+}
+
+
+void Item_sum_avg::set_distinct (bool is_distinct)
+{
+ Item_sum_sum::set_distinct(is_distinct);
+
+ if (distinct)
+ {
+ result_item_ptr = &Item_sum_avg::distinct_result_item;
+ }
+ else
+ {
+ result_item_ptr = &Item_sum_avg::plain_result_item;
}
}
--- 1.99/sql/item_sum.h 2006-04-07 14:07:07 +03:00
+++ 1.100/sql/item_sum.h 2006-04-11 18:25:47 +03:00
@@ -353,36 +353,6 @@
{ decimals=0; max_length=21; maybe_null=null_value=0; }
};
-
-class Item_sum_sum :public Item_sum_num
-{
-protected:
- Item_result hybrid_type;
- double sum;
- my_decimal dec_buffs[2];
- uint curr_dec_buff;
- void fix_length_and_dec();
-
-public:
- Item_sum_sum(Item *item_par) :Item_sum_num(item_par) {}
- Item_sum_sum(THD *thd, Item_sum_sum *item);
- enum Sumfunctype sum_func () const {return SUM_FUNC;}
- void clear();
- bool add();
- double val_real();
- longlong val_int();
- String *val_str(String*str);
- my_decimal *val_decimal(my_decimal *);
- enum Item_result result_type () const { return hybrid_type; }
- void reset_field();
- void update_field();
- void no_rows_in_result() {}
- const char *func_name() const { return "sum("; }
- Item *copy_or_same(THD* thd);
-};
-
-
-
/* Common class for SUM(DISTINCT), AVG(DISTINCT) */
class Unique;
@@ -400,70 +370,118 @@
uint tree_key_length;
protected:
Item_sum_distinct(THD *thd, Item_sum_distinct *item);
-public:
Item_sum_distinct(Item *item_par);
~Item_sum_distinct();
- bool setup(THD *thd);
- void clear();
- void cleanup();
- bool add();
- double val_real();
- my_decimal *val_decimal(my_decimal *);
- longlong val_int();
- String *val_str(String *str);
+ bool distinct_setup(THD *thd);
+ void distinct_clear();
+ void distinct_cleanup();
+ bool distinct_add();
+ double distinct_val_real();
+ my_decimal *distinct_val_decimal(my_decimal *);
+ longlong distinct_val_int();
+ String *distinct_val_str(String *str);
/* 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
- virtual void no_rows_in_result() {}
- void fix_length_and_dec();
- enum Item_result result_type () const { return val.traits->type(); }
+ virtual enum Sumfunctype distinct_sum_func () const { return SUM_DISTINCT_FUNC; }
+ void distinct_reset_field() {} // not used
+ void distinct_update_field() {} // not used
+ void distinct_no_rows_in_result() {}
+ virtual void distinct_fix_length_and_dec();
+ virtual const char *distinct_func_name() const = 0;
+ enum Item_result distinct_result_type () const { return val.traits->type(); }
+
+public:
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
+class Item_sum_sum :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) {}
+protected:
+ /* common part */
+ bool distinct;
- 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); }
-};
+ /* pointers */
+ bool (Item_sum_sum::*setup_ptr) (THD *thd);
+ void (Item_sum_sum::*clear_ptr) ();
+ bool (Item_sum_sum::*add_ptr) ();
+ enum Sumfunctype (Item_sum_sum::*sum_func_ptr) () const;
+ double (Item_sum_sum::*val_real_ptr) ();
+ longlong (Item_sum_sum::*val_int_ptr) ();
+ String *(Item_sum_sum::*val_str_ptr) (String *str);
+ my_decimal *(Item_sum_sum::*val_decimal_ptr) (my_decimal *d);
+ enum Item_result (Item_sum_sum::*result_type_ptr) () const;
+ void (Item_sum_sum::*reset_field_ptr) ();
+ void (Item_sum_sum::*update_field_ptr) ();
+ void (Item_sum_sum::*no_rows_in_result_ptr) ();
+ const char *(Item_sum_sum::*func_name_ptr) () const;
+ void (Item_sum_sum::*fix_length_and_dec_ptr) ();
+ void (Item_sum_sum::*cleanup_ptr) ();
+
+ /* plain part */
+ Item_result hybrid_type;
+ double sum;
+ my_decimal dec_buffs[2];
+ uint curr_dec_buff;
+protected:
-/* Item_sum_avg_distinct - SELECT AVG(DISTINCT expr) FROM ... */
+ /* plain part */
+ virtual bool plain_setup(THD *thd) { return Item_sum_num::setup(thd); }
+ virtual enum Sumfunctype plain_sum_func () const {return SUM_FUNC;}
+ virtual void plain_clear();
+ virtual bool plain_add();
+ virtual double plain_val_real();
+ virtual longlong plain_val_int();
+ virtual String *plain_val_str(String*str);
+ virtual my_decimal *plain_val_decimal(my_decimal *);
+ virtual enum Item_result plain_result_type () const { return hybrid_type; }
+ virtual void plain_reset_field();
+ virtual void plain_update_field();
+ virtual void plain_no_rows_in_result() {}
+ virtual const char *plain_func_name() const { return "sum("; }
+ virtual void plain_fix_length_and_dec(); /*{ Item_sum_num::fix_length_and_dec(); } */
+ void plain_cleanup() { Item_sum_num::cleanup(); }
+
+ /* distinct part */
+ enum Sumfunctype distinct_sum_func () const { return SUM_DISTINCT_FUNC; }
+ const char *distinct_func_name() const { return "sum(distinct "; }
-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:
- uint prec_increment;
- Item_sum_avg_distinct(Item *item_arg) : Item_sum_distinct(item_arg) {}
+ Item_sum_sum(Item *item_par, bool is_distinct) :
+ Item_sum_distinct(item_par)
+ {
+ set_distinct(is_distinct);
+ }
+ Item_sum_sum(THD *thd, Item_sum_sum *item);
+ Item *copy_or_same(THD* thd);
- void fix_length_and_dec();
- 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); }
+ bool setup(THD *thd) { return (this->*setup_ptr)(thd); }
+ void clear() { return (this->*clear_ptr) (); }
+ bool add() { return (this->*add_ptr) (); }
+ enum Sumfunctype sum_func() const { return (this->*sum_func_ptr) (); }
+ double val_real () { return (this->*val_real_ptr) (); }
+ longlong val_int() { return (this->*val_int_ptr) (); }
+ String *val_str(String *str) { return (this->*val_str_ptr) (str); }
+ my_decimal *val_decimal(my_decimal *d)
+ {
+ return (this->*val_decimal_ptr) (d);
+ }
+ enum Item_result result_type() const { return (this->*result_type_ptr) (); }
+ void reset_field() { (this->*reset_field_ptr) (); }
+ void update_field() { (this->*update_field_ptr) (); }
+ void no_rows_in_result() { (this->*no_rows_in_result_ptr) (); }
+ const char *func_name () const { return (this->*func_name_ptr) (); }
+ void fix_length_and_dec () { (this->*fix_length_and_dec_ptr) (); }
+ void clean () { (this->*cleanup_ptr) (); }
+
+ virtual void set_distinct (bool is_distinct);
};
+
class TMP_TABLE_PARAM;
@@ -642,36 +660,69 @@
enum Item_result result_type () const { return hybrid_type; }
};
-
class Item_sum_avg :public Item_sum_sum
{
+protected:
+ Item *(Item_sum_avg::*result_item_ptr) (Field *field);
+
public:
- ulonglong count;
+ /* plain part */
+ uint f_precision, f_scale, dec_bin_size;
+
+ /* common part */
uint prec_increment;
- uint f_precision, f_scale, dec_bin_size;
- Item_sum_avg(Item *item_par) :Item_sum_sum(item_par), count(0) {}
- Item_sum_avg(THD *thd, Item_sum_avg *item)
- :Item_sum_sum(thd, item), count(item->count),
- prec_increment(item->prec_increment) {}
+protected:
- void fix_length_and_dec();
- enum Sumfunctype sum_func () const {return AVG_FUNC;}
- void clear();
- bool add();
- double val_real();
+ /* plain part */
+ bool plain_setup(THD *thd) { return Item_sum_sum::plain_setup(thd); }
+ void plain_fix_length_and_dec();
+ enum Sumfunctype plain_sum_func () const {return AVG_FUNC;}
+ void plain_clear();
+ bool plain_add();
+ double plain_val_real();
// In SPs we might force the "wrong" type with select into a declare variable
- longlong val_int() { return (longlong) rint(val_real()); }
- my_decimal *val_decimal(my_decimal *);
- String *val_str(String *str);
- void reset_field();
- void update_field();
- Item *result_item(Field *field)
- { return new Item_avg_field(hybrid_type, this); }
- void no_rows_in_result() {}
- const char *func_name() const { return "avg("; }
+ longlong plain_val_int() { return (longlong) rint(plain_val_real()); }
+ my_decimal *plain_val_decimal(my_decimal *);
+ String *plain_val_str(String *str);
+ void plain_reset_field();
+ void plain_update_field();
+ Item *plain_result_item(Field *field)
+ {
+ return new Item_avg_field(hybrid_type, this);
+ }
+ void plain_no_rows_in_result() {}
+ const char *plain_func_name() const { return "avg("; }
+
+ /* distinct part */
+ void distinct_fix_length_and_dec();
+ enum Sumfunctype distinct_sum_func () const { return AVG_DISTINCT_FUNC; }
+ const char *distinct_func_name() const { return "avg(distinct "; }
+ Item *distinct_result_item(Field *field)
+ {
+ return Item_sum::result_item(field);
+ }
+
+public:
+ Item_sum_avg(Item *item_par, bool is_distinct) :
+ Item_sum_sum(item_par, is_distinct)
+ {
+ set_distinct(is_distinct);
+ }
+ Item_sum_avg(THD *thd, Item_sum_avg *item)
+ :Item_sum_sum(thd, item),
+ prec_increment(item->prec_increment)
+ {
+ set_distinct(item->distinct);
+ }
+
Item *copy_or_same(THD* thd);
- Field *create_tmp_field(bool group, TABLE *table, uint convert_blob_length);
+ virtual void calculate_val_and_count();
+ Field *create_tmp_field(bool group, TABLE *table,
+ uint convert_blob_length);
+
+ void set_distinct (bool is_distinct);
+ Item *result_item(Field *field) { return (this->*result_item_ptr) (field); }
};
class Item_sum_variance;
--- 1.392/sql/sql_select.cc 2006-04-07 14:07:07 +03:00
+++ 1.393/sql/sql_select.cc 2006-04-11 18:25:47 +03:00
@@ -1081,6 +1081,14 @@
case Item_sum::COUNT_DISTINCT_FUNC:
((Item_sum_count *)item)->set_distinct(false);
break;
+ case Item_sum::AVG_DISTINCT_FUNC:
+ case Item_sum::SUM_DISTINCT_FUNC:
+ {
+ Item_sum_sum *sum_item = (Item_sum_sum *)item;
+ sum_item->set_distinct(false);
+ sum_item->fix_length_and_dec();
+ }
+ break;
default:
DBUG_ASSERT(0);
DBUG_RETURN(1);
@@ -3275,7 +3283,9 @@
Item_sum *sum_item= join->sum_funcs[0];
/* aggregate is not AGGFN(DISTINCT) or more than 1 argument to it */
- if (sum_item->sum_func() != Item_sum::COUNT_DISTINCT_FUNC
+ if (sum_item->sum_func() != Item_sum::COUNT_DISTINCT_FUNC &&
+ sum_item->sum_func() != Item_sum::SUM_DISTINCT_FUNC &&
+ sum_item->sum_func() != Item_sum::AVG_DISTINCT_FUNC
/*|| sum_item->arg_count != 1*/)
return false;
--- 1.465/sql/sql_yacc.yy 2006-04-07 14:07:07 +03:00
+++ 1.466/sql/sql_yacc.yy 2006-04-11 18:25:47 +03:00
@@ -6726,9 +6726,9 @@
sum_expr:
AVG_SYM '(' in_sum_expr ')'
- { $$=new Item_sum_avg($3); }
+ { $$=new Item_sum_avg($3, false); }
| AVG_SYM '(' DISTINCT in_sum_expr ')'
- { $$=new Item_sum_avg_distinct($4); }
+ { $$=new Item_sum_avg($4, true); }
| BIT_AND '(' in_sum_expr ')'
{ $$=new Item_sum_and($3); }
| BIT_OR '(' in_sum_expr ')'
@@ -6769,9 +6769,9 @@
| VAR_SAMP_SYM '(' in_sum_expr ')'
{ $$=new Item_sum_variance($3, 1); }
| SUM_SYM '(' in_sum_expr ')'
- { $$=new Item_sum_sum($3); }
+ { $$=new Item_sum_sum($3, false); }
| SUM_SYM '(' DISTINCT in_sum_expr ')'
- { $$=new Item_sum_sum_distinct($4); }
+ { $$=new Item_sum_sum($4, true); }
| GROUP_CONCAT_SYM '(' opt_distinct
{ Select->in_sum_expr++; }
expr_list opt_gorder_clause
--- 1.21/mysql-test/r/group_min_max.result 2006-04-07 14:07:06 +03:00
+++ 1.22/mysql-test/r/group_min_max.result 2006-04-11 18:25:46 +03:00
@@ -2114,4 +2114,60 @@
SELECT COUNT(DISTINCT a), 12 FROM t1;
COUNT(DISTINCT a) 12
2 12
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by
+SELECT SUM(DISTINCT a) FROM t1;
+SUM(DISTINCT a)
+3
+EXPLAIN SELECT SUM(DISTINCT b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT DISTINCT SUM(DISTINCT a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT SUM(DISTINCT a + 0) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1 having SUM(DISTINCT b) < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT SUM(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1_1 index NULL a 10 NULL 16 Using index; Using temporary; Using filesort
+1 SIMPLE t1_2 index NULL a 10 NULL 16 Using index
+SELECT SUM(DISTINCT a), 12 FROM t1;
+SUM(DISTINCT a) 12
+3 12
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by
+SELECT AVG(DISTINCT a) FROM t1;
+AVG(DISTINCT a)
+1.5000
+EXPLAIN SELECT AVG(DISTINCT b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1 GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT DISTINCT AVG(DISTINCT a) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT AVG(DISTINCT a + 0) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1 having AVG(DISTINCT b) < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index
+EXPLAIN SELECT AVG(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1_1 index NULL a 10 NULL 16 Using index; Using temporary; Using filesort
+1 SIMPLE t1_2 index NULL a 10 NULL 16 Using index
+SELECT AVG(DISTINCT a), 12 FROM t1;
+AVG(DISTINCT a) 12
+1.5000 12
DROP TABLE t1;
--- 1.19/mysql-test/t/group_min_max.test 2006-04-07 14:07:07 +03:00
+++ 1.20/mysql-test/t/group_min_max.test 2006-04-11 18:25:46 +03:00
@@ -741,11 +741,13 @@
# WL#3220 (Loose index scan for COUNT DISTINCT)
+
CREATE TABLE t1 (a INT, b INT, c INT, KEY (a,b));
INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1), (1,4,1);
INSERT INTO t1 SELECT a, b + 4, 1 FROM t1;
INSERT INTO t1 SELECT a + 1, b, 1 FROM t1;
+# COUNT(DISTINCT) tests
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1;
SELECT COUNT(DISTINCT a) FROM t1;
@@ -768,5 +770,41 @@
EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
SELECT COUNT(DISTINCT a), 12 FROM t1;
+
+# SUM(DISTINCT) tests
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1;
+SELECT SUM(DISTINCT a) FROM t1;
+
+EXPLAIN SELECT SUM(DISTINCT b) FROM t1;
+
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1 GROUP BY a;
+
+EXPLAIN SELECT DISTINCT SUM(DISTINCT a) FROM t1;
+
+EXPLAIN SELECT SUM(DISTINCT a + 0) FROM t1;
+
+EXPLAIN SELECT SUM(DISTINCT a) FROM t1 having SUM(DISTINCT b) < 10;
+
+EXPLAIN SELECT SUM(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
+
+SELECT SUM(DISTINCT a), 12 FROM t1;
+
+# AVG(DISTINCT) tests
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1;
+SELECT AVG(DISTINCT a) FROM t1;
+
+EXPLAIN SELECT AVG(DISTINCT b) FROM t1;
+
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1 GROUP BY a;
+
+EXPLAIN SELECT DISTINCT AVG(DISTINCT a) FROM t1;
+
+EXPLAIN SELECT AVG(DISTINCT a + 0) FROM t1;
+
+EXPLAIN SELECT AVG(DISTINCT a) FROM t1 having AVG(DISTINCT b) < 10;
+
+EXPLAIN SELECT AVG(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
+
+SELECT AVG(DISTINCT a), 12 FROM t1;
DROP TABLE t1;
| Thread |
|---|
| • bk commit into 5.2 tree (gkodinov:1.2155) | kgeorge | 11 Apr |
