maria-developers team mailing list archive

[Varun Gupta <varungupta1803@xxxxxxxxx>]

With the cursor approach, I think it looks more easier to understand . Also
it covers the point that the state has to be saved when the function is
called for different values of x ,so we know the approach is correct. We
are using the function attribute X in calculating the sum. I don't
understand what you mean by not using declared argument directly.
Well at least the cursor method looks far simpler from the ones we
discussed earlier .

On Wed, Mar 2, 2016 at 6:41 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> Hi, Varun!
>
> On Mar 02, Varun Gupta wrote:
> > Well in HSQL we have the limitation of having maximum of 4 arguments ,
> but
> > in our syntax we can increase the number of arguments.The main thing is
> > what I understood from HSQL and PostgreSQL is that an aggregate function
> > would have 2 states.
> > 1)  when we compute the result that is the result keeps on getting
> updated
> > on various calls to the function
> > 2)  when we return the result
> >
> > HSQL does that using the flag so we need to call an additional time with
> > flag == TRUE so that result is returned .
> > While in PostgreSQL we split the work into two regular functions . The
> > problem is that the second function in PostgreSQL is optional so that
> adds
> > to a bit of confusion.
>
> What about other ideas presented in MDEV-7773? I like the cursor one.
> Compare the standard SQL function:
>
>   CREATE FUNCTION avg() RETURNS DOUBLE
>   BEGIN
>     DECLARE count INT DEFAULT 0;
>     DECLARE sum DOUBLE DEFAULT 0;
>     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
>     DECLARE x DOUBLE;
>     DECLARE cur1 CURSOR FOR SELECT col1 FROM t1;
>     OPEN cur1;
>     LOOP
>       FETCH cur1 INTO x;
>       SET count:=count+1;
>       SET sum:=sum+x;
>     END LOOP;
>   END
>
> This is a regular standard non-aggregate function that returns an
> average of all values in a column 'col1' of the table 't1'.
> The cursor idea of a syntax for an aggregate function uses the same
> familiar logic:
>
>   CREATE AGGREGATE FUNCTION avg(x DOUBLE) RETURNS DOUBLE
>   BEGIN
>     DECLARE count INT DEFAULT 0;
>     DECLARE sum DOUBLE DEFAULT 0;
>     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
>     LOOP
>       FETCH GROUP NEXT ROW;
>       SET count:=count+1;
>       SET sum:=sum+x;
>     END LOOP;
>   END
>
> This looks simple, familiar, natural, leaves no artefacts.
> What I don't like is that a function's declared argument is not used
> directly. Looks a bit artificial :(
>
> > The syntax for ORACLE I did not understand properly, would give it a try
> > again.
>
> Don't bother, it's an object oriented approach. An aggregate function is
> an *object*. Object has different methods for result and for getting
> updated. Object data fields used to store the intermediate state.
> This is quite natural, if the DBMS supports objects. But we don't.
>
> > For SYBASE I have been going through:
> >
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1510/doc/html/asc1238775307180.html
>
> Okay, so "External C/C++."
>
> > For SQL server
> > https://msdn.microsoft.com/en-in/library/ms182741.aspx
>
> Same, "implementation is defined in a class of an assembly in the .NET
> Framework"
>
> > DB2 does not support user-defined aggregate functions
> >
> http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309stolze.html
>
> Right.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and security@xxxxxxxxxxx
>