maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09349
Re: [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function
On Mon, 14 Mar 2016 at 21:43 Sergey Petrunia <sergey@xxxxxxxxxxx> 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@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
>
>
>
References
