diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 9 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 10 | ||||
| -rw-r--r-- | sql/item_sum.cc | 31 | ||||
| -rw-r--r-- | sql/item_sum.h | 29 | ||||
| -rw-r--r-- | sql/item_uniq.h | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 32 | ||||
| -rw-r--r-- | sql/sql_select.h | 1 |
7 files changed, 69 insertions, 45 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cf02eda9aba..dd8aeba8563 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1352,3 +1352,12 @@ ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_ select * from t1 where s1 > any (select max(s2) from t1); ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>' drop table t1; +create table t1(toid int,rd int); +create table t2(userid int,pmnew int,pmtotal int); +insert into t2 values(1,0,0),(2,0,0); +insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2); +select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1); +userid pmtotal pmnew calc_total calc_new +1 0 0 9 3 +2 0 0 4 2 +drop table t1, t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 457b64a5a11..c9dba498428 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -912,3 +912,13 @@ select * from t1 where s1 > (select max(s2) from t1); -- error 1266 select * from t1 where s1 > any (select max(s2) from t1); drop table t1; + +# +# aggregate functions reinitialization +# +create table t1(toid int,rd int); +create table t2(userid int,pmnew int,pmtotal int); +insert into t2 values(1,0,0),(2,0,0); +insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2); +select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1); +drop table t1, t2; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 4680be828c3..c429346a2e6 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -223,10 +223,9 @@ Item *Item_sum_sum::copy_or_same(THD* thd) } -bool Item_sum_sum::reset() +void Item_sum_sum::clear() { null_value=1; sum=0.0; - return Item_sum_sum::add(); } @@ -251,10 +250,9 @@ Item *Item_sum_count::copy_or_same(THD* thd) } -bool Item_sum_count::reset() +void Item_sum_count::clear() { - count=0; - return add(); + count= 0; } @@ -286,10 +284,9 @@ Item *Item_sum_avg::copy_or_same(THD* thd) } -bool Item_sum_avg::reset() +void Item_sum_avg::clear() { sum=0.0; count=0; - return Item_sum_avg::add(); } @@ -342,11 +339,10 @@ Item *Item_sum_variance::copy_or_same(THD* thd) } -bool Item_sum_variance::reset() +void Item_sum_variance::clear() { sum=sum_sqr=0.0; count=0; - return Item_sum_variance::add(); } bool Item_sum_variance::add() @@ -592,10 +588,9 @@ longlong Item_sum_bit::val_int() } -bool Item_sum_bit::reset() +void Item_sum_bit::clear() { - bits=reset_bits; - return add(); + bits= reset_bits; } Item *Item_sum_or::copy_or_same(THD* thd) @@ -1280,7 +1275,7 @@ Item *Item_sum_count_distinct::copy_or_same(THD* thd) } -bool Item_sum_count_distinct::reset() +void Item_sum_count_distinct::clear() { if (use_tree) reset_tree(tree); @@ -1290,7 +1285,6 @@ bool Item_sum_count_distinct::reset() table->file->delete_all_rows(); table->file->extra(HA_EXTRA_WRITE_CACHE); } - return add(); } bool Item_sum_count_distinct::add() @@ -1353,11 +1347,11 @@ longlong Item_sum_count_distinct::val_int() #ifdef HAVE_DLOPEN -bool Item_udf_sum::clear() +void Item_udf_sum::clear() { - DBUG_ENTER("Item_udf_sum::reset"); + DBUG_ENTER("Item_udf_sum::clear"); udf.clear(); - DBUG_RETURN(0); + DBUG_VOID_RETURN; } bool Item_udf_sum::add() @@ -1685,7 +1679,7 @@ Item *Item_func_group_concat::copy_or_same(THD* thd) } -bool Item_func_group_concat::reset() +void Item_func_group_concat::clear() { result.length(0); result.copy(); @@ -1699,7 +1693,6 @@ bool Item_func_group_concat::reset() } if (tree_mode) reset_tree(tree); - return add(); } diff --git a/sql/item_sum.h b/sql/item_sum.h index bd946609745..8ceada278b8 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -62,7 +62,8 @@ public: enum Type type() const { return SUM_FUNC_ITEM; } virtual enum Sumfunctype sum_func () const=0; - virtual bool reset()=0; + inline bool reset() { clear(); return add(); }; + virtual void clear()= 0; virtual bool add()=0; virtual void reset_field()=0; virtual void update_field(int offset)=0; @@ -124,7 +125,7 @@ class Item_sum_sum :public Item_sum_num Item_sum_sum(THD *thd, Item_sum_sum &item) :Item_sum_num(thd, item), sum(item.sum) {} enum Sumfunctype sum_func () const {return SUM_FUNC;} - bool reset(); + void clear(); bool add(); double val(); void reset_field(); @@ -151,7 +152,7 @@ class Item_sum_count :public Item_sum_int table_map used_tables() const { return used_table_cache; } bool const_item() const { return !used_table_cache; } enum Sumfunctype sum_func () const { return COUNT_FUNC; } - bool reset(); + void clear(); void no_rows_in_result() { count=0; } bool add(); void make_const(longlong count_arg) { count=count_arg; used_table_cache=0; } @@ -225,7 +226,7 @@ class Item_sum_count_distinct :public Item_sum_int table_map used_tables() const { return used_table_cache; } enum Sumfunctype sum_func () const { return COUNT_DISTINCT_FUNC; } - bool reset(); + void clear(); bool add(); longlong val_int(); void reset_field() { return ;} // Never called @@ -269,7 +270,7 @@ class Item_sum_avg :public Item_sum_num Item_sum_avg(THD *thd, Item_sum_avg &item) :Item_sum_num(thd, item), sum(item.sum), count(item.count) {} enum Sumfunctype sum_func () const {return AVG_FUNC;} - bool reset(); + void clear(); bool add(); double val(); void reset_field(); @@ -322,7 +323,7 @@ class Item_sum_variance : public Item_sum_num Item_sum_num(thd, item), sum(item.sum), sum_sqr(item.sum_sqr), count(item.count) {} enum Sumfunctype sum_func () const { return VARIANCE_FUNC; } - bool reset(); + void clear(); bool add(); double val(); void reset_field(); @@ -391,13 +392,12 @@ class Item_sum_hybrid :public Item_sum table_map used_tables() const { return used_table_cache; } bool const_item() const { return !used_table_cache; } - bool reset() + void clear() { sum=0.0; sum_int=0; value.length(0); null_value=1; - return add(); } double val(); longlong val_int(); @@ -451,7 +451,7 @@ class Item_sum_bit :public Item_sum_int Item_sum_bit(THD *thd, Item_sum_bit &item): Item_sum_int(thd, item), reset_bits(item.reset_bits), bits(item.bits) {} enum Sumfunctype sum_func () const {return SUM_BIT_FUNC;} - bool reset(); + void clear(); longlong val_int(); void reset_field(); void fix_length_and_dec() @@ -509,8 +509,7 @@ public: enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; } virtual bool have_field_update(void) const { return 0; } - bool reset() { return 0; } /* TO BE FIXED */ - bool clear(); + void clear(); bool add(); void reset_field() {}; void update_field(int offset_arg) {}; @@ -592,7 +591,7 @@ class Item_sum_udf_float :public Item_sum_num ~Item_sum_udf_float() {} enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; } double val() { return 0.0; } - bool reset() { return 0; } + bool clear() {} bool add() { return 0; } void update_field(int offset) {} }; @@ -609,7 +608,7 @@ public: enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; } longlong val_int() { return 0; } double val() { return 0; } - bool reset() { return 0; } + void clear() {} bool add() { return 0; } void update_field(int offset) {} }; @@ -629,7 +628,7 @@ public: enum Item_result result_type () const { return STRING_RESULT; } void fix_length_and_dec() { maybe_null=1; max_length=0; } enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; } - bool reset() { return 0; } + void clear() {} bool add() { return 0; } void update_field(int offset) {} }; @@ -715,7 +714,7 @@ class Item_func_group_concat : public Item_sum const char *func_name() const { return "group_concat"; } enum Type type() const { return SUM_FUNC_ITEM; } virtual Item_result result_type () const { return STRING_RESULT; } - bool reset(); + void clear(); bool add(); void reset_field(); bool fix_fields(THD *, TABLE_LIST *, Item **); diff --git a/sql/item_uniq.h b/sql/item_uniq.h index f2c64c4bde9..e41397dac44 100644 --- a/sql/item_uniq.h +++ b/sql/item_uniq.h @@ -41,7 +41,7 @@ public: :Item_sum_num(thd, item) {} double val() { return 0.0; } enum Sumfunctype sum_func () const {return UNIQUE_USERS_FUNC;} - bool reset() { return 0;} + void clear() {} bool add() { return 0; } void reset_field() {} void update_field(int offset) {} diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e6e7abf14a0..37454d93b6d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1003,12 +1003,6 @@ JOIN::reinit() /* Reset of sum functions */ first_record= 0; - if (sum_funcs) - { - Item_sum *func, **func_ptr= sum_funcs; - while ((func= *(func_ptr++))) - func->null_value= 1; - } if (exec_tmp_table1) { @@ -5979,8 +5973,7 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (!join->first_record) { /* No matching rows for group function */ - clear_tables(join); - copy_fields(&join->tmp_table_param); + join->clear(); } if (join->having && join->having->val_int() == 0) error= -1; // Didn't satisfy having @@ -6246,8 +6239,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (!join->first_record) { /* No matching rows for group function */ - clear_tables(join); - copy_fields(&join->tmp_table_param); + join->clear(); } copy_sum_funcs(join->sum_funcs); if (!join->having || join->having->val_int()) @@ -8542,6 +8534,26 @@ int JOIN::rollup_send_data(uint idx) return 0; } +/* + clear results if there are not rows found for group + (end_send_group/end_write_group) + + SYNOPSYS + JOIN::clear() +*/ + +void JOIN::clear() +{ + clear_tables(this); + copy_fields(&tmp_table_param); + + if (sum_funcs) + { + Item_sum *func, **func_ptr= sum_funcs; + while ((func= *(func_ptr++))) + func->clear(); + } +} /**************************************************************************** EXPLAIN handling diff --git a/sql/sql_select.h b/sql/sql_select.h index 208eaaea7bd..9ca46a505aa 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -279,6 +279,7 @@ class JOIN :public Sql_alloc Item_sum ***func); int rollup_send_data(uint idx); bool test_in_subselect(Item **where); + void clear(); }; |