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 <[email protected]> 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 <[email protected]> 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 [email protected] >> > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
