date:20160314

Re: [Maria-developers] [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function

2016-03-14 Thread Vicențiu Ciorbaru

On Mon, 14 Mar 2016 at 21:43 Sergey Petrunia  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 000..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.199
> > +104  1   10  20  7.199
> > +105  2   20  40  13.2
> > +106  2   20  60  21.35
> > +107  2   20  50  20.35
> > +108  2   10  30  13.252
> > +109  4   20  40  19.3
> > +110  4   20  40  19.3
> > +111  5   NULL1   23.4
> > +112  5   1   1   36.75
> > +113  5   NULL1   40.1
> > +114  5   NULL0   43.5
> > +115  5   NULL0   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 000..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_ERR

Re: [Maria-developers] [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function

2016-03-14 Thread Sergey Petrunia

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 000..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.199
> +104  1   10  20  7.199
> +105  2   20  40  13.2
> +106  2   20  60  21.35
> +107  2   20  50  20.35
> +108  2   10  30  13.252
> +109  4   20  40  19.3
> +110  4   20  40  19.3
> +111  5   NULL1   23.4
> +112  5   1   1   36.75
> +113  5   NULL1   40.1
> +114  5   NULL0   43.5
> +115  5   NULL0   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 000..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 (!

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

2016-03-14 Thread Sergei Golubchik

Hi, Sachin!

On Mar 14, Sachin Setia wrote:
> Hello Developers
>My name is sachin , a third year undergrad student in India.I am
> interested in gsoc 2016
> I was debugging the maria db for following query
> 
> create table c4(a1 int primary key,b1 int unique,c1 blob) engine=myisam;
> 
> In mysql_prepare_create_table  there is a function
> name prepare_create_field which sets the sql_field pack flag . for a1 field
> it sets the pack flag =27  . do not quiet get what it means.

If you check prepare_create_field, you'll see how pack_flag is set.

Regards,
Sergei
Chief Architect MariaDB
and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

2016-03-14 Thread Sachin Setia

Hello Developers
   My name is sachin , a third year undergrad student in India.I am
interested in gsoc 2016
I was debugging the maria db for following query

create table c4(a1 int primary key,b1 int unique,c1 blob) engine=myisam;

In mysql_prepare_create_table  there is a function
name prepare_create_field which sets the sql_field pack flag . for a1 field
it sets the pack flag =27  . do not quiet get what it means.
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function

Re: [Maria-developers] [Commits] a0c06ba: Preliminary implementation for the aggregate sum function as a window function

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

4 matches

Site Navigation

Mail list logo

Footer information