maria-developers team mailing list archive

[Varun Gupta <varungupta1803@xxxxxxxxx>]

With the syntax more or less clear to us , can we have a discussion about
how we have to go on with the implementation for the aggregate functions.
Firstly I thought I should look at the implementation of CREATE FUNCTION,
any other suggestions ?

On Thu, Mar 3, 2016 at 4:56 PM, Varun Gupta <varungupta1803@xxxxxxxxx>
wrote:

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