Re: [Maria-developers] [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function
Hi Vicentiu, I think it add_helper() is a really poor choice of name when the function can do removal. Maybe add_or_remove() name would be better? On Mon, Mar 14, 2016 at 03:44:48PM +0200, Vicentiu Ciorbaru wrote:
revision-id: a0c06ba1edb54c8c4705189c0455137a85658297 (mariadb-10.1.8-154-ga0c06ba) parent(s): ce8a0d8e19e7bf1486a2c80ff6b7a30ef35bf99f author: Vicențiu Ciorbaru committer: Vicențiu Ciorbaru timestamp: 2016-03-14 15:42:00 +0200 message:
Preliminary implementation for the aggregate sum function as a window function
This implementation does not deal with the case where removal of elements from the window frame causes the item to turn to a null value.
--- mysql-test/r/win_sum.result | 42 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/win_sum.test | 32 ++++++++++++++++++++++++++++++++ sql/item_sum.cc | 29 +++++++++++++++++++++++++---- sql/item_sum.h | 4 ++++ sql/sql_window.cc | 1 + 5 files changed, 104 insertions(+), 4 deletions(-)
diff --git a/mysql-test/r/win_sum.result b/mysql-test/r/win_sum.result new file mode 100644 index 0000000..1db6c6e --- /dev/null +++ b/mysql-test/r/win_sum.result @@ -0,0 +1,42 @@ +create table t1 ( +pk int primary key, +a int, +b int, +c real +); +insert into t1 values +(101 , 0, 10, 1.1), +(102 , 0, 10, 2.1), +(103 , 1, 10, 3.1), +(104 , 1, 10, 4.1), +(108 , 2, 10, 5.1), +(105 , 2, 20, 6.1), +(106 , 2, 20, 7.1), +(107 , 2, 20, 8.15), +(109 , 4, 20, 9.15), +(110 , 4, 20, 10.15), +(111 , 5, NULL, 11.15), +(112 , 5, 1, 12.25), +(113 , 5, NULL, 13.35), +(114 , 5, NULL, 14.50), +(115 , 5, NULL, 15.65); +select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), +sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a b sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 10 20 3.2 +102 0 10 20 3.2 +103 1 10 20 7.199999999999999 +104 1 10 20 7.199999999999999 +105 2 20 40 13.2 +106 2 20 60 21.35 +107 2 20 50 20.35 +108 2 10 30 13.250000000000002 +109 4 20 40 19.3 +110 4 20 40 19.3 +111 5 NULL 1 23.4 +112 5 1 1 36.75 +113 5 NULL 1 40.1 +114 5 NULL 0 43.5 +115 5 NULL 0 30.15 +drop table t1; diff --git a/mysql-test/t/win_sum.test b/mysql-test/t/win_sum.test new file mode 100644 index 0000000..3c12b08 --- /dev/null +++ b/mysql-test/t/win_sum.test @@ -0,0 +1,32 @@ +create table t1 ( + pk int primary key, + a int, + b int, + c real +); + + +insert into t1 values +(101 , 0, 10, 1.1), +(102 , 0, 10, 2.1), +(103 , 1, 10, 3.1), +(104 , 1, 10, 4.1), +(108 , 2, 10, 5.1), +(105 , 2, 20, 6.1), +(106 , 2, 20, 7.1), +(107 , 2, 20, 8.15), +(109 , 4, 20, 9.15), +(110 , 4, 20, 10.15), +(111 , 5, NULL, 11.15), +(112 , 5, 1, 12.25), +(113 , 5, NULL, 13.35), +(114 , 5, NULL, 14.50), +(115 , 5, NULL, 15.65); + +--sorted_result +select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), + sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) + +from t1; + +drop table t1; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index c78c206..3f4853a 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1318,25 +1318,39 @@ void Item_sum_sum::fix_length_and_dec() bool Item_sum_sum::add() { DBUG_ENTER("Item_sum_sum::add"); + add_helper(false); + DBUG_RETURN(0); +} + +void Item_sum_sum::add_helper(bool perform_removal) +{ + DBUG_ENTER("Item_sum_sum::add_helper"); if (Item_sum_sum::result_type() == DECIMAL_RESULT) { my_decimal value; const my_decimal *val= aggr->arg_val_decimal(&value); if (!aggr->arg_is_null(true)) { - my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1), - val, dec_buffs + curr_dec_buff); + if (perform_removal) + my_decimal_sub(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1), + dec_buffs + curr_dec_buff, val); + else + my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1), + val, dec_buffs + curr_dec_buff); curr_dec_buff^= 1; null_value= 0; } } else { - sum+= aggr->arg_val_real(); + if (perform_removal) + sum-= aggr->arg_val_real(); + else + sum+= aggr->arg_val_real(); if (!aggr->arg_is_null(true)) null_value= 0; } - DBUG_RETURN(0); + DBUG_VOID_RETURN; }
@@ -1386,6 +1400,13 @@ my_decimal *Item_sum_sum::val_decimal(my_decimal *val) return val_decimal_from_real(val); }
+void Item_sum_sum::remove() +{ + DBUG_ENTER("Item_sum_sum::remove"); + add_helper(true); + DBUG_VOID_RETURN; +} + /** Aggregate a distinct row from the distinct hash table.
diff --git a/sql/item_sum.h b/sql/item_sum.h index f1f5b22..24dfd9b 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -773,6 +773,10 @@ class Item_sum_sum :public Item_sum_num, return has_with_distinct() ? "sum(distinct " : "sum("; } Item *copy_or_same(THD* thd); + void remove(); + +private: + void add_helper(bool perform_removal); };
diff --git a/sql/sql_window.cc b/sql/sql_window.cc index df9899e..f0d74b1 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -1584,6 +1584,7 @@ bool JOIN::process_window_functions(List<Item> *curr_fields_list) } case Item_sum::COUNT_FUNC: case Item_sum::SUM_BIT_FUNC: + case Item_sum::SUM_FUNC: { /* Frame-aware window function computation. It does one pass, but _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
On Mon, 14 Mar 2016 at 21:43 Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Vicentiu,
I think it add_helper() is a really poor choice of name when the function can do removal. Maybe add_or_remove() name would be better?
Agreed.
revision-id: a0c06ba1edb54c8c4705189c0455137a85658297 (mariadb-10.1.8-154-ga0c06ba) parent(s): ce8a0d8e19e7bf1486a2c80ff6b7a30ef35bf99f author: Vicențiu Ciorbaru committer: Vicențiu Ciorbaru timestamp: 2016-03-14 15:42:00 +0200 message:
Preliminary implementation for the aggregate sum function as a window function
This implementation does not deal with the case where removal of elements from the window frame causes the item to turn to a null value.
--- mysql-test/r/win_sum.result | 42 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/win_sum.test | 32 ++++++++++++++++++++++++++++++++ sql/item_sum.cc | 29 +++++++++++++++++++++++++---- sql/item_sum.h | 4 ++++ sql/sql_window.cc | 1 + 5 files changed, 104 insertions(+), 4 deletions(-)
diff --git a/mysql-test/r/win_sum.result b/mysql-test/r/win_sum.result new file mode 100644 index 0000000..1db6c6e --- /dev/null +++ b/mysql-test/r/win_sum.result @@ -0,0 +1,42 @@ +create table t1 ( +pk int primary key, +a int, +b int, +c real +); +insert into t1 values +(101 , 0, 10, 1.1), +(102 , 0, 10, 2.1), +(103 , 1, 10, 3.1), +(104 , 1, 10, 4.1), +(108 , 2, 10, 5.1), +(105 , 2, 20, 6.1), +(106 , 2, 20, 7.1), +(107 , 2, 20, 8.15), +(109 , 4, 20, 9.15), +(110 , 4, 20, 10.15), +(111 , 5, NULL, 11.15), +(112 , 5, 1, 12.25), +(113 , 5, NULL, 13.35), +(114 , 5, NULL, 14.50), +(115 , 5, NULL, 15.65); +select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), +sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a b sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 10 20 3.2 +102 0 10 20 3.2 +103 1 10 20 7.199999999999999 +104 1 10 20 7.199999999999999 +105 2 20 40 13.2 +106 2 20 60 21.35 +107 2 20 50 20.35 +108 2 10 30 13.250000000000002 +109 4 20 40 19.3 +110 4 20 40 19.3 +111 5 NULL 1 23.4 +112 5 1 1 36.75 +113 5 NULL 1 40.1 +114 5 NULL 0 43.5 +115 5 NULL 0 30.15 +drop table t1; diff --git a/mysql-test/t/win_sum.test b/mysql-test/t/win_sum.test new file mode 100644 index 0000000..3c12b08 --- /dev/null +++ b/mysql-test/t/win_sum.test @@ -0,0 +1,32 @@ +create table t1 ( + pk int primary key, + a int, + b int, + c real +); + + +insert into t1 values +(101 , 0, 10, 1.1), +(102 , 0, 10, 2.1), +(103 , 1, 10, 3.1), +(104 , 1, 10, 4.1), +(108 , 2, 10, 5.1), +(105 , 2, 20, 6.1), +(106 , 2, 20, 7.1), +(107 , 2, 20, 8.15), +(109 , 4, 20, 9.15), +(110 , 4, 20, 10.15), +(111 , 5, NULL, 11.15), +(112 , 5, 1, 12.25), +(113 , 5, NULL, 13.35), +(114 , 5, NULL, 14.50), +(115 , 5, NULL, 15.65); + +--sorted_result +select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), + sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) + +from t1; + +drop table t1; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index c78c206..3f4853a 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1318,25 +1318,39 @@ void Item_sum_sum::fix_length_and_dec() bool Item_sum_sum::add() { DBUG_ENTER("Item_sum_sum::add"); + add_helper(false); + DBUG_RETURN(0); +} + +void Item_sum_sum::add_helper(bool perform_removal) +{ + DBUG_ENTER("Item_sum_sum::add_helper"); if (Item_sum_sum::result_type() == DECIMAL_RESULT) { my_decimal value; const my_decimal *val= aggr->arg_val_decimal(&value); if (!aggr->arg_is_null(true)) { - my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1), - val, dec_buffs + curr_dec_buff); + if (perform_removal) + my_decimal_sub(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1), + dec_buffs + curr_dec_buff, val); + else + my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff ^ 1), + val, dec_buffs + curr_dec_buff); curr_dec_buff^= 1; null_value= 0; } } else { - sum+= aggr->arg_val_real(); + if (perform_removal) + sum-= aggr->arg_val_real(); + else + sum+= aggr->arg_val_real(); if (!aggr->arg_is_null(true)) null_value= 0; } - DBUG_RETURN(0); + DBUG_VOID_RETURN; }
@@ -1386,6 +1400,13 @@ my_decimal *Item_sum_sum::val_decimal(my_decimal *val) return val_decimal_from_real(val); }
+void Item_sum_sum::remove() +{ + DBUG_ENTER("Item_sum_sum::remove"); + add_helper(true); + DBUG_VOID_RETURN; +} + /** Aggregate a distinct row from the distinct hash table.
diff --git a/sql/item_sum.h b/sql/item_sum.h index f1f5b22..24dfd9b 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -773,6 +773,10 @@ class Item_sum_sum :public Item_sum_num, return has_with_distinct() ? "sum(distinct " : "sum("; } Item *copy_or_same(THD* thd); + void remove(); + +private: + void add_helper(bool perform_removal); };
diff --git a/sql/sql_window.cc b/sql/sql_window.cc index df9899e..f0d74b1 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -1584,6 +1584,7 @@ bool JOIN::process_window_functions(List<Item> *curr_fields_list) } case Item_sum::COUNT_FUNC: case Item_sum::SUM_BIT_FUNC: + case Item_sum::SUM_FUNC: { /* Frame-aware window function computation. It does one
On Mon, Mar 14, 2016 at 03:44:48PM +0200, Vicentiu Ciorbaru wrote: pass, but
_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
participants (2)
-
Sergey Petrunia -
Vicențiu Ciorbaru