[Firebird-devel] ODP: User-defined aggregate functions
Hi, I know that i am not Firebird core developer and I have less influence. But, why do you still say about WHILE, SUSSPEND, YIELD, FETCH if there is simple alternative to not have it at all? Can someone tell me what is wrong with my proposition? -- Instead of simple create aggregate function custom_sum (i integer) returns (o integer) as begin if (i is not null) then begin if (o is null) then o = 0; o = o + i; end end you propose something like this create aggregate function custom_sum (i integer) returns (o integer) as begin while (not agg_finished) do begin if (i is not null) then begin if (o is null) then o = 0; o = o + i; end suspend; end end -- instead of simple: create aggregate function custom_avg (i double precision) returns (o double precision) as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_result_needed and (accumulated is not null) then o = accumulated / agg_i; end you propose: create aggregate function custom_avg (i double precision) returns (o double precision) as declare count integer = 0; declare accumulated double precision = 0; begin while (not agg_finished) do begin if (i is not null) then begin count = count + 1; accumulated = accumulated + i; o = accumulated / count; end suspend; end end -- Can you tell me also how you prevent: - Infinite while do (i know user should use it with care but why increase risk?) - that user do not write SUSPEND/YIELD/FETCH or user call it to many times? - minimize code execution like in avg? Instead of make one division you propose design where you must divide it always row by row. And e.g. if you have 1000 rows with only one grouping key, you have 1000 of additions and 1000 of divisions. -- How work internal SUM, AVG already? They call yead/fetch/suspend or something like this? Or engine send them required state of parameters/variables? regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
>>I think four adjustments makes the initial syntax better: >>- Allow usage of more than one input parameter. Maybe even zero would be >>allowed. ok >> Instead of have special semantics for empty data source, declare the >> constant to be returned for it, say: >> create aggregate function my_count returning 0 for empty data source If >> this clause is omitted, null is returned for empty data source. This will limit functionality. What if you need to return something based on input parameters values? Not real sample but SELECT (SELECT CUSTOM_AGG(T2.A, T1.B, T1.C) FROM T2 WHERE T2.X=T1.Y) FROM T1 And you need to return e.g. T1.B*T1.C if there is empty resultset. With declarative value this will be not possible. >>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. This need to declare what.parameters >>shluld be considered This will be over complicated. And have same problem as above. >>- Instead of agg_finished, adjust SUSPEND (probably with another keyword) to >>somehing like: >>SUSPEND WHEN FETCHED DO WHEN FINISHED DO This is only my humble opinion, but i do not like suspend concept here. Engine know when provide values and when it need result from agg. See my previous email (with „Agg_result_needed”) and criticize it if you see any problems there. regards, Karol Bieniaszewski Od: Adriano dos Santos Fernandes Wysłano: niedziela, 30 września 2018 04:52 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] User-defined aggregate functions I think four adjustments makes the initial syntax better: - Allow usage of more than one input parameter. Maybe even zero would be allowed. - Instead of have special semantics for empty data source, declare the constant to be returned for it, say: create aggregate function my_count returning 0 for empty data source If this clause is omitted, null is returned for empty data source. - A clause to ignore null input values. If used, engine will filter out null input and not pass to the routine. This need to declare what.parameters shluld be considered - Instead of agg_finished, adjust SUSPEND (probably with another keyword) to somehing like: SUSPEND WHEN FETCHED DO WHEN FINISHED DO Adriano (Sorry to not be more detailed, I'm writing from smartphone) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: User-defined aggregate functions
On 29-9-2018 16:46, Karol Bieniaszewski wrote: Hi, Maybe i show my concept not so clearly. Look how simple it is with my proposition and also how simple to understand by users. Your syntax looks too much like a normal function, which I think is confusing. How will your proposal work when the aggregate function is used in for example a window function with an order by? In that case intermediate results are needed. Consider for example the difference between `count(*) over()` and `count(*) over(order by something)`, or say something like (Firebird 4) `avg(something) over(order by something rows between 5 preceding and 5 following)`. I also don't see how your syntax discerns between accumulation and finishing, for example how would the value of `accumulated` in `custom_avg` be retained? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
Hi, Maybe i show my concept not so clearly. Look how simple it is with my proposition and also how simple to understand by users. Adriano, below you can see your samples addapted to my concept. Function is called also for empty resultset once then agg_i=0. Agg_i will control the whole execution choices – for every row it is increased by 1. Vars declaration section is called onece at start of groupung level. this can be simple for use also for udr you can have there udr init for every groupin level – there you declare variables and udr function (body) will be called for every row in grouping level -- Works as standard SUM. create aggregate function custom_sum (i integer) returns (o integer) as begin if agg_i=1 then o = 0; if agg_i>0 then o = o + i; end -- Works as standard AVG. create aggregate function custom_avg (i double precision) returns (o double precision) as declare accumulated double precision = 0; begin if (i is not null) then accumulated = accumulated + i; if (agg_finished and (agg_i>0)) then o = accumulated / agg_i; end -- Works as standard COUNT. create aggregate function custom_count (i integer) returns (o integer) as begin if (agg_finished) then o = agg_i; end -- This function shows the difference of returning value when data set is not empty and returning in function termination when data set is empty. -- select custom_count_plus_1000(1) from rdb$database -- returns 1 -- select custom_count_plus_1000(1) from rdb$database where 1 = 0 -- returns 1000 create aggregate function custom_count_plus_1000 (i integer) returns (o integer) as begin if agg_finished then Begin if agg_i>0 then o = agg_i; else o = 1000; end; end regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: User-defined aggregate functions
Karol, If user code something wrongly or create inifinite loops, wrongly results he/she will have, no matter how things are defined. I like your opinion to not limit to only one input parameters. This limitation seems to not have sense. Adriano On 28/09/2018 16:33, Karol Bieniaszewski wrote: > > Hi, > > > > First – good concept > > > > But, how this will work? > > In every WHILE loop, engine will lock loop until next row is > „fetched” and susspend is called? > > I do not know if you understand me? > > > > What if function will do not call susspend at all or do not run while > loop properly? > > > > Maybe this can be done simpler without while do? Assuming that > function is in WHILE .. DO .. already and every „call to function” do > susspend. > > Also why limiting to only one parameter? > > > > Lets introduce: > > agg_finished – is true in last row in agg grouping level > > agg_i – row numer in the current grouping level > > > > Lets assume: > > Variables are „declared” only once per group level only body is called. > > > > /* while (not agg_finished) do */ > > create aggregate function custom_count_plus_1000 (i integer) returns (o > > integer) > > as > > Begin > > If (agg_i<=1) then > > Begin > > --init vars and more > > o = 0; > > end; > > -- here body of this function > > -- do whatever you want > > o= o + 1; > > > > if (agg_finished) then > > o = o + 1000; > > end > > > > Maybe you can mix this with your proposition > > > > > > Regards, > > Karol Bieniaszewski > > > > > > > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] ODP: User-defined aggregate functions
Hi, First – good concept But, how this will work? In every WHILE loop, engine will lock loop until next row is „fetched” and susspend is called? I do not know if you understand me? What if function will do not call susspend at all or do not run while loop properly? Maybe this can be done simpler without while do? Assuming that function is in WHILE .. DO .. already and every „call to function” do susspend. Also why limiting to only one parameter? Lets introduce: agg_finished – is true in last row in agg grouping level agg_i – row numer in the current grouping level Lets assume: Variables are „declared” only once per group level only body is called. /* while (not agg_finished) do */ create aggregate function custom_count_plus_1000 (i integer) returns (o integer) as Begin If (agg_i<=1) then Begin --init vars and more o = 0; end; -- here body of this function -- do whatever you want o= o + 1; if (agg_finished) then o = o + 1000; end Maybe you can mix this with your proposition Regards, Karol Bieniaszewski Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
