maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09348
Re: [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@xxxxxxxxxxx
> 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
Follow ups
