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.
 
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