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@gmail.com> 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@mariadb.org> 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.dc0...
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/0309st...
Right.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org