maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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