Window Function Crash -- overriding builtin aggregate

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Window Function Crash -- overriding builtin aggregate

Keith Medcalf

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).

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window Function Crash -- overriding builtin aggregate

Simon Slavin-3
On 2 Jul 2018, at 7:40am, Keith Medcalf <[hidden email]> wrote:

> 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 ...

As a test, and not a final resolution of your problem, what happens if you register your function with a new name, rather than using 'AVG' ?  Does it work or do you still get mystery calls to xValue ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window Function Crash -- overriding builtin aggregate

Keith Medcalf

If I rename the function then both the builtin avg and my new function work correctly.

---
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-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Monday, 2 July, 2018 04:27
>To: SQLite mailing list
>Subject: Re: [sqlite] Window Function Crash -- overriding builtin
>aggregate
>
>On 2 Jul 2018, at 7:40am, Keith Medcalf <[hidden email]> wrote:
>
>> 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 ...
>
>As a test, and not a final resolution of your problem, what happens
>if you register your function with a new name, rather than using
>'AVG' ?  Does it work or do you still get mystery calls to xValue ?
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window Function Crash -- overriding builtin aggregate

Simon Slavin-3
On 2 Jul 2018, at 11:48am, Keith Medcalf <[hidden email]> wrote:

> If I rename the function then both the builtin avg and my new function work correctly.

Thanks.  Worth testing just in case something weird happened.  But it didn't.  I leave it up to the experts.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window Function Crash -- overriding builtin aggregate

Dan Kennedy-4
In reply to this post by Keith Medcalf
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window Function Crash -- overriding builtin aggregate

Keith Medcalf

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-
>[hidden email]] On Behalf Of Dan Kennedy
>Sent: Monday, 2 July, 2018 10:21
>To: [hidden email]
>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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users