Dan,
Yes, the exact query looks like this:
with gsrange(f, t) as (values(1, 10))
select if(value == f, null, value-1) as rowvalue_1,
value as rowvalue_2,
if(value == t, null, value+1) as rowvalue_3,
if(value == f or value == t,
(if(value == f, 0, value-1) + value + if(value == t, 0, value+1)) /
2.0,
value) as checkresult,
avg(value) over (rows between 1 preceding and 1 following) as
windowresult
from generate_series
join gsrange
where start=f
and stop=t;
It now returns a "avg() may not be used as a window function" error.
(the if(expr1, expr2, expr3) function implementes pretty much expr1 ? expr2 :
expr3 (ie, case when expr1 then expr2 else expr3 end))
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy
>Sent: Monday, 2 July, 2018 10:21
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Window Function Crash -- overriding builtin
>aggregate
>
>On 07/02/2018 01:40 PM, Keith Medcalf wrote:
>> I have overridden the builtin AVG function with a function of my
>own that computes the average by using the "running average" method
>rather than the simple sum/count method. This function is registered
>as an old fashioned aggregate function.
>>
>> After the window extension is put in place, it appears that
>somewhere the wires are getting crossed as it appears to call my
>aggregate step function is getting called, however, when it tries to
>call the xValue function, SQLite3 crashes. (the call is apparently
>to "my" override xValue function, which is, of course, null -- not to
>the original builtin avg xValue pointer which would not work either
>but which would not result in a crash).
>
>Thanks for reporting this. The query causing the crash is a
>window-function query (i.e. "avg(...) OVER ...", correct?
>
>Assuming so, should now be fixed here:
>
> https://www.sqlite.org/src/info/4f3c8a82fd1c5b14
>
>Dan.
>
>
>
>>
>> If I register "all" the methods (even though they do not work
>correctly with a running calculation) the internal function is fully
>overridden.
>>
>> Even if I register the override function using the new
>sqlite_create_window_function and explicitly set the xValue and
>xInverse to 0 (null), the program still crashes when attempting to
>run the non-existent xValue function ...
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
